How to Identify port number of SQL server

sql

server-logs

sql-server-configuration

network-configuration

byNikita Barsukov·Jan 3, 2025

Seek your answer in the SQL Server Configuration Manager. Here's the path: Expand SQL Server Network Configuration, choose Protocols for [YourInstanceName], and select TCP/IP. In the Properties dialog, shift to the IP Addresses tab and peek at the TCP Port. Conventionally, default instances are bonded with port 1433.

-- Say hello to port number finding query. It's more sociable than it looks.
SELECT local_tcp_port FROM sys.dm_exec_connections WHERE local_net_address IS NOT NULL;

This merry little snippet will meet up with the active port number from your SQL Server and bring it to you.

Deeper exploration of network configuration

Set your sails for a deeper voyage into the vast blue sea of server logs and network configurations. Here we go!

Deciphering the logs' cryptic tales

Port numbers don't always jump up and introduce themselves. Sometimes, they're wallflowers hiding in the corner - the corner of your server logs:

-- A romantic interlude with your server logs
EXEC xp_readerrorlog 0, 1, N'Server is listening on', N'any', NULL, NULL, N'asc'

It's like reading a love letter. Look for phrases like "Server is listening on". Where it's listening - that's your port number.

Dressing up the server

Good looks count! The SQL Server Configuration Manager offers a visual venue to check and adjust the server's proto-clothing

Open the wardrobe -- Configuration Manager
Navigate to the fashion district -- SQL Server Network Configuration
Search for the perfect outfit -- TCP/IP properties
Survey your choices -- IP Addresses tab and stroll down to the IPAll section

Making friends with the firewall

An open door is not an invitation for everyone if there is a bouncer. On the server, your bouncer is Windows Firewall:

Navigate to the club door -- Control Panel > System and Security > Windows Defender Firewall
Speak with the bouncer -- click on Advanced settings
Propose a new rule -- Inbound rules > New Rule
Ensure the SQL Server port holds a VIP card -- Port is permitted

A special "cliconfg.exe" party invitation

It's time to dust off cliconfg.exe and party with the network protocol

Open the cliconfg.exe guest invite from the Run prompt
Get ready the drinks -- enable TCP/IP
Set the table and plates -- under Alias, set your server name and schedule the port number

Practical use cases

Not all SQL Servers have the same story to tell. Let's delve into a few unique narratives.

The curious case of dynamism

Dynamic ports are like kittens – they love to play hide and seek. Here, the SQL Server Browser service helps find the port playing Hide and Seek.

Express delivery

SQL Server Express versions buck the trend and don't use the default 1433 port, instead depending on the Browser service to guide the mailman to the right port. Check if it's 'On Duty' via services.msc.

Code magic

If you're a wizard of codes, wielding PowerShell can enlighten your quest.

-- Who doesn't love a good shell script? It's like the butter on your coding bread.
Get-NetTCPConnection | Where-Object {$_.LocalAddress -like "*:1433"} | Select-Object -Property LocalAddress, LocalPort, State