What are the security risks with enabling Ad Hoc Remote Queries?

Question

What are the security risks with enabling Ad Hoc Remote Queries?

DBADave

SSChampion

Points: 10962
More actions
July 21, 2010 at 10:32 am

#224279

We have a security vendor that is using OPENROWSET in their code to provide security-related Profiler information to their application. The problem with this approach is we need to enable Ad Hoc Remote Queries and OPENROWSET on all our database servers. What risks does this present and should this be avoided?
Thanks, Dave

Viewing 9 posts - 1 through 8 (of 8 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

From BOL

When ad hoc access is enabled, any user logged on to that instance can execute SQL statements that contain ad hoc connector names referencing any data source on the network that can be accessed by using that OLE DB provider.

http://msdn.microsoft.com/en-us/library/ms187873(v=SQL.90).aspx

You lose control of access to specific servers. To control access a linked server should be used instead of the Ad hoc.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

DBADave SSChampion Points: 10962 More actions · Answer 2

Here a comment from the link you provided.

When connecting to another data source, SQL Server impersonates the login appropriately for Windows authenticated logins; however, SQL Server cannot impersonate SQL Server authenticated logins. Therefore, for SQL Server authenticated logins, SQL Server can access another data source, such as files, nonrelational data sources like Active Directory, by using the security context of the Windows account under which the SQL Server service is running. Doing this can potentially give such logins access to another data source for which they do not have permissions, but the account under which the SQL Server service is running does have permissions. This possibility should be considered when you are using SQL Server authenticated logins.

If I understand this correctly it means that someone with a SQL Server id can remotely connect to another SQL Server and execute code under the security context of the service account on the source server. Is this correct? If so, doesn't that mean a user can connect to a remote server and potentially create a login id with sysadmin permission? For example, I connect to SERVER A and execute an OPENROWSET command against SERVER B, which creates a SQL account with sysadmin.

Thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Yes - that is a potential issue.

I would test a couple of scenarios in your dev environment. Try to perform that very action, document it and use that as your basis for denying that level of access. It is better to have tested evidence to back up your decision, based on your experiments.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

DBADave SSChampion Points: 10962 More actions · Answer 4

Trying it now. Thanks. We're not too familiar with the syntax for OPENROWSET so it make take a while to get it working.

Dave

DBADave SSChampion Points: 10962 More actions · Answer 5

The security appeared to work fine. I connected to SERVER_A using a SQL account named DBA. I then ran the following statement.

SELECT a.*

FROM OPENROWSET('SQLOLEDB','SERVER_B',

'SELECT * FROM master.dbo.sysobjects') AS a

It failed with:

Access to the remote server is denied because no login-mapping exists.

If I understood the link correctly it should have impersonated the SQL Server services account, but it didn't. I then created an account named DBA on the destination server, SERVER_B, and successfully executed the code below.

SELECT a.*

FROM OPENROWSET('SQLOLEDB','DEV-EGSQL01';'DBA';'test',

'SELECT * FROM master.dbo.sysobjects') AS a

GO

At least in this case I'm not seeing the security risk. Obviously a risk exists or Microsoft would not have disabled this feature by default.

Thanks again, Dave

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

I would proceed cautiously. I think there is still adequate risk. I would probably try to test a few more scenarios.

Another possible solution, find out what it is that they need openrowset to do that can't be done alternatively.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

DBADave SSChampion Points: 10962 More actions · Answer 7

Agreed. We're not sure why they need OPENROWSET. We imagine it's to avoid a requirement of a linked server, but they also turn on and off xp_cmdshell to get to flat files. Why not use BULK INSERT or even xp_cmdshell (which I'm not a fan of enabling on all servers). Not sure OPENROWSET is really needed to load data from a flat file (CSV) into SQL.

Thanks, Dave

ManuelRdgz SSC Rookie Points: 29 More actions · Answer 8

To load a csv file into a database OPENROWSET is not needed at all. Have them take the time to create a simple SSIS package to handle this on a schedule. That is what we are doing at my place. Saves time and all actions/steps are tracked and accounted for should any errors arise.

😀