Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Security implications of allowing 'Ad hoc distributed queries' Expand / Collapse
Author
Message
Posted Tuesday, August 25, 2009 9:17 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:55 AM
Points: 914, Visits: 281
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




Post #776793
Posted Friday, October 30, 2009 4:27 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:52 AM
Points: 347, Visits: 1,069
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
Post #811915
Posted Monday, November 2, 2009 9:03 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:55 AM
Points: 914, Visits: 281
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



Post #812415
Posted Sunday, April 18, 2010 9:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, April 18, 2010 9:52 PM
Points: 1, Visits: 0
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
Post #905677
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse