SQL Server Security Update and Best Practices ssqlnewsmicrosoft
SQL Server Security Update and Best Practices ssqlnews@microsoft. com Ken Burge Sr. Technical Solutions Advisor Microsoft Corporation
Incidents Reported l l l CERT/CC incident statistics 1988 through Q 3 2003 Incident: single security issue grouping together all impacts of that issue Issue: disruption, DOS, loss of data, misuse, damage, loss of confidentiality Source: http: //www. cert. org/stats/cert_stats. html
Evolving DB Threat Environment l A decade ago, databases were: Ø Ø l Now increasingly DB’s externally accessible: Ø Ø Ø l Physically secure Housed in central data centers – not distributed External access mediated through customer service reps, purchasing managers, etc. Security issues rarely reported Suppliers 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
The Cost of Weak Security l CSI/FBI Denial of Service Attack can be caused by certain worms and viruses in addition to Internet based attacks
DB Attack Toolkit: Well Armed l Brute force & dictionary-based password crackers l Network sniffers and Port scanners l Object code de-compilers and Quality debuggers Ø Symbols typically available for problem determination l Application source code not needed for deep attacks l Leveraging cracked systems: Ø Ø l DB Security tools & consulting: Ø Ø l Credentials: leverage & escalate by steps Compute power: host distributed denial of service 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
Slammer Overview l Slammer re-using existing ideas: Ø Ø Ø l "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 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
Reality Check Responding to the Crisis l l Patches proliferating Time to exploit decreasing Exploits are more sophisticated Current approach is not sufficient Days between patch and exploit 331 180 151 25 Sla SQL da mm er Nim We Bla l ste c Na hia r ch / i Security has become our #1 Priority There is no silver bullet Change requires innovation
Microsoft’s Responsibility l l l 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
SQL Injection Attacks Explained l l 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
SQL Injection Attacks Explained l A query that looks like this: l Can be injected with a single quote and a comment to look like this: select * from login where login_nm = “Input. From. Screen” select * from login where login_nm = ‘ ; DELETE FROM login - ‘ l l This is not a bug in SQL Server but in the application Firewall is bypassed with these types of attacks
SQL Injection Attacks Explained l 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
SQL Injection Attacks Explained l Validate all screen input how to protect yourself l l 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
Getting Secure Microsoft SQL Server security best practices
Best Practices Service Packs and Hot Fixes l l l Install SQL Server SP 3 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/secu rity
Best Practices The big three l Use Windows authentication if at all possible: Ø l l Can be difficult if you run in a multidomain 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.
Best Practices SA Account l l 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 Ø l Should be virtually impossible to rememeber Never 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.
Best Practices Firewall and Port Assignments l l 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 Ø l 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
Best Practices Default Logins l Remove BUILTINAdministrators account Ø l l Don’t start SQL Server with the localsystem account Start SQL Server and Agent with an account with minimal permissions Ø l If needed, you should only explicitly give access to Windows administrators SP 3 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
Best Practices Communicating to SQL Server l A strong firewall policy is a must Ø l Attempt to use SSL for communication with SQL Server Ø l Do not allow employees other than DBAs to have direct access to the production machines. This includes developers. Will see a slight performance hit Multiprotocol or IPSec are alternatives but not as string as TCP/IP and SSL
Best Practices Public Role and Guest l Any login with rights to your SQL Server Account account is automatically given all the l l 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 Temp. DB Northwind and Pubs by default give the Guest account sysadmin privileges thereby creating a security hole
Best Practices Auditing l Always turn on Failed Login auditing Ø l l 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) Ø l Create alerts to email you when this occurs UPDATE sysmessages SET dlevel = (dlevel | 0 x 80) WHERE error = 229 C 2 level auditing is handy, but will cause a performance hit
Best Practices C 2 level auditing l l Audits access to every object and use of permissions on the server Turn on by using sp_configure proc Ø l l Sp_configure ‘c 2 audit mode’, 1 Causes a noticeable performance hit If the drive that holds the logs fills, SQL Server will stop
Tighten Access Directories and registry keys l l Always install SQL Server on a NTFS partition Watch who has access to the SQL Server binaries and data files Ø Ø l If you have the MDF and LDF files, the you have all the sensitive data you need You can encrypt the physical files using 3 rd party or Windows encryption Watch who has access to the followign registry keys: Ø Ø HKEY_LOCAL_MACHINESOFTWAREMICROSOFTMSSQLSERVER HKEY_LOCAL_MACHINESOFTWAREMICROSOFT SQL SERVERINSTANCE
Tighten Access Protection of Extended Procs l Revoke access to extended sprocs that read and write to the registry: Ø Ø Ø Xp_regwrite xp_regread xp_regremovemultistring xp_addmultistring xp_regdeletevalue xp_regenumvalues
Tighten Access OA Sprocs l l l l Revoke access to stored procs that can create objects Sp_OACreate Sp_OADestroy Sp_OAStop Sp_OAGet. Property Sp_OASet. Property Sp_OAMethod Sp_OAGet. Error. Info
Tighten Access Other extended sprocs l Revoke access to stored procs tha can cause other damage: Ø Ø Ø l Xp_command. Shell sp_run. Web. Task sp_read. Web. Task sp_MSSet. Server. Properties sp_MScopy. Script. Files sp_MSset. Alert. Info Manually delete the underlying. dll file for each extended sproc you remove
Tighten Access DTS Lockdown l l l 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: Ø Ø l RTbl. DBMProps 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.
Tighten Access Job System Lockdown l Too man sprocs here to list, but here a few: Ø Ø l 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.
Tighten Access l l 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!
Staying Secure Microsoft SQL Server management update
Microsoft’s Responsibility You’ve Told Us “The quality of the SQL Server patching process is low and inconsistent” “I need to know the right way to run an enterprise on SQL Server” “I can’t keep up…new SQL patches are released too often” “There are still too many vulnerabilities in SQL Server” Our Action Items Improve the Patching Experience Provide Guidance and Training Mitigate Vulnerabilities Without Patches Continue Improving Quality
Improve the Patching Experience New Patch Policies l Extending security support to June 2004 Ø Ø l Windows 2000 SP 2 Windows NT 4 Workstation SP 6 a Security patches on a monthly predictable release cycle l l 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
Improve the Patching Experience Patch Enhancements Your Need Reduce patch complexity Reduce risk of patch deployment Reduce patch size Reduce downtime Our Response By late 2004: Consolidation to 2 patch installers for W 2 k and later, SQL 2000, Office & Exchange 2003; all patches will behave the same way (update. exe, MSI 3. 0) Now: Increased internal testing; customer testing of patches before release By mid-2004: Rollback capability for W 2 k generation products and later (MSI 3. 0 patches) By late 2004: Substantially smaller patches for W 2 k generation and later OS & applications (Delta patching technology, next generation patching installers) Now: Continued focus on reducing reboots By late 2004: 30% of critical updates on Windows Server 2003 SP 1 installed w/o rebooting (“hot patching”) 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 May 2004: Microsoft Update (MU) hosts patches for W 2 k 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
Patch Management Roadmap l Microsoft Baseline l Unified infrastructure l Windows Update l 2 Standard Installers l Software Update l Common scanning “The Software Update Services for Security Analyzer Service l SMS + Feature Pack l Patch Management Guides “Microsoft Update” Feature Pack has had a major all tools l impact on how we manage security patchesstandards at CNF. Common We went from not managing install behavior security patches to managing them–and very effectively. ” – Roger Wilding, l MBSA 1. 2 March 17, 2003 Senior Technical Engineer, CNF l SUS 2. 0 l SMS 2003 for
Providing Guidance and Training IT Professionals l Global Education Program Ø Ø Ø l New Prescriptive Guidance Ø Ø Ø l Tech. Net Security Seminars Monthly Security Webcasts www. microsoft. com/events 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
Continue Improving Quality For some widely-deployed, existing products: 11 6 Service Pack 3 2 Successfully withstood >80 K 1 attacks Bulletins in 10 Bulletins in 16 Bulletins since Microsoft solution was never month period prior Tw. C release to Tw. C release successfully hacked Shipped Jan. 2003, 10 months ago (as of Nov. 2003) Shipped July 2002, 16 months ago (as of Nov. 2003) Solution included Windows 2000 Server, IIS 5. 0, ASP. NET, SQL Server 2000 Mandatory for all new products: http: //www. eweek. com/article 2/0, Critical or important vulnerabilities in the first… 3959, 743411, 00. asp … 90 days … 180 days Tw. C release? 8 21 No 3 6 Yes
Continue Improving Quality Making Progress 23 Products In the Tw. C Release Process. NET Framework (for 2002 & 2003) ASP. NET (for 2002 & 2003) Biztalk Server 2002 SP 1 Commerce Server 2000 SP 4 Commerce Server 2002 SP 1 Content Management Server 2002 Exchange Server 2003 Host Integration Server 2002 Identity Integration Server 2003 Live Communications Server 2003 Map. Point. NET Office 2003 Rights Mgmt Client & Server 1. 0 Services For Unix 3. 0 SQL Server 2000 SP 3 Visual Studio. NET 2002 Visual Studio. NET 2003 Virtual PC Virtual Server Windows CE (Magneto) Windows Server 2003 ADAM
Resources SQL Server Security Chip Andrews, David Litchfield, Bill Grindley Mc. Graw-Hill Osborne Media; ISBN: 0072225157 Whitepaper: SQL Server 2000 SP 3 Security Features and Best Practices http: //www. microsoft. com/technet/treeview/default. asp? url=/technet/prodtechnol/sql/maintain/security/sp 3 sec/Default. asp
URLs l General Ø Ø l Technical Resources for IT Professionals Ø l http: //www. microsoft. com/technet/security/bestprac. asp How Microsoft Secures Microsoft Ø l http: //www. microsoft. com/technet/security Best Practices for Defense in Depth Ø l http: //www. microsoft. com/security http: //www. sqlserversecurity. com (See the lockdown. sql script at this site) http: //www. microsoft. com/technet/itsolutions/msit/ security/mssecbp. asp MSDN Security Development Tools Ø http: //msdn. microsoft. com/security/downloads/tools/
Call To Action 1. Read SQL Server SP 3 whitepaper 2. Install SQL SP 3 as soon as possible 3. Run MBSA on your servers 4. Sign up for the quarterly SQL Server Newsletter by emailing ssqlnews@microsoft. com You can get this presentation by emailing ssqlnews@microsoft. com.
- Slides: 40