Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Credentials to Access Network File Expand / Collapse
Author
Message
Posted Friday, December 13, 2013 11:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 3, 2014 8:56 AM
Points: 5, 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.
Post #1522809
Posted Friday, December 13, 2013 11:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:23 PM
Points: 12,927, Visits: 32,330
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1522821
Posted Friday, December 13, 2013 11:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 3, 2014 8:56 AM
Points: 5, 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$]')
Post #1522825
Posted Monday, December 16, 2013 6:57 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #1523198
Posted Monday, December 16, 2013 7:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 3, 2014 8:56 AM
Points: 5, 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?
Post #1523229
Posted Monday, December 16, 2013 8:02 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #1523245
Posted Monday, December 16, 2013 8:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 3, 2014 8:56 AM
Points: 5, 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.
Post #1523280
Posted Monday, December 16, 2013 9:00 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #1523284
Posted Monday, December 16, 2013 10:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 3, 2014 8:56 AM
Points: 5, 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.
Post #1523331
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse