How to break into Sql Server in 15 min – he can do it in 3 min
Windows authentication – rename/disable sa
Use GPO or PBM to restrict access times to logins that should not be doing things after hours
Testing permissions – should do but not everyone does
Permissions to backup and data files – if someone can access your backup/data they can create their own instance with the backup or data files
Separating user accounts
Data integrity – data is accurate and reliable; has not been changed or tampered with by an unauthorized person
Common forms of data integrity-
- source control locking
- isolation levels
- data modeling/types constraints
C2 – just say NO! Only if there is a business reason!
Build your own auditing with sp_trace_create
fn_trace_gettable – auditing reports
Geo clustering – seamless Site2Site failover; one connection string; apps un-aware of failover; $$$$ to implement; SAN level replication or 3rd party too to handle bit level replication.
Always On (SQL Server 2012) – requires availability groups; one/multiple connection strings; can leverage multiple servers at the same time to offset reads/writes on different servers; offload backup overhead to another server on another site; user databases only so permissions managed per instance.
Database Mirroring – user databases with 1 mirror max; synchronous/asynchronous; automatic failover for witness – depending on where can report false positve of failover; permissions on both servers; repairs torn pages and send changes back to principal.
Log Shipping – only as good as your last log backup; requires file shares – permissions?
Replication – “picture” of the database from a point in time; can be applied to multiple servers; snapshot/merge/transactional; no mention of intializing through backup.
Secuity Best Practices
- change default port – network enumeration/fingerprinting?
- revoke connect from guest – any database permission can see any other database
- Disable xp_cmdshell
- Disable un-needed protocols (VIA,TCP/IP,shared memory, named pipes)
- Enable Common Criteria Compliance (standard in 2008)
- Disable SQL Browser – not possible with named instance or cluster
- Hide instance – will not respond to eneumeration requests but will respond to telnet requests
- Minimal Service Install – no BIDS in production; separate dev from prod
- Baselines – Baseline Analyzer; know your sysadmins; SQL permissions/users; databases
- No DMZ, IIS or Domain Controllers
- Dev/Test Data – mask sensitive data; MSSQL.DataMask