OPENDATASOURCE query execution error while using SQL public account
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:
- https://msdn.microsoft.com/en-IN/library/ms179856.aspx
- https://blogs.msdn.com/b/cindygross/archive/2010/10/18/do-i-need-dtc-for-my-sql-server.aspx
- https://blogs.msdn.com/b/spike/archive/2008/07/23/ole-db-provider-microsoft-jet-oledb-4-0-for-linked-server-null-returned-message-unspecified-error.aspx
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.