As Netsh Firewall commands are now deprecated , I have written a PowerShell script for use with deploying SQL or accessing remote instances.
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
Great post Ryan, very informative. Unfortunately, I had to disable my domain firewall to connect from SQL Management studio on another domain computer.
Set-NetFirewallProfile -Profile DOMAIN -Enabled False
Have you ran into this issue before? I am not using a custom SQL port as far as I know. I am using WIndows Server Core 2012 with SQL Server 2012 SP1 Enterprise.
Firewalls are a pain to configure via shell vs GUI.
Issue was because I was using a SQL instance, which have dynamic ports instead of 1433. http://benchmarkitconsulting.com/colin-stasiuk/2009/02/02/what-tcp-port-is-sql-server-running-under/ and http://msdn.microsoft.com/en-us/library/ms177440.aspx
You can change the port from dynamic to static using the SQL server configuration manager. You would need to change the dynamic ports to 1433. (remove the 0’s) and remove the option for listen all under TCP settings.
Best Regards,
Windows Server Core 2012 does not have SQL config manager GUI 😦 Have to use PowerShell or CMD.
Sent from Windows Mail
apologies, i didn’t see the bit a bout running on server core.
please see the following:
http://madhuottapalam.blogspot.co.uk/2006/12/connect-to-remote-sql-server.html
http://sirsql.net/blog/2011/6/21/set-the-sql-tcpip-port-with-powershell.html
Best Regards,
I gave up, ended up disabling the domain firewall via PS. Set-NetFirewallProfile -profile Domain -Enabled False
That does open up security risks, and I would not advise on production systems.
Best regards,
Thank you for the script, it save me sometime
Thanks Guy, amended!