SQL Server Security Best Practices Round Table Nancy

SQL Server Security Best Practices Round Table Nancy Hidy Wilson

Agenda � What is CIS? � CIS SQL 2008 R 2 & 2012 Benchmarks � What to Remove What to Add � Microsoft Best Practices & Security Compliance Manager (SCM)

What is CIS? � � � www. cisecurity. org The Center for Internet Security (CIS) is a nonprofit organization focused on enhancing the cyber security readiness and response of public and private sector entities, with a commitment to excellence through collaboration. Through its four divisions-Security Benchmarks, Multi-State ISAC, Trusted Purchasing Alliance, and the Integrated Intelligence Center--CIS serves as a central resource in the development and delivery of high-quality, timely products and services to assist our partners in government, academia, the private sector and the general public in improving their cyber security posture. The CIS Security Benchmarks division provides cost-effective, consensus-based and internationally recognized solutions that help organizations improve their cyber security and compliance posture.

SQL Server 2012 Benchmark � Starting point is the SQL Server 2008 R 2 Benchmark V 1. 0. 0. � Any settings no longer applicable will be removed (e. g. sp_configure ‘remote access’)

SQL Server 2008 R 2 Benchmark � 7 ◦ ◦ ◦ ◦ Sections Updates and Patches Surface Area Reduction Extended Stored Procedures Authentication and Authorization Password Policies Auditing and Logging Application Development

Surface Area Reduction 2. 1 Set the 'Ad Hoc Distributed Queries' Server Configuration Option to 0 2. 2 Set the 'CLR Enabled' Server Configuration Option to 0 2. 3 Set the 'Cross DB Ownership Chaining' Server Configuration Option to 0 2. 4 Set the 'Database Mail XPs' Server Configuration Option to 0 2. 5 Set the 'Ole Automation Procedures' Server Configuration Option to 0 2. 6 Set the 'Remote Access' Server Configuration Option to 0 2. 7 Set the 'Remote Admin Connections' Server Configuration Option to 0 2. 8 Set the 'Scan For Startup Procs' Server Configuration Option to 0 2. 9 Set the 'SQL Mail XPs' Server Configuration Option to 0 2. 1 Set the 'Trustworthy' Database Property to Off 2. 11 Disable Unnecessary SQL Server Protocols 2. 12 Configure SQL Server to use non-standard ports 2. 13 Set the 'Hide Instance' option to 'Yes' for Production SQL Server instances 2. 14 Disable the 'sa' Login Account 2. 15 Rename the 'sa' Login Account

Extended Stored Procedures 3. 1 Revoke Execute on 'xp_availablemedia' to PUBLIC 3. 2 Set the 'xp_cmdshell' option to disabled 3. 3 Revoke Execute on 'xp_dirtree' to PUBLIC 3. 4 Revoke Execute on 'xp_enumgroups' to PUBLIC 3. 5 Revoke Execute on 'xp_fixeddrives' to PUBLIC 3. 6 Revoke Execute on 'xp_servicecontrol' to PUBLIC 3. 7 Revoke Execute on 'xp_subdirs' to PUBLIC 3. 8 Revoke Execute on 'xp_regaddmultistring' to PUBLIC 3. 9 Revoke Execute on 'xp_regdeletekey' to PUBLIC 3. 1 Revoke Execute on 'xp_regdeletevalue' to PUBLIC 3. 11 Revoke Execute on 'xp_regenumvalues' to PUBLIC 3. 12 Revoke Execute on 'xp_regremovemultistring' to PUBLIC 3. 13 Revoke Execute on 'xp_regwrite' to PUBLIC 3. 14 Revoke Execute on 'xp_regread' to PUBLIC

Authentication & Authorization 4. 1 Set The 'Server Authentication' Property To Windows Authentication mode 4. 2 Revoke CONNECT permissions on the 'guest user' within all SQL Server databases excluding the master, msdb and tempdb 4. 3 Drop Orphaned Users From SQL Server Databases

Password Policies 5. 1 Set the 'MUST_CHANGE' Option to ON for All SQL Authenticated Logins 5. 2 Set the 'CHECK_EXPIRATION' Option to ON for All SQL Authenticated Logins Within the Sysadmin Role 5. 3 Set the 'CHECK_POLICY' Option to ON for All SQL Authenticated Logins

Auditing and Logging 6. 1 Set the 'Maximum number of error log files' setting to greater than or equal to 12 6. 2 Set the 'Default Trace Enabled' Server Configuration Option to 1 6. 3 Set 'Login Auditing' to Both failed and successful logins

Application Development 7. 1 Sanitize Database and Application User Input 7. 2 Set the 'CLR Assembly Permission Set' to SAFE_ACCESS for All CLR Assemblies

What Should Be Removed? � Revokes on Microsoft’s “undocumented” Extended Stored Procedures � Rename “sa” � ? ? ?

What’s Missing and Needs to Be Added? � Do not install on a Domain Controller � Encryption ◦ No user keys in system databases ◦ Which algorithms & key lengths to use or avoid? � Service Accounts (e. g. not Windows Admins) � Should Performance Settings be included? (e. g. Lightweight Pooling, Lock configuration not dynamic) � Sample databases not installed � 2012 – Server Audit � ? ? ?

Microsoft Best Practices � Microsoft Baseline Configuration Analyzer aka Best Practices Analyzer ◦ For 2008/2008 R 2: �http: //www. microsoft. com/enus/download/details. aspx? id=15289 ◦ For 2012: �http: //www. microsoft. com/enus/download/details. aspx? id=29302 � Microsoft Security Compliance Manager (SCM) ◦ Join the Beta for SQL 2012 ◦ http: //technet. microsoft. com/enus/solutionaccelerators/cc 835245. aspx

Feedback � Send feedback on this presentation in general or specific CIS Benchmark feedback to Nancy Hidy Wilson at: ◦ Nancy. Hidy. Wilson@gmail. com � Join the CIS Benchmarks Consensus Team! ◦ http: //benchmarks. cisecurity. org/community/