I have successfully created a linked server in SOL Server 2005 that connects to a remote server in SOL Server 2008 r2. It just stopped working for some reason. After spending a lot of time trying to get it to work I decided to start over from scratch but I am getting the same thing.
Here are my steps:
A. Allow Remote connections on Remote SOL Server that I will be creating a linked Server for:
In Configuration Manager:
1. Made Sure both Browser and SQLExpress Database engine services are running and that Start mode is Automatic.
2. Enabled TCP/IP Protocol and restarted SQLExpress Database engine service
In SOL Server Management Studio:
1. Verified “SQL Server and Windows Authentication Mode” mode is selected in Security properties SQLExpress Instance.
2. Restarted service
B. Create a Linked Server (to Remote server) in SSMS on local server
1. Click New Linked Server
2. On “New Linked Server” General tab:
- Linked Server: TESTLINKEDSERVER
- Provider: Microsoft OLE DB Provider for SOL Server (this always resolves to Native Client)
- Product Name: SOL SERVER
- Data Source: 192.168.4.241\SQLEXPRESS (I can PING 192.168.4.241 from machine where I am setting up the Linked Server)
3. On “New Linked Server” Security tab:
- Under “Local Server to remote server login mappings”: Local Login: sa, Impersonate checked
- Under “For a login not defined in the list above. Connections will”: Be made using this security context: Remote Login: sa, With Password:(verified correct sa password on remote server)
C. Test Connection to new Linked Server (TESTLINKEDSERVER) with following query:
Cast(a.CustomerID as varchar(30)) as CustomerID
Cast(CustomerID as varchar(30)) as CustomerID
note. Query times out with following message:
OLE DB provider "SQLNCLI" for linked server "TESTLINKEDSERVER" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "TESTLINKEDSERVER" 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].
I have checked the sa password and that I can reach the remote server over the network several times...
Can anyone see what I am doing wrong here?