Find which port SQL Server is using to establish connections

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Dan Hess » Find which port SQL Server is using to...

12 posts, Page 1 of 2

1 2 »»

Find which port SQL Server is using to establish connections

Rate Topic

Display Mode

Topic Options

Author

Message

SQLDCH

Posted Wednesday, September 07, 2011 11:20 PM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 5:30 PM
Points: 896, Visits: 2,282

Comments posted to this topic are about the item Find which port SQL Server is using to establish connections

----------------------------------------------------------------------------
My LinkedIn profile - LinkedIn
Sacramento SQL Server users group - http://sac.sqlpass.org
My blog - http://www.expressnetsolutions.com/sqldch
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------

Yeah, well...The Dude abides.

Post #1171523

Avinash Barnwal

Posted Wednesday, September 07, 2011 11:21 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 1:47 AM
Points: 58, Visits: 15

The third way doesn't seem to be option to find out which of the port SQL Server is listening, since if you are already connected to the SQL server, then you already know the Port.

Kindest Regards,

Avinash

avin_barnwal@hotmail.com

Post #1171524

spaghettidba

Posted Thursday, September 08, 2011 1:15 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:43 AM
Points: 4,804, Visits: 8,091

Avinash Barnwal (9/7/2011)

The third way doesn't seem to be option to find out which of the port SQL Server is listening, since if you are already connected to the SQL server, then you already know the Port.

Not completely true. You can connect using a named instance and never know the port you're attaching to.
The SQL Browser service listens on port UDP 1434 and returns the port of the named instances in a datagram. The client application parses the datagram and connects to the port returned by the SQL Browser.

Get your two-cent-answer quickly
The Spaghetti DBA

Post #1171546

Håvard

Posted Thursday, September 08, 2011 1:27 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 08, 2013 5:51 AM
Points: 145, Visits: 140

I really liked this article. It's short and simple, and I learned something Smile

Post #1171550

d.majoor

Posted Thursday, September 08, 2011 2:09 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 3:12 AM
Points: 2, Visits: 30

If you are connected (using the instance name) you can also find it by query (SQl2000 and up):

set nocount on
go

DECLARE @SqlPort Nvarchar(10), @instance_name Nvarchar(30), @reg_key Nvarchar(500), @value_name Nvarchar(20)
if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),2) = '10'
BEGIN
select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)
if @instance_name is NULL
BEGIN
set @reg_key = 'SOFTWARE\Microsoft\MSSQLServer\MSSQlServer\SuperSocketNetLib\Tcp'
END
ELSE BEGIN
set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer\SuperSocketNetLib\Tcp'
END
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@reg_key, @value_name='TcpPort',
@value=@SqlPort output
select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @SqlPort as Port
END
if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '9'
BEGIN
select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)
if @instance_name is NULL
BEGIN
set @reg_key = 'SOFTWARE\Microsoft\MSSQLServer\MSSQlServer\SuperSocketNetLib\Tcp'
END
ELSE BEGIN
set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer\SuperSocketNetLib\Tcp'
END
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@reg_key, @value_name='TcpPort',
@value=@SqlPort output
select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @SqlPort as Port
END
if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '8'
BEGIN
Create table #Port_2000 (value nvarchar(20),Data nVarchar(10))
insert into #Port_2000 exec master..xp_instance_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Supersocketnetlib\tcp', 'tcpPort'
select @SqlPort = Data from #Port_2000
select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @SqlPort as Port
drop table #Port_2000
END

Post #1171567

bertrand.leroy

Posted Thursday, September 08, 2011 2:20 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, January 04, 2012 2:28 AM
Points: 2, Visits: 22

Thanks for the tips;
For info:
I found out though that the only way that worked for me was to check the event details - although for that I had to restart the server to generate new events.

With regards to the second option, I have no values displayed in that field, or any other fields labeled "Port" in that IP-Addresses tab.

The third option had to be tinkered with a wee bit -
@@SERVICENAME returns MSSS2008R2
but the key is actually found under MSSQL10_50.MSSQLSERVER2008R2

and then, just like for option 2, that tcp key value is empty anyway:
Value Data
tcpPort NULL

thanks again for the tips,

B

Post #1171573

Lawrence Moore

Posted Thursday, September 08, 2011 2:22 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 5:16 AM
Points: 58, Visits: 263

How about using this for a TSQL based solution?:

SELECT DISTINCT(local_tcp_port) FROM sys.dm_exec_connections
WHERE net_transport='TCP'

Post #1171574

Steve Smith-149662

Posted Thursday, September 08, 2011 3:30 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, June 06, 2012 3:42 PM
Points: 12, Visits: 29

You can also use the following from the command line :

netstat -n -b

You can then check the returned values for the executable you're interested in to see the port (or ports) being listened on.

Post #1171598

mohammed moinudheen

Posted Thursday, September 08, 2011 5:12 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:29 PM
Points: 2,178, Visits: 3,599

Nice simple article. We could also get this information from SQL Server error log.

Mohammed Moinudheen

Post #1171627

tskelley

Posted Thursday, September 08, 2011 8:21 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, June 14, 2013 2:51 PM
Points: 33, Visits: 773

Figured I would throw one more method, albeit quite a bit more manual. You can also check the error log in found in the Log folder where your SQL Server is installed (for example, C:\Program Files\Microsoft SQL Server\MSSQL.1\Log). The latest log file will be named ERRORLOG (w/out the numbered extension). Using a standard text editor, like Notepad, you can do a quick search for the text 'listening'.

Post #1171765

« Prev Topic | Next Topic »

12 posts, Page 1 of 2

1 2 »»

Permissions