The answer to the question which port is used by SQL Server? seems easy. By default it is 1433 but there are exceptions from this rule. If more than one SQL Server instance is needed on the same machine, the easiest option is to use named instances. Each of them work on a different port so only one can use 1433 or even neither of them! In this article, I will present a few methods of finding out which port is used by a particular SQL Server instance. They require different level of access and various tools so choose one that works for you.
Why would I need to know the port?
As mentioned in the preface, one SQL Server installation can have multiple instances configured and all of them are hosted on one operating system. SQL Server Management Studio can connect to them by using an address built like this: <host name>/<instance name> e.g. DBAPRESENTS\DEV01. This way does not require knowing the port number but it does not work in all cases. Some programs do not allow using this address format. Network and firewall rules may block it - the initial phase of the connection happens on port 1433 then it is switched to a proper port for the instance. If the firewall blocks port 1433, the switch will not happen either because the application will not know the instance port number. Connection will fail even if the instance is up and running.
As some reasons for knowing the port number are clarified, a few ways of finding it are described below.
Configuration Manager
If you have access to the operating system that hosts SQL Server, Configuration Manager can be the first place to go.
All Programs -> Microsoft SQL Server 20XY -> Configuration Tools -> SQL Server Configuration Manager.
If Configuration Manager does not exist on this path, look for SQLServerManager12.msc file (for SQL 2014), SQLServerManager11.msc (for SQL 2012) or other.
Expand SQL Server Network Configuration, choose Protocols for the instance and double click TCP/IP. A window with properties should pop up. Find a value in TCP Dynamic Ports or TCP Port. This should be the port on which the instance is listening.
SQL Server error log
Find SQL Server error log file e.g. ERRORLOG in <SQL Server main dir>/<instance name>/MSSQL/Log/. Open the file in a text editor and search for listening keyword.
SQL Server reports the port number to the error log when it starts. Of course, the same log can be opened from SQL Server Management Studio instead of a file editor.
dm_exec_connections view
If you do not have access to the operating system but you can connect to the database instance, you can try querying a dynamic view - dm_exec_connections.
SELECT *
FROM sys.dm_exec_connections
WHERE local_net_address IS NOT NULL
Unfortunately, it does not always show the port but it is worth trying.
Those are only a few methods of finding the port used by a SQL Server instance. There are more but at least one of them should be helpful.
