Home Forums Data Warehousing Analysis Services Cannot create an instance of OLE DB provider "MICROSOFT.ACE.OLEDB.12.0" for linked server "(null)". RE: Cannot create an instance of OLE DB provider "MICROSOFT.ACE.OLEDB.12.0" for linked server "(null)".

  • Andrew Pankov (2/27/2015)


    error message: Cannot create an instance of OLE DB provider "MICROSOFT.ACE.OLEDB.12.0" for linked server "(null)".

    SQL Server 2008 R2

    User is getting this message trying to run a query in SSMS

    select * from OPENROWSET('MICROSOFT.ACE.OLEDB.12.0', 'Excel 12.0;HDR=NO;IMEX=1;DATABASE=\\server\folder\file.xls',['2014$'])

    user's machine is 64-bit Win7

    Driver 'MICROSOFT.ACE.OLEDB.12.0' is 64 bit, too

    Query is running ok when SQL Server is connected under 'sa' or any other sqladmin account. So, it's definitely a security problem. Please help, what I should set up?

    OPENROWSET requires "SA" privs to be executed. Of course, you don't ever want to give that priv to users.

    You could use OPENDATASOURCE, instead, with the understanding that the privs will be based on the user and that user might not be able to "see" whatever UNC the spreadsheet lives at.

    The basic syntax for OPENDATASOURCE to import Excel data is kind of like the following...

    SELECT *

    FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',

    'Data Source=\\machinenname\sharename\path\filename.xlsx;Extended Properties="Excel 12.0;IMEX=1;HDR=YES;"')...[Sheet1$]

    ;

    If you need the "reach" that OPENROWSET has and if your DB is owned by "SA", you could write a proc that would take a full path name (check for DOS/SQL injection before you use it) that has an EXECUTE AS OWNER in it and then grant the user privs to execute the proc. The user won't need "SA" privs for that.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)