1 / 38

Secure Data SQL Server Best Practices

Secure Data SQL Server Best Practices. Monica DeZulueta , Ph.D. Data Platform Technology Specialist Microsoft Corporation. Session Objectives And Agenda. Session Objectives Describe security best practices and how they help protect your valuable data

zivanka
Download Presentation

Secure Data SQL Server Best Practices

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. Secure DataSQL Server Best Practices Monica DeZulueta, Ph.D. Data Platform Technology Specialist Microsoft Corporation

  2. Session Objectives And Agenda • Session Objectives • Describe security best practices and how they help protect your valuable data • Focus on operational/administrative tasks • Address them in roughly “lifetime” sequence What I hope you takeaway from this session 3 things you can do today to better secure your installation  How to best leverage new SQL Server security capabilities

  3. Surface Area Reduction What • Minimize Enabled/Exposed features • “Off by Default” for new SQL2K5 and SQL2K8 installs • Features, services, connections • On Upgrade, remain in pre-upgrade state Recommendation • New installs – leave features off • Enable only what you will actually use • Keep connectivity to a minimum • Upgrades – turn off whatever you don’t need

  4. Surface Area Reduction Why • Reduced attack surface • Heterogeneous installation footprint How • Surface Area Configuration tool (SQLSAC) • Launch from Setup or Start Menu • SAC command line utility sac out server1.out -S server1 -U admin -I MSSQLSERVER sac in server1.out -S server2 • sp_configure EXEC sp_configure ‘SMO and DMO XPs’, 0

  5. Service Accounts What • Services can run under built-in accounts (Local System, Network Service, or Local Service) or user account • Each service can use a different account Recommendation • Most desirable: Local or Domain user account • Can change password without shutdown • Least desirable: Local System • Workable: Network Service/Local Service • Use different accounts for different services

  6. Service Accounts Why • Least Privilege • Isolation • Defense in depth How • Specified during Setup • Change using SQL Configuration Manager tool • Do not change service account from Windows

  7. Authentication Mode What • Windows Authentication (default) • Windows principals only • Mixed Authentication (optional) • Windows and SQL principals Recommendation • Use Windows Authentication whenever possible • Use Mixed Authentication to get • Legacy application support • Cross platform client/server • Improved administrator separation • Encrypt communications channel • Uses self-signed cert during login by default

  8. Authentication Mode Why • Single sign on • Simplified administration • No password management • Protect conversations and credentials in transit • Use “real” cert to prevents MITM attacks How • Selected during Setup • Updated via Management Studio

  9. Network Connectivity What • Protocols and endpoints enabled • Demands on strength of channel protection Recommendation • Enable minimal protocols (e.g. TCP/IP) • Change and block default ports (1433, 1434) • Grant user access through restrictive endpoints • Do not expose to internet

  10. Network Connectivity Why • Minimize potential client population • Block known attacks • Restrict access paths How • SQL Surface Area Configuration tool • SQL Configuration Manager tool • Endpoint DDL CREATE ENDPOINT myEndpoint AS ... GRANT CONNECT ON ENDPOINT::myEndpoint TO Fred

  11. Lockdown of System Procedures What • Removal of system XPs • REVOKE EXECUTE permission on SPs and XPs from PUBLIC Recommendation • Leave system XPs in place • Reconsider need to revoke EXECUTE permission

  12. Lockdown of System Procedures Why • Many are Off By Default (including xp_cmdshell) • Others made “safe” (e.g. xp_dirtree, xp_regread) • Procedures contain appropriate authorization check • Permission held, role membership • Removal of XPs results in unsupported configuration How • N/A

  13. Password Policy What • Complexity, Expiration, Lockout enforcement • Common across Windows and SQL • Win2K3 onwards (hard-coded rules for older versions) • SQL Logins, App Roles, pass phrases, etc. • Everywhere passwords are used Recommendation • Leave CHECK_POLICY on • Set CHECK_EXPIRATION on to avoid old passwords • Set MUST_CHANGE for new logins

  14. Password Policy Why • Deter brute-force and dictionary attacks • Prevent blank passwords • Blank/trivial SA password is game over! How CREATE LOGIN Barney WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE ALTER LOGIN Barney WITH CHECK_EXPIRATION = ON

  15. Administrator Privileges What • Principals with highly elevated privileges • “SA” built-in login • Members of SYSADMIN built-in server role • Holders of CONTROL permission at server level Recommendation • Use admin privileges only when needed • Minimize number of administrators • Provision admin principals explicitly • Have multiple distinct admins if more than one needed • Avoid dependency on builtin\administrators Windows group

  16. Administrator Privileges Why • Least privilege • Repudiation/accountability • Windows Vista “User Account Control” How EXEC sp_addsrvrolemember 'Corporate\BamBam', 'sysadmin‘ EXEC sp_dropsrvrolemember ‘Fred', 'sysadmin‘ GRANT CONTROL SERVER TO Barney

  17. Database Ownership & Trust What • Each database is owned by • DBO user (default = database creator) • DB_OWNER role members • Can confer trust on other databases Recommendation • Have distinct owners for databases • Not all owned by “SA” • Minimize owners for each database • Confer trust selectively • Leave CDOC setting off • Migrate usage to selective trust instead

  18. Database Ownership & Trust Why • Least privilege • Repudiation/accountability • Isolation How ALTER AUTHORIZATION ON DATABASE::myDB to Barney EXEC sp_addrolemember ‘db_owner', ‘Wilma’ ALTER DATABASE myDB SET TRUSTWORTHY ON

  19. Schemas What • Namespace in the container hierarchy • Server>database->schema->object • Can be owned by any user (SQL2K5) • Permissions grantable at schema level Recommendation • Group related objects together into same schema • Leverage ownership and permissions at schema level • Have distinct owners for schemas • Not all owned by “DBO” • Minimize owners for each schema

  20. Schemas Why • Isolation, aggregation • Flexibility • Separate administrative grouping from application access • Change owner without updating applications How CREATE SCHEMA mySchema AUTHORIZATION Betty CREATE TABLE mySchema.myTable (C1 int, C2 varchar(20)) GRANT SELECT ON SCHEMA::mySchema TO Dino

  21. Authorization What • Who can access what Recommendation • Encapsulate access within modules • Manage permissions via database roles • Leverage permission granularity • Many new permissions in SQL 2005 • Do not enable Guest access • Use Login-less users instead of Application Roles

  22. Authorization Why • Least Privilege • Administrative ease • Avoid password management How CREATE PROCEDURE mySchema.mySP WITH EXECUTE AS ‘Wilma’ AS ... CREATE ROLE myDBRole AUTHORIZATION db_securityadmin GRANT EXECUTE ON OBJECT::mySchema.mySP TO myDBRole EXEC sp_addrolemember ‘myDBRole’, ‘Betty’

  23. Catalog Security What • Metadata visible only for objects permission is held on • Some objects visible to public (e.g. filegroups) • VIEW DEFINITION permission grantable • Provides metadata visibility only – no access Recommendation • Grant VIEW DEFINITION selectively • Legacy applications • Delegating administration

  24. Catalog Security Why • Information disclosure • System fingerprinting/profiling How • Secure by default – no action required • Grant VIEW DEFINITION permission at object/schema/database/server level GRANT VIEW DEFINITION ON OBJECT::mySchema.myTable TO Dino

  25. Encryption What • Cryptographic protection of data against disclosure • Applicable at column and cell level • Algorithm choices depends on operating system Recommendation • Encryption is very scenario specific • Encrypt high value/sensitive data • Symmetric key for data, asymmetric key to protect symmetric key • Password protect keys and remove master key encryption for most secure configuration

  26. Encryption Why • Protection of data at rest (lost laptop, backups) • Advanced/selective access control • Need permission AND key to see data How CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Pa$$w0rD1’ CREATE SYMMETRIC KEY mySymKey AUTHORIZATION Dino WITH ALGORITHM=TRIPLE_DES ENCRYPTION BY PASSWORD=‘MemorableYetObscurePassPhrase'

  27. Auditing What • Record of security relevant activity • Profile system and track potential security violations Recommendation • Auditing is very scenario specific • Password policy in place -> audit failed logins (default) • Sensitive database content -> audit security events • Including successful logins

  28. Auditing Why • Profile system and track potential security violations • Forensic analysis of incidents How • SQL Profiler • Stored procedures EXEC sp_configure ‘c2 audit mode’, ‘1’ EXEC sp_trace_xxx ...

  29. Best Practice Analyzer What • Scan installation for best practices usage • Report on issues found Recommendation • Regularly run MBSA 2.0 • SQL 2000 only • Run SQL BPA against SQL 2005 • incorporates security checks

  30. Best Practice Analyzer Why • Maintain baseline of best practices usage • Detect and correct deviations quickly How • Microsoft Baseline Security Analyzer • SQL Best Practices Analyzer

  31. Patching What • Keeping software up to date with security fixes • SQL2000 SP4 onwards: • Patching via Microsoft Update • SQL2005 onwards: • Separate code line for security fixes Recommendation • Stay as current as possible! • Enable automatic updates (where appropriate)

  32. Patching Why • Old attacks never go away (e.g. port 1434 probing) • Proliferation of installations • New issues can occur at any time How • Enable automatic updates, or • Run Microsoft Update explicitly

  33. Demos

  34. SQL Server Certifications • FIPS 140-2 • Defines which algorithms can be used for encryption • Algorithms certified for Windows 2003 • In process for Vista/Windows Server 2008 • Common Criteria • International set of guidelines for security products • In process of evaluating strategy and targets for Common Criteria

  35. Summary • Best Practices • Surface Area Reduction  Enable only what you need • Service Accounts  Local/Domain user account • Authentication Mode  Windows authentication • Network Connectivity  Enable minimal endpoints • Lockdown System Procs Secure by default • Password Policy  Enable Expiration/Must Change • Admin Privileges Only where needed • DB Ownership & Trust Distinct owners, no CDOC • Schemas  Group related objects

  36. Summary • Best Practices • Authorization  Use roles, granular perms • Catalog Security Grant access sparingly • Encryption Use symmetric key • Auditing Audit security events • Best Practice Analyzer Run regularly • Patching  Stay current with Microsoft Update

More Related