How to find the port for MS SQL Server 2008?

Question

I am running MS SQL Server 2008 on my local machine. I know that the default port is 1433 but some how it is not listening at this port. The SQL is an Express edition.

I have already tried the log, SQL Server Management Studio, registry, and extended stored procedure for finding the port. But, I could not find it. Please help me. Thanks.

Answer 1

Click on Start button in Windows.

Go to All Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager

Click on SQL Native Client 10.0 Configuration -> Client Protocols -> TCP/IP double click ( Right click select Properties ) on TCP/IP.

You will find Default Port 1433.

Depending on connection, the port number may vary.

Answer 2

You could also look with a

netstat -abn

It gives the ports with the corresponding application that keeps them open.

Edit: or TCPView.

Answer 3

Here are 5 methodes i found:

• 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 4: sys.dm_exec_connections DMV
I think this is almost the easiest way...
DMVs return server state that can be used to monitor SQL Server Instance. We can use sys.dm_exec_connections DMV to identify the port number SQL Server Instance is listening on using below T-SQL code:

SELECT local_tcp_port
FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID
GO

Result Set:
local_tcp_port
61499

(1 row(s) affected)

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

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 IPAll group.

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 Ports textbox. Here my instance is listening on port number 61499.

The other methods you can find here: http://sqlandme.com/2013/05/01/sql-server-finding-tcp-port-number-sql-instance-is-listening-on/