CODEWITHSUNDEEP
sqlsql-servert-sql
räph
räph

Reputation: 3684

How to create Linked Server with non-default port

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?

Upvotes: 12

Views: 43600

Answers (5)

Shane Castle
Shane Castle

Reputation: 1759

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

Upvotes: 9

joym8
joym8

Reputation: 4232

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.

example screenshot

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'

Upvotes: 1

smoore4
smoore4

Reputation: 4866

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

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

Upvotes: 1

NightShovel
NightShovel

Reputation: 3252

Based on Shane's suggestion, adding an alias worked for me.

(SQL Server 2008 R2):

  • Open SQL Server Configuration Manager
  • Expand "SQL Server Configuration Manager (local)"
  • Expand "SQL Native Client 10.0 Configuration (32bit)"
  • Click "Aliases"
  • Right-click in the right-side alias list, and choose "New Alias"
  • "Alias Name" is whatever you want to reference the linked server as.
  • "Port No" is your non-default port.
  • "Protocol" depends, but you can most likely leave this as "TCP/IP".
  • "Server" is the address of the server you're trying to connect to (not including port).

(repeat the steps for "SQL Native Client 10.0 Configuration" (minus the '32bit' text))

Adding an alias this way allowed me to add a linked server with the Server Type as "SQL Server", without configuring provider options, etc.

Upvotes: 7

Charles Fleming
Charles Fleming

Reputation: 141

Another way to achieve this (assuming that you have set up 8080 in SQL Server) is with the following code:

EXEC sp_addlinkedserver 'myserver', '', 'SQLNCLI', 'xx.xx.xx.xx,8080'

EXEC sp_addlinkedsrvlogin 'myserver', 'FALSE', NULL, 'user', 'pwd'

Upvotes: 14

Related Questions