OPENDATASOURCE query execution error while using SQL public account

  • Article

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.