Job Failure while Trying to Fetch data through Linked Server in SSMS

Question

Hi,

I have created a Linked Server to connect my Server3 with the prod Server. I am basically writing a procedure to fetch the data from prod server to Server3 using linked Server and it seems to be working fine, when executed manually.

Now I have created a SQL Server Agent job to automate the Same, but the job keeps on failing with the error as given below:

Message
Executed as user: NT SERVICE\SQLSERVERAGENT. Named Pipes Provider: Could not open a connection to SQL Server [5]. [SQLSTATE 42000] (Error 5) OLE DB provider "MSOLEDBSQL" for linked server "SERVER\PROD" returned message "Login timeout expired". [SQLSTATE 01000] (Error 7412) OLE DB provider "MSOLEDBSQL" for linked server "SERVER\PROD" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". [SQLSTATE 01000] (Error 7412). The step failed.

I have the named Pipes Enabled and below is what I am using as the Configuration for the Job:

1) The owner of the job is me with Sysadmin Permissions
2) Run as User tab is set to blank
3) In the Linked Server I have created a mapping where under Local Login I have given my user and under the Remote mapping and Creds the credentials of the user having access to prod server have been given
4) Impersonate has been unchecked.
5) Login has been Set to -"Be made using Login's Current Security Context"

I am not sure What I am missing here and Why I am getting this error. I need urgent Help on this.

Thanks in Advance

Answer 1

The error message tells us that the remote server cannot be found. This is a little surprising given that it seems to work when you run the query from SSMS. After all, the point of origin is the same in both cases.

I would try setting up the linked server with FQDN. That is, instead of just

EXEC sp_addlinkedserver 'SERVER3'

do

EXEC sp_addlinkedserver 'SERVER3', '', 'MSOLEDBSQL', @datasrc='server3.domain.com'

I would not be surprised if you get other messages once you are able to reach the server, but let's see.

Answer 2

Hi @Altarian ,

Given that you have tried a lot, my suggestion is as follows, you can try the example like this：

USE AdventureWorks2012 ;    
GO    
EXEC sp_configure 'remote query timeout', 0 ;    
GO    
RECONFIGURE ;    
GO  

For more information, please see: Configure the remote query timeout Server Configuration Option.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @shubham gautam

Sorry for the delay response.
You can check this link which is a similar thread. Hope this can help you.
https://www.sqlservercentral.com/forums/topic/error-using-linked-server-from-sql-agent-job-but-works-from-my-login#post-1983636

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it.