Agent Jobs

Automatically Add FireWall Rule For SQL Server

42985001 - icon of firewall and database

Automatically Add SQL Firewall Rules

Here’s some SQL logic that checks for the most recent SQL instance created, and automatically adds a port firewall rule. This isn’t too terribly hard to do on it’s own, but anything to help automate is always a big help, and of course also encourages consistency, and and uniformity in setting up environments.

This will run a general remove statement so you don’t run into duplicate issues for your firewall rule names, then it runs the addrule statement.

The rule name will be created with the following Convention.

SQL [MyInstanceName] Port [MyPort]

Thats it. Easy peasy.

On with the logic:

use master;
set nocount on
  
declare @sql_instances  table
(   
    [rootkey]   varchar(255)
,   [value]     varchar(255)
)
insert into @sql_instances 
exec master.dbo.xp_instance_regenumvalues 
    @rootkey    = N'HKEY_LOCAL_MACHINE'
,   @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  
        begin
            declare @port_table table
            (
		[id]	    int	identity(1,1)
            ,	[Instance]  varchar(255)
            ,   [Port]      int
            )
            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
             
            insert into @port_table ([instance], [port])
            select
                'Instance'  = @instance_name
            ,   'Port'  = isnull(convert(varchar(10), @port), 1433)
            fetch next from db_cursor into @instance_name, @instance_path
        end;
    close db_cursor
deallocate db_cursor;

declare @add_port_rule	varchar(255) = (select top 1'netsh advfirewall firewall add rule name="SQL '		+ [instance] + '  Port ' + cast([port] as varchar) + '" dir=in action=allow protocol=tcp localport=' + cast([port] as varchar) + '' from @port_table order by [id] desc)
declare	@rem_port_rule	varchar(255) = (select top 1'netsh advfirewall firewall delete rule name="SQL '		+ [instance] + '  Port ' + cast([port] as varchar) + '"' from @port_table order by [id] desc)

exec	master..xp_cmdshell @rem_port_rule  -- if already exists it will remove port rule before creating the new rule.
exec	master..xp_cmdshell @add_port_rule

If you need to verify; here’s a quick Powershell script to return all the Firewall rules with the letters “SQL”

get-netfirewallrule `
| where { $_.enabled -eq 'true' -and $_.direction -eq 'inbound' -and $_.'displayname' -like "*sql*"}`
| select displayname, enabled, action, direction `
| out-string -width 98
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s