Understanding ad hoc distributed queries

In SQL Server, the default configuration to not allow ad hoc distributed queries. For infrequent references to a data source, the OPENROWSET or OPENDATASOURCE functions are specified with the information needed to connect to the remote server. The rowset can then be referenced the same way a table is referenced in Transact-SQL statements. When ad-hoc access is enabled, any user logged on to that instance can execute SQL statements containing ad-hoc connector names, referencing any data source on the network that can be accessed using that OLE DB provider. This example allows SQL Server to query data from an Access database:

SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
        'c:\MSOffice\Access\Samples\Northwind.mdb';'Admin';'';
        Employees);

There are 3 basic security concerns with Ad Hoc Distributed Queries, and each has it’s own level of risk:

  1. If the allowed provider on the remote server has a bug in it (such as a buffer overflow) that compromises your security. This is probably the largest risk to your SQL Server environment.
  2. This technology could be used to connect from a compromised server to a non-compromised server on the same network, even one that normally doesn’t allow external internet connections, and that might compromise your seemingly secure SQL Server environment.
  3. IT might be allowed on a compromised database, allowing that SQL Server instance to report back to the attacker, giving them additional information to be used to enable further attacks or to steal your data.

To enable the ad hoc distributed query feature on your server:

exec sp_configure 'show advanced options', 1;
RECONFIGURE;
exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

To disable the ad hoc distributed query feature:

exec sp_configure 'show advanced options', 1;
RECONFIGURE;
exec sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
GO
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s