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)
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'