SQL Server Performance

Linked Server Setup

Discussion in 'SQL Server 2005 General DBA Questions' started by TRACEYSQL, Nov 14, 2007.

  1. TRACEYSQL New Member

    Im following this as i cannot get the linked sever setup
    http://www.databasejournal.com/features/mssql/article.php/3691721
    I have selected the be made using the logins current security context
    as example...Server1 to link to Server2
    Server1 and Server2 has login called test2.
    So i want to put in the
    local login
    test2 and remote login test 2 with pwd and i keep getting this error.
    How to get the be made using the logins current security context to work
    (Will be having 10 different users all coming in to the same linked server2)
    -------------------
    TITLE: Microsoft SQL Server Management Studio
    ------------------------------
    "The linked server has been updated but failed a connection test. Do you want to edit the linked server properties?"
    ------------------------------
    ADDITIONAL INFORMATION:
    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
    ------------------------------
    TCP Provider: An existing connection was forcibly closed by the remote host.
    Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
    OLE DB provider "SQLNCLI" for linked server "10.50.40.157" returned message "Communication link failure". (Microsoft SQL Server, Error: 10054)
    For help, click: http://go.microsoft.com/fwlink?Prod...2&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476
    ------------------------------
    BUTTONS:
    &Yes
    &No
    ------------------------------
  2. chopeen Member

    Server2 is configured to use Windows Authentication.
    You have to either change Server 2 authentication mode or use Windows login (not SQL Server login).
  3. L0st_Pr0phet Member

    I had this problem, are you doing it through SSMS, if so try and add the linked server via TSQL Script!
    eg.EXEC master.dbo.sp_addlinkedserver @server = 'Linkedserver', @provider=N'SQLNCLI', @datasrc=N'PC0001SQL2005',@srvproduct=''

Share This Page