SQL Server Ad Hoc Access to OLE DB Provider Has Been Denied Error


By:   |   Updated: 2016-12-29   |   Comments (5)   |   Related: More > Linked Servers


Problem

I was trying to use OPENROWSET to query a remote database and I experienced the following error "Ad hoc access to OLE DB provider has been denied error. You must access this provider through a linked server". When I ran this same query using a sysadmin account it worked fine. Why doesn't this work for other logins?

Solution

To show the error and how to resolve, I am using the below OPENROWSET query with a non-sysadmin login (note: I replaced server name and other details with dummy names).

SELECT a.*  
FROM OPENROWSET('SQLNCLI10', 'Server=SERVERNAME;UID=USERNAME;PWD=PASSWORD;',  
     'SELECT  Column  
      FROM database.dbo.table  
      ') AS a

After running the query I received the error below.

SQL Server OPENROWSET example in Management Studio

Now from within SQL Server Management Studio (SSMS), go to the Linked Server > Providers and double click on the provider you are using in the OPENROWSET query and check if "Disallow adhoc access" is enabled or not as shown below.

SQL Server Linked Server Provider Properties

If "Disallow adhoc access" is enabled all the non-sysadmin users cannot use OPENROWSET and OPENDATASOURCE functions.  This option does not impact sysadmins.

You can also find the value of this option in the Registry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\INSTANCE NAME \PROVIDERS\SQLNCLI10.  Run regedit and go to above path and find "DisallowAdHocAccess" as shown below.

Registry setting to Disallow Adhoc Access

We can disable "Disallow adhoc access" from the SSMS provider properties, but it removes the entry in the registry instead of changing the value to zero and we still get the ad hoc access to OLE DB provider has been denied error.  The below screenshot shows what the Registry looks like if we uncheck the option in SSMS.  You can see the "DisallowAdHocAccess" was removed from the Registry.

Registry Setting when Disallow Adhoc Access is Disabled in SSMS

What needs to be done, is this Registry entry needs to be changed to zero from one for this to work for non-sysadmin logins.  So, double click on the entry and change the value to zero and then save the record.

Registry entry needs to be changed to zero from one for this to work for non-sysadmin logins

Notes

Even though "Disallow adhoc access" is disabled, we need to check the Registry entry for "DisallowAdHocAccess". If the entry is missing enable the option from the provider properties in SSMS and then disable it by changing the value in the Registry to zero.

Next Steps





get scripts

next tip button



About the author
MSSQLTips author Ranga Babu Ranga Babu is a SQL Server DBA with experience on performance tuning and high availability.

View all my tips


Article Last Updated: 2016-12-29

Comments For This Article




Wednesday, May 19, 2021 - 1:12:14 AM - Michael Loaiza Back To Top (88704)
Excellent. This fix my issue for non sa users. Thanks

Friday, October 2, 2020 - 10:58:12 AM - JM Back To Top (86586)
Even though this is an old article, you should probably put a note at the top that enabling OPENROWSET use for non-sysadmin accounts can allow a lot of vulnerabilities.

Monday, January 9, 2017 - 11:17:02 AM - Richard Speiss Back To Top (45192)

 

You can also execute the following code on the destination server.  This has worked for me. I'm sure that I got it off of the internet ages ago but can't recall exactly from where

EXECUTE sp_configure 'show advanced options', 1

RECONFIGURE WITH OVERRIDE

GO

EXECUTE sp_configure 'Ad Hoc Distributed Queries', '1'

RECONFIGURE WITH OVERRIDE

GO

EXECUTE sp_configure 'show advanced options', 0

RECONFIGURE WITH OVERRIDE

GO


Friday, January 6, 2017 - 9:33:35 AM - Ranga Back To Top (45169)

@Peng : On the Server

 


Friday, January 6, 2017 - 8:19:36 AM - Peng Back To Top (45166)

Can you clarify about the registry edit? On the server or client? 

 



download














get free sql tips
agree to terms