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:
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:
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:
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:
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:
In SQL Server Profiler you can easily see what happens:
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
Description of the problem at Microsoft's security site:
Patch available for download at:
Frequently Asked Questions concerning this issue: