Using SQL Credential to Open a file with OpenRowSe

I am attempting to run the below statement:

INSERT INTO table SELECT * FROM 
OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;
Database=//server/folder/file.xls;
HDR=YES;',
'SELECT * FROM [Sheet1$]')

However, I am receiving the below error:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine cannot open or write to the file '\\server\folder\file.xls'. It is already opened exclusively by another user, or you need permission to view and write its data.".

Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

I am running SQL Server 2005 on a 32 bit machine. The code being executed will eventually be from c# code behind on a IIS6 web server. However, currently I'm just trying to get it to run on the SQL Server. I'm logged into the SQL Server using SQL Auth, but the file must be accessed using Windows Auth specific to the share drive (an AD account on our network). Ad-Hoc priveleges have been given to the SQL Auth account in order to allow OPENROWSET.

I have tried adding UID=user;PASS=pswd into the OPENROWSET code as below:

INSERT INTO table SELECT * FROM 
    OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;
    Database=//server/folder/file.xls;
    HDR=YES;
UID=user;
PASS=pswd',
    'SELECT * FROM [Sheet1$]')

This yielded the same error. I also set up an SQL CREDENTIAL with the user id and pass of the windows auth Active Directory account with access to that share drive, but the error remained.

I considered using a proxy, but this isn't an sql server agent job. It's an adhoc call.

I have also logged into the SQL Server using Windows Auth with a user that has rights to the share, and get the same error.

I've researched this a lot, but can't seem to find an answer that works. Through my searches, it seems to be a problem many people have. Any help will be greatly appreciated. I'm stumped.

回答1:

I'm quite sure that when you use OpenRowSet that it's the Service user for the SQL Server that needs access to the file.

And since it appears that you are trying to access a file on another server, it can be a challenge to get it to work, if the SQL server is running as Local System or another local user account.

I have several times solved this by either changing the SQL server to run as a AD user (with the security implications it has) and then give that user access to the file on the network, or placed the external file (in this case the Excel) on the same computer as the SQL server.

回答2:

There is possible to use 2 different connection strings:

ACE OLEDB 12.0
JET OLEDB 4.0

Try both.

Sometimes Excel contains untypicall data type. Try to read entire data as a text using option "IMEX=1".

In case of further issues, try below solutions:
Linked Server using Microsoft.Jet.OLEDB.4.0 problem
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"