1 / 45

“Combining People and Technology for Enhanced Pharmacy Services”

SQL Security Best Practices & Shrinking Your Attack Surface. “Combining People and Technology for Enhanced Pharmacy Services”. The Obligatory “About Me“ Page. Matthew Brimer 8 years experience with SQL Server

oliver-gill
Download Presentation

“Combining People and Technology for Enhanced Pharmacy Services”

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Security Best Practices & Shrinking Your Attack Surface “Combining People and Technology for Enhanced Pharmacy Services”

  2. The Obligatory “About Me“ Page Matthew Brimer • 8 years experience with SQL Server • 3 were spent as Senior DBA and Database Security “Expert” for The Defense Information Systems Agency, a Department of Defense Agency • Database Manager and Security Manager for the leader in independent retail pharmacy services • Independent Contractor for SolidQ • Hold several Security and Microsoft Certifications • Vice President of OKCSQL User Group • Chairman for SQL Saturday Oklahoma City TWITTER - @SQLCENTURION, @OKCSQL, @SQLSATOKC WEBSITE - SQLCENTURION.COM “Combining People and Technology for Enhanced Pharmacy Services”

  3. Does Anybody Actually Read These Things? “Combining People and Technology for Enhanced Pharmacy Services”

  4. INFORMATION SECURITY • First the boring stuff

  5. INFORMATION SECURITY • What does it mean? • Title 44 of the United States Code defines Information Security as protecting information and information systems from unauthorized access, use, disclosure, disruption, modification, perusal, inspection, recording or destruction.

  6. INFORMATION SECURITY • What are the 3 information assurance attributes? • Confidentiality • Integrity • Availability

  7. PRINCIPAL OF LEAST PRIVILEGE • Pretty much the first line of defense for all facets of information security • Can be nearly impossible to enforce, depending on granularity • If it doesn’t exist, it can’t be exploited

  8. IA ATTRIBUTES (1) - CONFIDENTIALITY • What does confidentiality mean? • Privacy or the ability to control or restrict access so that only authorized individuals can view sensitive information. One of the underlying principles of confidentiality is "Need-to-know" or "Least Privilege". In effect, access to vital information should be limited only to those individuals who have a specific need to see or use that information.

  9. IA ATTRIBUTES (1) - CONFIDENTIALITY • What does confidentiality mean to us? • Ensuring only those who are authorized can see what they are supposed to and nothing more. • What are the most common methods? • Encryption • Authentication • Access Control

  10. IA ATTRIBUTES (1) - CONFIDENTIALITY Types Of Encryption • Symmetric Key – Shared key • Asymmetric Key – Public/Private-key infrastructure (PKI) • Certificate – Issued by a certificate authority (can be SQL server generated), it can be either Symmetric or Asymmetric depending on issuer, but it does have an expiration date!

  11. IA ATTRIBUTES (1) - CONFIDENTIALITY • What are the various types of encryption offered by SQL? Column Level • Encryptbypassphrase • Encryptbykey • Encryptbyasymkey • Encryptbycert

  12. IA ATTRIBUTES (1) - CONFIDENTIALITY • What are the various types of encryption offered by SQL? Database Level • Transparent Database Encryption (TDE) • Requires a key • Encrypts data files on disk • Encrypts backups (you will lose a significant amount of your backup compression) • Not encrypted in memory!

  13. IA ATTRIBUTES (1) - CONFIDENTIALITY • What are the various types of encryption offered by SQL? Instance Level • Force Encryption – This encrypts ALL traffic to and from SQL server

  14. IA ATTRIBUTES (1) - CONFIDENTIALITY • What are the various types of encryption offered by SQL? Instance Level • Force Encryption – This encrypts ALL traffic to and from SQL server. • The traffic is not sniff-able. This is great for safeguarding sensitive information over the wire but makes it much harder to detect SQL injection attacks.

  15. IA ATTRIBUTES (1) - CONFIDENTIALITY Authentication • Best practice to use windows authentication only • Rename and disable SA (as well as Windows /domain administrator accounts)

  16. IA ATTRIBUTES (1) - CONFIDENTIALITY Access Control • Using GPO or PBM to restrict access times to logins that should never be doing anything after hours. • Properly testing permissions. • Proper permissions to data file and backup directories. • Separating user accounts.

  17. IA ATTRIBUTES (2) - INTEGRITY Data Integrity • Information is accurate and reliable and has not been subtly changed or tampered with by an unauthorized party. • Authenticity: The ability to verify content has not changed in an unauthorized manner. • Non-repudiation & Accountability: The origin of any action on the system can be verified and associated with a user.

  18. IA ATTRIBUTES (2) - INTEGRITY Most Common Forms of Data Integrity • Hashing/Checksums • Source Control • Locking • Isolation Levels • Data Modeling/Data Types • Constraints • Following auditing requirements set forth by your industry standard (HIPAA/HITECH, PCI, STIG, Sarbanes-Oxley, etc.) • BACKUPS!!!

  19. IA ATTRIBUTES (2) - INTEGRITY Hashing/Checksums • Page_Verify - ALWAYS set to Checksum (SQL 2005 +) • When CHECKSUM is enabled for the PAGE_VERIFY database option, the SQL Server Database Engine calculates a checksum over the contents of the whole page, and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value that is stored in the page header. This helps provide a high level of data-file integrity.

  20. IA ATTRIBUTES (2) - INTEGRITY Hashing/Checksums • Hashbytes

  21. IA ATTRIBUTES (2) - INTEGRITY Data Modeling • Restricting the type and length of data that can fit into a field • A number can be a character but a character can’t be an integer. • ANSI vs. Unicode – Varchar vs. Nvarchar • Let the battle begin!

  22. IA ATTRIBUTES (2) - INTEGRITY Keys/Constraints • The key, the whole key and nothing but the key, so help me Codd • Forces data integrity across relationships

  23. IA ATTRIBUTES (2) - INTEGRITY Backups They say a DBA needs one of two things……… Either a really great backup/recovery solution or a really great resume. Which do you have? (Also falls into Availability)

  24. IA ATTRIBUTES (2) - INTEGRITY Auditing • Many different types of auditing depending on your industry standard. • C2 Audit Mode….. No…. Just, No! (unless there is a business reason to do so and you have a metric ton of drive space). • Have to know the 5 W’s - not just the application user name, but the user as well. • Lots of 3rd party tools out there or you can build your own using sp_trace_create or logging to a table. • As of SQL 2008 you can use Extended Events but in 2012+ it is a lot friendlier to use.

  25. IA ATTRIBUTES (2) - INTEGRITY Auditing So You’ve Got Auditing, Now What? • If you are using a 3rd party tool, make sure you’ve set your sensitive columns and thoroughly examined everything that requires reporting. • Build your own reports using fn_trace_gettable to read the trace file or by building alerting off of your trace table. • Most standards require automatic notification and auditors will want to see your auditing policy, procedure and evidence of Implementation.

  26. IA ATTRIBUTES (2) - INTEGRITY Auditing What Happens If You Tell An Auditor I Don’t Know?

  27. IA ATTRIBUTES (3) - AVAILABILITY What Does SQL Offer To Meet Availability? • Failover Clustering/Geo Clustering • Always On • Database Mirroring • Log Shipping • Replication

  28. IA ATTRIBUTES (3) - AVAILABILITY Failover Clustering • Uses shared storage. • Protects the data in the event of a server/hardware failure. • Can only run in an active/passive scenario which can be expensive due to not being able to utilize both servers. • Only 1 set of permissions to maintain. • Susceptible to network and SAN failures.

  29. IA ATTRIBUTES (3) - AVAILABILITY Geo Clustering • Allows for seamless Site2Site failover. • One connection string to maintain/applications unaware of failover. • Can be extremely expensive to implement. • Requires either SAN level replication or a 3rd party utility to handle bit-level replication. • 3rd party utilities can be used with local storage. • Only 1 set of permissions to maintain.

  30. IA ATTRIBUTES (3) - AVAILABILITY Always On (SQL 2012) • Requires you to set up availability groups. • Can use either 1 or multiple connection strings. • Can leverage multiple servers at the same time/ offset reads and writes on different servers (2 live copies and 2 standby’s). • Servers can be anywhere in the world. • Offload backup overhead to another server at another site. • User DB’s only, so you will need to manage permissions for each instance.

  31. IA ATTRIBUTES (3) - AVAILABILITY Database Mirroring • User DB’s only with 1 Mirror Max • Synchronous/Asynchronous • Can have automatic failover with a witness • Depending on where the witness resides it can cause a false positive for a failover. • Requires you to apply any permissions changes to both servers. • Repairs torn pages and sends the changes back to the Principal!!!! (Integrity)

  32. IA ATTRIBUTES (3) - AVAILABILITY Log Shipping • Can Use Multiple Servers(Not From SSMS) • Only As Good As Your Last Log Backup • Can Use Automatic Failover • Can Be Used With Database Mirroring • Requires The Use Of File Shares

  33. IA ATTRIBUTES (3) - AVAILABILITY Replication Snapshot/Merge/Transactional • A “Picture” of the database at a certain point in time. • Depending on the type of replication chosen, after the initial snapshot is taken changes are applied to the subscribers (merge/transactional). • Read-only (snapshot). • Can be applied to multiple servers. • Great for reporting. • Requires file sharing.

  34. INFORMATION SECURITY – BEST PRACTICES So what are some other simple things that we can do to help meet CIA, as well as shrink our attack surface?

  35. INFORMATION SECURITY – BEST PRACTICES Change The Default Port That SQL Uses • By default SQL uses port 1433. • What is network enumeration/fingerprinting? (NMAP, Metasploit, Etc.) • Depends on network security theory, DMZ or enclave. Revoke Connect From Guest • Any user with database permission can see any other database!

  36. INFORMATION SECURITY – BEST PRACTICES Revoke Connect From Guest • Any user with database permission can see any other database! Disable XP_CMDSHELL Disable Unneeded Protocols • You don’t need all of the protocols enabled (VIA, TCP/IP, shared memory and named pipes)(highly recommend turning off named pipes!)

  37. INFORMATION SECURITY – BEST PRACTICES Enable Common Criteria Compliance • Enabled as of SQL 2005 SP2 and standard as of SQL 2008. • Residual information protection, which overwrites memory with a bit pattern before it is reallocated to a new resource. • The ability to view login statistics. (sys.dm_exec_sessions) • A column-level GRANT does not override table-level DENY.

  38. INFORMATION SECURITY – BEST PRACTICES Disable SQL Browser • SQL Browser tells on you and any instances you are running. • Not possible with named instances/clusters.

  39. INFORMATION SECURITY – BEST PRACTICES Hide Instance • Will not respond to enumeration requests but will still respond to telnet requests. • Will not show up in “Browse For More”.

  40. INFORMATION SECURITY – BEST PRACTICES Minimal Service Install • I’ll use it eventually, right? • Principal of Least Privilege • 1st step in minimizing your attack surface • No BIDS on production • Separate Dev from Prod

  41. INFORMATION SECURITY – BEST PRACTICES Baselines • Baselines? We don’t need no stinking Baselines! • Baseline Analyzer • Know who your SysAdmin’s are • SQL Permissions/Users • Databases

  42. INFORMATION SECURITY – BEST PRACTICES No DMZ, IIS Or DC’S • If at all possible, do not install SQL on the same box as IIS. • Don’t install SQL on a domain controller(SQL will not let you install on a domain controller but an SQL Server can become a domain controller). • Depending on your security theory and setup, don’t install SQL on a DMZ. • Unless your webserver is also on the DMZ.

  43. INFORMATION SECURITY – BEST PRACTICES DEV/TEST DATA • Protect it just like you would production data and then some! • Attackers would compromise a test environment before a prod environment, why? • Most attackers don’t want you to know they are there. • Mask sensitive production data. • Your developers don’t need credit card info, social security numbers, etc. • I use MSSQL.DataMask (not a paid endorsement). http://www.wintestgear.com/products/MSSQLDataMask/MSSQLDataMask.html

  44. INFORMATION SECURITY – BEST PRACTICES What Did We Learn? • Basic security comes down to a few simple things. • Don’t be predictable. • Stay away from defaults. • Don’t offer up your information to the world. • Protect dev/test data just as you would prod. • Keep an eye on users and permissions. • BE VIGILANT!

  45. INFORMATION SECURITY – BEST PRACTICES

More Related