1

I have successfully added, linked, and queried other sql servers from linux sql server 2017, but only with the default instance name. (no instance name)

I cannot connect any other specific instance named servers. (it's ok from windows sql server2016). it looks like the backslash + instance name is considered as the hole hostname !? I tried with double //, \ \ or / ...

is it a known issue or I am doing something wrong ?

I have SSMS 14.0.17119.0 on Windows 8.1 Pro,

Microsoft SQL Server 2017 (CTP2.1) - 14.0.600.250 (X64) May 10 2017 12:21:23 Copyright (C) 2017 Microsoft Corporation. All rights reserved. Developer Edition (64-bit) on Linux (Ubuntu 17.04)

I tried with the user inteface on SSMS, and with T-SQL Exec master.dbo.sp_addlinkedserver ...

and receive this error message which I beleive is not the real error:

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

------------------------------

SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. 
OLE DB provider "SQLNCLI11" for linked server "SAGE-10\L1000" returned message "Login timeout expired".
OLE DB provider "SQLNCLI11" for linked server "SAGE-10\L1000" 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: -1)
jjdesign
  • 344
  • 2
  • 8

2 Answers2

4

I had the same issue. Luckily in my case the linked instance had been set up to listen to another static tcp port. So I could use that as a workaround.

If you have to option to change the server you are linking to, you can set the tcp port in SQL Server Configuration manager - Network configuration - Protocols for INST2 - TCP/IP - properties - IP Addresses - TCP port.

After that I could add the linked server like this. Port number is 1435.

EXEC master.dbo.sp_addlinkedserver @server = N'Server20_inst2', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'192.168.1.112,1435', @catalog=N'Test'

EXEC master.dbo.sp_addlinkedsrvlogin 
 @rmtsrvname=N'Server20_inst2', @useself=N'False', @locallogin=NULL, @rmtuser=N'sa', @rmtpassword='##########'
pojanisu
  • 41
  • 5
1

When you try to connect to SQLEXPRESS often port is set randomized. In my instance is set to 61423. To verify port number on server you can do this:

SELECT DISTINCT local_net_address, local_tcp_port FROM sys.dm_exec_connections

Then for example:

EXEC master.dbo.sp_addlinkedserver @server = N'Server20_inst2', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'192.168.1.112,61423', @catalog=N'Test'
KriSS
  • 26
  • 3