SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Credentials to Access Network File


SQL Credentials to Access Network File

Author
Message
steventnorris
steventnorris
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 14
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.
Lowell
Lowell
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37215 Visits: 40280
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
steventnorris
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 14
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
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (13K reputation)

Group: Moderators
Points: 13790 Visits: 1917
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
steventnorris
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 14
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
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (13K reputation)

Group: Moderators
Points: 13790 Visits: 1917
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
steventnorris
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 14
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/. 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
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (13K reputation)

Group: Moderators
Points: 13790 Visits: 1917
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
steventnorris
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 14
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search