Doing distributed environments setups rather frequently these days I realized that I really don’t want wasting my time setting up SQL Server firewall rules via GUI (I described the process here) and luckily enough Ryan Mangan already created such script. All I had to do is try it (confirm that it works), save it on GitHub and share on my blog for the benefit of wider community:
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned
#Enabling SQL Server Ports
New-NetFirewallRule -DisplayName “SQL Server” -Direction Inbound –Protocol TCP –LocalPort 1433 -Action allow
New-NetFirewallRule -DisplayName “SQL Admin Connection” -Direction Inbound –Protocol TCP –LocalPort 1434 -Action allow
New-NetFirewallRule -DisplayName “SQL Database Management” -Direction Inbound –Protocol UDP –LocalPort 1434 -Action allow
New-NetFirewallRule -DisplayName “SQL Service Broker” -Direction Inbound –Protocol TCP –LocalPort 4022 -Action allow
New-NetFirewallRule -DisplayName “SQL Debugger/RPC” -Direction Inbound –Protocol TCP –LocalPort 135 -Action allow
#Enabling SQL Analysis Ports
New-NetFirewallRule -DisplayName “SQL Analysis Services” -Direction Inbound –Protocol TCP –LocalPort 2383 -Action allow
New-NetFirewallRule -DisplayName “SQL Browser” -Direction Inbound –Protocol TCP –LocalPort 2382 -Action allow
#Enabling Misc. Applications
New-NetFirewallRule -DisplayName “HTTP” -Direction Inbound –Protocol TCP –LocalPort 80 -Action allow
New-NetFirewallRule -DisplayName “SSL” -Direction Inbound –Protocol TCP –LocalPort 443 -Action allow
New-NetFirewallRule -DisplayName “SQL Server Browse Button Service” -Direction Inbound –Protocol UDP –LocalPort 1433 -Action allow
#Enable Windows Firewall
Set-NetFirewallProfile -DefaultInboundAction Block -DefaultOutboundAction Allow -NotifyOnListen True -AllowUnicastResponseToMulticast True
Original blog post by Ryan/source of this script: PowerShell Script for SQL Firewall rules
This post is an addition to my older post about configuring MSDTC in K2 environment and it has been triggered by the following error:
So basically I had freshly installed K2 4.6.6 environment (don’t ask me why I’m using such an old version 🙂 ) and it was first deployment of a simplistic workflow which gave me this error.
And if error message text which says: “The partner transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D025)” doesn’t tell you that something wrong with your MSDTC config then quick google search will confirm this to you.
The thing is that all you need to know is indeed covered by K2 documentation, but problem of any software documentation is that somewhat like a good dictionary its creation is driven by certain standards making it perfect for specific look ups for information but at the same time deter reader from reading it end to end, and by contrast with dictionaries software documentation does not have super simple data organization facilitating quick and precise look ups. So I mean rarely people do read specific sections of it unless they driven by specific error to specific page 🙂
To recap MSDTC side requirements for K2: You need to have it configured on all K2 servers and SQL servers used by K2 (have clusters? do config it on all nodes). As you have seen in my previous blog post it boils down to setting number of check boxes on Security tab of Local DTC properties which is reachable through the following commands: dcomcngf or comexp.msc (still keep forgetting these 🙂 ).
It is worth noting that K2 Setup Manager is capable to set these properties on K2 servers, but you have to go to SQL and set the same settings too. This was first correction I made in my environment after seeing this error. But it was enough. Looking a little bit more into K2 documentation I noticed this:
I actually decided to this via GUI on SQL server, and what you need to do is to enable all 3 rules from MSDTC group:
And you have to enable this on all K2 servers and SQL servers. Trust me, I tried to enable this on SQL servers only first 🙂 The same error persist till you enable it both on K2 and SQL servers.
In situations when you unable to connect to remote WS 2008 box via RDS due to Windows Firewall being enabled without inbound RDS rule enabled you may try to enable required firewall rule remotely.
If PS on machine in question is configured for remoting you may open remote PS session on this machine, using following command:
enter-pssession -computername REMOTE_COMPUTER_NAME
If remote PS session opened successfully you may run both PS commands and regular CLI commands for remote machine there. To check inbound RDS rule for Windows firewall use:
netsh advfirewall firewall show rule name=”Remote Desktop (TCP-IN)”
If this role not enabled issue following command to enable it:
netsh advfirewall firewall set rule name=”Remote Desktop (TCP-IN)” new enable=yes
Starting with Windows Server 2012 you may control Windows Firewall with PS commandlets which is much more convenient and easier.