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

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


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?


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.*  
     '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


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



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



EXECUTE sp_configure 'show advanced options', 0



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? 



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