SQL Saturday #223 – SQL Security Best Practices and Shrinking Your Attack Surface with Matthew Brimer

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-

  • hashing/checksums
  • 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


One Response to SQL Saturday #223 – SQL Security Best Practices and Shrinking Your Attack Surface with Matthew Brimer

Leave a Reply

Your email address will not be published. Required fields are marked *