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


By:   |   Updated: 2016-12-29   |   Comments (3)   |   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


Last Updated: 2016-12-29


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
Related Resources





Comments For This Article




Monday, January 09, 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 06, 2017 - 9:33:35 AM - Ranga Back To Top (45169)

@Peng : On the Server

 


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

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

 



download


Recommended Reading

Querying Active Directory Data from SQL Server

Understanding SQL Server Linked Servers

Access MySQL data from SQL Server via a Linked Server

Create a linked server to MySQL from SQL Server

How to setup linked servers for SQL Server and Oracle 64 bit client





get free sql tips
agree to terms


Learn more about SQL Server tools