On which Port SQL Server is Listening to?

Question

Post reply

On which Port SQL Server is Listening to?

rambilla4

SSCrazy Eights

Points: 8715
More actions
May 27, 2009 at 1:31 pm

#205408

Hi,

I want know on which port sql server is listening to. We have a named instance called QA. Iam looking in the error log and I found the port number in 2 places with different value. On which of these 2, actullay the sql server is listening to?

Server is listening on [ 'any' 1298].

2009-03-27 15:54:20.58 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\QA ].

2009-03-27 15:54:20.58 Server Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$qa\sql\query ].

2009-03-27 15:54:20.59 Server Server is listening on [ 127.0.0.1 4673].

thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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

PaulB-TheOneAndOnly SSC Guru Points: 66773 More actions · Answer 1

This is why I love Microsoft's KB... http://support.microsoft.com/kb/823938

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 2

rambilla4

SSCrazy Eights

Points: 8715

May 27, 2009 at 2:40 pm

#1001019

Server is listening on [ 'any' 1298] or

2009-03-27 15:54:20.59 Server Server is listening on [ 127.0.0.1 4673].

I went through the KB article But still Iam not clear on which port sql server is listening to

1298 or 4673????

plz clarify me

Shawn Melton SSC-Insane Points: 24675 More actions · Answer 3

To get a definite answer on what ports your instance is listening on try this this article out: http://support.microsoft.com/kb/294453

It shows the location the ports are actually set at. I don't think it is completely accurate in how to locate this in SQL 2005. You will open SQL Server Configuration Manager then go under 'SQL Server 2005 Network Configuration'. You should see 'Protocols for Instance_Name'. Right clicking on TCP/IP and go to properties it should show you the port.

Then open a command prompt up on the server and run a [font="Verdana"]'netstat -an'[/font]and see if you see those ports in a LISTENING state.

SQL Books Online will give you some more detailed information on configuring static ports and such, if you have time to weed through all that stuff.

This is another article on ports for SQL Server:

http://support.microsoft.com/kb/287932

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 4

You can look in SQL configuration manager or run the below script:

DECLARE @test-2 varchar(15),@value_name varchar(15),@RegistryPath varchar(200)

IF (charindex('\',@@SERVERNAME)0) -- Named Instance

BEGIN

SET @RegistryPath = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + RIGHT(@@SERVERNAME,LEN(@@SERVERNAME)-CHARINDEX('\',@@SERVERNAME)) + '\MSSQLServer\SuperSocketNetLib\Tcp'

END

ELSE -- Default Instance

BEGIN

SET @RegistryPath = 'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp'

END

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE' ,@key=@RegistryPath,@value_name='TcpPort',@value=@test OUTPUT

Print 'The Port Number is '+ char(13)+ @test-2

John H Marsh SSChampion Points: 11671 More actions · Answer 5

Hello,

I suspect that the important thing to notice from the KB Article provided by Pablo Berzukov is that, by default, Named Instances listen on Dynamic Ports.

I guess nobody has changed this for your “QA” Instance?

This means that each time the Instance starts a different Port can be assigned.

If you want the Instance to always listen on one particular Port, then you need to configure it to use a Static Port. As the KB Article states, this configuration change is describe in "How to: Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager)" topic in SQL Server 2005 Books Online.

BTW – Why do you need to know which Port SQL Server is listening on? For example, are you trying to configure a related Firewall, or is a Client having connectivity problems?

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

Perry Whittle SSC Guru Points: 233792 More actions · Answer 6

look in sql config manager. Find the instance and on the protocols click TCP. Right click and select preoperties then scroll all the way down. Dynamically assigned port will be under IP All

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉