SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find which port SQL Server is using to establish connections


Find which port SQL Server is using to establish connections

Author
Message
SQLDCH
SQLDCH
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4487 Visits: 3435
Comments posted to this topic are about the item Find which port SQL Server is using to establish connections

----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------

Yeah, well...The Dude abides.
Avinash Barnwal
Avinash Barnwal
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
Points: 340 Visits: 16
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



Gianluca Sartori
Gianluca Sartori
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38737 Visits: 13367
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.

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Håvard
Håvard
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 142
I really liked this article. It's short and simple, and I learned something:-)
d.majoor
d.majoor
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 37
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
bertrand.leroy
bertrand.leroy
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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
Lawrence Moore
Lawrence Moore
SSC-Addicted
SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)

Group: General Forum Members
Points: 465 Visits: 400
How about using this for a TSQL based solution?:

SELECT DISTINCT(local_tcp_port) FROM sys.dm_exec_connections
WHERE net_transport='TCP'
Steve Smith-149662
Steve Smith-149662
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 56
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.
M&M
M&M
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8890 Visits: 3914
Nice simple article. We could also get this information from SQL Server error log.

M&M
tskelley
tskelley
Mr or Mrs. 500
Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)

Group: General Forum Members
Points: 595 Visits: 1192
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'.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search