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,