共用方式為

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:

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.

Comments

  • Anonymous
    July 11, 2016
    Finally! A solution that works! Appreciate you posting this AND your very succinct directions!
  • Anonymous
    July 18, 2016
    Thanks. Works good for me. No restart is required in my case (win 2012).
  • Anonymous
    August 11, 2016
    Thank you so much, I have been searching all day long for a solution, I also did most of the other stthe trick was for me to go and enable disallow adhoc access like you showed in figure 6, and only then the registry key was created in figure 7 which allowed me to change it to 0. Thank you so much for posting this
    • Anonymous
      August 11, 2016
      p.s. I also did not have to restart, it worked immediately
  • Anonymous
    September 07, 2016
    Thank you! It solved this issue for me (Win 10, SQL 2016)
  • Anonymous
    September 07, 2016
    Server Objects > Linked Servers > Providers > SQLOLEDB. Enable Allow InProcess. Reset SQL service and verify.
    • Anonymous
      September 14, 2016
      Hello Quazi,Yes. To set Allow InProcess for the provider which you are working on, for example Oracle provider, that will be ORAOLEDB. In the blog, since we were trying to connect to Excel source hence the provider name is Microsoft.ACE.OLEDB. Once the Allow in process bit is set , restart the SQL service because the dll's of that provider need to be loaded within SQL address space.
  • Anonymous
    October 08, 2016
    The comment has been removed
    • Anonymous
      December 24, 2017
      I'm also getting same error, despite of running through multiple solutions. Unable to have the solution for this problem. Same query runs fine with different user whereas one user is unable to run this query on the machine SQL is installed.
  • Anonymous
    February 08, 2017
    This saved my life, thank you!
  • Anonymous
    February 16, 2017
    Thanks for posting this article. Its really helpful and easy to understand. Now i can easily insert excel fields data into physical table :)
  • Anonymous
    September 29, 2018
    Thanks a lot! Finally, I made it work! :)