Advanced Firewall Configuration

Developer
Feb 22, 2012 at 2:46 PM

I'm just starting to dive into this script, but thus far I am in love with it. I am looking at the firewall configuration, and it looks like in essence you basically disable the firewall (change default from block to allow). I'm wondering what it would take to create a rule that would actually allow the sqlservr.exe process through the firewall instead. I'm trying to do this, but am finding it difficult to be able to get the actual directory that the instance being installed was actually installed to. Obviously with 2008+ we follow a pattern, "MSSQL10.INSTANCENAME" but with 2005 that wasn't the case. Does anyone have any suggestions?

Developer
Feb 22, 2012 at 2:59 PM

Finally found something.

The Firewall Configuration would have to move to a post script. You could then use SMO to get the installation Root Directory and build the Binn/sqlservr.exe path onto it. Then use netsh to create the new rule.

Developer
Feb 22, 2012 at 3:29 PM

Below is my completed firewall configuration script.

$configParams = $args[0]
$computerName = gc env:computername
$instanceName = $configParams["InstanceName"]

if([string]::IsNullOrEmpty($instanceName))
{
	#Default Instance
	$name = $computerName
	$instanceName = "MSSQLSERVER"
}
else
{
	$name = $computerName + "\" + $instanceName
}

# Load SMO
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null

$smo = New-Object Microsoft.SqlServer.Management.Smo.Server $name
$rootDirectory = $smo.RootDirectory
$sqlServrExe = Join-Path -Path $rootDirectory -ChildPath "Binn\sqlservr.exe"

$cmdResult = netsh advfirewall firewall add rule name="SQL Server - $instanceName" dir=in action=allow program="$sqlServrExe" enable=yes profile=domain 

Write-Log -level "Info" -message "Windows Firewall Domain Profile complete - $cmdResult"

 

 

May 31, 2012 at 8:06 AM
Edited Jul 19, 2012 at 12:25 PM

How about this? I had written this couple of months ago as a post script for SQL Server install.

Function Remove-ComObject
{ 
    <# 
        .SYNOPSIS 
            Remove a COM object and do garbage collection. 
              
  
        .DESCRIPTION 
            This removes a COM Object by releasing the allocations in memory. 
            It also calls the garbage collection routines afterwards for cleanup. 
              
        .PARAMETER ComObject 
            A COM object. 
              
            Required                                         true 
            Position                                         named 
            Default value                                  
            Accept pipeline input                            false 
            Accept wildcard characters                       false 
                      
        .EXAMPLE 
            Remove-ComObject -ComObject $objExcl 
          
        .EXAMPLE 
            Remove-ComObject -O $objExcl 
          
        .NOTES 
    #>
    param( 
        [alias("O")] 
        [parameter(Mandatory=$true)] 
        [ValidateNotNullOrEmpty()] 
        [System.__ComObject]
        $ComObject
    ) 
    try 
    { 
          
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($ComObject) | Out-Null
    } 
    catch 
    { 
        throw $("Unable to release ComObject") 
    } 
    finally
    { 
        #Clean whatever is released or just pass through func stack to see if we clean anything up. 
        [GC]::Collect()  
        [GC]::WaitForPendingFinalizers() 
    } 
      
} 


Function Get-SQLInstanceList
{
<#
    Currently only runs on local machine
    TODO: Allow to query and run on remote machine.
#>
    try
    {
        $instNameKey = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\'
        $sqlInstLst = @()
        $msSqlKey = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\"  

        $instNames = (Get-Item -Path $instNameKey).GetValueNames()
        foreach($inst in $instNames)
        {
            $instIDKey = $instNameKey
            $instID = (Get-ItemProperty -Path $instIDKey -Name $inst).$inst
            $sqlBin = (Get-ItemProperty -Path ($msSqlKey + $instID + '\Setup') -Name SQLBinRoot).SQLBinRoot
            
            #HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.D11\Setup
            $objInstDtls = New-Object System.Object|
                            Add-Member NoteProperty InstName $inst -PassThru|
                            Add-Member NoteProperty InstID $instID -PassThru |
                            Add-Member NoteProperty SQLBin $sqlBin -PassThru
            
            $sqlInstLst += $objInstDtls
        }
        return $sqlInstLst
    }
    catch
    {
        $(throw "Unable to get SQL Server instances on: $($env:COMPUTERNAME)")
    }
}

Function Search-FireWallRules
{
    try
    {
        $sqlInstLst = Get-SQLInstanceList
        $sqlCmdPath = 'C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE'
        $sqlBrowerPath = 'C:\Program Files (x86)\Microsoft SQL Server\90\Shared\SQLBROWSER.EXE'
        
        #Get the COM objects for firewall
        $fWall = New-Object -ComObject HNetCfg.FWPolicy2
        $CurrentProfiles = $fWall.CurrentProfileTypes
        $fWallRules = $fWall.Rules
        $fWallRuleApps = @()
        $fWallRuleApps += ($fWallRules | Select ApplicationName) | Where-Object {$_.ApplicationName -match "sql"}

        foreach($sqlInst in $sqlInstLst)
        {
            Write-Debug "Searching for Pattern: $($sqlInst.InstID)\MSSQL\Binn\sqlservr.exe"
            if($fWallRuleApps -match "$($sqlInst.InstID)\\MSSQL\\Binn\\sqlservr\.exe" )
            {
                Write-Host "Found FireWall Rule for SQL Server instance: $($sqlInst.InstName)" -Fore Green
            }
            else
            {
                $sqlBin = $($sqlInst.sqlBin).ToString() + "\sqlservr.exe"
                $sqlRuleName = "SQL" + $($sqlInst.InstName).ToString()
                Write-Debug "$sqlBin -> $sqlRuleName"
                Write-Host "Did not FireWall Rule for SQL Server instance: $($sqlInst.InstName)" -Fore Red
                Write-Host "Adding firewall rule... $sqlRuleName" -Fore Yellow

                $fWallNewRule = New-Object -ComObject HNetCfg.FWRule
                $fWallNewRule.Name = $sqlRuleName
                $fWallNewRule.ApplicationName = $sqlBin
                #$fWallNewRule.Protocol = 6 #NET_FW_IP_PROTOCOL_TCP; 17 for UDP
                #if we do not specify then it will be added for all protocols.
                #could not find a way to only specify TCP and UDP
                $fWallNewRule.Enabled = $true
	            $fWallNewRule.Profiles = 7 #All Profiles
                #$fWallNewRule.Profiles = $CurrentProfiles
                $fWallNewRule.Action = 1 # NET_FW_ACTION_ALLOW
                $fWallNewRule.EdgeTraversal = $false
                $fWallRules.Add($fWallNewRule)
                Remove-ComObject -ComObject $fWallNewRule
            }
        }
            
    }
    catch
    {
        Write-Host "$_" -Fore Red
    }
    finally
    {	
	
        Remove-ComObject -ComObject $fWallRules
        Remove-ComObject -ComObject $fWall
        Write-Host "Exiting..." -fore Yellow
    }
}

Search-FireWallRules