none
SQL Server 2005 agent job executing a stored procedure and receiving login timeout error [SQLSTATE 01000] (Error 7412)

    Question

  • I have a sql agent job executing on ServerA, one of the steps executes a stored procedure in ServerA.DatabaseA. The stored procedure then reaches across the wire to ServerB via a linked server, and tries to select data from ServerB.DatabaseB.SchemaB.TableB. When it does this I get the following error message:

     

     

    Message

    Executed as user: <domain>\<user>. Named Pipes Provider: Could not open a connection to SQL Server [1450]. [SQLSTATE 42000] (Error 1450)  OLE DB provider "SQLNCLI" for linked server "ServerB" returned message "Login timeout expired". [SQLSTATE 01000] (Error 7412)  OLE DB provider "SQLNCLI" for linked server "<remote server>" 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.". [SQLSTATE 01000] (Error 7412).  The step failed.

     

    Anybody have any ideas?

    ServerA: Microsoft SQL Server 2005 - 9.00.4035.00 (X64)   Nov 24 2008 16:17:31   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) 

    ServerB: Microsoft SQL Server 2005 - 9.00.5000.00 (X64)   Dec 10 2010 10:38:40   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) 

    Wayne E. Pfeffer

    Friday, February 25, 2011 8:23 PM

Answers

  • In order to get a scenario such as yours to work in my test environment, I configured everything as per Case 3 in the link previously provided.

    • Create a SQL Server Login, with NON-SYSADMIN rights i.e. just the rights required to execute the procedure on Server A.
    • Make the SQL Server Login the "Owner" (not RunAs, that is for proxy Accounts) of the SQL Server Agent job.
    • Create a SQL Server Login with the same name on Server B that has access to the tables/objects referenced in the procedure on Server A.
    • Map the SQL Server Login from Server A to Server B in the Linked Server Connection configuration and test independently of the SQL Server Agent job to validate security and access.
    • Now run via the SQL Server Agent job on Server A.


    John Sansom | SQL Server DBA Blog | Twitter
    • Marked as answer by wpfeffer Monday, February 28, 2011 5:44 PM
    Saturday, February 26, 2011 10:02 AM

All replies

  • Has Remote Connections been enabled on the target server?

    Also have you validated the Linked Server connection using the relevant execution context? i.e. that of the SQL Server Agent service account.

    Take a look at the excellent SQL Server Agent jobs and user contexts to see how execution context work via SQL server agent jobs.


    John Sansom | SQL Server DBA Blog | Twitter
    Friday, February 25, 2011 8:40 PM
  • Thank you for the quick reply. I checked the context and the user that the step is executing as:

     

    OriginalLogin         Effective_login       Db_user             

    -------------------- -------------------- --------------------

    <domain>\<agent service> <domain>\<job owner>     dbo

     

    The <job owner> is also the account I have set as the 'Run as user' on the 'Advanced' tab of the 'Job Step Properties' page. I had tried a few different login configurations and finally got even more frustrated with this and assigned the <job owner> login to the serveradmin and sysadmin roles. I am still receiving the same error:

    Msg 1450, Sev 16, State 1: Named Pipes Provider: Could not open a connection to SQL Server [1450]. [SQLSTATE 42000]
    
    Msg 7412, Sev 16, State 1, Line 87 : OLE DB provider "SQLNCLI" for linked server "<remote server>" returned message "Login timeout expired". [SQLSTATE 01000]
    
    Msg 7412, Sev 16, State 1, Line 87 : OLE DB provider "SQLNCLI" for linked server "<remote server>" 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.". [SQLSTATE 01000]


    The remote server is configured to accept connections and the login is configured on the remote server to be able to connect and the login is enabled. The login is the same on both servers and configured the same. 

    Thanks again for any help.

    Wayne E. Pfeffer

    Friday, February 25, 2011 11:08 PM
  • In order to get a scenario such as yours to work in my test environment, I configured everything as per Case 3 in the link previously provided.

    • Create a SQL Server Login, with NON-SYSADMIN rights i.e. just the rights required to execute the procedure on Server A.
    • Make the SQL Server Login the "Owner" (not RunAs, that is for proxy Accounts) of the SQL Server Agent job.
    • Create a SQL Server Login with the same name on Server B that has access to the tables/objects referenced in the procedure on Server A.
    • Map the SQL Server Login from Server A to Server B in the Linked Server Connection configuration and test independently of the SQL Server Agent job to validate security and access.
    • Now run via the SQL Server Agent job on Server A.


    John Sansom | SQL Server DBA Blog | Twitter
    • Marked as answer by wpfeffer Monday, February 28, 2011 5:44 PM
    Saturday, February 26, 2011 10:02 AM
  • Thanks for replying. I followed the above steps, but things still aren't working. I did a 'run as' on management studio as the <domain>/<user> I set up in the databases and began running the job process by hand. I am able to log into both servers from inside SSMS as the user. I am able to execute all of the job steps except for the one giving me problems. If I pull out just the one update statement and execute it by it self, I get the same error.

    (The error I'm getting inside SSMS is:

    Msg 18456, Level 14, State 1, Line 1 

    Login failed for user '<domain>\<user>'

    )

    I have also tried just executing a simple select statement on ServerA that reaches across to ServerB, and I get the above error, but if I change the connection of the window from ServerA to ServerB, it will execute just fine. Does the login need some special permission like the ability to interact with the desktop or to login as a service in order for this to work?

    Thanks again for your help.

    Wayne E. Pfeffer

    Monday, February 28, 2011 5:02 PM
  • I was speaking with our sys admin and he thought it might be a problem with security delegation at the OS level. It wasn't that, but as he was getting ready to work on that train of thought, I went through and reset the linked server definition on ServerA for ServerB from defining the mapping to having no mapping. Now here is where things get hinky ... it works. I can now perform the selects, updates and execute the sproc without a problem. I have _no_ idea what just happened, but it works. Do you have any idea?

    Thank you for all of your help.

    Wayne E. Pfeffer

    Monday, February 28, 2011 5:43 PM