none
Problems while executing OPENROWSET 'Microsoft.ACE.OLEDB.12.0' RRS feed

  • Question

  • Hello Community,

    I have a client that is executing the following query:

    SELECT *INTO #testdata
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;Database=D:\test.xlsx',
    'SELECT * FROM [upload$]');

    In the server is already installed:

    Microsoft.ACE.OLEDB.12.0, enable options:

    • Dynamic Paramaters
    • Allow inprocess

    Microsoft.ACE.OLEDB.15.0, in this case all options are disabled.

    When we execute the query we are getting the following error messages:

    Error Message 1(Current 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.

    Error Message 2(intermittent Error):The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
    We faced this issue intermittently and the last occurrence was on 19 Dec 2019

    To be honest I don´t know if we are missing some configuration or if something is wrong with the script. Can you tell if we are doing something wrong, or if we are missing some configuration?

    Thanks ind advance


    Tuesday, December 24, 2019 3:30 PM

All replies

  • Error Message 1(Current 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.

    This is error is because the configuration option "Ad Hoc Distributed Queries" is disabled, which is the default, and also the recommended setting from a security perspective.

    To enable this setting, run

    EXEC sp_configure 'Ad Hoc Distributed Queries', 1
    RECONFIGURE

    You may first have to enabled "Show advanced options".

    *Error Message 2(intermittent Error):*The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
    We faced this issue intermittently and the last occurrence was on 19 Dec 2019

    Reasonably, you can only get this error if the setting 2Ad Hoc Distributed Queries" is enabled. Exactly what this error is due, I don't know, but it is not uncommon to see people who have problem with accessing Excel files from SQL Server.

    Have the query been working at some point?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, December 24, 2019 6:20 PM
  • Hello Erland,


    Thank you for your reply.


    As far as I know, the client says that this already worked before, but I can say it worked because to be honest I´m not sure.

    I just checked the configuration option Ad Hoc Distributed Queries and it seems that is already enabled.

    Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.

    The access denied message could be related due to the lack of privileges where that file is located?

    And is really curious because when I get the access denied issue and try to execute it againe the error message will change with the:Error Message 2(intermittent Error):*The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

    Another thing the I just tried was changing the format from the file from xlsx to xls, like this:

    SELECT *INTO #testdata
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;Database=D:\test.xls',
    'SELECT * FROM [sheet$]');

    And it seems that it working now, but if I change it to xlsx it will fail, this could be related with compatibility with the files?

    Tuesday, December 24, 2019 6:47 PM
  • Hi Asolano016,

    Please issue the following command in SSMS, and share its results:

    -- Check what 64-bit OLEDB providers are installed
    -- and check their settings
    EXEC master.sys.sp_MSset_oledb_prop;
    Tuesday, December 24, 2019 6:49 PM
  • Another thing the I just tried was changing the format from the file from xlsx to xls, like this:

    SELECT *INTO #testdata
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;Database=D:\test.xls',
    'SELECT * FROM [sheet$]');


    And it seems that it working now, but if I change it to xlsx it will fail, this could be related with compatibility with the files?

    I would take that to imply that the name of the file is text.xls and there is no file test.xlsx.

    It is strange that you get that error message despite that "Ad Hoc Distributed Queries" was 1 already. Or it was 0 when the customer reported the error, and it has been changed since.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, December 24, 2019 7:55 PM
  • Hi,

    Maybe you should check the file type of 'test'. 

    1. Open FileExplorer
    2. Click view in the folder menu bar.
    3. Find "Options" in view
    4. Click "Options" to open the folder Options dialog.
    5. Click "View" 
    6. Cancel the check mark in the box before "Hide extensions for known file types" and click "OK" 
    7. Find your file and check the file type 

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 25, 2019 2:08 AM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 26, 2019 2:44 AM
  • Hello,

    I just executed the script EXEC master.sys.sp_MSset_oledb_prop and got the following:

    I got that configuration already.


    Since, the query works for me using the .xls file, this could be related to the lack of permissions for the service account on the folder?

    SELECT *INTO #testdata
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;Database=D:\test.xls',
    'SELECT * FROM [sheet$]');

    The query above works for me, but it seems that the customer is getting the access denied, I guess that it is working for me since I have sa access to the server and the instance, but the customer don´t.


    • Edited by Asolano016 Thursday, December 26, 2019 1:34 PM
    Thursday, December 26, 2019 1:23 PM
  • Hi Asolano016,

    The results of the EXEC master.sys.sp_MSset_oledb_prop show that both ACE OLEDB providers, i.e. 12.0 and 15.0, are installed and configured correctly. So it should work for *.xslx files.

    Let's concentrate on the customer machine and SSMS.

    Please do the following on the customer machine:

    • Launch SSMS As Administrator
    • Issue the following command in SSMS to see the visibility of the D:\test.xlsx file on the server file system:

    -- first parm is path. make sure the SQL has permissions to the folder
    -- second parm is how deep (folder depth) to query
    -- third parm is should it return folders only, or files and folders
    EXEC xp_dirtree 'd:\', 1, 1;
    • Issue the original SELECT statement as follows:

    SELECT *
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;Database=D:\test.xlsx',
    'SELECT * FROM [upload$]');
    Thursday, December 26, 2019 2:07 PM
  • Hello Yitzhak,

    Thank you for the reply.

    -- first parm is path. make sure the SQL has permissions to the folder

    Are you referring to the permissions of the account that is executing the query over the folder, or the account that is configured as Log On As in the SQL Server Service?

    While I test, my user have sysadmin over the SQL Instance and the Windows Server, the account that is executing the server is the default one NT Server\MSSQLSERVER.

    -- second parm is how deep (folder depth) to query

    Well in this case the files are in the root of D:.

    -- third parm is should it return folders only, or files and folders

    EXEC xp_dirtree 'd:\', 1, 1;

    When I execute the script you added, I got the following:


    I ran the SSMS as an Admin, I when I execute the select statement I got the following:

    SELECT *
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;Database=D:\test.xlsx',
    'SELECT * FROM [upload$]');

    Msg 7399, Level 16, State 1, Line 1
    The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

    If I execute the same select statement but using the .xls file instead of the .xlsx I got the following:

    SELECT *
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;Database=D:\test.xls',
    'SELECT * FROM [upload$]');

    Cannot process the object "SELECT * FROM [upload$]". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.


    Thursday, December 26, 2019 2:45 PM
  • Try to use a folder under root (not the root directly):

     D:\somefolder\test.xls

    Assign proper permissions on this somefolder and try again.

    Thursday, December 26, 2019 3:12 PM
    Moderator
  • Hi Asolano016,

    >> "...Are you referring to the permissions of the account that is executing the query over the folder, or the account that is configured as Log On As in the SQL Server Service?..."

    It is an account that runs SQL Server DB instance. It could be NT AUTHORITY\NETWORKSERVICE and the like. You can check it in the SQL Server Configuration Manager.

    Now back to the errors you got:

    Msg 7399, Level 16, State 1, Line 1

    The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

    Please do the following to resolve the errors:

    1. Re-run with repair option 64-bit ACE engine installation:
    "d:\~\AccessDatabaseEngine_X64.exe" 
    2. Restart SQL Server service via SQL Server Configuration Manager.
    Thursday, December 26, 2019 4:19 PM
  • Also, the Database Engine service must have permission of the Windows file system to access the file folder where database files are stored. Permission to the default location is configured during setup. If you place your database files in a different location, you might need to follow these steps to grant the Database Engine the full control permission to that location.

    For more information , please check following article. Configure File System Permissions for Database Engine Access

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 27, 2019 3:36 AM