Secure SQL Server configuration

1. Secure SQL Server configuration Pat Larkin Ward Solutions Pat.larkin@ward.ie

2. Session Prerequisites • Understanding of network security essentials • Hands-on experience with Windows® 2000 Server or Windows Server™ 2003 • Experience with Windows management tools • Hands-on experience with SQL Server 2000 and management tools Level 300

3. Agenda • Introduction • Protecting the application layer • Protecting SQL Server 

4. Defense in Depth • Using a layered approach: • Increases an attacker’s risk of detection • Reduces an attacker’s chance of success Policies, Procedures, & Awareness ACL, encryption Physical Security Data Application hardening, antivirus Application OS hardening, update management, authentication, HIDS Host Internal Network Network segments, IPSec, NIDS Firewalls, VPN quarantine Perimeter Guards, locks, tracking devices User education

5. Why Application Security Matters • Perimeter defenses provide limited protection • Many host-based defenses are not application specific • Most modern attacks occur at the application layer

6. Why Data Security Matters • Secure your data as the last line of defense • Configure file permissions • Configure data encryption • Protects the confidentiality of information when physical security is compromised

7. Application Server Best Practices Configure security on the base operating system Apply operating system and application service packs and patches Install or enable only those services that are required Assign only those permissions needed to perform required tasks Application accounts should be assigned minimal permissions Apply defense-in-depth principles to increase protection

8. Efficiency Continuity Storage Management Hardware Upgrades Performance Monitoring Performance Tuning SQL Server Policies Capacity Management UPS Recovery Testing Availability Monitoring Availability Management Disaster Recovery Support Antivirus Event Monitoring Change Management Group Policies Backup Security Policies Firewall Issues SQL Server Policies AD Group Membership Security Defense in Depth

9. Agenda • Introduction • Protecting the application layer • Protecting SQL Server

10. Basic Security Configuration • Apply service packs and patches • Use MBSA to detect missing SQL updates • Use SQL Critical Update Kit • Disable unused services • MSSQLSERVER (required) • SQLSERVERAGENT • MSSQLServerADHelper • Microsoft Search • Microsoft DTC

11. Too many members of the sysadmin fixed server role. Granting of right to create CmdExec jobs to roles other than sysadmin. Blank or trivial passwords. Weak authentication mode. Excessive rights granted to the Administrators group Incorrect ACLs on SQL Server data directories. Plain-text sa password in setup files Excessive rights granted to the guest account. SQL Server running on a system that is also a domain controller. Improper configuration of the Everyone group, providing access to certain registry keys. Improper configuration of SQL Server service accounts. Missing service packs and security updates What MSBA typically checks on SQL Server

12. Password Cracking Web App Vulnerabilities Over-privileged accounts Week input validation Network Eavesdropping SQL Injection Perimeter Firewall Internal Firewall SQL Server Web App Browser Unauthorized External Access Network Vulnerabilities Failure to block SQL ports Configuration Vulnerabilities Over-privileged service account Week permissions No certificate Common Database Server Threats and Countermeasures

13. SQL Server Security SQL Server Logins, Users, and Roles Database Objects Shares Auditing and Logging Patches and Updates Operating System Services Files and Directories Accounts Registry Network Protocols Ports Database Server Security Categories

14. Network Security • Restrict SQL to TCP/IP • Harden the TCP/IP stack • Restrict ports

15. Operating System Security • Isolate SQL from all other server roles • Configure the SQL Server service account with the lowest possible permissions • Delete or disable unused accounts • Secure authentication traffic

16. Logins, Users, and Roles • Use a strong system administrator (sa) password • Remove the SQL guest user account • Remove the BUILTIN\Administrators server login • Do not grant permissions for the public role

17. Access & Permission Models

18. Alternate Model

19. Files, Directories, and Shares • Use NTFS (EFS) & non system volumes • Verify permissions on SQL Server installation directories • Verify that Everyone group does not have permissions to SQL Server files • Secure setup log files • Secure or remove tools, utilities, and SDKs • Remove unnecessary shares • Restrict access to required shares • Secure registry keys with ACLs

20. SQL Security • Set authentication to Windows only • If you must use SQL Server authentication, ensure that authentication traffic is encrypted

21. Authentication Modes

22. SQL Auditing • Log all failed Windows login attempts • Log successful and failed actions across the file system • Enable SQL Server login auditing • Enable SQL Server general auditing

23. Securing Database Objects • Remove the sample databases • Secure stored procedures • Secure extended stored procedures • Restrict cmdExec access to the sysadmin role

24. Using Views and Stored Procedures • SQL queries may contain confidential information • Use stored procedures whenever possible • Use views instead of direct table access • Implement security best practices for Web-based applications

25. Restrict Remote Administration • Restrict number of Windows Administration accounts • Restrict the tools • Internet services manager • Terminal Services • SQL Server Enterprise Manager – restrict to a single windows user • Restrict the computers using IPSec

26. Securing Web Applications • Validate all data input • Secure authentication and authorization • Secure sensitive data • Use least-privileged process and service accounts • Close all unneeded DB connections • Configure auditing and logging • Use structured exception handling

27. Completely un-validated user input! Completely un-validated user input! Knocking Down The Side Door //Three mistakes in this statement alone: SqlConnection conn =new SqlConnection(); conn.ConnectionString = "data source=PYN-SQL;" + "initial catalog=pubs;" + "user id=sa;" + "password=password;" + "persist security info=True;“+ "packet size=4096"; conn.Open(); //Don't do this at home folks: SQL Query Composition string strQuery; strQuery = "select * from Users where UserName = '" + username.Text + "' and Password ='" + password.Text + "';"; Oh, that’s bad, not using DSNs Worse, privileged user… …with a lame password

28. But Wait It Gets Better int rowCount = ds.Tables["Users"].Rows.Count; if(rowCount > 0) // If we get back something... { //...we must be succesfully logged in Session["LoggedIn"] = true; // Store the username in a session variable Session["username"] = username.Text; ... <body> <asp:Labelid="Username"runat="server"> Label </asp:Label> ... private void Page_Load(object sender, System.EventArgs e) { ... Username.Text = Session["username"].ToString(); } This is the user name from the form Which we cross-site script onto the page!

29. Top Ten Things to Protect SQL Server Install the most recent service pack 1 Run MBSA 2 Configure Windows authentication 3 Isolate the server and back it up 4 Check the sa password 5 Limit privileges of SQL services 6 Block ports at your firewall 7 Use NTFS 8 Remove setup files and sample databases 9 10 Audit connections

30. Next Steps • Stay informed about security • Sign up for security bulletins: http://www.microsoft.com/security/security_bulletins/alerts2.asp • Get the latest Microsoft security guidance: http://www.microsoft.com/security/guidance/ • Get additional security training • Find online and in-person training seminars: http://www.microsoft.com/seminar/events/security.mspx • Find a local CTEC for hands-on training: http://www.microsoft.com/learning/

31. For More Information • Microsoft Security Site (all audiences) • http://www.microsoft.com/security • TechNet Security Site (IT professionals) • http://www.microsoft.com/technet/security • MSDN Security Site (developers) • http://msdn.microsoft.com/security

32. Questions and Answers