Allow Remote Connections & Linked Servers
Got the following question via the North Texas SQL Server UG (http://northtexas.sqlpass.org/) mailing list.
Hi,
I am trying to add linked server in SQL Server 2005 (let say server1)for a another remote server(server2) which is also SQL Server 2005. Do I need to create a login on the server2 in order to use from the server1.
Because after creating linked server when I try to query the server2 from server1 getting the following error.
OLE DB provider "SQLNCLI" for linked server "SQL2K" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "SQL2K" 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 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].
From the surface area configuration I checked the local and remote connections on server1 and server2.
Is that something I am missing?
Also if some one can give me the T-SQL query to add linked server I would appreciate.
Since I saw that error message yesterday I thought I would blog the link to the instructions on how to enable remote connections on SQL Server.
http://msdn.microsoft.com/en-us/library/ms188279.aspx
To answer the persons question here is the syntax for adding a linked server:
http://msdn.microsoft.com/en-us/library/ms190479.aspx
Happy coding.