SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 ACE Driver and Permissions Issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

albertkohl
Aged Yak Warrior

USA
740 Posts

Posted - 06/17/2013 :  02:13:17  Show Profile  Visit albertkohl's Homepage  Reply with Quote
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

India
52317 Posts

Posted - 06/17/2013 :  02:19:06  Show Profile  Reply with Quote
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

USA
740 Posts

Posted - 06/17/2013 :  02:45:49  Show Profile  Visit albertkohl's Homepage  Reply with Quote
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

USA
740 Posts

Posted - 06/17/2013 :  13:24:55  Show Profile  Visit albertkohl's Homepage  Reply with Quote
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

India
52317 Posts

Posted - 06/18/2013 :  00:30:55  Show Profile  Reply with Quote
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

USA
740 Posts

Posted - 07/02/2013 :  18:47:23  Show Profile  Visit albertkohl's Homepage  Reply with Quote
it is indeed. sorry, didnt get an alert to your reply.
Go to Top of Page

albertkohl
Aged Yak Warrior

USA
740 Posts

Posted - 07/11/2013 :  18:12:19  Show Profile  Visit albertkohl's Homepage  Reply with Quote
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

USA
740 Posts

Posted - 07/11/2013 :  18:22:57  Show Profile  Visit albertkohl's Homepage  Reply with Quote
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

USA
740 Posts

Posted - 07/11/2013 :  18:24:15  Show Profile  Visit albertkohl's Homepage  Reply with Quote
another follow up, i created a random SQL account, and that works fine too...
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/11/2013 :  18:35:03  Show Profile  Reply with Quote
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

USA
740 Posts

Posted - 07/11/2013 :  22:54:16  Show Profile  Visit albertkohl's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000