Agent Jobs

Add All SQL Instance Ports To Firewall

43340454 - firewall rules showing no access and regulations

Here’s some SQL logic that extrapolates all instances ports, and creates a nifty netsh command so you can add those ports to the firewall with a display name of "SQL Instance MyInstanceName".

use master;
set nocount on

declare	@sql_instances		table ([rootkey] varchar(255), [value] varchar(255))
declare @firewall_commands	table ([int] int identity(1,1), [command] varchar(1000))
declare	@run_commands		varchar(max) = ''

insert into @sql_instances 
exec master.dbo.xp_instance_regenumvalues 
,	@key		= N'SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL';

declare db_cursor	cursor for select upper([rootkey]), upper([value]) from @sql_instances
declare	@instance_name	varchar(255)
declare	@instance_path	varchar(255)
open	db_cursor;
fetch next from db_cursor into @instance_name, @instance_path
	while @@fetch_status = 0  
			declare @port	varchar(50)
			declare @key	varchar(255) = 'software\microsoft\microsoft sql server\' + @instance_path + '\mssqlserver\supersocketnetlib\tcp\ipall'
			exec master..xp_regread
			@rootkey        = 'hkey_local_machine'
			,   @key        = @key
			,   @value_name = 'tcpdynamicports'
			,   @value      = @port output
			declare	@add_firewall_rule	varchar(255)
			set	@add_firewall_rule	= 'exec master..xp_cmdshell ''netsh advfirewall firewall add rule name="SQL Instance ' 
							+ upper(@instance_name) + '" dir=in action=allow protocol=tcp localport=' + isnull(convert(varchar(10), @port), 1433) + ''''
			insert into @firewall_commands
			select	(@add_firewall_rule)
			fetch next from db_cursor into @instance_name, @instance_path
	close db_cursor
deallocate db_cursor;
select	@run_commands = @run_commands + '' + command + ';' + char(10) from @firewall_commands
exec    (@run_commands)

Your results will look something like this which can be run from the server directly, or concatenated into variable, and run from a Job. As long as the SQL Service accounts have rights within the OS; the ports will be added accordingly. Using the logic above it is packaged into a variable @run_commands which is run on the server, and adds the firewall rules.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s