Nakul's Blog

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Blogs

Upload Image

Close it

Select File

Nakul Vachhrajani is a Technical Specialist & Systems development professional with iGATE. He holds a MCTS (SQL Server 2008: Implementation & Maintenance)


	Like

Invite Friends

Browse by Tags · View All

#SQLServer	234
SQL Server	233
Administration	200
DBA	189
Tips	178
Development	178
T-SQL	173
#TSQL	171
Guidance	115
Tools and Utilities	112

Archive · View All

April 2011	14
March 2012	11
December 2011	11
March 2011	11
December 2012	10
October 2011	10
January 2011	10
January 2013	9
November 2012	9
October 2012	9

Home · Posts · Most Liked · Most Discussed · Ask a Question · Fans (246) · Preferences

#0152–SQL Server-Connecting to a SQL Sever named instance using the IP address and TCP/IP port number

Apr 16 2012 12:00AM by Nakul Vachhrajani

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.

Tags:

Nakul Vachhrajani

4 · 33% · 10575

Liked

Lifesaver

Refreshed

Learned

Incorrect


Like this
Post this to my facebook wall
Tweet this
This was a true life saver
Thanks, this helped me to refresh my memory
Very informative, I just learned something new
I disagree, this information is incorrect


	Submit

2 Comments

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.

commented on May 2 2012 10:44AM

Marc Jellinek

97 · 2% · 546

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"! :)

commented on May 2 2012 11:51AM

Nakul Vachhrajani

4 · 33% · 10575

Your Comment

Sign Up or Login to post a comment.

"#0152–SQL Server-Connecting to a SQL Sever named instance using the IP address and TCP/IP port number" rated 5 out of 5 by 3 readers +Nakul Vachhrajani