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

Question

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

Andrew Pankov

Mr or Mrs. 500

Points: 596
More actions
February 27, 2015 at 11:45 am

#150865

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?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 1

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)

Andrew Pankov Mr or Mrs. 500 Points: 596 More actions · Answer 2

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?

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 3

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)