How to create Linked Server with non-default port

Question

I want to create a Linked Server in MS SQL Server 2000 to a MS SQL 2005 Server which runs on port x (not the default port 1433). But it doesn't work, as I can't specify the port anywhere!?

Using sqlcmd (specifying port x), I can connect to the server without problems - but I can't set it up as a Linked Server.

How can this be done?

Answer 1

Note that 4-part queries will look similar to this:

  SELECT * FROM [SQLSERVER,14333].[DATABASE].[dbo].[Table1]

Answer 2

I had to do this today as well (add a linked server with non-default port). In my case it was adding a SQL Server 2014 linked server to a SQL Server 2016.

Steps using SQL Server Management Studio:

1. Open SSMS and go to Server Objects > Linked Server > New Linked Server

2. Use this format for the Linked Server ip-address-of-linked-server\instance-name,non-default-port or, 192.168.10.5\dev-sql,25250. Instance name is required only if that instance is not the default instance on target linked server. Also, you can replace ip address by host name if the linked server is on your local network.

3. Select SQL Server for Server Type

4. Add any credentials required to connect using the Security tab
5. Query the new server using the format just like SQLDBA specified above.

Same thing using T-SQL:

EXEC master.dbo.sp_addlinkedserver @server = N'192.168.10.5\dev-sql,25250', @srvproduct=N'SQL Server'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'192.168.10.5\dev-sql,25250',@useself=N'False',@locallogin=NULL,@rmtuser=N'my_username',@rmtpassword='my_pswd'