1 / 41

SQL Server Security Attack & Defense

SQL Server Security Attack & Defense. Chip Andrews (www.sqlsecurity.com) January 2003 AtlantaMDF Users Group. Presentation Outline. Introduction Attack Fingerprinting Account Acquisition Privilege Escalation SQL-Injection Defense Tools (Scanners/Patches) Hardening Scripts

yael
Download Presentation

SQL Server Security Attack & Defense

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. SQL Server Security Attack & Defense Chip Andrews (www.sqlsecurity.com) January 2003 AtlantaMDF Users Group www.sqlsecurity.com

  2. Presentation Outline • Introduction • Attack • Fingerprinting • Account Acquisition • Privilege Escalation • SQL-Injection • Defense • Tools (Scanners/Patches) • Hardening Scripts • Secure Deployment • Input Validation • Best-Practices www.sqlsecurity.com

  3. SQL Server Presence • Biztalk Server 2000 • Commerce Server 2000 • Application Center Server 2000 • Third-Party Apps (SQL/MSDE) • Tumbleweed Worldsecure • Valadeo Technologies, Inc. • O’Reilly & Associates, Inc. (WebBoard) • Telemate.net • Hierarchical File systems looming in the future (rumored link between Yukon and Longhorn development) www.sqlsecurity.com

  4. MSDE Proliferation Microsoft SQL Server Desktop Engine (formerly the Microsoft Data Engine) • Development methodologies (agile) often suggest each developer (or team) have a local database instance • http://martinfowler.com/articles/evodb.html • Visual Studio.NET installs MSDE (vsDotNet/NetSDK) • .NET Framework SDK (required for samples) • Office XP www.sqlsecurity.com

  5. Section 1 - Attack • Fingerprinting/Discovery • Acquiring Access • Privilege Escalation • Backdoors • Covering tracks www.sqlsecurity.com

  6. Discovery - Target Acquisition • Server fingerprinting via IP stack identification • Nmap/nmapnt • www.insecure.org/nmap • www.eeye.com • IIS Web Servers - a good bet SQL Server is driving it • telnet targetname 80 • HEAD / HTTP/1.0 • www.netcraft.com • Dig for hints about target database usage • Newsgroups • Groups.google.com • SQL discussion boards (www.swynk.com) • Job Postings • Corporate website (click ‘careers’) • www.monster.com etc. www.sqlsecurity.com

  7. Discovery - Port Scanning • Use nslookup / whois to determine IP addresses whois “target.”@whois.arin.net • TCP port 1433 (and 2433) • SQL Server defaults to listen on these ports since ip-sockets net-lib is installed by default (along with named pipes) • Hosting providers are particularly juicy targets • UDP port 1434 (SQL Server Resolution Service) www.sqlsecurity.com

  8. SQL Server Discovery – UDP 1434 • Multiple instancing capabilities of SQL Server 2000 make enumeration a functional requirement • A specially formed UDP packet directed at port 1434 will cause the SQL 2K listener service to divulge information about every instance of SQL Server running on that machine • Packet Information • Version (Inaccurate – does not show patch level) • Instance names • Net-libs supported • TCP ports and pipe names • Clustered status (bullseye!) www.sqlsecurity.com

  9. Broadcast Discovery • Since the listener may exist on multiple machines, it is possible to send a broadcast UDP packet to port 1434 to discover all instances of SQL Server 2000 on a subnet • Osql –L (will return a raw listing) but only of server names and only via broadcast to 255.255.255.255 • Network subnets such as 192.168.1.x (with a 255.255.255.0 mask) can be scanned using a single packet directed at 192.168.1.255 • Any network class works find (Class A, B, C) www.sqlsecurity.com

  10. SQLPing Utility http://www.sqlsecurity.com • Directs a custom udp packet at a specific target or subnet and enumerates the server info across multiple instances SQLPinging 192.168.1.255 Listening.... ServerName:LANDROVER InstanceName:SQL2K IsClustered:No Version:8.00.194 tcp:1241 np:\\LANDROVER\pipe\MSSQL$SQL2K\sql\query ServerName:LANDROVER InstanceName:MSSQLServer IsClustered:No Version:7.00.623 np:\\LANDROVER\pipe\sql\query tcp:1433 rpc:LANDROVER www.sqlsecurity.com

  11. Discovery – Service Enumeration sc \\machine_name query bufsize= 60000|find “MSSQL” • Requires Windows authenticated user and NetBIOS connectivity • Easily scriptable (replace machine name with IP address) • Provides a way to see where SQL Servers exist on hosts even the server is not running or SQL TCP ports have been blocked www.sqlsecurity.com

  12. Account Acquisition • Common Accounts • Sa (In the name of all that is holy – do not leave this blank) • distributor_admin (nor this one…) • Brute Force (mixed security model) • Attacks the inherent weakness of the native SQL Server security model • Multiple freeware tools (sqldict, sqlpoke,sqlbf) • Mssqlserver lacks account lockouts or password complexity requirements • Do we even need to mention null ‘sa’ account passwords? www.sqlsecurity.com

  13. Account Acquisition (cont.) • Connection strings (mixed mode) • Client registry (regedit) • Imbedded in ASP source or client-side script (RDS) • Config files (global.asa, connect.inc, web.config) • Source code disclosure • IIS has had plenty (online.securityfocus.com) • Sniffing (mixed or NT security mode) • Wifi networks • L0phtcrack (to obtain NT account) • Sniffing SQL (TCP 1433) traffic (non SSL/RPC) • Plaintext transmission of credentials • Passwords trivially obfuscated (combination of Unicode sprinkled with an XOR operation) www.sqlsecurity.com

  14. De-obfuscating SQL Server Passwords www.sqlsecurity.com

  15. Account Acquisition - SQL Injection Defined: The introduction of unintended SQL code to an application’s database at runtime. • This effectively provides an attacker with a SQL Server account by using your application as a middle-man in the attack • SQL injection attacks rarely alerts IDS systems (worsened by SSL) • Difficult to track down all the areas of exploitation since the only real solution is manual code review • No amount OS security, firewalls, patch diligence will stop SQL injection. • The best solution is good coding practices www.sqlsecurity.com

  16. SQL Injection Sample • ASP Code Sample <% Set Conn = Server.CreateObject("ADODB.Connection") Conn.open “dsn=myapp;Trusted_Connection=Yes“ Set RS = Conn.Execute("SELECT * from book_list where book_name=‘" & request.form(“txtBookname”) & “’) %> www.sqlsecurity.com

  17. SQL Injection Sample Cont. • Consider if the attacker searches for a book named: ‘ union select name from sysobjects-- • Single quote closes the intended statement • Union statement appends a new dataset to the query so the attacker can see information he wants • Could just as easily call any stored procedure or DDL command the attacker wanted given appropriate level of privilege www.sqlsecurity.com

  18. SQL Injection - Tricks • Tricks attackers use • UNIONstatements to append data ripped from other SQL • “—” double hyphen comment indicator to block out the rest of the intended SQL • Try a single quote in input fields to see if the query fails (failure usually indicates bad input validation and possible exploitation) • exec master..xp_cmdshell ‘ping HACKER_IP’to check for ‘sa’-level exploitable hosts • select name from sysobjects where type = ‘u’can expose tables to exploit • Insert tablename exec sp_whatever – good way to see output of stored procedures • Use @@version to return SQL Server and OS versions and Service Packs www.sqlsecurity.com

  19. Account Acquisition – Buffer Overflows • By overflowing buffers on listening ports it is possible to gain a system shell on an un-patched target server with the privilege level of the SQL Server service account (often LocalSystem) • There are known attacks against both the SQL Resolution service by David Litchfield (http://online.securityfocus.com/bid/5311 ) and the TCP SQL Service listener “Hello Bug” by Dave Aitel (http://lists.insecure.org/lists/pen-test/2002/Aug/0016.html) • Exploit code for both is “in the wild” • Buffer overflows exist externally as well as internally (privilege escalation) through server functions and extended stored procedures www.sqlsecurity.com

  20. Privilege Escalation • Primary methods • Un-patched Server bugs such as • _Many_ extended stored procedure privilege escalation attacks • Multiple Openrowset vulnerabilities • Check online.securityfocus.com/bid for the latest • Trojan stored procedures (sp_who for example) • Deferred compilation (EXECUTE) www.sqlsecurity.com

  21. Privilege Escalation (cont.) • Other methods • xp_regread (registry enumeration) • Any normal user can see the service context via master..xp_regread 'HKEY_LOCAL_MACHINE' ,'SYSTEM\CurrentControlSet\Services\MSSQLSERVER','ObjectName' • Openrowset – heterogeneous queries can allow attackers to brute-force their way into other systems • Errors in extended stored procedures and system stored procedures • SQLAgent Jobs - http://www.ngssoftware.com/advisories/mssql-esppu.txt • Webtasks http://www.ngssoftware.com/advisories/mssql-webtasks.txt • The list goes on…. • See online.securityfocus.com for a complete listing • Bottom Line: if you are not keeping up with patches then any user with SQL Server access can trivially elevate privilege unless extensive steps have been taken www.sqlsecurity.com

  22. They’re in as sysadmins - Now What? • Create a backdoor SQL or NT account • Insert trojan extended stored procedures to capture passwords/data/events • Use tftp to pull in toolkits • Use this SQL Server to launch attacks against other hosts • Install proxy server to make this machine a platform for further attackers on other systems • Take your data and make you suffer • As usual – possibilities are limited only by the imagination www.sqlsecurity.com

  23. Attack Conclusion • Take time to put yourself in the place of someone who really wants to get at you or your employer • How far can you get? • What if the attacker is a former employee? • Do our defenses match the threat and risk levels? • Would be even be aware if we were under attack? • Scan your networks and determine what people on the inside and the outside can access • Brute-force your own systems to be sure password complexity and account policies (if using Windows security) are sufficient • Multitude of free tools at www.sqlsecurity.com www.sqlsecurity.com

  24. Defense • What can you do to protect against each level of attack? • How do I keep up to date with patches without an army of admins? • What are some general defensive guidelines? • What is the best defense for each type of SQL Server deployment? www.sqlsecurity.com

  25. Secure Installation/Configuration • Lockdown scripts (www.sqlsecurity.com) • Check service context • Check patch level • Set mode to Windows Authentication • Assign long, random ‘sa’ account password • Enable authentication auditing • Disable SQLAgent, Microsoft Distributed Transaction Coordinator (MSDTC), and MSSEARCH • Disable adhoc queries on all providers • Remove sample databases • Tighten permissions on master/msdb database objects • Increase log history • Delete temp files used for install/upgrades www.sqlsecurity.com

  26. Discovery Defense • Block UDP 1434 inbound • BOL says you cannot do this but you can • Remember that named instances must be port configured on client • Firewall connections from all untrusted clients by IP address • Remove un-needed netlibs • Keep credentials out of connection strings • Unfortunately the “Hide Server” option in TCP/IP properties does not help • TCP port forced to 2433 • If any other netlibs are installed the server will still respond to UDP 1434 calls www.sqlsecurity.com

  27. Account Acquisition Defense • Strong password enforcement • Entropy, Lifetime, Length • Enable auditing (at least failed logins) • Encrypt all sensitive traffic • SSL recommended • Multi-protocol encryption not recommended • No multiple instance support (instances only supported on Named Pipes, TCP/IP, NWLink, and Shared Memory netlibs) • RPC encryption API only – weaker key management • Keep credentials out of connection strings and encrypted when possible • DPAPI (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetHT08.asp) • Use Windows Auth when possible www.sqlsecurity.com

  28. Privilege Escalation Defense • Especially important to KEEP CURRENT WITH PATCHES • Perform custom scripting if need-be • Use Commercially available patch tools • HFNetChk Pro - www.shavlik.com • UpdateExpert – www.stbernard.com • Service Pack Manager 2000 - www.securitybastion.com • Write Microsoft at sqlwish@microsoft.com and emplore them to: • Include SQL Patches in Windows Update • Perform patches to all instances on demand • Make SQL Server patch installs more user-friendly www.sqlsecurity.com

  29. Privilege Escalation Defense • As an admin, never run user-created stored procedures and functions without inspecting it first • This can be particularly problematic when users encrypt stored procedures (see www.sqlsecurity.com for tools to decrypt) • Run extended stored procedures using fully-qualified designation (master.dbo.xp_cmdshell) • Block access to public role to all procedures that could be abused (submit suggestions for Lockdown Script) • No longer recommending the “dropping” of extended stored procedures • Complicates Service Pack Installations • Breaks vital SQL Server mechanisms such as the use of Enterprise Manager, Query Analyzer, and Profiler • Could break as Microsoft introduces new functionality www.sqlsecurity.com

  30. SQL Injection Defenses: Input Validation • Scrub input data to make sure it contains only acceptable characters www.sqlsecurity.com

  31. Input Validation – Stored Procedures • Stored procedures can help enforce stronger typing but using them at every database access can be brutal due to the sheer number of procs that may need to be created or because of application requirements • Since SQL Server has already compiled the query plan for the query, no further code injection is possible if we properly invoke the procedure Create procedure sp_login @username varchar(20), @password varchar(20) AS Select * from users where username = @username and password = @password www.sqlsecurity.com

  32. Secure Implementation of SP • Use SqlCommand and SqlParameter objects to explicitly identify parameters SqlConnection cn = new SqlConnection(yourConnectionString); SqlCommand cmd = new SqlCommand("usp_authors", cn); // Mark the Command as a SPROC cmd.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC SqlParameter prmLast = new SqlParameter("@last", SqlDbType.VarChar,50); prmLast.Value = TextBox1.Text; cmd.Parameters.Add(prmLast); // Create and Fill the SqlDataReader cn.Open(); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); • Use the same method even for text command types. Do not use string concatenation to build query strings. www.sqlsecurity.com

  33. Discipline • Make sure developers adhere to the standards • Develop a methodology • Command/Parameter objects / stored procs • No access to production servers • Consistent database access and developer education • Encourage reusable security components • Code review • QA Test Plans • Code with an intruder’s mindset www.sqlsecurity.com

  34. Best Practices • Use principle of least-privilege • Assign MSSQLServer service non-administrator user context • Take the time to properly implement trusted security (Windows Auth Mode) • Don’t place passwords in script/code • Assign complex ‘sa’ password even when using Windows Authentication www.sqlsecurity.com

  35. Best Practices (cont.) • Write re-usable input validation routines and make their use mandatory • Use stored procedures wherever possible but avoid “string building” for executing them • Code reviews are an absolute necessity • Evaluate third-party code and applications with great scrutiny • Use SSL or IPSec to encrypt network traffic on suspect subnets (more applicable to client/server deployments but a powerful option) www.sqlsecurity.com

  36. Special Consideration : MSDE • MSDE Difficulties • Stealth install – users rarely aware it exists • Defaults to over-privileged LocalSystem security context • Separate service pack download • Multiple MSDE installer editions (the MSI file used to install – KB Q311762) • If you used any of the 15 other MSI installers besides sqlrun01.msi you must order an upgrade CD from Microsoft to apply a service pack • No tools to easily modify netlibs, audit level, security mode, or anything else for that matter www.sqlsecurity.com

  37. Deployment Solutions: 1 - MSDE and Single User • Remove ALL netlibs w/Server Network Utility or EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib', N'ProtocolList',N'REG_SZ',’’ Or (for non-default instances) EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE', SOFTWARE\Microsoft\Microsoft SQL Server\(instance_name)\MSSQLServer\SuperSocketNetLib’, N'ProtocolList',N'REG_SZ',’’ • Local connections allowed only through the shared memory netlib (which cannot be removed) • User (local) or . To specify the server name in connection strings • Secure, lightweight, simple • Can easily restore netlibs temporarily if need be • Lockdown scripts www.sqlsecurity.com

  38. Special Deployment Solutions 2 : Web-Based Applications (Forms Auth) • Use Windows Authentication mode in SQL Server • Alter IIS Metabase for Anonymous access specifying a low-priv local or domain user account • If local user is used, make sure same account/password exist on SQL Server • Turn of “Allow IIS to Control Password” option as this uses iissuba.dll and defeats ability to authenticate to the SQL Server • ASP.NET : aspnet_wp process runs in context specified in web.config or machine.config. Look for processModel key – userName attribute. (note that when an ASP.NET app runs with impersonation, this will cause authentication to occur using impersonated context and not the service context) • Block ALL direct access to SQL Server except through web server and secure back-end systems (inbound only) • Encrypt data using asymmetric encryption methods using public key only – never store private key on this server • All this does *NOT* defeat connection pooling since SQL Server user context is always the same www.sqlsecurity.com

  39. Special Deployment Solutions 3 : Client/Server Deployments • Patches especially important due to direct access issues • Implement SSL as a means of protecting data/credentials in-transit • Use a private CA to issue server certificate for the SQL Server • Clients will not be able to connect without certificate in local “trusted” store (assuming SQL Server is requiring SSL) • Application roles can be used to control access but this can also be achieved through the use of views and stored procedures which provide other benefits • Be aware the application role activation (sp_setapprole) passwords are stored on the client (they cannot be invoked via a stored procedure) so reverse engineering can yield the role password which would give the attacker access to whatever the role can access www.sqlsecurity.com

  40. New at SQLSecurity.com • SQLPing.NET • C# version of SQLPing. Source code included so functionality can be integrated into other applications • SQL Server Tools Listing (Free and Commercial) • Lockdown Script Project • I am soliciting feedback from anyone who wants to help • Full SQL Server Version list (thanks to Ken Klaft) www.sqlsecurity.com

  41. Recommended Reading • (*)Birkholz.   Special Ops: Host and Network Security for Microsoft, UNIX, and Oracle. Syngress, 2003. • (*)McClure, Scambray, and Kurtz. Hacking Exposed: Windows 2000. Osborne, 2001. • Lewis, Morris. SQL Server Security Distilled. Curlingstone, 2002. • Howard, Levy, and Waymire. Designing Secure Web-Based Applications for Microsoft Windows 2000. Microsoft Press, 2000. • Rain Forest Puppy – Phrack Magazine Volume 8, Issue 54 Dec 25th, 1998, article 8 of 12. • David Litchfield. Remote Web Application Disassembly With ODBC Error Messages http://www.blackhat.com/presentations/win-usa-01/Litchfield/BHWin01Litchfield.doc • LeBlanc, Howard. Writing Secure Code. Microsoft Press, 2001. • McClure, Shah. Web Hacking. Addison Wesley, 2003. (*) Books to which I have contributed chapters www.sqlsecurity.com

More Related