Security implications of allowing 'Ad hoc distributed queries'

Question

Security implications of allowing 'Ad hoc distributed queries'

wisl

SSCommitted

Points: 1554
More actions
August 25, 2009 at 9:17 am

#137531

I have a stored procedure that uses 'Ad hoc distributed queries' to query excel data, combine it with data from the database perform some manipulations and then BCP a csv file of the combined data.
The stored procedure has :
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE WITH OVERRIDE
Insert INTO ##MyTable ([col1],[col2])
Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\somefile.xls;HDR=YES;IMEX=1', 'SELECT [col1], [col2] FROM [Sheet1$]')
EXEC sp_configure 'Ad Hoc Distributed Queries', 0
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
This works perfectly the first time it is run, but fails on the second run with the following error:
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.
Is my syntax incorrect for setting the option, is it not possible to set this this option from within a stored Procedure.
What are the security implications of setting this option and leaving it on - this is our live server.
Thank you in advance

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

john.moreno Default port Points: 1485 More actions · Answer 1

wisl (8/25/2009)
I have a stored procedure that uses 'Ad hoc distributed queries' to query excel data, combine it with data from the database perform some manipulations and then BCP a csv file of the combined data.
-snip-
What are the security implications of setting this option and leaving it on - this is our live server.

I've been researching this as well, and think I have come to a final understanding:

There are 3 basic security concerns with Ad Hoc Distributed Queries. 1) If the allowed provider has a bug in it (such as a buffer overflow) that compromises your security then this is one of way getting to it. I consider this the greater of the three problems as it can potentially be used to compromise your system.

2) It could be used to connect from a compromised server on your network to a non-compromised server, one that doesn't normally allow external connections from outside of your network.

3) It allows an already compromised database to report back to the attacker, giving additional information to be used to enable further attacks and/or to copy your data. I put this as the lesser of the three problems, because while it makes it easier to copy your data and decode your system, it doesn't actually allow them any extra rights or create a vulnerability.

John

wisl SSCommitted Points: 1554 More actions · Answer 2

Thank you for your reply.

I decided that I was not comfortable taking the risk and used this as an opportunity to try using SSIS.

I found the learning curve with SSIS quite steep (basic tasks are very straight forward but creating complex packages was more challenging). But I took advantage of the excellent resources available here and on SQLShare http://www.sqlshare.com/channels/SQL.aspx .

I still have not managed to create the highly complex packages that I know are possible but I'm certainly getting there.

Thanks again

lucy

mah_uma SSC Journeyman Points: 81 More actions · Answer 3

Thanks for the useful information. it is working ..

EXEC sp_configure 'show advanced options', 1

RECONFIGURE WITH OVERRIDE

EXEC sp_configure 'Ad Hoc Distributed Queries', 1

RECONFIGURE WITH OVERRIDE

after running the above statements

we need to run 'RECONFIGURE' once.

after if you run the openrowset statement.. the data can be imported from .xls to destination database table.

Thanks