September 27, 2011 at 4:45 am
Hi all,
I am stuck since yesterday morning with a stupid problem on which I hope you can help me.
I can't run the following query from my workstation:
SELECT * FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0', 'Excel 5.0; Database=\\server\Data\test\file.xls;HDR=Yes', 'Select * from [Sheet1$]' )
The error message says:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
Running following query on my workstation works fine:
SELECT * FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0', 'Excel 5.0; Database=c:\test\file.xls;HDR=Yes', 'Select * from [Sheet1$]' )
Please note that the file is located here elsewhere.
If I run both queries with a remote access to the sql server, everything runs just fine.
I am administrator from the domain, the user running the SQL Server service is Administrator of the machine and has full access on the database.
Allow Inprocess is checked for the Provider Microsoft.Jet.OLEDB.4.0.
The only difference is that once it is run from my workstation via SSMS and the other time it is run from my workstation through remote access on the Sql server via SSMS as well.
Any idea where the problem could hide?
Many thanks for your help ...
September 29, 2011 at 7:18 am
Nobody knows how to help me?
Please tell me if I am on the wrong forum ... Thanks
September 29, 2011 at 7:23 am
First, permissions. Does the SQL service account have access to the UNC path?
Second, is the file open in another process? I've seen that error when I accidentally left the file open in Excel.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 29, 2011 at 8:03 am
Thanks for your answer Gsquared.
1. Yes, the user under which the service runs has access to the UNC path
2. No, the file isn't opened in another application, nor is it locked by any process
September 30, 2011 at 7:19 am
What about Kerberos? What do you have set up to handle the double-hop security token?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 30, 2011 at 7:26 am
To be honest, that's Chinese to me 😉
Another department handles all the networks and their setup (although I am Domain Admin)
Can you refine your question, so I can try to pass it onto this department? Thanks!
September 30, 2011 at 7:44 am
I guess instead of excel 5.0
it should be excel 8.0 version
btw why can't you use SSIS/Import export wizard for this?
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
September 30, 2011 at 7:49 am
Windows integrated security can only pass credentials to one other computer, not pass through one to another.
So, if you connect from your workstation to a server, your workstation passes credentials to the server. That's "single hop" or "single step".
If you connect from your workstation to a server, and then tell the server to connect to another server, that's "double hop" or "double step". The server-to-server connection will pass "NT AUTHORITY\ANONYMOUS LOGON" as the credential, not your credentials, and not the SQL credentials. Kerberos is a way to handle that. It's not a simple subject, so I won't even try to go over it here, but its basic function is to persist credentials across multi-hop connections.
Hence, telling the server, or workstation, to connect to a local drive, C: in this case, is single-hop, and doesn't require kerberos or anonymous login permissions, but telling it to connect from your workstation, to the database server, and then to a UNC path that probably connects to a different server, is double-hop, and either needs kerberos, or for the anonymous login to have access to the UNC destination.
If you set up the connection to run in an SQL Agent job, then it again becomes single-hop, because it's the database server connecting to the UNC destination, and Windows will pass credentials without difficulty. (Well, assuming both are on the same domain and all the usual restrictions on that.)
Does that at least help clarify the problem?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 30, 2011 at 7:51 am
All that is assuming the SQL engine you're connected to isn't local. If you have SQL Server on your workstation and are running the query on a "(local)" connection, then kerberos isn't involved. Is that the case, or was my assumption correct that you're connected to a server and running the query on that?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 30, 2011 at 7:57 am
Thanks for your hints Sushant
SKYBVI (9/30/2011)
I guess instead of excel 5.0it should be excel 8.0 version
I already tried this out ... it didn't help 🙁
SKYBVI (9/30/2011)
btw why can't you use SSIS/Import export wizard for this?
SSIS is not a solution for now in our environment
Thanks for your answer ...
September 30, 2011 at 8:09 am
Your first assumption is correct Gsquared. It is not a local SQL server.
Your explanations about multi-hops are very clear! No need to dig into kerberos, I fully understand what you mean and I think this can be the problem. I will check with the people in charge of the networks and the servers to see if multi-hops are possible/allowed as single-hops work from my WS or the SQL server.
Again many thanks for your help and clear explanations.
I'll keep you informed ...
September 30, 2011 at 8:10 am
[double post deleted]
January 13, 2016 at 1:42 pm
GSquared (9/30/2011)
...So, if you connect from your workstation to a server, your workstation passes credentials to the server. That's "single hop" or "single step"....but telling it to connect from your workstation, to the database server, and then to a UNC path that probably connects to a different server, is double-hop, and either needs kerberos, or for the anonymous login to have access to the UNC destination.
10 years of working with SQL Server and 10 years of logging into the server directly just so I can run an OpenRowSet command that uploads my XLS and XLSX templates. I sure wish I would have found this and understood it back in 2011 when it was posted. I've tried many times to use the server query on my local workstation and couldn't get it to work. Both computers have "P" drive mapped to the same share and due to my lack of network understanding, I just assumed if "My Computer" can show it, then it must be a valid path. I always assumed it was an OLEDB issue and never considered the credentials, much less double-hop credentials. Gotta love these SSC forums!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply