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
Change is inevitable... Change for the better is not.