SQL Credentials to Access Network File

Question

Post reply

SQL Credentials to Access Network File

steventnorris

SSC-Addicted

Points: 477
More actions
December 13, 2013 at 11:10 am

#395986

I've noticed a lot of good info here, so I'm hoping this community can help. For days now, I've been trying to find a way to get the below statement to run:
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 keep getting the 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 using SQL Auth to login to the server (running a command via some c# code), but I have created a credential with the Windows AD Login needed to access the shared network file and mapped it to my SQL login in the server management tool. I know the AD login has full rights to that folder, but it still seems to not be able to access it. I have seen several people with this issue, but no solution that seems to work.
Any help or suggestions would be appreciated. I'm running out of ideas.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

can you see if you add the option for readonly, if it will work?

i have this saved in my snippets:

SELECT * FROM OPENROWSET('MSDASQL',

'DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);

UID=admin;

UserCommitSync=Yes;

Threads=3;

SafeTransactions=0;

ReadOnly=1;

PageTimeout=5;

MaxScanRows=8;

MaxBufferSize=2048;

FIL=excel 12.0;

DriverId=1046;

DefaultDir=C:\Data\BlockGroups_2010;

DBQ=C:\Data\BlockGroups_2010\AKblockgroup.xls',

'SELECT * FROM [AK$]')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

steventnorris SSC-Addicted Points: 477 More actions · Answer 2

No dice. I added ReadOnly=1, but I ended up with the same error. Our two snippets seem very different though, so I'm not sure how they match up exactly. I do notice you're accessing the C-drive, which I can do fine. It's just the share drive that is giving me trouble. My changed code is:

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

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 3

Use procmon (http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx) to see what account is hitting that file. It'll need to run from the system where the Access database is. My guess is it's probably the SQL Server service account that's attempting to access the file.

K. Brian Kelley
@kbriankelley

steventnorris SSC-Addicted Points: 477 More actions · Answer 4

I cannot download and run procmon on the server containing the excel file due to restrictions in our policies. However, I do agree that the SQL Server Agent login must be what is running. My questions is, how do i get it to use a login I specify?

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 5

This is being executed by SQL Server, so it's going to use the SQL Server service account (not the Agent).

To use a different account, the easiest way to do this is using SSIS and running the package under the appropriate credentials.

K. Brian Kelley
@kbriankelley

steventnorris SSC-Addicted Points: 477 More actions · Answer 6

I have at least one limitation on that. I have to run this as an ad-hoc query. Eventually, this will be a script run by C# codebehind from a webpage on an IIS6 server.

I could probably switch the login user in my SqlConnection to a windows authenticated login using the AD user with share rights, but that produces a few security risks I'd rather avoid.

I had read that credentials could provide access to files on other servers, even if the given login did not have access, as stated here http://www.jasonstrate.com/2013/07/security-questions-logins-credentials-and-proxies/[/url]. Do you know anything about credentials and their place in my query? I may have the wrong idea of how they work.

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 7

It's interesting that you mentioned security because in order to use openrowset you're using a configured SQL Server that is likely less secure than it ought to be. Ad hoc distributed queries using this manner was turned off by default starting with SQL Server 2000 SP3 (see details here: http://msdn.microsoft.com/en-us/library/ms187569.aspx).

Credentials do allow the process to run as the domain account, but for the process executing under SQL Server Agent. However, the problem is that the openrowset connection is coming from SQL Server. So you execute the job as the credential and the connection to SQL Server is as the credentialed user. But because you're going through SQL Server it flips to SQL Server's authentication. If you were connecting directly to the Access DB, say to copy it, from the SQL Agent job step, you would see it as the credential.

There may be a way to do what you're trying to do using a linked server and having Kerberos delegation set up. But that would require the domain credentials to be used to connect and the linked server would have to be configured to pass the credentials through. In general, SQL Server logins are considered less secure than Windows logins. Is there a reason you want to use a SQL login?

K. Brian Kelley
@kbriankelley

steventnorris SSC-Addicted Points: 477 More actions · Answer 8

The windows logins are used throughout our network. They are active directory based. Using a windows login opens up not just access to the sql, but access to anything on the network. On top of this, there is only one login in our system that does not require regular updates to its password (and thus, regular updates to my code). This login is a super-user of sorts, and has far-reaching abilities.