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

By:   |   Comments (8)   |   Related: > 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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, November 23, 2023 - 11:12:20 AM - Hernan Back To Top (91773)
You're the best

Monday, December 12, 2022 - 8:23:41 AM - Victor Brink Back To Top (90758)
Since 2016 - this still affects SQL Server 2019!

Thursday, November 17, 2022 - 5:25:50 AM - Robin Wilson Back To Top (90694)
Thanks so much, this solved the issue, and I couldn't understand why I was getting the issue even though disallow was not ticked.
This was the answer.

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



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



EXECUTE sp_configure 'show advanced options', 0



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? 


get free sql tips
agree to terms