Linked Server from SQLExpress to Excel - fails in job but works on PC
-
I set up a linked server on my Localhost instance of SQL Server to import an Excel workbook on the network. I used an SSMS T-SQL query. It works fine. I use Windows authentication on my machine which gets me into SQL Server and gives me access to the network folder. However, when I put it into a job and invoke the job, it fails. I get the following error:
07/21/2010 12:37:32,Import_MSDN,Error,0,OPTI745-23845D1,Import_MSDN,(Job outcome),,The job failed. The Job was invoked by User NOAM\X649390. The last step to run was step 1 (Step1).,00:00:01,0,0,,,,0
07/21/2010 12:37:33,Import_MSDN,Error,1,OPTI745-23845D1,Import_MSDN,Step1,,Executed as user: NT AUTHORITY\NETWORK SERVICE. The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MSDN_IMPORT" reported an error. The provider did not give any information about the error. [SQLSTATE 42000] (Error 7399) Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MSDN_IMPORT". [SQLSTATE 42000] (Error 7303). The step failed.,00:00:00,16,7303,,,,0
Frankly, I am getting tired of linked server errors. I can easily get them to work on my Localhost version of SQL Server when I run it from SSMS, but as soon as I put the query on a server or in a job on my PC, it fails. Microsoft needs to change something here or at least document it better.
-
Just in case, you probably already checked this, but verify that the Agent service account has permissions to the same stuff that you do. It's possible that permissions are different. Also realize that the Agent will look at its own local drives for Excel files, and if they are not there and you didn't use UNC paths, the provider won't be able to find the Excel file. Did you set up the linked server in such a way so that the path is available on the remote SQL server?
-
Thank you for helping out. Here is my linked server script:
EXEC master.dbo.sp_addlinkedserver @server = N'MSDN_IMPORT', @srvproduct=N'Excel', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'\ccicmshr001\desktop services\DTS\Software Reporting\MSDN\MSDN user list for HR authentication.xls', @provstr=N'Excel 8.0; IMEX=1;'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MSDN_IMPORT',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
I didn't add the linked server login; it was done by default like this. I don't understand about the Agent service account and how to find out its permissions. How do I do that?
-
the security part that is tripping you up is this part:
@useself=N'True'
that means when you select from the linked server, your sql login is being used as the credentials for the linked server.
but as you can see from the error message, the access to the linked server is denied for NT AUTHORITY\NETWORK SERVICE, which is the account used for running jobs an accessing items outside of SQL, like local harddrives, network shares, etc.
you will need to change the linked server to use a different user when NT AUTHORITY\NETWORK SERVICE tries to access the linked server: i put sa in for the screenshot and testing, but you should pick a different user than that.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply