SQL Server Security Update and Best Practices

1. SQL Server Security Update and Best Practices ssqlnews@microsoft.com Ken Burge Sr. Technical Solutions Advisor Microsoft Corporation

2. Incidents Reported • CERT/CC incident statistics 1988 through Q3 2003 • Incident: single security issue grouping together all impacts of that that issue • Issue: disruption, DOS, loss of data, misuse, damage, loss of confidentiality Source: http://www.cert.org/stats/cert_stats.html

3. Evolving DB Threat Environment • A decade ago, databases were: • Physically secure • Housed in central data centers – not distributed • External access mediated through customer service reps, purchasing managers, etc. • Security issues rarely reported • Now increasingly DB’s externally accessible: • Suppliers directly connected • Customers directly connected • Customers & partners directly sharing data • Data is most valuable resource in application stack • Value increases with greater integration & aggregation • Opportunities for data theft, modification, or destruction

4. CSI/FBI Denial of Service Attack can be caused by certain worms and viruses in addition to Internet based attacks The Cost of Weak Security

5. DB Attack Toolkit: Well Armed • Brute force & dictionary-based password crackers • Network sniffers and Port scanners • Object code de-compilers and Quality debuggers • Symbols typically available for problem determination • Application source code not needed for deep attacks • Leveraging cracked systems: • Credentials: leverage & escalate by steps • Compute power: host distributed denial of service • DB Security tools & consulting: • NGSSoftware (http://www.nextgenss.com/) • Internet Security Services (http://www.iss.net/) • Application Security Inc. (http://www.appsecinc.com) • And many others… • Community shared resources: • Exploit, risk, & data sharing in the community

6. Slammer Overview • Slammer re-using existing ideas: • "The Slammer code is a straight cut-and-paste job" – D. Litchfield • Most attacks exploit known vulnerabilities • Recent SQL Server Service Levels unaffected • Single UDP packet delivery very effective (376 bytes) • Spread doubles every 8.5 seconds • 90% of vulnerable computers in 10 min • Max rate hit 55 million scans/second • 74,855 systems affected in one minute

7. Days between patch and exploit 331 180 151 25 SQL Slammer Nimda Blaster Welchia/ Nachi Security has become our #1 PriorityThere is no silver bulletChange requires innovation Reality CheckResponding to the Crisis • Patches proliferating • Time to exploit decreasing • Exploits are more sophisticated • Current approach is not sufficient

8. Microsoft’s Responsibility • We understand your frustration • We sympathize with the inconvenience you’ve experienced due to deploying patches and dealing with viruses • We’re accountable to helping you secure your environment ssqlnews@microsoft.com

9. SQL Injection Attacks Explained • Method to pass rogue statements into SQL Server • Allows a hacker to access the rest of the network, probe the SQL Server, or create data • Not just a SQL Server problem • Runs under the authentication of whatever SQL Server login the application uses

10. SQL Injection Attacks Explained • A query that looks like this:select * from login where login_nm = “InputFromScreen” • Can be injected with a single quote and a comment to look like this:select * from login where login_nm = ‘ ; DELETE FROM login - - ‘ • This is not a bug in SQL Server but in the application • Firewall is bypassed with these types of attacks

11. SQL Injection Attacks Explained • Based on the level of permissions your application has, a hacker could: • Drop your database • Delete your data • Insert a login for himself • Create a Windows domain account with admin rights • Gain access to your network • Backup the SAM for hash cracking

12. SQL Injection Attacks Explainedhow to protect yourself • Validate all screen input • Use the ADO command object with strict parameter validation • Permissions • Make sure SQL Server runs with the lowest possible permissions. It does not need admin privileges on the server. • Make sure the login that your application uses doesn’t have direct access to your data. • Use stored procs to access all data

13. Getting SecureMicrosoft SQL Server security best practices

14. Best PracticesService Packs and Hot Fixes • Install SQL Server SP3 as soon as you can • Install hot fixes as soon as you test for application regressions • Sign up for the Microsoft security bulletin at: http://www.microsoft.com/technet/security

15. Best PracticesThe big three • Use Windows authentication if at all possible: • Can be difficult if you run in a multi-domain or an environment that has a strong firewall. • Start with the lowest permissions possible for the user then work your way up • Avoid easily guessed login names like ksmith. Instead use something more obscure and less obvious.

16. Best PracticesSA Account • SQL Snake virus preyed on SQL Servers with SA and no password • Make the password a hard PW to crack, even if you use Windows Authentication • Should be virtually impossible to rememeber • Never ever use the SA Account • It is a known account which makes it an obvious target • If a developer knows the SA password, he will use it because it is convenient. It encourages lazy security measures.

17. Best PracticesFirewall and Port Assignments • Block SQL Server TCP/IP port 1433 and UDP port 1434 from vulnerable areas (such as Internet) • Set each SQL Server instance to use a unique TCP/IP port that is not 1433 • Note: you cannot change UDP port 1434 • If you are using MSDE or multiple SQL Server instances the nodes could choose a random available port

18. Best PracticesDefault Logins • Remove BUILTIN\Administrators account • If needed, you should only explicitly give access to Windows administrators • Don’t start SQL Server with the localsystem account • Start SQL Server and Agent with an account with minimal permissions • SP3 fixes a problem where SQL Server Agent had to start with Windows domain account • Always change login accounts for SQL Server through the Enterprise Manager interface. • Much easier and sets the appropriate registry settings for you

19. Best PracticesCommunicating to SQL Server • A strong firewall policy is a must • Do not allow employees other than DBAs to have direct access to the production machines. This includes developers. • Attempt to use SSL for communication with SQL Server • Will see a slight performance hit • Multiprotocol or IPSec are alternatives but not as string as TCP/IP and SSL

20. Best PracticesPublic Role and Guest Account • Any login with rights to your SQL Server account is automatically given all the privileges that the Guest login has • Any user with rights to a given database has all the rights given to the Public role has • Never let the guest account have access to your database • You cannot remove guest account from Master or TempDB • Northwind and Pubs by default give the Guest account sysadmin privileges thereby creating a security hole

21. Best PracticesAuditing • Always turn on Failed Login auditing • Create alerts to email you when this occurs • Increase the number of error logs you keep to prevent the hacker from cycling the error log to cover his tracks • Audit failed access to objects (error 229) • UPDATE sysmessages SET dlevel = (dlevel | 0x80) WHERE error = 229 • C2 level auditing is handy, but will cause a performance hit

22. Best PracticesC2 level auditing • Audits access to every object and use of permissions on the server • Turn on by using sp_configure proc • Sp_configure ‘c2 audit mode’,1 • Causes a noticeable performance hit • If the drive that holds the logs fills, SQL Server will stop

23. Tighten AccessDirectories and registry keys • Always install SQL Server on a NTFS partition • Watch who has access to the SQL Server binaries and data files • If you have the MDF and LDF files, the you have all the sensitive data you need • You can encrypt the physical files using 3rd party or Windows encryption • Watch who has access to the followign registry keys: • HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\MSSQLSERVER • HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\MICROSOFT SQL SERVER\INSTANCE

24. Tighten AccessProtection of Extended Procs • Revoke access to extended sprocs that read and write to the registry: • Xp_regwrite • xp_regread • xp_regremovemultistring • xp_addmultistring • xp_regdeletevalue • xp_regenumvalues

25. Tighten AccessOA Sprocs • Revoke access to stored procs that can create objects • Sp_OACreate • Sp_OADestroy • Sp_OAStop • Sp_OAGetProperty • Sp_OASetProperty • Sp_OAMethod • Sp_OAGetErrorInfo

26. Tighten AccessOther extended sprocs • Revoke access to stored procs tha can cause other damage: • Xp_commandShell • sp_runWebTask • sp_readWebTask • sp_MSSetServerProperties • sp_MScopyScriptFiles • sp_MSsetAlertInfo • Manually delete the underlying .dll file for each extended sproc you remove

27. Tighten AccessDTS Lockdown • By default anyone who has a login to SQL Server can create a DTS package • Protect your packages with a user and owner password • Revoke public access to: • RTblDBMProps table • Too many sprocs to list (See books online) • Create role for users who need to use DTS • As always, test and retest this before applying in production. Have a good rollback script ready.

28. Tighten AccessJob System Lockdown • Too man sprocs here to list, but here are a few: • Sp_ add_job • Sp_delete_job • Sp_start_job • Sp_purge_jobhistory • You should create a SQL Server user defined role for all who need to be able to create and start jobs.

29. Tighten Access • Revoke access to the guest account • Remove or revoke access to certain system stored procedures • Create roles for users who can create DTS packages and jobs • Caution: make sure you test all of these types of changes as you may affect an application or Enterprise Manager!

30. Staying SecureMicrosoft SQL Server management update

31. Microsoft’s Responsibility You’ve Told Us Our Action Items “The quality of the SQL Server patching process is low and inconsistent” Improve the Patching Experience “I need to know the right way to run an enterprise on SQL Server” Provide Guidance and Training “I can’t keep up…new SQL patches are released too often” Mitigate Vulnerabilities Without Patches “There are still too many vulnerabilities in SQL Server” Continue Improving Quality

32. Improve the Patching ExperienceNew Patch Policies • Extending security support to June 2004 • Windows 2000 SP2 • Windows NT4 Workstation SP6a • Security patches on a monthly predictable release cycle • Allows for planning a predictable monthly test and deployment cycle • Packaged as individual patches that can be deployed together NOTE: Exceptions will be made if customers are at immediate risk from viruses, worms, attacks or other malicious activities

33. By late 2004: Consolidation to 2 patch installers for W2k and later, SQL 2000, Office & Exchange 2003; all patches will behave the same way (update.exe, MSI 3.0) Reduce patch complexity Now: Increased internal testing; customer testing of patches before release By mid-2004: Rollback capability for W2k generation products and later (MSI 3.0 patches) May 2004: Microsoft Update (MU) hosts patches for W2k server, and over time SQL 2000, Office & Exchange 2003 By mid-2004: SUS 2.0 receives content from MU & adds capabilities for targeting, basic reporting and rollback Reduce risk of patch deployment By late 2004: Substantially smaller patches for W2k generation and later OS & applications (Delta patching technology, next generation patching installers) Reduce patch size Now:Continued focus on reducing reboots By late 2004: 30% of critical updates on Windows Server 2003 SP1 installed w/o rebooting (“hot patching”) Reduce downtime Improved tools consistency By mid-2004: Consistent results from MBSA, SUS, SMS, Windows Update (will all use SUS 2.0 engine for detection) Improved tools capabilities Improve the Patching ExperiencePatch Enhancements Your Need Our Response

34. “The Software Update Services Feature Pack has had a major impact on how we manage security patches at CNF. We went from not managing security patches to managing them–and very effectively.” – Roger Wilding, March 17, 2003 Senior Technical Engineer, CNF Patch Management Roadmap • Unified infrastructure “Microsoft Update” • 2 Standard Installers • Common scanning for all tools • Common standards for install behavior • MBSA 1.2 • SUS 2.0 • SMS 2003 • Microsoft Baseline Security Analyzer • Windows Update • Software UpdateService • SMS + Feature Pack • Patch Management Guides

35. Providing Guidance and TrainingIT Professionals • Global Education Program • TechNet Security Seminars • Monthly Security Webcasts • www.microsoft.com/events • New Prescriptive Guidance • Patterns and practices • How-to configure for security • How Microsoft Secures Microsoft • Online Community • Security Zone for IT Professionals • Authoritative Enterprise Security Guidance • http://www.microsoft.com/technet/security/bestprac.asp

36. 11 6 Service Pack 3 2 1 Successfully withstood >80K attacks Microsoft solution was never successfully hacked Solution included Windows 2000 Server, IIS 5.0, ASP.NET, SQL Server 2000 Service Pack 3 Bulletins sinceTwC release Bulletins in 10 month period prior to TwC release Bulletins in 16 month period prior to TwC release Bulletins sinceTwC release Shipped Jan. 2003, 10 months ago (as of Nov. 2003) Shipped July 2002, 16 months ago (as of Nov. 2003) …90 days …180 days TwC release? http://www.eweek.com/article2/0,3959,743411,00.asp 8 21 No 3 6 Yes Continue Improving Quality For some widely-deployed, existing products: Mandatory for all new products: Critical or important vulnerabilities in the first…

37. Continue Improving QualityMaking Progress 23 Products In the TwC Release Process Office 2003 Rights Mgmt Client & Server 1.0 Services For Unix 3.0 SQL Server 2000 SP3 Visual Studio .NET 2002 Visual Studio .NET 2003 Virtual PC Virtual Server Windows CE (Magneto) Windows Server 2003 Windows Server 2003 ADAM .NET Framework (for 2002 & 2003) ASP.NET (for 2002 & 2003) Biztalk Server 2002 SP1 Commerce Server 2000 SP4 Commerce Server 2002 SP1 Content Management Server 2002 Exchange Server 2003 Host Integration Server 2002 Identity Integration Server 2003 Live Communications Server 2003 MapPoint.NET

38. Resources SQL Server Security Chip Andrews, David Litchfield, Bill Grindley McGraw-Hill Osborne Media; ISBN: 0072225157 Whitepaper: SQL Server 2000 SP3 Security Features and Best Practices http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/security/sp3sec/Default.asp

39. URLs • General • http://www.microsoft.com/security • http://www.sqlserversecurity.com(See the lockdown.sql script at this site) • Technical Resources for IT Professionals • http://www.microsoft.com/technet/security • Best Practices for Defense in Depth • http://www.microsoft.com/technet/security/bestprac.asp • How Microsoft Secures Microsoft • http://www.microsoft.com/technet/itsolutions/msit/ security/mssecbp.asp • MSDN Security Development Tools • http://msdn.microsoft.com/security/downloads/tools/ default.aspx

40. Call To Action • Read SQL Server SP3 whitepaper • Install SQL SP3 as soon as possible • Run MBSA on your servers • Sign up for the quarterly SQL Server Newsletter by emailing ssqlnews@microsoft.com You can get this presentation by emailing ssqlnews@microsoft.com.