SQL%20Server%20Security%20%20Attack%20

1
SQL Server Security Attack Defense

• Chip Andrews (www.sqlsecurity.com)
• January 2003
• AtlantaMDF Users Group

2
Presentation Outline

• Introduction
• Attack
• Fingerprinting
• Account Acquisition
• Privilege Escalation
• SQL-Injection
• Defense
• Tools (Scanners/Patches)
• Hardening Scripts
• Secure Deployment
• Input Validation
• Best-Practices

3
SQL Server Presence

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

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

5
Section 1 - Attack

• Fingerprinting/Discovery
• Acquiring Access
• Privilege Escalation
• Backdoors
• Covering tracks

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.

7
Discovery - Port Scanning

• Use nslookup / whois to determine IP addresses
• whois target._at_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)

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!)

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)

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 InstanceNameSQL2K IsClusteredNo Versi
on8.00.194 tcp1241 np\\LANDROVER\pipe\MSSQLSQL
2K\sql\query ServerNameLANDROVER InstanceNameMS
SQLServer IsClusteredNo Version7.00.623 np\\LAN
DROVER\pipe\sql\query tcp1433 rpcLANDROVER
11
Discovery Service Enumeration

• sc \\machine_name query bufsize 60000find
  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

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?

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)

14
De-obfuscating SQL Server Passwords
Hex A2 B3 92 92
Swap Digits 2A 3B 29 29
Binary 0010 1010 0011 1011 0010 1001 0010 1001
5A 0101 1010 0101 1010 0101 1010 0101 1010
XOR 0111 0000 0110 0001 0111 0011 0111 0011
Hex 70 61 73 73
Text P a s s
15
Account Acquisition - SQL Injection

• Defined The introduction of unintended SQL code
  to an applications 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

16
SQL Injection Sample

• ASP Code Sample

lt Set Conn Server.CreateObject("ADODB.Connecti
on") Conn.open dsnmyappTrusted_ConnectionYes
Set RS Conn.Execute("SELECT from book_list
where book_name" request.form(txtBookname)
) gt
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

18
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 _at__at_version to return SQL Server and OS
  versions and Service Packs

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/20
  02/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

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)

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/advisor
  ies/mssql-esppu.txt
• Webtasks
• http//www.ngssoftware.com/advisories/mssql-webta
  sks.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

22
Theyre 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

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

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?

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

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

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.a
  sp?url/library/en-us/dnnetsec/html/SecNetHT08.asp
  )
• Use Windows Auth when possible

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.co
  m
• Write Microsoft at sqlwish_at_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

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_cmdshel
  l)
• 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

30
SQL Injection Defenses 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 cleaninputmyregex.replace Remove all characters except a-zA-Z0-9
Set myregex new regexp myregex.global True myregex.pattern \D cleaninputmyregex.replace Numbers only
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 _at_username
varchar(20), _at_password varchar(20) AS Select
from users where username _at_username and
password _at_password
32
Secure Implementation of SP

• Use SqlCommand and SqlParameter objects to
  explicitly identify parameters
• SqlConnection cn new SqlConnection(yourConnectio
  nString)
• 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("_at_last",
  SqlDbType.VarChar,50)
• prmLast.Value TextBox1.Text
• cmd.Parameters.Add(prmLast)
• // Create and Fill the SqlDataReader
• cn.Open()
• SqlDataReader dr cmd.ExecuteReader(CommandBehavi
  or.CloseConnection)
• Use the same method even for text command types.
  Do not use string concatenation to build query
  strings.

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 intruders mindset

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)
• Dont place passwords in script/code
• Assign complex sa password even when using
  Windows Authentication

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)

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

37
Deployment Solutions 1 - MSDE and Single User

• Remove ALL netlibs w/Server Network Utility or
• EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHIN
  E',N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Su
  perSocketNetLib', N'ProtocolList',N'REG_SZ',
• Or (for non-default instances)
• EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHIN
  E', SOFTWARE\Microsoft\Microsoft SQL
  Server\(instance_name)\MSSQLServer\SuperSocketNetL
  ib, 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

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

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

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)

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/L
  itchfield/BHWin01Litchfield.doc
• LeBlanc, Howard. Writing Secure Code. Microsoft
  Press, 2001.
• McClure, Shah. Web Hacking. Addison Wesley,
  2003.
• () Books to which I have contributed chapters