SQL Agent Job failing

Question

I have a BizTalk Server that has been configured per the instructions for BizTalk disaster recovery using the BizTalk Log Shipping implementation.

On the backup SQL Server, call it SQL02, the "Get Backup History" job fails constantly with the following error:

Executed as user: DOMAIN\User. SQL Server Network Interfaces: Error getting enabled protocols list from registry [xFFFFFFFF]. [SQLSTATE 42000] (Error 65535) OLE DB provider "SQLNCLI10" for linked server "SQL01" returned message "Login timeout expired". [SQLSTATE 01000] (Error 7412) OLE DB provider "SQLNCLI10" for linked server "SQL01" 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.

This job executes a local stored procedure, which basically executes another stored procedure on a linked server, called SQL01.

When i execute this stored procedure in a query window on SQL02:
EXEC [SQL01].[BizTalkMgmtDb_Prod].[dbo].[sp_GetBackupHistory]
it executes without any errors.

When the SQL agent job attempts to execute the same query it fails with the error above.

Why can i execute the procedure manually and the sql agent job cannot?

Answer 1

check the sql agent and sql instance users it should have the rights and the users should be common for every instance or must have all the required rights.

In query you connect through a user which has the rights but the sql agent user does not have it (as per my understanding).

check your sql server configuration manager for checking users for services of sql server.