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.
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.
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.
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.
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.
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
- Read these additional tips about OPENROWSET.
- Please be careful while changing the Registry values and be sure to have a Registry backup before starting the process.
- Check out the tips on SQL Server Linked Servers.
- Learn about SQL Server Management Studio.
