Tuesday, September 13, 2016

Configuring Windows Firewall for SQL Server using PowerShell

You can configure Windows Firewall using GUI or you can ask your sysadmin to do it, but what if you do not have Sysadmin or GUI?
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.

Monday, September 12, 2016

10 PowerShell scripts to configure SQL Server.


Previously in my blog I described following technics of administering SQL Server using PowerShell:
"How to Install and prepare Windows Core for SQL Server installation"
"Five Steps of Remote SQL Server installation using PowerShell"

In my most recent post I've started "Configuration" topic, by providing very important PowerShell script, which I want to use in this post post too: "Collect SQL Server Registry information using PowerShell"

In this post I want to talk about the biggest problem for SQL Server DBA, who works on Windows Core machine: Configuring the SQL Server.

If you ever touched SQL Server installed on Windows Core you probably faced the issue of not being able to change default port or add startup parameter because Microsoft did not provide you a tool to do that.
The tool everybody is using: "SQL Server Configuration Manager" does not work in Windows Core environment!!! Moreover, although it is designed as console application it does not easily allow you to connect to another instance. So, as SQL DBA you are doomed.


Now I want to change the thing. I'll give you the set of scripts to configure SQL Server in Core environment.

Script #0. Connect remotely to SQL Server.

At first, you have to run PowerShell as an administrator and run following command to connect to remotely to our Windows Core server:
PS> Enter-PSSession -ComputerName <ServerName> -Credential <Account Name>

Script #1. Get List of SQL Server Services.

The very first step on the server is to check for SQL Server services. Unless somebody completely renamed them you can get them by running following:
#Get SQL Server Services:
Get-WMIObject win32_service | Where-Object { $_.DisplayName+$_.Name -like "*SQL*" } | Sort-Object state,displayname | Format-Table -Autosize -Property State, Name, DisplayName, StartMode, PathName 
As the result you'll get something like this:

In my case I have two SQL Server Services:
Default instance: MSSQLSERVER - SQL Server (MSSQLSERVER)
Named Instance: MSSQL$MSI2016 - SQL Server (MSI2016)

Both instances are stopped because they are set for "Manual" Start Mode

Script #2. Stop/Start/Restart the service.

That is the most important script during the SQL Server configuration.
Stop-Start or Restart must be performed to apply any configured changes:
#Stop/Start/Restart SQL Server Service:
Stop-Service -displayname "SQL Server (MSI2016)"
Start-Service -displayname "SQL Server (MSI2016)"

Restart-Service -displayname "SQL Server (MSI2016)"

In this particular example I purposely used parameter "DisplayName" instead of "Name", which would be easier and more logical.
As you can see, the name of "named instance" is "MSSQL$MSI2016", which contains special PowerShell symbol "$". That special symbol requires extra handling. So, to make life easier I go with "DisplayName".

Script #3. Change Service Start Mode.

As you can see, my SQL Services are stopped after my server rebooted.
To make these services starting automatically after the server starts I have to change the StartMode to "Auto". The other options are "Manual" and "Disabled".
#Change SQL Server Startup mode:
$SQLService = "SQL Server (MSI2016)"
$NewMode = "Auto" # Options: Auto/Manual/Disabled
Set-Service -StartupType $NewMode -Name (Get-WMIObject win32_service | Where-Object { $_.DisplayName -eq $SQLService }).Name

Script #4. Get SQL Server Registry Instance Path.

That is the second most important configuration script. It searches for specific sub-folder in the registry, where SQL Server Instance information is located and stores it inside of "$SQLInstancePath" variable.

#Get SQL Server Instance Path:
$SQLService = "SQL Server (MSI2016)";
$SQLInstancePath = "";
$SQLServiceName = ((Get-Service | WHERE { $_.DisplayName -eq $SQLService }).Name).Trim();
If ($SQLServiceName.contains("`$")) { $SQLServiceName = $SQLServiceName.SubString($SQLServiceName.IndexOf("`$")+1,$SQLServiceName.Length-$SQLServiceName.IndexOf("`$")-1) }
foreach ($i in (get-itemproperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server").InstalledInstances)
{
  If ( ((Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL").$i).contains($SQLServiceName) )
  { $SQLInstancePath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\"+`
  (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL").$i}
}
$SQLInstancePath

In my case the resulting string is:
HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSI2016
That is Registry Path to my "named instance" info.
That Path is stored inside of "$SQLInstancePath" variable. So, be careful and do not change this value during the following operations. To check its value you always can run its name as it was done in the last line of the script.

Script #5. Read SQL Server Error Log file.

That is extremely important troubleshooting script. When you start/restart the SQL Server service and it does not come up, you can run this script to see what was going on during the SQL Server startup and what was the problem (just note that value of "$SQLInstancePath" must be pre-set by previous script):
#Read SQL Server Error Log file:
$LogFile = (Get-ItemProperty "$SQLInstancePath\CPE").ErrorDumpDir + "ERRORLOG"
Get-Content "$LogFile" | more

Script #6. Enabling/Disabling Network properties:

I do not want to make life too complicated and included in this section only two basic properties: "ForceEncryption" and "HideInstance". If you going to the advanced you have the general idea:
# Enable/Disable protocol properties:
$Property = "HideInstance" # Options: ForceEncryption/HideInstance
$Enabled = "0"             # Options: "0" - Disabled / "1" - Enabled
(Get-ItemProperty "$SQLInstancePath\MSSQLServer\SuperSocketNetLib").$Property
Set-ItemProperty -Path "$SQLInstancePath\MSSQLServer\SuperSocketNetLib" -Name "$Property" -Value $Enabled
(Get-ItemProperty "$SQLInstancePath\MSSQLServer\SuperSocketNetLib").$Property

Script #7. Enabling/Disabling Network protocols.

In most cases you just need to have only TCP protocol enabled and all other disabled. So, use this script to control that situation and do not forget to restart SQL Server service to put the change into an effect:
#Enable/Disable network protocols:
$Protocol = "Tcp" # Options: Tcp/Np/Via/Sm
$Enabled = "1"     # Options: "0" - Disabled / "1" - Enabled
(Get-ItemProperty "$SQLInstancePath\MSSQLServer\SuperSocketNetLib\$Protocol").Enabled
Set-ItemProperty -Path "$SQLInstancePath\MSSQLServer\SuperSocketNetLib\$Protocol" -Name "Enabled" -Value $Enabled
(Get-ItemProperty "$SQLInstancePath\MSSQLServer\SuperSocketNetLib\$Protocol").Enabled

Script #8. Changing "ListenOnAllIPs" TCP protocol property.

That script does a tiny, but very necessary change. By default "ListenOnAllIPs" option is enabled, but you might need to have it disabled on your very secured Core server:

# Enable/Disable ListenOnAllIPs TCP protocol property:
$Enabled = "0"    # Options: "0" - Disabled / "1" - Enabled
(Get-ItemProperty "$SQLInstancePath\MSSQLServer\SuperSocketNetLib\Tcp").ListenOnAllIPs
Set-ItemProperty -Path "$SQLInstancePath\MSSQLServer\SuperSocketNetLib\Tcp" -Name "ListenOnAllIPs" -Value $Enabled
(Get-ItemProperty "$SQLInstancePath\MSSQLServer\SuperSocketNetLib\Tcp").ListenOnAllIPs

Script #9. Configuring TCP/IP protocols' properties.

That PowerShell script sets all parameters for one single TCP/IP protocol at the time. You have a choice to run it individually for all your TCP/IP protocols and for "IPALL", in case ListenOnAllIPs is enabled.
Will go through parameters in that script:
1. $IPProtocol. Possible options: "IPALL","IP1","IP2".... for all IP protocols in in your system.
2. $Enabled. That is easy: "0" - Disabled / "1" - Enabled
3. $Active. Same choice: "0" - Inactive / "1" - Active
4. $Port. Here you can specify a port. By default it 1433, but you can put there any value up to 64K. You also can put there "0"(zero) or just leave it blank. If you make a wrong call just see the ERRORLOG file for an error or SQL Server is listening to.
5. $DynamicPort. Not really sure if you can control this, but I'd suggest to keep it "0" or empty.
6. $IPAddress. The IP address that IP protocol will be listening in the system. The main rule here that IP address for each protocol MUST BE UNIQUE, otherwise you'll get duplication error during the SQL Server restart.
In this particular script I've made an example of settings for "IPALL" when ListenOnAllIPs is disabled:
# Set IP protocol's properties:
$IPProtocol="IPALL"   # Options: "IPALL"/"IP4"/"IP6"/Etc
$Enabled = "0"            # Options: "0" - Disabled / "1" - Enabled
$Active = "0"              # Options: "0" - Inactive / "1" - Active
$Port = ""                   # Options: "0"/"" (Empty)
$DynamicPort = "0"    # Options: "0"/"" (Empty)
$IPAddress="::0"        # There must not be IP Address duplication for any IP Protocol

$SQLTcpPath = "$SQLInstancePath\MSSQLServer\SuperSocketNetLib\Tcp"
Get-ChildItem $SQLTcpPath | ForEach-Object {Get-ItemProperty $_.pspath} `
| Format-Table -Autosize -Property @{N='IPProtocol';E={$_.PSChildName}}, Enabled, Active, TcpPort, TcpDynamicPorts, IpAddress

Set-ItemProperty -Path "$SQLTcpPath\$IPProtocol" -Name "Enabled" -Value $Enabled
Set-ItemProperty -Path "$SQLTcpPath\$IPProtocol" -Name "Active" -Value $Active
Set-ItemProperty -Path "$SQLTcpPath\$IPProtocol" -Name "TcpPort" -Value $Port
Set-ItemProperty -Path "$SQLTcpPath\$IPProtocol" -Name "TcpDynamicPorts" -Value $DynamicPort
Set-ItemProperty -Path "$SQLTcpPath\$IPProtocol" -Name "IPAddress" -Value $IPAddress

Get-ChildItem $SQLTcpPath | ForEach-Object {Get-ItemProperty $_.pspath} `
| Format-Table -Autosize -Property @{N='IPProtocol';E={$_.PSChildName}}, Enabled, Active, TcpPort, TcpDynamicPorts, IpAddress

Here is a typical output from this script:


Here are typical settings for configuring  "IP4":
$IPProtocol="IP4"  # Options: "IPALL"/"IP4"/"IP6"/Etc
$Enabled = "1"      # Options: "0" - Disabled / "1" - Enabled
$Active = "1"        # Options: "0" - Inactive / "1" - Active
$Port = "1433"      # Options: "0"/"" (Empty)
$DynamicPort = "0"  # Options: "0"/"" (Empty)
$IPAddress="10.10.1.10" # There must not be IP Address duplication for any IP Protocol

Script #10. Create/Read/Update/Delete SQL Server Startup Parameters

That is the most advance and dangerous script. If you do not know what you are doing do not touch it.

The general advise would be to do not touch "SQL Arguments 0-1-2":
# Read Existing SQL Server Startup Parameter
(Get-ItemProperty "$SQLInstancePath\MSSQLServer\Parameters" | Select SQLArg*  | Format-List | Out-String ).trim() -replace "SQLArg","`tSQLArg"

#Create New SQL Server Startup Parameters
$ParamNumber = "3"
$ParamValue = "-T1117"
New-ItemProperty -Path "$SQLInstancePath\MSSQLServer\Parameters" -Name ("SQLArg$ParamNumber") -Value $ParamValue -PropertyType String -Force | Out-Null
(Get-ItemProperty "$SQLInstancePath\MSSQLServer\Parameters" | Select SQLArg*  | Format-List | Out-String ).trim() -replace "SQLArg","`tSQLArg"

#Update SQL Server Startup Parameter
$ParamNumber = "3"
$ParamValue = "-T1118"
Set-ItemProperty -Path "$SQLInstancePath\MSSQLServer\Parameters" -Name ("SQLArg$ParamNumber") -Value $ParamValue
(Get-ItemProperty "$SQLInstancePath\MSSQLServer\Parameters" | Select SQLArg*  | Format-List | Out-String ).trim() -replace "SQLArg","`tSQLArg"

#Delete SQL Server Startup Parameter
$ParamNumber = "3"
Remove-ItemProperty -Path "$SQLInstancePath\MSSQLServer\Parameters" -Name ("SQLArg$ParamNumber")
(Get-ItemProperty "$SQLInstancePath\MSSQLServer\Parameters" | Select SQLArg*  | Format-List | Out-String ).trim() -replace "SQLArg","`tSQLArg"


In the script I'm creating new parameter for setting trace flag "1117", then change it to a flag "1118" and then delete that parameter.


These 10 scripts do not cover ALL possible advanced features of SQL Server, but if you are smart enough to use these advanced features, I hope you will be able to modify these scripts for your task.

If you think some scripts are working incorrectly in your environment or you'd like me to cover some additional features please leave a comment.

And do not forget to use "Registry Collection Info Script" from my prior post. It will make your navigation much easier.