How to identify Ports used by SQL Server

Question

How to identify Ports used by SQL Server

HookSQLDBA7

SSChampion

Points: 10709
More actions
August 18, 2005 at 7:34 am

#90773

How would I go about identifying which ports are used by SQL Server 2000 (SP3)? Also, how would I identify which ports are being used on the Windows 2000 Server?
Thanks in advance for you help, Kevin

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

mdaniel SSCarpal Tunnel Points: 4616 More actions · Answer 1

You can see it in Server Network Utility, select TCP/IP protocol and click Properties. Also, you can find it in the SQL Error Log, look for a line that starts "SQL Server listening on...", it tells the IP and port that the server is using.

You can run in command prompt "netstat -a -o" to see all open ports and connections via TCP/IP to the server, it will also list the PID that opened the port.

mdaniel SSCarpal Tunnel Points: 4616 More actions · Answer 2

Also, this free utility show the ports:

http://www.sysinternals.com/Utilities/TcpView.html

Lowell SSC Guru Points: 323518 More actions · Answer 3

here is a script to run on your instances to find out what port it is using.

CREATE TABLE #GetPort

(

token varchar(100),

value varchar(20))

go

DECLARE @inst varchar(200)

DECLARE @inst1 varchar(100)

--Determine registry path and key

IF(charindex('\',@@servername) > 0)

BEGIN

SELECT @inst = substring(@@servername,charindex('\',@@servername) ,50)

SELECT @inst = 'SOFTWARE\Microsoft\Microsoft SQL Server'+@inst+'\MSSQLServer\SuperSocketNetLib\Tcp'

--SELECT @inst1 = 'TcpDynamicPorts'

SELECT @inst1 = 'TcpPort'

END

ELSE

BEGIN

if SUBSTRING(@@VERSION,23,1) = '7'

begin

SELECT @inst = 'SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\VIA'

SELECT @inst1 = 'DefaultServerPort'

end

else

begin

SELECT @inst = 'SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\Tcp'

SELECT @inst1 = 'DefaultPort'

end

END

print @inst + '\\\\////'+ @inst1

INSERT #GetPort

EXEC master..xp_regread 'HKEY_LOCAL_MACHINE', @inst, @inst1

SELECT substring(@@servername,1,25) as ServerName, value as port FROM #GetPort

DROP TABLE #GetPort

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!