Perhaps one of the most common questions on the forums is “Can I connect to my SQL Sever named instance using the IP address and/or TCP/IP port number?”. It was this question that prompted me to write today’s post.
Last week, I wrote about how to use SQL Server utilities to determine the port number on which the SQL Server service is listening for connection requests. I will be using this information as base to start off today.
Assumptions & Disclaimer:
- This post demonstrates an administrative procedure that deals with SQL Server instance security. Please do not perform the steps outlined in this post without consulting your database administrator
- It is assumed that the SQL Server under question is configured for accepting remote connections via TCP/IP protocol
Connecting to a named instance using a TCP/IP address and port number
Any SQL Server instance, named or default, can be connected to by using the TCP/IP address and the port number, provided it is configured to allow remote connections. All one needs to do is:
- Determine the TCP/IP port at which the SQL Server instance is listening on by using one of the methods shown here
- You can get information about the hosts’ IP address can be available by using the “ipconfig” command line utility.
- Once the IP address and the TCP/IP port number are available, launch the SQL Server Management Studio
- In the “Connect to Server” dialog box, mention the server name in the following format:
- <IP address>,<TCP/IP port number>
- Click on “Connect” and notice that the SSMS is now connected to the desired SQL Server instance by using just the IP Address and the TCP/IP port number
I hope that this post will be helpful to all those who are looking for solutions on how to use the TCP/IP address and port number to connect to a specific SQL Server instance. Please note that using IP addresses and port numbers may not be a successful connection strategy if the server is on a DHCP network (where the IP address may change with each restart).
Until we meet next time,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 
> You can get information about the
> hosts’ IP address can be available by
> using the “ipconfig” command line
> utility
If you can connect to a SQL Server instance by name, then the name is registered within DNS. Just ping the name and you’ll be able to determine the IP address of the database server. No reason to run ipconfig on the server itself.
In order to determine the port the server is using to listen for incoming connections, run the following query (requires VIEW SERVER STATE permissions)
SELECT
[c].[local_net_address] as [server_ip_address],
[c].[local_tcp_port] as [server_ip_port]
FROM
[sys].[dm_exec_connections] [c]
WHERE
[c].[session_id] = @@SPID
> Please note that using IP addresses
> and port numbers may not be a
> successful connection strategy if the
> server is on a DHCP network (where the
> IP address may change with each
> restart).
Servers should have static IP addresses.
LikeLike
Absolutely, Marc – I agree that servers should have static addresses. The reason I have put that line is that I have seen production servers using DHCP. All it takes is one restart of the DHCP server itself for the novice IT team to realize they are in deep trouble. In fact, I always prefer connecting to SQL Servers using the fully-qualified instance name, and not through the IP address.
Thanks for reminding about the “ping”! 🙂
LikeLike