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

In the new linked server dialog, choose "Other data source", select "Microsoft OLE DB Provider for SQL Server" as your provider name, then use this as your provider string:

Data Source=192.168.1.100,1433;Network Library=DBMSSOCN;Initial Catalog=yourdbname;User ID=username;Password=password;

Replace the IP and "1433" with your IP and port number.

More info on connection strings: http://support.microsoft.com/kb/238949