On which port is SQL Server running?

Earlier this week, a good friend asked me the simple question: “How can I check on which port my SQL Server is currently running?” There are multiple ways, so let’s take a look at the ways you can find out the answer!

 
SQL Server Error Log
It’s a misconception that only error messages are logged in the SQL Server error log. There are also informational messages logged, which you can use to find the port on which SQL Server is currently running:

xp_readerrorlog 0, 1, N'Server is listening on'

 
The result:

 
SQL Server Configuration Manager
The SQL Server configuation manager is a tool which is installed alongside SQL Server. In this tool, you can change for example the TCP/IP settings of your SQL Server. It also shows you the current port on which SQL Server is running:

 
DMV
You can also query the system DMV’s:

SELECT DISTINCT 
    local_tcp_port
FROM sys.dm_exec_connections
WHERE local_tcp_port IS NOT NULL

or

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

 
Command prompt
Finding the used port via a command prompt requires some more information. First, you need to find the process ID (PID) that SQL Server is using. The quickest way is to use the Task Manager in Windows. Once you have the PID, you can run the commmand below:

“netstat -ano | findstr [PID]”

The result:

 
Registry
The port number can also be found in the registry. Just remember, that the folder you see in the screenshot below contains my instance name (SQL2014DEV). Change this to your instance name!

 
You can also use the “master.dbo.xp_regread” stored procedures to read this registry key from T-SQL:

DECLARE @TCPPort NVARCHAR(5),
        @RegKeyName VARCHAR(8000);

SET @RegKeyName = CONCAT('Software\Microsoft\Microsoft SQL Server\',
                         @@SERVICENAME,
                         '\MSSQLServer\SuperSocketNetLib\TCP')

EXEC xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKeyName,
  @value_name = 'TcpPort',
  @value = @TCPPort OUTPUT;

SELECT @TCPPort;

 
Event Viewer
SQL Server also logs the port in the Windows Event Viewer. If you open the application log, and you filter on eventid 26022, you’ll see the port that is in use:

 
PowerShell
After I posted this blog, Johan Bijnens (@alzdba) sent me a message: I forgot the PowerShell option!

#tcpport.ps1
#Evaluates the SQL Server instances on a Windows server and returns the TCP port number used by each instance
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")  | Out-Null
$m = New-Object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') 'HOME'
$m.ServerInstances | ForEach-Object { $m.Name + '\' + $_.Name + ', ' +
       $m.ServerInstances[$_.Name].ServerProtocols['Tcp'].IPAddresses['IP1'].IPAddress.IPAddressToString + ':' +
       $m.ServerInstances[$_.Name].ServerProtocols['Tcp'].IPAddresses['IPAll'].IPAddressProperties['TcpDynamicPorts'].Value
       }

 
Thanks Johan for reminding me on this! The full code can be found on MSDN.

Error: Unable to connect to SQL Instance

Every once in a while you encounter strange, but interesting issues. This time a colleague asked me to fix the problem that another colleague could not connect to his SQL Engine. After some research we found out that the machine he was working on ran 2 versions of SQL: SQL 2005 Express, and SQL 2008 Enterprise.

The first thing that came to mind was conflicting installations (shared DLL’s for example). That wasn’t it… Second thought: Both instances are default named instances, so I stopped the 2005 Express service. Still nothing…

Then it came to mind. I’ve seen this before! Both instances use the same default TCP/IP port 1433, and this causes issues! So I opened the configuration tool, and changed some IP addresses and port numbers and (already there, so why not) started the SQL Browser Service (why it was stopped? I don’t know!). Result: still nothing…

Now I’m getting pissed! This is impossible! After some searching I tried (as a last resort) to turn of the Windows Firewall. BAM! Works! Damn it!!! In order to make sure it isn’t a coincidence. Restart SQL Service: still works. Turn Firewall on: no connection possible. This is it! Finally, after 30 minutes of trial and error, I get a result.

So I ended up adding a custom rule to the Firewall to allow SQL connections through. This can be done by following these steps:

1) Open Firewall Window:

2) Click on “Inbound Rules”, and then “New Rule…”

3) Choose Port:

4) Add TCP Port 1433

5) You need to allow the connection:

6) You can choose whatever you want in this screen. I personally only allow Domain connections to my SQL Server:

7) Name the Firewall Rule:

When you click Finish, there’s no need to restart SQL Service or your workstation. The changes take effect instantly.

From now on you will be able to connect to your SQL Server Instance from another machine over the internet or your network.

Follow

Get every new post delivered to your Inbox.

Join 70 other followers