none
How many ways can find sql server port number...?

Answers

  • There are two ways in this link.

    SP_readerrorlog 0,1,'listening','.' 
    -------------------------
    CREATE PROCEDURE getport_number AS
    BEGIN
    	DECLARE @port_number nvarchar(5) 
    	EXEC xp_regread 
    	@rootkey    =    'HKEY_LOCAL_MACHINE', 
    	@key = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP', 
    	@value_name    =    'TcpPort', 
    	@value        =    @port_number OUTPUT
    	print 'The server port number = '+@port_number 
    END  
    -----------------------------
    EXEC getport_number 

    http://www.sqlserverblogforum.com/2011/01/how-to-find-the-sql-server-port-number/


    Regards, RSingh

    • Marked as answer by vijay_1234 Sunday, September 29, 2013 4:52 AM
    Saturday, September 28, 2013 6:01 PM
  • Hi Vijay,

    The below link explains you different ways to find out the TCP port SQL Server is listening to

    http://sqlandme.com/2013/05/01/sql-server-finding-tcp-port-number-sql-instance-is-listening-on/

    The easiest would be to run

    SELECT local_tcp_port
    FROM   sys.dm_exec_connections
    WHERE  session_id = @@SPID
    GO

    Thanks

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    • Marked as answer by vijay_1234 Sunday, September 29, 2013 4:52 AM
    Saturday, September 28, 2013 6:30 PM
  • Its in the error log

    Message
    Server is listening on [ 127.0.0.1 <ipv4> 1434].

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Marked as answer by vijay_1234 Sunday, September 29, 2013 4:52 AM
    Saturday, September 28, 2013 9:15 PM
  • 1.

    All Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager

    Click on SQL Native Client 10.0 Configuration -> Client Protocols -> TCP/IP double click ( Right click select Properties ) on TCP/IP


    2.

    SELECT SERVERNAME = CONVERT(NVARCHAR(128),SERVERPROPERTY('SERVERNAME'))  
    ,LOCAL_NET_ADDRESS AS 'IPAddressOfSQLServer' 
    ,local_tcp_port  AS 'PortNumber'
     FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID = @@SPID
    3.
    DECLARE       @portNumber   NVARCHAR(10)
    
     
    
    EXEC   xp_instance_regread
    
    @rootkey    = 'HKEY_LOCAL_MACHINE',
    
    @key        =
    
    'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
    
    @value_name = 'TcpDynamicPorts',
    
    @value      = @portNumber OUTPUT
    
     
    
    SELECT [Port Number] = @portNumber
    
    GO




    • Edited by Kranthi K Saturday, September 28, 2013 10:41 PM
    • Marked as answer by vijay_1234 Sunday, September 29, 2013 4:52 AM
    Saturday, September 28, 2013 10:41 PM

All replies

  • There are two ways in this link.

    SP_readerrorlog 0,1,'listening','.' 
    -------------------------
    CREATE PROCEDURE getport_number AS
    BEGIN
    	DECLARE @port_number nvarchar(5) 
    	EXEC xp_regread 
    	@rootkey    =    'HKEY_LOCAL_MACHINE', 
    	@key = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP', 
    	@value_name    =    'TcpPort', 
    	@value        =    @port_number OUTPUT
    	print 'The server port number = '+@port_number 
    END  
    -----------------------------
    EXEC getport_number 

    http://www.sqlserverblogforum.com/2011/01/how-to-find-the-sql-server-port-number/


    Regards, RSingh

    • Marked as answer by vijay_1234 Sunday, September 29, 2013 4:52 AM
    Saturday, September 28, 2013 6:01 PM
  • Hi Vijay,

    The below link explains you different ways to find out the TCP port SQL Server is listening to

    http://sqlandme.com/2013/05/01/sql-server-finding-tcp-port-number-sql-instance-is-listening-on/

    The easiest would be to run

    SELECT local_tcp_port
    FROM   sys.dm_exec_connections
    WHERE  session_id = @@SPID
    GO

    Thanks

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    • Marked as answer by vijay_1234 Sunday, September 29, 2013 4:52 AM
    Saturday, September 28, 2013 6:30 PM
  • Its in the error log

    Message
    Server is listening on [ 127.0.0.1 <ipv4> 1434].

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Marked as answer by vijay_1234 Sunday, September 29, 2013 4:52 AM
    Saturday, September 28, 2013 9:15 PM
  • 1.

    All Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager

    Click on SQL Native Client 10.0 Configuration -> Client Protocols -> TCP/IP double click ( Right click select Properties ) on TCP/IP


    2.

    SELECT SERVERNAME = CONVERT(NVARCHAR(128),SERVERPROPERTY('SERVERNAME'))  
    ,LOCAL_NET_ADDRESS AS 'IPAddressOfSQLServer' 
    ,local_tcp_port  AS 'PortNumber'
     FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID = @@SPID
    3.
    DECLARE       @portNumber   NVARCHAR(10)
    
     
    
    EXEC   xp_instance_regread
    
    @rootkey    = 'HKEY_LOCAL_MACHINE',
    
    @key        =
    
    'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
    
    @value_name = 'TcpDynamicPorts',
    
    @value      = @portNumber OUTPUT
    
     
    
    SELECT [Port Number] = @portNumber
    
    GO




    • Edited by Kranthi K Saturday, September 28, 2013 10:41 PM
    • Marked as answer by vijay_1234 Sunday, September 29, 2013 4:52 AM
    Saturday, September 28, 2013 10:41 PM
  • I am moving it to tools.


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Monday, September 30, 2013 5:54 PM