#0151 – SQL Server– Which TCP/IP port is my SQL Server listening on?


Some questions never cease to come up on various forums. The most common of them seems to be: What TCP/IP port is my SQL Server listening on? In today’s post, I will quickly demonstrate two simple ways to answer this question.


Assumptions & Disclaimer:



  1. 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
  2. It is assumed that the SQL Server under question is configured for accepting remote connections via TCP/IP protocol

Method 01: Using the SQL Server Configuration Manager


Launch the SQL Server configuration manager using one of the following ways:



  1. Go to Start –> Programs –> Microsoft SQL Server 2012 –> Configuration Tools –> SQL Server Configuration Manager
  2. Go to Start –> Run –> “SQLServerManager11.msc”


















The SQL Server Configuration Manager would look something like that demonstrated in the screenshot below:
image
Navigate to the “SQL Server Network Configuration” node and choose to see the information about the Protocols for your SQL Server instance.
image
Double-click on the “TCP/IP” protocol (or right-click and choose “Properties”)
image
Navigate to the section “IPAll” of the “IP addresses” tab.
The value of “TCP Dynamic Ports” is the port that this SQL Server instance is listening on
image

Method 02: Reading the SQL Server Error Log


The second method that I am about to demonstrate now, involves reading the SQL Server error log through the SQL Server Management Studio. This can be achieved using T-SQL queries also, and if you are interested, do leave a comment and I will cover that in one of my future posts.











Login to the SQL Server Management Studio (SSMS) and navigate to the Management –> SQL Server Logs node
image
Open the most current log by double-clicking on the “Current” node.
Scroll down through the event log untill you see an entry that reads something similar to:
image

This entry contains the port number on which the SQL server is listening to.


I hope you found this post useful. In my next post, I tackle one of the other most frequently asked questions – “Can I connect to my SQL Sever named instance using the IP address and/or TCP/IP port number?


Until we meet next time,


Be courteous. Drive responsibly.

Advertisements

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s