SQL Server security hole

Imagine this: A user who has no permissions on any of your databases shuts down your server or drops important databases!

This can't happen on your server? Well, if your server is running in mixed security mode you should read on:

The trap

SQL Server 7.0 introduced two new powerful functios: OpenRowset and OpenQuery. Both functions enable you to connect to another (or the same) server using an OLEdb-provider. To do this, you can just use these function inside a SELECT-statement. Whereas Openquery requires that you configure a so-called "linked server", you can execute ad-hoc queries using the OpenRowset-function. This function expects the name of the OLEdb-Provider and a connection string as parameters. In the third parameter you specify the command that should be executed. In Books-Online you'll find the following example:

USE pubs
GO
SELECT a.*
FROM OPENROWSET('SQLOLEDB','myServer';'sa';'MyPass',
    'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
GO

This example works fine, but having a password included in your script is not a good solution. A better approach would be to use the credentials of the currently logged on user to create the new connection. After some searching in Books online I found out, how this can be achieved:

'Trusted_Connection=yes;Data Source=myServer;'

The parameter "Trusted_Connection=Yes" tells the server to establish a trusted connection (as opposed to a connection using loginname/password).

By the way: Using this method you can not only execute SELECT-statements, but also retrieve resultsets from stored procedures as the following example shows:

SELECT * FROM
OPENROWSET('SQLOLEDB', 'Trusted_Connection= yes;Data Source=meinServer;', 'EXECsp_validatelogins') As result

A surprise

The surprise came, when one day I called a procedure (using the OpenRowset-function) for which I had no permission. To see what was happening on the server, I traced the session using SQL Server Profiler. That revealed, that for users who log on using a SQL Server login and their password a new connection is established under the account of the SQL Server service.Using the following little SELECT-statement you can easily test this:

SELECT result.*
FROM OPENROWSET('SQLOLEDB', 'Trusted_Connection=yes;Data Source=IDCMUC;', 'SELECT SUSER_SNAME() ') as result

In SQL Server Profiler you can easily see what happens:

1. Execution of the SELECT-statement starts. The user is logged on as SQL Server user "danger" and his NT account is "svenh" .
2. A new connection under the account of SQL Server is established.
3. The "inner" SELECT-statement (parameter of the Openrowset-function) are executed under the account of SQL Server .

That doesn't look to dangerous at first sight - because that would only allow users to have a look inside tables or views to which they should not have access. But with a little trick (which I do not want to show here (at the moment)) you can execute any Transact-SQL statement (including xp_cmdshell or DROP DATABASE) using such a connection.

Is your server affected?

The problem only occurs, when users log in using a SQL Server login. When your users use trusted connections, then everything works as expected: For the new connections the credentials of the currently logged in user are used. The following Trace-Capture does show this:

As before, a new connection to the server is established (2), but this time the credentials of the user (in this case 'svenh') are used. Thus the "inner" SELECT is executed under the proper account (3).

What you can do

Microsoft has released a hotfix that changes the behaviour of the OPENROWSET function. With the hotfix applied any user who tries to issue an adhoc-query with OPENROWSET gets a message that this function is disabled for non-trusted connections. If you do not want to apply the hotfix you can get the same behaviour by adding some registry keys to the Provider section. A detailed description on how to do this can be found at http://www.microsoft.com/technet/security/bulletin/fq00-014.asp .

sven hammesfahr, march 2000

More information:

Description of the problem at Microsoft's security site:

http://www.microsoft.com/technet/security/bulletin/ms00-014.asp

Patch available for download at:

http://www.microsoft.com/downloads/release.asp?ReleaseID=19132

Frequently Asked Questions concerning this issue:

http://www.microsoft.com/technet/security/bulletin/fq00-014.asp