none
Linked Server Problem while SQL Data Transfer using Non-sys Admin Account

    Question

  • Hi Team,

    I've an issue while transferring the data from ServerA to ServerB. I've made the ServerB as "Linked Server" to Server A. Pre requisite like:

    1) SQL Account is been created on ServerB.

    2) Timeout settings for remote connections is set to "No Timeout".

    When I execute the below script using Query Window, It executes successfully:

    Insert into ServerB.DBName.dbo.TableName1
    Select * from dbo.TableName1

    But when I execute the same step by creating a SQL Job, it fails with the below error message:
    Message
    Executed as user: DomainName\UserName. 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 "ServerB" 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.

    Could you please help me in fixing the above error message and I can transfer the SQL Data between 2 Servers.

    Thank You


    Sridhar D Naik

    Tuesday, June 25, 2013 10:02 AM

All replies

  • first of all, can you be able to "Test Connection" your server b?


    Srinivasan

    Tuesday, June 25, 2013 10:27 AM
  • Hi Sridhar,

    It seems that your Server2 is not reachable from Server1.

    If is unreachable and remote connection is not enable, you need to follow steps in mentioned this blog post from Pinal.

    Blog -> http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

    You also need to check whether test connection from linked server is succeeding or not. (To check from ssms, Select the linked server - > Right click -> Test Connection).

    One thing i am not sure if you are using the sql account to connect using the linked server, why it is showing the account as window account in error message. Can you please check you are using the sql account to connect to the linked server not the windows account.


    Regards Harsh

    Tuesday, June 25, 2013 10:28 AM
  • Hi Harsh,

    The test connection to the Linked Server is success!!!

    I've logged into the ServerA using Windows Authentication and executing the script.


    Sridhar D Naik

    Tuesday, June 25, 2013 10:50 AM
  • Sridhar

    If possible, can you paste the security tab of SERverB linked Server?

    What kind authentication (windows or sql) mapping you are using?


    Srinivasan

    Tuesday, June 25, 2013 10:58 AM
  • Hi Sridhar,

    Can you Connecting into Server B from ServerA using ssms, Does it allow to connect and allow you to execute the query?


    Regards Harsh


    • Edited by Harsh Kumar Tuesday, June 25, 2013 11:21 AM Updated
    Tuesday, June 25, 2013 11:20 AM
  • Hi Harsh,

    Yes I am logging to ServerA using Windows Authentication and Can execute the statement from a query window to transfer the data from ServerA to ServerB(Linked Server).

    But when I try to execute the same steps by creating a Job, it throws the above error message.


    Sridhar D Naik

    Tuesday, June 25, 2013 12:06 PM
  • Hi Sridhar,

    First, please map the login “DomainName\UserName” (in the error message) with a security account on a remote server who has permission to access dbo.TableName1 on the linked server. Additionally, you can refer to the steps on the following document.

    sp_addlinkedsrvlogin (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms189811(v=sql.105).aspx

    SQL Server Agent job fails when the job uses a linked server and the job owner is not a system administrator
    http://support.microsoft.com/kb/811031/en-us 

    Allen Li
    TechNet Community Support

    Wednesday, June 26, 2013 7:52 AM
    Moderator