April 23, 2014 at 9:02 am
Microsoft SQL Server Enterprise Edition 64 bit: 10.50.1600.1
Windows 7 Enterprise 64 bit (both client and server)
Microsoft Access Database Engine 2010 Redistributable
I'm trying to export the results of a query to an excel spreadsheet using something like
insert into
OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HRD=No;Database=\\client\share\Work\Excel\Test.xls','select f1,f2,f3 from [sheet1$]')
select X, Y, Z from Test
This works fine if I run the query on the Server and, as expected, the file is updated on the client machine.
However if I run the identical query on the client machine, I get the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.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.ACE.OLEDB.12.0" for linked server "(null)".
If I deliberately misspell the excel filename I get the expected message when running on the server, but the above error when run from the client.
I've run the following on the client and the .xls file is visible from the server.
exec master..xp_cmdShell 'dir \\client\share\Work\Excel\Test.xls'
My client machine is at home and I'm working over a VPN. I'm not seeing any other networking issues and can access shares in any direction between client and server. (Granted SQL Server is running under a different account, but it seems to me that the issue is not one of permissions, since SQL Server is able to write to the excel file when the query is run on the server).
I'd appreciate any suggestions or information on where I can look for further information on the errors.
Thanks
Ron
April 23, 2014 at 9:45 am
I've just realised that I spelled 'HRD' incorrectly.
I've changed it to 'HDR' - no difference I still have the same problem.
April 24, 2014 at 11:53 am
I can't help you with a solution but I can tell what the issue is. Kerberos multiple hops. You are starting from your client connecting to server 1 and then trying to connect to server 2(this is where it fails). Any time I need to test something like this, I have to RDC to the originating server and do it. Sorry I can't take you any further but maybe someone here can or talk to the network guys.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
April 24, 2014 at 1:44 pm
MG-148046 is right.
...but I can tell what the issue is. Kerberos multiple hops. You are starting from your client connecting to server 1 and then trying to connect to server 2(this is where it fails
Either Kerberos auth. is not enabled or the Sql server is not delegating credentials to the Access machine. Talk to your network admin, it's a pretty common problem.
I hope it'll work out for you.
April 24, 2014 at 2:17 pm
Many thanks.
For other peoples benefit, the clue was in the SQL Server log file, in my case C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG. This had the following snippet:
The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
It seems that my domain admins need to grant permissions to the account that SQL Server run under.
Anyway, just wanted to mention the SQL Server log file.
Thanks again
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply