Here is one of the very common interview questions I often ask people –
Q: What is the default Port SQL Server running on?
A: 1433 for TCP/IP and 1434 for USD connection.
I always get the correct answer for this question. However, when I ask a follow up question, so far I have yet not got a single correct answer.
Q: Great, now assumes that there is more than one instance of SQL Server running on the same server. What will be the port of the second SQL Server running on the server as the default port is already used by default instant?
A: (I have yet to get the correct answer for this one in interview).
Because of the same reason, I have decided to blog about this.
Here are different ways one can figure out on which port the second instance of SQL Server is running.
Method 1: using xp_readerrorlog
Execute following stored procedure on the instance where you want to find out port on which SQL Server is running.
USE MASTER
GO
xp_readerrorlog 0, 1, N'Server is listening on'
GO
The above query will return results something very similar to the following:
Method 2: Configuration Properties
Go to Windows >> Start >> SQL Server Program Folder >> SQL Server Configuration Manager
Now go to SQL Server Network Configurations >> Protocols for Your Server and go to TCP/IP and right click over it.
Now over here when you scroll down you will notice Port Details.
It is that easy to find the port of the second instance of SQL Server.
Reference: Pinal Dave (https://blog.sqlauthority.com)
14 Comments. Leave new
Slight correction. It’s not 100 % true that first instance takes default port of 1433. What you said is true If and only if the first instance is a default instance. If we happen to install a named instance first on server, SQL server dynamically assigns port # other than 1433 (shown in your example) which can be forced to listen on 1433, if it’s not being used by other SQL instance.
Even if you install a named instance first and then install default instance, SQL server will still assign 1433 for the default instance irrespective when in sequence was it installed ( first or second ).
So by default, default instance 1433 port # and named instance take port# other than 1433.
~ IM.
It’s also possible to get the portnumber by reading the registry with my scirpt below:
DECLARE @tcp_port nvarchar(5)
if @@SERVICENAME = ‘MSSQLSERVER’
BEGIN
EXEC xp_regread
@rootkey = ‘HKEY_LOCAL_MACHINE’,
@key = ‘SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP’,
@value_name = ‘TcpPort’,
@value = @tcp_port OUTPUT
END
ELSE
BEGIN
declare @keyname nvarchar(500)
set @keyname = ” + ‘Software\Microsoft\Microsoft SQL Server\’ + @@SERVICENAME + N’\MSSQLServer\SuperSocketNetLib\TCP’
EXEC xp_regread
@rootkey = ‘HKEY_LOCAL_MACHINE’,
@key = @keyname,
@value_name = ‘TcpPort’,
@value = @tcp_port OUTPUT
END
select @tcp_port
this is hard coded to default instance if your running name instance (or) multi instance needs total change of key value.
You are right, you can get the port by using the method you mentioned, However In the above example the port shown is dynamic and not fixed, so whenever the server restart, it will change again. It would be good you should assign a fixed port to the instance, so that people across different networks can connect using the same fixed port.
the following command works also :
SELECT DISTINCT
local_tcp_port
FROM sys.dm_exec_connections
WHERE local_tcp_port IS NOT NULL
Correct. you may want to add another filer ” and net_transport = ‘TCP'”
SELECT DISTINCT
local_tcp_port
FROM sys.dm_exec_connections
WHERE local_tcp_port IS NOT NULL
and net_transport = ‘TCP’
And this also assumes that there is atleast one connection is made using TCP :)
How does SQL Server Management Studio know the port number(s) of different instances? For example, the “Browse for Servers” dialog finds instances of SQL Server on the network (i.e. “Network Servers” tab) and you’re able to connect to any instance, including those using non-default ports (as is the case with multiple instances).
U know what, YOU ROCK. Whenever I find this site in google, I always find the right answer.
If I only have one default instance(MSSQLSERVER), can I change it to dynamic port?
Dear Pinal,
For Log Shipping and Windows Clustering port no 135 , 443, 445, 137, 138 are mandatory .
These are known and default ports used by SQL Server and Windows server and clustering
From security point of view what are alternative ports which can used instead of default ports or is there any other solution for the same.
What if I have multiple SQL Servers? (Want to make a report)
Thank you, it really helped me
How to set (or preserver) the port porgramatically? Sql Code? When I restrat SQl Instance changes the port, so I need to change conectionstring in every pc software :(. Sql Server 2014 Express I have try to set manually the previous port when in chages but at restarting sql changes again :(, How make it stable?