Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 ACE Driver and Permissions Issue

Author  Topic 

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2013-06-17 : 02:13:17
Okay, so i'm trying to setup some redundancy between two servers, however i have an issue where if i run the below openrowset command on one server, it works fine, but the other refuses to work.

both sql services run under a domain account. (svcsqlengine)
both have the ace 64x driver installed
both have file sharing enabled


if i connect to SQL using windows auth under my login, it wont work. (and i've sure i have access to both directories)

but if i run under SA, it works perfectly. and ideas? see below:

 
if logged in as me (Domain Admin)
from dbserver01
SELECT * FROM openrowset('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=\\dbserver01\c\orders\Template.xlsx;','select * from [''IL Enrollment Log$'']')
--WORKS PERFECTLY

SELECT * FROM openrowset('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=\\dbserver02\c\orders\Template.xlsx;','select * from [''IL Enrollment Log$'']')
--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 '\\dbserver02\c\orders\Template.xlsx'. 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)".

from dbserver02
SELECT * FROM openrowset('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=\\dbserver01\c\orders\Template.xlsx;','select * from [''IL Enrollment Log$'']')
--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 '\\dbserver01\c\orders\Template.xlsx'. 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)".

SELECT * FROM openrowset('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=\\dbserver02\c\orders\Template.xlsx;','select * from [''IL Enrollment Log$'']')
--WORKS PERFECTLY


if logged in as SA
from dbserver01
SELECT * FROM openrowset('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=\\dbserver01\c\orders\Template.xlsx;','select * from [''IL Enrollment Log$'']')
--WORKS PERFECTLY
SELECT * FROM openrowset('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=\\dbserver02\c\orders\Template.xlsx;','select * from [''IL Enrollment Log$'']')
--WORKS PERFECTLY

from dbserver02
SELECT * FROM openrowset('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=\\dbserver01\c\orders\Template.xlsx;','select * from [''IL Enrollment Log$'']')
--WORKS PERFECTLY
SELECT * FROM openrowset('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=\\dbserver02\c\orders\Template.xlsx;','select * from [''IL Enrollment Log$'']')
--WORKS PERFECTLY
edit: moved to proper forum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-17 : 02:19:06
error message suggests a permission issue on xlsx folders for your account.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2013-06-17 : 02:45:49
my account, as well as svcsqlengine account has full permissions, both directly on the file, and the folder the file is in. i can also call xp_cmdshell with a dir command, and see the file in the list that it returns. i can also do a dir >dir.txt with xpcmdshell into the directory.

any other ideas?
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2013-06-17 : 13:24:55
OH! also, if i try and do the SAME Thing from a 3rd machine (my actual client). they all work fine. it's like DBSERVER01 doesnt wanna play nice... any other ideas?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-18 : 00:30:55
was third machine in same domain as dbserver02?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2013-07-02 : 18:47:23
it is indeed. sorry, didnt get an alert to your reply.
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2013-07-11 : 18:12:19
okay, so here's some more info.... i've noticed that if i do the same thing on the same server under two different logins, it doesnt/doesnt work.

if i'm in SSMS, Windows Auth and run:

exec xp_cmdshell 'dir "\\MYSAN\order\IMPORT TEST\FILEIMLOOKINGFOR.txt">"\\MYSAN\order\IMPORT TEST\dir.txt"'
create table #temp (output varchar(2000))
BULK INSERT #temp FROM '\\MYSAN\order\IMPORT TEST\dir.txt' WITH (keepidentity,FIRSTROW = 2,DATAFILETYPE = 'char',FIELDTERMINATOR = '|',ROWTERMINATOR = '\n');


i get: Cannot bulk load because the file "\\MYSAN\order\IMPORT TEST\dir.txt" could not be opened. Operating system error code 5(Access is denied.).
(mind you, the file is DIR file is CREATED, but not read in.


if i switch to SA, it works PERFECTLY. any ideas?


also, if i switch to paths to the actual sql server instead of the SAN, it works under both accounts fine.
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2013-07-11 : 18:22:57
also, i have tons of scripts that write into the folders, just cant read anything into sql under windows auth. s.a. works fine though... it's CRAZY.
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2013-07-11 : 18:24:15
another follow up, i created a random SQL account, and that works fine too...
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-07-11 : 18:35:03
I've run into this a long time ago. Can you validate what account SQL Server is running under?

I believe it is a permissions issue. Like I said it's a been a long time, but I remember something like the SQL Server is running under an accout that has permissions to that share. But, when you run with windows Auth, the share is considered a different server so you run into a double-hop permissions issue.
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2013-07-11 : 22:54:16
yeah, sql server is it's own service account (svcsqlengine) and i'm using windows auth from a client (albert.kohl) any ideas on how to deal w/ the double hop problem? seems like i have to setup delegation, but i'm not having much luck.
Go to Top of Page
   

- Advertisement -