Finding the Port Number for a particular SQL Server Instance
Posted by decipherinfosys on January 2, 2008
One of the developers recently asked me this question: “I have a SQL Server instance running – how can I tell what port it is running on?”. There are a couple of ways of finding this information.
1) Using the GUI: In SQL Server 2000, you can use the Server Network Utility and in SQL Server 2005, you can use the SQL Server Configuration Manager. Look under SQL Server 2005 Network Configuration and look at the TCP port for the TCP/IP protocol.
2) Check the error log. You will see an entry like: “Server is listening on [ ‘any’ <ipv4> 1433].” The last 4 numbers denote the TCP/IP port number that is being used by that particular instance of SQL Server.
3) Registry entry: HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\TCP
and you will see TCPPort as one of the entries and it’s value represents the port number for that instance. In case you are using a named instance, then the registry entry will be: HKLM\Software\Microsoft\Microsoft SQL Server\<name of the instance>\MSSQLServer\SuperSocketNetLib\TCP
4) Using the extended stored procedure xp_regread, you can find out the value of the TcpPort by using SQL. Example:
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
5) You can also chose to trace the client to server communication by using Microsoft Network Monitor or a network sniffer utility. You can also use the TCP/IP netstat utility. Example: Using “netstat -an” on the database server, I get:
Proto Local Address Foreign Address State
TCP 192.168.20.196:1433 192.168.20.70:3655 ESTABLISHED
TCP 192.168.20.196:1433 192.168.20.70:3664 ESTABLISHED
TCP 192.168.20.196:1433 192.168.70.15:5277 ESTABLISHED
TCP 192.168.20.196:1433 192.168.70.15:5286 ESTABLISHED
TCP 192.168.20.196:1433 192.168.70.15:5795 ESTABLISHED
TCP 192.168.20.196:1433 192.168.70.123:1162 ESTABLISHED
TCP 192.168.20.196:1433 192.168.70.123:1177 ESTABLISHED
TCP 192.168.20.196:1433 192.168.70.123:1186 ESTABLISHED
TCP 192.168.20.196:1433 192.168.70.126:3529 ESTABLISHED
TCP 192.168.20.196:1433 192.168.70.126:3540 ESTABLISHED
TCP 192.168.20.196:1433 192.168.70.126:4088 ESTABLISHED
In this case, the IP Address “192.168.20.196” is the IP address of the SQL Server box where the instance is running and the other IP addresses are the IP addresses of the client machines from where the connections are being established.
7 Responses to “Finding the Port Number for a particular SQL Server Instance”
Sorry, the comment form is closed at this time.
Windows Firewall and SQL Server « Systems Engineering and RDBMS said
[…] your instance on). If you are not sure what port you are running your SQL Server instance on, see this blog […]
» Finding the Port Number for a particular SQL Server Instance Shoel's i-log book said
[…] Finding the Port Number for a particular SQL Server Instance « Systems Engineering and RDBMS […]
2010 in review – courtesy Wordpress.com « Systems Engineering and RDBMS said
[…] Finding the Port Number for a particular SQL Server Instance January 2008 2 comments […]
Identifying the correct MS-SQL JDBC port for the LiveCycle data source « LiveCycle Blog said
[…] https://decipherinfosys.wordpress.com/2008/01/02/finding-the-port-number-for-a-particular-sql-server-… […]
telnet router backtrack linux distro download iso said
telnet router backtrack linux distro download iso
Finding the Port Number for a particular SQL Server Instance « Systems Engineering and RDBMS
telnet linux mint vs ubuntu vs fedora said
telnet linux mint vs ubuntu vs fedora
Finding the Port Number for a particular SQL Server Instance « Systems Engineering and RDBMS
How To Find Port Number Of Sql Server | Information said
[…] Finding the Port Number for a particular SQL Server … – Jan 02, 2008 · Finding the Port Number for a particular SQL Server Instance « Systems Engineering and RDBMS … Finding the Port Number for a particular SQL Server … […]