SQL Server Listening Port
Script per identificare le porte TCP sulla quale è in ascolto SQL Server
------------------------------------------------------------------------
-- Version: 1
-- Release Date: 2012-08-01
-- Author: Davide Mauri (SolidQ)
-- License: Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Italy License.
------------------------------------------------------------------------
use tempdb
go
declare @is_named_instance bit;
declare @key nvarchar(1024)
declare @tcp_port nvarchar(1024);
declare @tcp_enabled bit;
declare @Protocols table (Value1 nvarchar(1024) null, Value2 nvarchar(1024) null, Data nvarchar(1024) null);
set @is_named_instance = case when @@servicename <> 'MSSQLSERVER' then 1 else 0 end;
if (@is_named_instance = 1)
begin
set @key = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\'+@@servicename+'\MSSQLServer\Supersocketnetlib'
end
else
begin
set @key = 'SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer\Supersocketnetlib'
end
;
insert into @Protocols exec master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@key, @value_name='ProtocolList';
if exists(select * from @Protocols where Value2 = 'tcp') set @tcp_enabled = 1 else set @tcp_enabled = 0;
set @key = @key + '\TCP';
exec master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@key, @value_name='TcpPort', @value=@tcp_port OUTPUT
select
server_name = @@SERVERNAME,
instance_name = @@SERVICENAME,
tcp_enabled = @tcp_enabled,
tcp_port = @tcp_port
;
Attenzione, lo script suddetto fa uso della stored procedure non documentata xp_regread! Con SQL Server 2012, invece, è sufficiente usare la DMV