SQL Agent job that executes a distributed query may fail with 65535, 782, or 7437 error messages

This article helps you resolve the problem where the SQL Agent job that executes a distributed query might fail with 65535, 782, or 7437 error messages.

Original product version:   SQL Server
Original KB number:   2492477

Symptoms

A SQL Agent job that executes a distributed (linked server) query might fail with one of the error messages similar to the following, when the owner of the job isn't a member of the sysadmin server role:

• OLE DB provider "<provider name>" for linked server "<Linkedserver Name>" returned message "Login timeout expired".
  OLE DB provider "<provider name>" for linked server ""<Linkedserver Name>" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
  Msg 65535, Level 16, State 1, Line 0
  SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

• Msg 782, Level 16, State 1, Line 0
  SSL Provider: No credentials are available in the security package
  OLE DB provider " <provider name> " for linked server "<Linkedserver Name>" returned message "Client unable to establish connection".

For example, in a SQL Server 2008 environment, the error messages may be similar to the following:

• OLE DB provider "SQLNCLI" for linked server "<Linkedserver Name>" returned message "Login timeout expired".
  OLE DB provider "SQLNCLI" for linked server ""<Linkedserver Name>" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
  Msg 65535, Level 16, State 1, Line 0
  SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

• Msg 782, Level 16, State 1, Line 0
  SSL Provider: No credentials are available in the security package OLE DB provider "SQLNCLI10" for linked server "<Linkedserver Name>" returned message "Client unable to establish connection".

• Msg 7437, Level 16, State 1, Line 3
  Linked servers cannot be used under impersonation without a mapping for the impersonated login.

You might also see the same behavior when using Openquery or when executing a distributed query using impersonation through the Execute as Login T-SQL statement.

Cause

Transact-SQL job step runs as the owner of the job step if the owner of the job step isn't a member of the sysadmin fixed server role. SQL Agent uses Execute as Login to execute the job step under the context of the owner of the job step. You can't use the EXECUTE AS statement across server boundaries. This behavior is by design. For more information, see the following topics in SQL Server Books Online:

• EXECUTE AS (Transact-SQL)
• Extending Database Impersonation by Using EXECUTE AS

Workaround

If a T-SQL job step is owned by a user that isn't part of the sysadmin server role and if the step contains a distributed query, perform the following steps to ensure the jobs or queries don't fail:

1. Create a mapping for each of the job-step owner on the local server to an existing or new login on the remote server.
2. Ensure that the login has sufficient privileges to execute various modules on the remote server that are accessed in the distributed query. For more information, see Linked Server Properties (Security Page).

More information

Sometimes you might notice that queries discussed in either of the scenarios in Symptoms section might run successfully. This usually occurs when the impersonated user had previously logged on to the remote system and the system still has kept open a connection established by the remote user. You shouldn't expect that the query will work all the time.

Steps to reproduce the behavior

1. On your SQL instance, create a linked server to another SQL instance either using SQL Server Management Studio (SSMS) or the following script.

   EXEC master.dbo.sp_addlinkedserver @server = <server name>, @srvproduct=N'SQL Server'
   /* For security reasons the linked server remote logins password is changed with ######## */
   EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=<servername> ,
   @useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
   

2. Execute the following query in SSMS using a login that's a member of sysadmin server role and ensure it works fine.

   SELECT * FROM <servername>.master.sys.sysobjects
   

3. Now change the context of the query to a non-sysadmin account and execute the same query.

   EXECUTE AS login='Domain\Login1'
   GO
   SELECT suser_sname()
   GO
   SELECT * FROM <servername>.master.sys.sysobjects
   GO
   

This step fails with the error mentioned in the Symptoms section.