Ask Learn
Preview
Please sign in to use this experience.
Sign inThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Hello all,
Recently, I was trying to load data from an Excel file without having SysAdmin rights. I used the OPENDATASOURCE command to accomplish this, and I failed. However, if I used the SysAdmin account to execute the query, I was able to successfully load the Excel data.
In this blog, I am covering this scenario. Before we get started, here’s some information on OPENDATASOURCE.
OPENDATASOURCE: This command provides ad hoc connection information as part of a four-part object name without using a linked server name. Any user can execute OPENDATASOURCE. The permissions that are used to connect to the remote server are determined from the connection string.
Issue Description:
While executing OPENDATASOURCE query with an account which has just public access on SQL Server, I was getting an error. I am including the query and the error below.
Query:
select *from OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source="\\Node2\Repro\repro.xlsx"; Extended Properties="Excel 12.0;IMEX=1;HDR=Yes"')...[Sheet1$]
Error:
Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied. You must access this provider through a linked server.
To illustrate this issue, I installed SQL 2012 instance named "SQL2012" on Windows 2012 R2 Server named "Node1" and hosted the Excel file at \\Node2\Repro\repro.xlsx.
Sysadmin account used was: WindowsCluster\Administrator
Public account used was: WindowsCluster\SQLUser
Repro Steps:
I got the following error while executing the OPENDATASOURCE query:
Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/Opendatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.
I fixed the error by enabling Ad Hoc Distributed Queries configuration setting in SQL Server using the following command:
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure with override
Post enabling Ad Hoc Distributed Queries configuration setting, I got the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
Figure 1. Error Raised while executing Opendatasource query
Fixed the issue by enabling Allow InProcess for [Microsoft.ACE.OLEDB.12.0] provider. To do this:
Expand Server Objects >> Providers>> Microsoft.ACE.OLEDB.12.0 properties
Check “Allow InProcess” option.
If I use sysadmin account WindowsCluster\Administrator, I am successfully able to execute the query:
Figure 2. Successful Execution of the OPENDATASOURCE query:
If I use an account WindowsCluster\SQLuser, I am getting the below error:
Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied. You must access this provider through a linked server.
Figure 3. Permission error while accessing the ACE Provider.
I provided the permissions to the public account WindowsCluster\SQLUser, however, I am still getting the same error:
Alter Settings
Alter any Linked Server
Adding WindowsCluster\Administrator to local Windows Administrator Group didn’t help.
I unchecked the option Disallow adhoc access. I then restarted the SQL Service, but I am still getting the following error:
Figure 4. Properties of 'Microsoft.ACE.OLEDB.12.0' provider
Also, copying the Excel file locally made no difference.
select *from OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source="C:\Temp\Repro\repro.xlsx";Extended Properties="Excel 12.0;IMEX=1;HDR=Yes"')...[Sheet1$]
From Components Services, I located MSDAInitialize package and made sure that the account WindowsCluster\SQLUser has local and remote access. That made no difference either.
Figure 5. Component Services, MSDAINITIALIZE Package
Resolution:
To resolve the issue, I checked the option Disallow adhoc access option for [Microsoft.ACE.OLEDB.12.0] provider.
Figure 6. Properties of 'Microsoft.ACE.OLEDB.12.0' provider
The above setting, created a registry entry DisallowAdHocAccess at: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012\Providers\Microsoft.ACE.OLEDB.12.0
Figure 7. Registry location of 'Microsoft.ACE.OLEDB.12.0' provider
Set the value of DisallowAdHocAccess to 0.
Figure 8. Setting DisallowAdHocAccess to 0.
Restart the SQL service. I was able to successfully execute the OPENDATASOURCE command using “WindowsCluster\SQLUser” account after this step.
Figure 9. Successful execution of Opendatasource query with public account.
I hope the guidelines in the blog helps you.
Reference articles:
Please share your feedback, questions and/or suggestions.
Thanks,
Don Castelino | SQL Server Support Team | CSS – Microsoft
Disclaimer: All posts are provided AS IS with no warranties and confer no rights. Additionally, views expressed here are my own and not those of my employer, Microsoft.
Please sign in to use this experience.
Sign in