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:
- 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.
- 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.
- 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