Here are a few methods which we can use to get this information.
Method 1: SQL Server Configuration Manager
Method 2: Windows Event Viewer
Method 3: SQL Server Error Logs
Method 4: sys.dm_exec_connections DMV
Method 5: Reading registry using xp_instance_regread
Method 6: Run cliconfis.exe
Let's see how you can use each of these methods in detail:
Method 1: SQL Server Configuration Manager:
Step 1. Click Start > All Programs > Microsoft SQL Server 2012 > Configuration Tools >SQL Server Configuration Manager
Step 2. Go to SQL Server Configuration Manager > SQL Server Network Configuration >Protocols for <Instance Name>
Step 3. Right Click on TCP/IP and select Properties
Step 4. In TCP/IP Properties dialog box, go to IP Addresses tab and scroll down to IPAllgroup.
If SQL Server if configured to run on a static port it will be available in TCP Port textbox, and if it is configured on dynamic port then current port will be available in TCP Dynamic Portstextbox. Here my instance is listening on port number 61499.
Method 2: Windows Event Viewer:
When SQL Server is started it logs an event message as 'Server is listening on [ 'any' <ipv4> <port number>' in windows event logs. Here <port number> will be actual port number on which SQL Server is listening.
To view this using Event Viewer:
Step 1. Click Start > Administrative Tools > Event Viewer.
Note: If Administrative Tools are not available on Start menu, go to Start > Control Panel >System and Maintenance > Administrative Tools > View event logs
Step 2. Navigate to Event Viewer > Windows Logs > Application
Step 3. Since huge amount of event are logged, you need to use filtering to locate the required logs. Right click on Application and select Filter Current Log...
Step 4. You can filter the events by Event ID and Event source. The event we are interested in has Event ID of 26022, and it's source is SQL Server Instance. You need to filter by both Event ID and SQL Server Instance if you have multiple instances installed, for a single instance you can filter by Event ID only. Click on OK to apply the filter.
Step 5. Once the filter is applied, Locate message 'Server is listening on [ 'any' <ipv4> …'. As we can see from below screenshot that SQL Server Instance is running on TCP Port61499.
Method 3: SQL Server Error Logs:
When SQL Server is started it also logs an message to SQL Server Error Logs. You can search for port number in SQL Server Error Logs by opening SQL Server Error Log in notepad or via T-SQL using extended stored procedure xp_ReadErrorLog as below:
EXEC xp_ReadErrorLog 0, 1, N'Server is listening on', N'any', NULL, NULL, 'DESC'
GO
Or
USE master
GO
xp_readerrorlog 0, 1, N'Server is listening on'
GO
Result Set:
LogDate ProcessInfo Text
2013-03-21 13:34:40.610 spid18s Server is listening on [ 'any' <ipv4> 61499].
2013-03-21 13:34:40.610 spid18s Server is listening on [ 'any' <ipv6> 61499].
(2 row(s) affected)
As we can see from the output that SQL Server Instance is listening on 61499.
Note: This method does not work if SQL Server Error Logs have been cycled. Seesp_Cycle_ErrorLog for more information.
Method 4: sys.dm_exec_connections DMV:
DMVs return server state that can be used to monitor SQL Server Instance. We can usesys.dm_exec_connections DMV to identify the port number SQL Server Instance is listening on using below T-SQL code:
select distinct local_net_address, local_tcp_port,@@SERVERNAME,@@VERSION from sys.dm_exec_connections where local_net_address is not null
OR
SELECT local_tcp_port
FROM sys.dm_exec_connections
WHERE session_id = @@SPID
GO
Result Set:
local_tcp_port
61499
(1 row(s) affected)
As we can see from the output… same as above 
Port number can also be retrieved from Windows Registry database.
We can use extended stored procedure xp_instance_regread to get port number information using below T-SQL code:
DECLARE @portNumber NVARCHAR(10)
EXEC xp_instance_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key ='Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpDynamicPorts',
@value = @portNumber OUTPUT
Note:
SQL 2005
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.<InstanceNumber>\MSSQLServer\
SuperSocketNetLib\TCP\
SQL 2008
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<InstanceName>\MSSQLServer\
SuperSocketNetLib\TCP\
SELECT [Port Number] = @portNumber
GO
Result Set:
Port Number
61499
(1 row(s) affected)
Method 6: Run cliconfis.exe:
Open Run in your system
Type %windir%\System32\cliconfg.exe
Click on ok button then check an TCP ip po-up is open
Highlight TCP/IP under the Enabled protocols window.
Click the Properties button.
Enter in the new port number, then click OK.
Port number can also be retrieved from Windows Registry database.
We can use extended stored procedure xp_in
As we can see … same as above
Note: The above code will only work if SQL Server is configured to use dynamic port number. If SQL Server is configured on a static port, we need to use @value_name = 'TcpPort' as opposed to @value_name = 'TcpDynamicPorts'.
Method 6:TCP Port & IP Address
CREATE TABLE #ipconfig(
captured_line VARCHAR(255)
)
INSERT #ipconfig
EXECUTE xp_cmdshell 'ipconfig /all';
SELECT
LTRIM(RTRIM(CAST(PARSENAME(SUBSTRING(captured_line,40,15),4) AS VARCHAR(4))))+'.'+
LTRIM(RTRIM(CAST(PARSENAME(SUBSTRING(captured_line,40,15),3) AS VARCHAR(3))))+'.'+
LTRIM(RTRIM(CAST(PARSENAME(SUBSTRING(captured_line,40,15),2) AS VARCHAR(3))))+'.'+
LTRIM(RTRIM(CAST(PARSENAME(SUBSTRING(captured_line,40,15),1) AS VARCHAR(3)))) [IP Address]
FROM
#ipconfig
WHERE
captured_line like '%IPv4 Address%';
DECLARE @tcp_port NVARCHAR(5)
EXEC xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',
@value_name = 'TcpPort',
@value = @tcp_port OUTPUT
SELECT @tcp_port [Port]
EXEC master.dbo.xp_cmdshell 'ipconfig'
DROP TABLE #ipconfig
