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)".

  • March 11, 2015 at 5:56 pm

    #1783483

    Andrew Pankov (3/2/2015)

    Hi Jeff, thank you for response.

    I tried OPENDATASOURCE instead of OPENROWSET.

    SELECT * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0', 'Data Source=\\server\share\filename.xls;Extended Properties="Excel 12.0;IMEX=1;HDR=NO;"')...['2014$']

    It's running ok again when sql server is connected in SSMS under [sa], but under his windows account it returns an error:

    Msg 7302, Level 16, State 1, Line 1

    Cannot create an instance of OLE DB provider "MSDASC" for linked server "(null)".

    User is MYDOMAIN\JDoe is a member of AD group MYDOMAIN\SQL_developers that is a member of [sysadmin] sqlserver role.

    MYDOMAIN\JDoe also has a maximal access to the share where xls file is located, via the group MYDOMAIN\SQL_developers.

    It's all working under my AD account that is a domain admin

    It's all working under 'sa'

    What the difference if sql is connected under "sa" or account that is a member of "sysadmin" role?

    Turn it into a stored procedure that contains "EXECUTE AS OWNER" and give the user privs to run the stored procedure. Do NOT give the user "SA" or other special privs.

    {edit}.... and make sure that you check any path inputs for DOS Injection as well as SQL Injection.

    --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)