October 6, 2011 at 10:03 am
Hi all,
I'm running on 64 bit SQL Server 2008 R2 build 10.50.1600.1 (don't worry this isn't a 32bit vs 64bit ACE driver question! :-D). I also have enabled Ad Hoc Distributed Queries via sp_configure.
I currently have a stored procedure which is used to import ad hoc excel sheets into a staging table; which dynamically builds the following statement from parameters for stagingtable, excel file name and sheet, producing something along these lines:-
SELECT *
INTO dbo.StagingTable
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0'
, 'Excel 12.0 xml;HDR=Yes;Database=D:\ExcelFile.xlsx'
, 'SELECT * FROM [Sheet1$]') x
This works happily both inside the procedure that builds the d-sql code, and if I print the code and run it manually from an SSMS query window.
However, if I run the code following an EXECUTE AS LOGIN statement to impersonate another login (code below), I always receive the following error message:-
EXECUTE AS LOGIN = 'Doman\BulkLoad'
SELECT *
INTO dbo.StagingTable
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0'
, 'Excel 12.0 xml;HDR=Yes;Database=D:\ExcelFile.xlsx'
, 'SELECT * FROM [Sheet1$]') x
Msg 7302, Level 16, State 1, Line 5
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
I also have a SQL Server Agent job set up to run the procedure under the 'Domain\BulkLoad' account, and the code runs successfully from the job, in addition, if I run EXECUTE AS LOGIN using my own login, I get the error message when running the code.
I've also read the bug http://connect.microsoft.com/SQLServer/feedback/details/257494/smss-does-not-handle-the-provider-option-disallowadhocaccess-correctly and manually created the DisallowAdHocAccess value under the Microsoft.ACE.OLEDB.12.0 node, setting the value to 0.
Following all this, I then tried setting up the connection as a linked server:-
EXEC sp_addlinkedserver
@server ='LoadExcel'
, @srvproduct = 'ACE for Excel'
, @provider = 'Microsoft.ACE.OLEDB.12.0'
, @datasrc= 'D:\ExcelFile.xlsx'
, @provstr = 'Excel 12.0 xml;HDR=Yes'
SELECT * FROM LoadExcel...[Sheet1$] -- works fine
EXECUTE AS LOGIN = 'Doman\BulkLoad'
SELECT * FROM LoadExcel...[Sheet1$] /*fails as with the previous error*/
REVERT
EXEC sp_dropserver 'LoadExcel'
The query run under the impersonated login again fails - I test it by using the job I created running under this account and again everything works ok.
The final thing I tried was switching the linked server from "Be made using the current security context" to "Be made without using a security context" by running the following:-
EXEC sp_addlinkedserver
@server ='LoadExcel'
, @srvproduct = 'ACE for Excel'
, @provider = 'Microsoft.ACE.OLEDB.12.0'
, @datasrc= 'D:\ExcelFile.xlsx'
, @provstr = 'Excel 12.0 xml;HDR=Yes'
EXEC sp_addlinkedsrvlogin 'LoadExcel', 'false' /*make it run using no security*/
SELECT * FROM LoadExcel...[Sheet1$] -- works fine
EXECUTE AS LOGIN = 'Doman\BulkLoad'
SELECT * FROM LoadExcel...[Sheet1$] /*works this time around!*/
REVERT
EXEC sp_droplinkedsrvlogin 'LoadExcel', NULL
EXEC sp_dropserver 'LoadExcel'
It seems a little strange to me that the connection will work ok if we're passing through no credentials at all, but if we do pass credentials through then the connection fails.
Also; if this is the only way in which this will work, is there any syntax to run the OPENROWSET statement directly in the same way, i.e. passing through no credentials? Or am I stuck having to dynamically run sp_addlinked server each time, with the sp_addlinkedsrvlogin following to set it to connect with no credentials?
Thanks in advance for any help!
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
October 7, 2011 at 7:32 am
October 7, 2011 at 8:33 am
Cheers, a lot of reading there so I'll go away and absorb it and test; will post back with results once I've done that!
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply