How do you find the port number a connection is using

Question

How do you find the port number a connection is using

MG-148046

SSCrazy Eights

Points: 9946
More actions
June 9, 2010 at 11:03 am

#140775

We have multiple ports listening on a SQL Server and I would like to find out which ports connections are using. Does anyone know how to do this?
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Viewing 12 posts - 1 through 11 (of 11 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

Which ports do you mean? The ports the server is listening on? Configuration manager or the error log list these. Or are you looking for which ports a particular connection has open? Do you need the port on the client?

Does sys.dm_exec_connections help? http://msdn.microsoft.com/en-us/library/ms181509%28v=SQL.100%29.aspx

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

Elliott Whitlow

SSC Guru

Points: 102402

June 9, 2010 at 11:12 am

#1178137

Ok, Steve beat me to it..

CEWII

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 3

Thanks Steve - sys.dm_exec_connections did it.

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

SanjayAttray SSChampion Points: 13157 More actions · Answer 4

Another way here:

DECLARE @key VARCHAR(100)

DECLARE @PortNumber VARCHAR(20)

SET @key = 'SOFTWARE\MICROSOFT'

IF CHARINDEX('\',@@SERVERNAME,0) <>0

BEGIN

SET @key = @key + '\Microsoft SQL Server\' + @@SERVICENAME

END

ELSE

BEGIN

SET @key = @key + '\MSSQLServer'

END

SET @key = @key + '\MSSQLServer\Supersocketnetlib\TCP'

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

@key=@key,@value_name='Tcpport',@value=@PortNumber OUTPUT

SELECT @PortNumber

SQL DBA.

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 5

Thank you Sanjay but I needed to get the port that a user connection is coming in from not the one(s) that SQL is listening on. The system view, sys.dm_exec_connections, contains that information. Steve's reply did it for me. I just needed to add sys.dm_exec_sessions to get the related data.

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 6

Post us a script, or send me an article, MG, when you get it working. That is something others can use.

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 7

Hi Steve,

How do you get the time to reply so quickly when you're at Tech Ed? Anyway, it would be a very short article so here is an example script. We use the default port for internal connections and add ports for external (VPN).

SELECT s.login_name, s.host_name, s.program_name

FROM sys.dm_exec_connections AS c INNER JOIN

sys.dm_exec_sessions AS s ON c.session_id = s.session_id

WHERE (c.local_tcp_port <> 1433)

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 8

LOL, it wouldn't be quick if I were at TechEd. I only have a couple times a day I check things. Back home today.

The article would be short, but it would be interesting in terms of why you need this, what problem you found, and then how you use the information. You could include in there a few things you tried and didn't work as well.

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 9

I'd be happy to try to put something together for the site. I've wanted to contribute something other than in the forums for a while but I'm a terrible writer. How do I submit it and any editorial assistance you are able to give would definitely be appreciated. 😀

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 10

Submit it here: http://www.sqlservercentral.com/Contributions/Home

We'll help you rewrite/draft it. Some notes here as well: http://www.sqlservercentral.com/About/WriteForUs

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 11

Thanks - I'll get it out to you in couple of days.

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.