When Databases Attack: Hacking with the OSQL Utility

The OSQL Utility is a command-line client for SQL Server that has shipped with every version since SQL Server 2000 was released. Many database administrators like it because it’s lightweight, makes scheduling TSQL jobs easy, and can be used for batch processing. Many hackers like it because it provides them with the ability to connect to local and remote database servers without having to provide credentials. This blog will provide some examples that illustrate how the OSQL utility can be used to gain unauthorized access to systems, databases, and sensitive information.

A Little History

All relevant versions of SQL Server have shipped with a command-line SQL Server client. The native command-line clients installed in past versions include: iSQL.exe, OSQL.exe, and SQLCMD.exe. Each utility Microsoft releases has more functionality than the last, but the important thing to note for this discussion is that the basic syntax has remained the same. That includes the –E “trusted connection” switch that will be important later on in this blog. Below I’ve provided a table that outlines which utilities ship with each version of SQL Server.

IMPORTANT NOTE: I focus on OSQL, because it’s installed on most production SQL servers today. However, after version 2008 R2 it will no longer be included in default installations. So, if you find yourself without OSQL, look to the other options.

Finding SQL Servers

Let’s start out by finding some SQL Servers on the network. It’s pretty hard to attack something if you don’t know where it is. There are a number of tools and methods for enumerating SQL Servers, but today I’m going to focus on finding them with native OSQL functionality. Very simply, local and network SQL Servers can be listed by executing the command below:

C:>osql -L

The command sends a UDP request across the broadcast network and any SQL Server listening will respond. The resulting output will be a list of SQL servers on the broadcast network. So, with one switch, you can turn your database client into a database scanner. Also, the server list can be directed into a file with the following command:

C:>osql -L > sql_servers.txt

IMPORTANT NOTE: In older versions of SQL server, OSQL may have to be executed directly from the installation directory. Also, Microsoft warns that “Due to the nature of broadcasting on networks, OSQL may not receive a timely response from all servers. Therefore the list of servers returned may vary for each invocation of this option.” You may want to run the command a few times to ensure you get the full list.

Trusted Connections

Normally when a user queries an SQL Server with OSQL, they provide a username and password to authenticate. As a result, many administrators end up placing sensitive usernames and passwords in their scripts. Depending on the configuration, local, domain, and SQL Server accounts could be exposed. Trusted connections provide database users with the option to query SQL Servers without having to supply their credentials. When the trusted connections option is selected, the OSQL client attempts to authenticate to the database server using the current user context. In a way, this option increases security, because it keeps passwords out of scripts and in some cases can be used to enforce least privilege. However, there are some negatives aspects to having a “Trusted Connection” option: mainly the “Trusted” part.

Executing Queries with a Trusted Connection

Let’s take a look at how a database administrator might use this tool to check the version of a remote server. -E Uses a trusted connection for authentication (no credentials are required). I’ve also listed additional switches below:

• -S Specifies the local or remote server (IP, hostname or hostnameinstance)
• -Q Runs a query and immediately exists
• -h Indicates number of headers for the output
• -s Indicates separating character for the output
• -w Sets the width for the output

The example below will query the SQL Server at 192.168.100.110 for its version.

C:>osql -E -S 192.168.100.110 -Q "select @@version" -h 1 -s "," -w 500

Based on this example, it’s obvious that trusted connections are a handy tool for a database administrator. The problem starts to occur when an unauthorized user gets access to the database administrator’s machine or the database administrator decides they want more access to the system. Below are a few additional command line examples for connecting to remote databases using OSQL or SQLCMD. Connect to a remote database using an IP address:

C:>SQLCMD -E -S 192.168.100.110 -Q "select @@version"

Connect to a remote database using the instance name:

C:>SQLCMD -E -S DBSERVER1BankAppDB -Q "select @@version"

Connect to a remote database using a non standard port:

C:>SQLCMD -E -S tcp:DBSERVER1,8000 -Q "select @@version"

Executing System Commands with a Trusted Connection

Attackers aren’t the only threat. Both attackers and database administrators can leverage this next trick to escalate their privileges. Using the OSQL utility and the xp_cmdshell extended stored procedure, DBAs and hackers can execute commands with the privileges of the SQL Server service account. Usually I find the SQL Server service account running as SYSTEM, a domain account, or an almighty Domain Admin account. For those of you who are not as familiar – if we obtain SYSTEM privileges, we have more power than the local administrator account and, if we obtain Domain Admin, we can control most (if not all) of the devices on the network. How does this magic happen? Well, let’s take a look. In the first example, I will execute the “whoami” command to return the name of the account I’m currently using. In the example below I am running as the “DBAdmin”domain user.

C:>whoami
demodbaadmin

In the second example, I will run the same command using OSQL, a “trusted connection”, and xp_cmdshell. This time, the command returns “nt authoritysystem”. That means I can run any command as SYSTEM without being a part of any local or domain groups.

C:>osql -E -S 192.168.100.110 -Q "xp_cmdshell 'whoami'" 
output ---------
nt authoritysystem
NULL (2 rows affected)

If the database user running the command has been assigned the “sysadmin” fixed server role (most DBAs have), then the command above can be executed to determine what user the SQL Server is running as. If not, then escalation may be required.

IMPORTANT NOTE: The command above did not require any credentials and our actions most likely have not been logged. Also, sometimes the SQL Server service is not configured with a local Administrator, SYSTEM, or Domain Admin account. When that is, the case I usually find that it is configured with a shared service account. That can be almost as good.

Leveraging Shared Service Accounts and Trusted Connections

“Shared service account” is a term that describes one account that is used to run many services. The account can be a local or domain Windows account. In this case, we are referring to one account running the SQL Server service on many servers. Server administrators often use this approach because it makes managing database service accounts a whole lot easier. In enterprise environments, it can actually reduce the number of required service accounts by hundreds. However, managing accounts this way does come with some risk.

Configuring SQL Servers with a shared service account usually creates a trust relationship between every database server using the account. This happens because of privilege inheritance. In the OSQL command example below, the database admin is able to access a database that the account does not have privileges to. The inheritance happens as follows:

1. The database admin (sysadmin) is able to execute a local command on SQL Server 1 with the SQL Server service account’s privileges using the OSQL utility, a “trusted connection, and the xp_cmdshell extended store procedure. (SYSADMIN on Server 1 = Service Account Privileges on Server 1)
2. In versions of SQL server prior to 2008, the SQL Server service account is automatically placed in the local administrators group. That means the shared service account can authenticate to any SQL Server using it. (Service Account Privileges on Server 1 = Administrator Privileges on Server 2)
3. In versions of SQL server prior to 2008, the local administrators group is assigned the sysadmin fixed server role. As a result, the shared service account has the privileges to run queries and local commands on Server 2. Through inheritance, so does the sysadmin from Server 1. (Administrator Privileges on Server 2 = SYADMIN on Server 2)

IMPORTANT NOTE: Despite of the fact that SQL Server 2008 ships with more secure configurations, administrators often change them back to the 2005 default settings. Below is my crazy privilege inheritance abstract that shows the privilege flow starting from an SQL injection attack vector. Hopefully it helps to illustrate the process.

The real world attack would use a process like the one below. The database admin could first verify that their account can execute commands with the shared account’s privileges with the command below:

C:>osql -E -S 192.168.100.110 -Q "xp_cmdshell 'whoami'" output ---------------------------------------------------------------------------------------------------------------------------------- DEMOShared NULL
(2 rows affected) Next, the database admin can enumerate SQL Server targets with the command below: C:>osql -L Servers: DB1 HVA LVA (192.168.100.110)

Then, the database admin can issue commands on the remote SQL Server targets that use the DEMOShared account.  IMPORTANT NOTE: In this example, the database admin is using the interactive mode to issue queries to the servers.

IMAGE: Using Shared Service Account to Gain Unauthorized Access

Batch Attacks

Let’s automate some of this. We can use simple Windows batch scripts and our OSQL tool to run queries across the accessible databases on the broadcast network. Below is a simple command-line example that will write the hostname and SQL Server version to “accessible_servers.txt” for each server that the database administrator has access to.

C:FOR /F %i in ('osql -L') do osql -E -S %i -Q "selectrtrim(CONVERT(char(50),SERVERPROPERTY('servername')))+' ('+rtrim(CONVERT(char(20),SERVERPROPERTY('productversion')))+')'+' '+rtrim(CONVERT(char(30),SERVERPROPERTY('Edition')))+' '+rtrim(CONVERT(char(20),SERVERPROPERTY('ProductLevel')))+char(10)"

The output will look something like the text below. IMPORTANT NOTE: The login timeout errors usually indicate that the database user does not have access to the target SQL Server. It does not mean that a database service is not listening on that server.

[SQL Native Client]Named Pipes Provider: Could not open a connec-tion to SQL Server [53]. [SQL Native Client]Login timeout expired [SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. ---------------------------------------------------------------- ---------------------------------------------------------------- HVA (9.00.4053.00) Express Edition SP3 (1 row affected) ---------------------------------------------------------------- LVA (9.00.4053.00) Express Edition SP3 (1 row affected)

We could, of course, automate queries to execute local commands, install software, and search for sensitive data on target servers, but I’ll save that for another day.

Wrap Up

The main lesson here is that configuring accounts with LEAST PRIVILEGE is important. Another take away should be that most of these attacks don’t generate any alerts. So, consider creating triggers on sensitive stored procedures like xp_cmdshell to generate audit trails. If you don’t feel like creating triggers manually, policy based management can be used. Policy based management has been around since SQL Server 2008, and allows DBAs to enforce detective and preventative controls on a SQL Server. The policies can be centrally managed to enforce controls across all of the 2005 and 2008 SQL Servers in your environment. I’ve provided a link below and strongly recommend DBAs take a look if they are not already familiar.

References

• SQL Server Policy Based Management
• https://msdn.microsoft.com/en-us/library/aa214012%28v=sql.80%29.aspx
• https://msdn.microsoft.com/en-us/library/ms162806(SQL.90).aspx
• https://msdn.microsoft.com/en-us/library/ms162806.aspx