Also, if you have to document your configuration, the PowerShell scripts that exactly what you need.
As it was said: "One line of PowerShell worth 15 GUI screen shoots".
The very first script I want to introduce is the collection of Firewall basic information.
That Script does not collect ALL information, only the most common for handling SQL Server requests.
Description: Returns list of firewall rules, which are protocol set as "TCP" or "UDP" or have set associated application.
That script has the purpose of identifying any relevant rule in the long list.
For instance you might already have a SQL Server related rule, but it is disabled or addresses wrong port or even blocking a port. And you definitely do not want to have collisions nor duplicates.
The Script has list of adjustable filters:
$Direction - Direction of firewall rule: Inbound or Outbound;
$Action - Action rule performs: Allow or Block;
$Enabled - Status of a rule: Enabled - True or False;
$RuleGroup - Group rule has been assigned. By default script uses "$Null" variable, which filters all rules without assigned group. However you can specify a group a name if necessary;
$DisplayName - Name of a rule. By default I use an expression "*SQL*" to search for rules which have word "SQL" in their name. To retrieve all rules us "*". To retrieve any particular rule use rule name.
cls
$Direction = "Inbound" # Options:
"Inbound"/"Outbound"
$Action = "Allow" # Options: "Allow"/"Block"
$Enabled = "True" # Options: "True"/"False"
$RuleGroup = $Null # Options: $Null
/ Gropup Name
$DisplayName = "*SQL*" # Option: "*SQL*" / "*" / Any
regular expression
$Firewall =
@()
$Rules = Get-NetFirewallRule |`
where { $_.Direction
-eq $Direction}
|`
where { $_.Action -eq $Action}
|`
where { $_.Enabled -eq $Enabled}
|`
where { $_.RuleGroup
-eq $RuleGroup}
|`
where { $_.DisplayName
-like $DisplayName}
|`
Select DisplayName,
RuleGroup,
InstanceID,
Enabled,
Direction,
Action
foreach ($App
in Get-NetFirewallApplicationFilter
| Select Program, InstanceID)
{
$InstanceID
= ($App
| Format-Table
-Property InstanceID
-HideTableHeaders |
Out-String).Trim()
$Rule
= $Rules
| Where { $_.InstanceID
-eq $InstanceID}
$DisplayName
= ($Rule.DisplayName +
"").Trim()
If ($DisplayName -ne
""
-or $DisplayName
-like "*SQL*"
) {
$Program
= ($App
| Format-Table
-Property Program
-HideTableHeaders |
Out-String).Trim()
$Ports
= Get-NetFirewallPortFilter
| Where { $_.InstanceID
-eq $InstanceID}
If
($Ports.Protocol
-eq "TCP"
-or $Ports.Protocol -eq
"UDP" -or
$Program -ne
"Any") {
$Firewall
+= New-Object
-TypeName PSObject
-Property @{
DisplayName = $DisplayName
RuleGroup = $Rule.RuleGroup
Direction = $Rule.Direction
Action = $Rule.Action
Enabled = $Rule.Enabled
Protocol = $Ports.Protocol
LocalPort = $Ports.LocalPort
RemotePort = $Ports.RemotePort
RemoteAddress = (Get-NetFirewallAddressFilter
| where { $_.InstanceID
-eq $InstanceID}).RemoteAddress
Program = $Program
InstanceID = $InstanceID
}
}
}
}
$Firewall | Format-table -AutoSize -Property
DisplayName,
RuleGroup,
Direction,
Action,
Enabled,
Protocol,
LocalPort,
RemotePort,
RemoteAddress,
Program
You just have to copy paste that script, maybe do little filter adjustments and paste it into PowerShell window, which is supposed to run under Administrator's privileges.
Here is what you should expect:
If you want to research one of the returned rules alone you can run following script right after by just specifying rule's DisplayName like this:
$DisplayName = "SQ Test
1"
$Firewall | Where { $_.DisplayName -eq
$DisplayName } |`
Format-List -Property DisplayName, RuleGroup, Direction, Action, Enabled, Protocol, LocalPort, RemotePort, RemoteAddress, Program, InstanceID
Result of that query returns very important "InstanceID", which you potentially can use to address some informational pieces of the Firewall Rule.
Now, when we identified existing rules or did not find appropriate one we can start doing changes:
Create a new rule
New-NetFirewallRule -DisplayName "SQL
Server Access via Port 1433" -Direction
Inbound -Action
Allow -Protocol
TCP –LocalPort
1433 -Description
"SQL Server Port access Rule"
That script creates a new rule, which allows inbound connections via default SQL Server port 1433.
Enable/Disable rule
There are 2 methods to Enable/Disable rules: Using "Enable-NetFirewallRule" and "Disable-NetFirewallRule" or just by universal "Set-NetFirewallRule":
Enable-NetFirewallRule -DisplayName "SQL
Server Access via Port 1433"
Disable-NetFirewallRule -DisplayName "SQL
Server Access via Port 1433"
Set-NetFirewallRule -DisplayName "SQL
Server Access via Port 1433" -Enabled
True
Set-NetFirewallRule -DisplayName "SQL
Server Access via Port 1433" -Enabled
False
Edit Rule
The most common changes to a rule can be changing a Protocol, Port Number or list/range of remote computers, which can go through that rule:
Set-NetFirewallRule -DisplayName "SQL
Server Access via Port 1433" -Protocol
"UDP" -LocalPort
1434
Set-NetFirewallRule -DisplayName "SQL
Server Access via Port 1433" -RemoteAddress
"192.168.58.1-192.168.58.11","192.168.58.102"
You also can change "Profile" with values: Any, Domain, Private, Public, or NotApplicable;
If you are really want to secure your server you can dig into settings of "Authentication", "RemoteUser", "RemoteMachine", but to do so you'd need more advanced technics to access Active Directory and that might be area you do not want to go.
Delete rule
Obviously, if you created rule by mistake, or have a rule, which should not exist you can delete it by following:
Remove-NetFirewallRule -DisplayName "SQL
Server Access via Port 1433"
After each command you can re-run the very first reporting script to make sure the changes you've made are correct.
Be aware that all changes will take immediate effect and you do not need to restart the server or even a service.
And that is it. Not a lot and pretty simple. Much easier for understanding and making any corrections than using GUI with all extra and often unnecessary features.
No comments:
Post a Comment