Sql-server – Linking SQL servers error

linked-serversql serverssms

When i try to connect a linked server, i´ll get this error message :

enter image description here

TITLE: Microsoft SQL Server Management Studio

The linked server has been created but failed a connection test. Do you want to keep the linked server?

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Named Pipes Provider: Could not open a connection to SQL Server [5].
OLE DB provider "SQLNCLI10" for linked server "10.237.69.11" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "10.237.69.11" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Microsoft SQL Server, Error: 5)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=10.50.4000&EvtSrc=MSSQLServer&EvtID=5&LinkId=20476

enter image description here

enter image description here

I have tried resolve by steps described in here :

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1121ae61-1224-406a-8b8a-49f236df3edf/cannot-setup-a-linked-server-in-sql-server-get-this-connection-error-message?forum=sqlexpress

But still no change,
I have set TCP/IP protocol enabled in SQL conf. manager for both servers.
I have tried to ping first PC from the second and it worked.
I have set the Incoming TCP rules for 1433 port in firewall for both servers.
I have set the incoming UDP rules for 1434 port in firewall for both servers.
( I did not set the same rules for Outcome ) — could it be the problem ?

Remote connections on both servers are enabled,

I want to connect MS SQL 2008 with MS SQL 2014 could it be problem with different versions ?

Best Answer

Your error here is

server not found

This is because you used @srvproduct=N'SQL Server', in this case you should use SQL Server's pc name in case of default instance, i.e. this connection string would be correct in case of default instance:

EXEC master.dbo.sp_addlinkedserver @server = N'my_pc_name', @srvproduct=N'SQL Server'

or use this one in case of named instance:

EXEC master.dbo.sp_addlinkedserver @server = N'my_pc_name\inst_name', @srvproduct=N'SQL Server'

If you want to use any other name, even just IP (or IP,port in case of named instance, use @provider=N'SQLNCLI':

for default instance 

EXEC master.dbo.sp_addlinkedserver @server = N'my_linked_server_name_as_I_want_it', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'10.237.69.11'

for named instance:

EXEC master.dbo.sp_addlinkedserver @server = N'my_linked_server_name_as_I_want_it', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'10.237.69.11,your_port'

If you want to use GUI (SSMS), or change server name to your pc name, or use another radio button position, for ex. SQL Native Client (other than SQL Server)

Related Question