4

Using TSQL, how can I find if the MS SQL server (any version) is configured to use static port or is it using dynamic port?

Script i am trying to use:

DECLARE @TcpPort VARCHAR(5)
        ,@RegKey VARCHAR(100)

IF @@SERVICENAME !='MSSQLSERVER'
    BEGIN
        SET @RegKey = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @@SERVICENAME + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'
    END
    ELSE
    BEGIN
        SET @RegKey = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP\IPAll'
    END

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

EXEC master..xp_regread
    @rootkey = 'HKEY_LOCAL_MACHINE'
    ,@key = @RegKey
    ,@value_name = 'TcpDynamicPorts'
    ,@value = @TcpPort OUTPUT

SELECT @TcpPort AS PortNumber
        ,@@SERVERNAME AS ServerName
        ,@@SERVICENAME AS ServiceName
1
  • 1
    For your @@SERVICENAME key, remove the \IPAll from the end of the string. Oct 18 '11 at 14:27
9

You can determine which port an instance is using from the error log but not whether its static or dynamic.

EXEC xp_readerrorlog 1, 1, 'Server is listening'

Alternative would be to read from the registry via xp_regread.

DECLARE 
    @StaticPort NVARCHAR(512)
    , @DynamicPort NVARCHAR(512)

EXEC xp_regread
    'HKEY_LOCAL_MACHINE'
    , 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'
    , 'TcpPort'
    , @StaticPort OUTPUT

EXEC xp_regread
    'HKEY_LOCAL_MACHINE'
    , 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'
    , 'TcpDynamicPorts'
    , @DynamicPort OUTPUT

SELECT
    @StaticPort AS StaticPort
    , @DynamicPort AS DynamicPort
4
  • xp_regread: is it present in sql server 2008?
    – garik
    Oct 17 '11 at 8:43
  • 2
    Undocumented and unsupported but yes it is there. Oct 17 '11 at 10:01
  • @MarkStorey-Smith I tried to generalize this for multiple SQL servers but I don't see anything. Did i miss something? Script has been appended to the question
    – Manjot
    Oct 17 '11 at 22:28
  • @Manjot, daft typo in my script above, now corrected. Prefer your version by the way, have filed it away in the script library. Oct 18 '11 at 14:25
2

I think, it is possible (theoretically):

2
  • thanks..yes Use static, dynamic port allocation what and how to read from registry sounds appropriate... but how can I know which key to read?
    – Manjot
    Oct 17 '11 at 0:09
  • @Manjot see SQL Server xp_regread with T-SQL
    – garik
    Oct 17 '11 at 7:45
1

another way to find which port sql server is using is through the DMV called sys.dm_exec_connections as per the script below.

--===============================
-- sys.dm_exec_connections
--===============================
SELECT 
[user]=suser_sname()
,local_tcp_port
,session_id
,connect_time
,net_transport
,protocol_type
,encrypt_option
,auth_scheme
,last_read
,last_write

FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID
GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.