SQL Instance & Firewall

V5 Question
1

Hi guys,

I have a basic question.

How can I allow an SQL Instance through firewall on Windows 10?

Ive this steps below.

– Control Panel > Firewall Settings > Advanced Settings > Inbound Rules > Add New Rule > TCP/IP 1433 Port
– Control Panel > Firewall Settings > Advanced Settings > Inbound Rules > Add New Rule > UDP 1433 Port
– Control Panel > Firewall Settings > Advanced Settings > Outbond Rules > Add New Rule > TCP/IP 1433 Port
– Control Panel > Firewall Settings > Advanced Settings > Outbond Rules > Add New Rule > UDP 1433 Port

However, without turning off the Private Firewall I cant locate the SQL instance via Microsoft Data Link (.udl).

As soon as I turn off the firewall its instantly shown on the UDL.
image

2

Names pipes? I had missed those before and showed similar simprons.

3

Oh I forgot to mention those.

I have
TCP/IP Enabled
and
Named Pipes Enabled

4

This guide describes it in full detail everything you need to configure in Windows firewall for SQL Server and Message Service. It really should be made into a tutorial as it’s the best guide for this on the forum IMHO.

I use this PowerShell script (further down on the same topic) to configure the firewall on all setups, it saves a bit of time.

I have modified it a little to use the common port 9000 for message service that everyone uses now, and also I set the network profile to “ANY” - this is not the best from a security perspective, but it saves a lot of hassle on support when something changes (i.e. they connect to new wifi network, they change router or their router does a firmware update without their knowledge), it means the firewall settings will work in both the Private and Public network configurations under Windows. Feel free to change it back if you want a more secure setup.

Here is my amended script, same setup instructions as described in the link above:

function getRule {
    param(
        [string] $name
    )
    $fw = Get-NetFirewallRule -DisplayName "$name" -ErrorAction:SilentlyContinue
    if ($fw) {
        return [bool]$true
    } else {
        return [bool]$false
    }
}
function setRule {
    param(
        [string] $name,
        [string] $port,
	[string] $protocol,
	[string] $profile
    )
    Set-NetFirewallRule -DisplayName "$name" -Action "Allow" -Direction "Inbound" -Enabled "True" -LocalPort "$port" -Profile "$profile" -Protocol "$protocol" -Verbose
}

function addRule {
    param(
        [string] $group,
        [string] $name,
        [string] $port,
	[string] $protocol,
	[string] $profile
    )

    $fw = getRule("$name")
    if ($fw) {
	Write-Host -ForegroundColor:Black -BackgroundColor:Magenta "`r`n*** Rule already exists, Modifying Rule: '$name' ..."
	setRule -name:"$name" -port:"$port" -profile:"$profile" -protocol:"$protocol" -Action "Allow" -Direction "Inbound" -Enabled "True"
    } else {
	Write-Host -ForegroundColor:Black -BackgroundColor:Green "`r`n****** Adding Rule: '$name' ..."
        New-NetFirewallRule -Group "$group" -DisplayName "$name" -Action "Allow" -Direction "Inbound" -Enabled "True" -LocalPort "$port" -Profile "$profile" -Protocol "$protocol"
    }

}

addRule -profile:"Any" -protocol:"TCP" -port:1433           -group:"Custom Rule - SambaPOS Multi-terminal" -name:"SambaPOS SQL Traffic"
addRule -profile:"Any" -protocol:"UDP" -port:1434           -group:"Custom Rule - SambaPOS Multi-terminal" -name:"SambaPOS SQL Browser Traffic"
addRule -profile:"Any" -protocol:"TCP" -port:9000           -group:"Custom Rule - SambaPOS Multi-terminal" -name:"SambaPOS Messaging Server"
2 Likes
5

Good idea I will work on that tonight. I never saw those posts and that script is dang useful. Thanks.

2 Likes
6

Oh wow! I havent seen that tutorial before.

1 Like