0

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 ?

  • is the password correct? I usually get this error when the password is incorrect. – Edgar Allan Bayron Feb 27 '18 at 12:58
  • Yep, double checked it :) – Adam Feb 27 '18 at 13:05
  • >>>is the password correct<<< has no sense here, the server is not found – sepupic Feb 27 '18 at 13:55
  • Can you connect your remote server using SSMS and the same IP? – sepupic Feb 27 '18 at 13:56
1

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.