Select the product you need help with

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

Article ID: 2492477 - View products that this article applies to.

Symptoms

A SQL Agent job that executes a distributed (linked server) query may fail with one of the error messages similar to the following, when the owner of the job is not 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 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 may also see the same behavior when using Openquery or when executing a distributed query using impersonation through "Execute as Login" T-SQL statement.

Back to the top | Give Feedback

Cause

Transact-SQL job step runs as the owner of the job step if the owner of the job step is not 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 cannot use EXECUTE AS statement across server boundaries. This behavior is by design. For additional information refer to the following topics in SQL Server Books Online:

EXECUTE AS (Transact-SQL)
(http://msdn.microsoft.com/en-us/library/ms181362.aspx)
Extending Database Impersonation by Using EXECUTE AS
(http://msdn.microsoft.com/en-us/library/ms188304.aspx)

Note: The same cause applies to the scenario wherein you manually try to change the execution context of a distributed query in management studio using EXECUTE AS statement.

Back to the top | Give Feedback

Workaround

Important: The following workaround requires you to define an explicit local server login to remote server login mappings using the Security page under Properties of the linked server object. Since the Remote User column must be a SQL Server Authentication login on the remote server, the remote server’s authentication mode should either already be set to Mixed mode or should be changed to Mixed mode before using the workaround discussed below.

If a T-SQL job step is owned by a user that is not part of sysadmin server role and if the step contains a distributed query take the following steps to ensure the jobs or queries do not fail:

Create a mapping for each of the job step owner on the local server to an existing or new login on the remote server.
Ensure that the login has sufficient privileges to execute various modules on the remote server that are accessed in the distributed query.

For additional information refer to the following topic in SQL Server Books Online:

Linked Server Properties (Security Page)
(http://technet.microsoft.com/en-us/library/ms188477.aspx)

Back to the top | Give Feedback

More information

Sometimes you may notice that queries discussed in either of the scenarios in Symptoms section may 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 use. You should not expect that the query will work all the time.

Steps to reproduce the behavior

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

Execute the following query in SSMS using a login that is a member of Sysadmin server role and ensure it works fine.
select * from <servername>.master.sys.sysobjects
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 of the article.

Back to the top | Give Feedback

Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use

(http://go.microsoft.com/fwlink/?LinkId=151500)

for other considerations.

Back to the top | Give Feedback