(Updated 2016-10-24)
Intro
If you want to allow remote computers on your network to access an instance of Sql Server running on one machine, you’ll have to add some new Incoming rules to the Windows Firewall, or no connections can come through. So, let’s show you what has to be considered and how to achieve this.
Sql Server Instance Types
Before you can configure the Windows Firewall to allow remote access to your Sql Server instance, it’s important to understand the “instance type” of the Sql Server that you are running. For Sql Server, its “instance type” can either be the default instance, an unnamed instance, or a named instance. Which one you have determines which port you have to open in Windows Firewall.
- When SQL Server is installed as the default instance, it does not require a client to specify the name of the instance to make a connection. The client only has to know the server name. This typically uses Port 1433 in the TCP protocol.
- A named instance is identified by the network name of the computer plus the instance name that you specify during installation (i.e. \\ComputerName\SqlExpress or something similar). The client must specify both the server name and the instance name when connecting. This typically uses the Sql Server Browser service to find the Sql Server instance, and that requires access to Port 1434 UDP protocol.
Windows Firewall Setup to Allow Remote Access
So, in order to allow remote access to the Sql Server instance, you will need to add these Incoming rules to the Windows Firewall:
- Add an Incoming rule to all access the application SqlServr.exe (C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\Binn\sqlservr.exe)
- If you want to access the Sql Server Instance from other computers by using the computer name (i.e. {ComputerName}\SQLEXPRESS, you will need to add an Incoming rule to allow access to the application SqlBrowser.exe (C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe). Note: If you are only going to use the IP address {xxx.xxx.xxx.xxx}\SQLEXPRESS, then you will not need this rule for SqlBrowser.exe.
- Add an Incoming rule for the correct port (1433 TCP or 1434 UDP depending on Sql Server instance type (from above)).
You can use these handy Poweshell scripts to add the above Firewall rules:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
# http://mattslay.com/opening-up-windows-firewall-to-allow-sql-server-to-allow-remote-connections/ # https://blog.brankovucinec.com/2015/12/04/scripts-to-open-windows-firewall-ports-for-sql-server/ Write-host Enabling SQLServer Default instance port 1433 TCP Inbound New-NetFirewallRule -DisplayName "SQL Server TCP Port 1433" –Direction inbound -Protocol TCP –LocalPort 1433 -Action Allow Write-host Enabling SQLServer Named Instance port 143 UDP Inbound New-NetFirewallRule -DisplayName “SQL Server UDP Port 1434” -Direction Inbound –Protocol UDP –LocalPort 1434 -Action allow Write-host Enabling SQLServer EXE Application rule New-NetFirewallRule -DisplayName “SQL Server EXE” -Direction Inbound -Program "C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\Binn\sqlservr.exe" -Action allow Write-host Enabling SQLServer Browser Application rule New-NetFirewallRule -DisplayName “SQL Server Browser” -Direction Inbound -Program "C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe" -Action allow |
You should see the new rules here:
Sql Server Full version
For the full version of Sql Server (not Express), by default it installs as the default instance unless you specify an instance name. So, the next step to allow remote connections to the default instance is to add a Port Rule to open Port 1433 for the TCP protocol.
Sql Server Express
SQL Server Express always installs by default as a named instance unless you force a default instance during setup. So, if you are running a named instance of Sql Server (or Sql Server Express), and you want to allow remote connections to it, add an Incoming port rule for Port 1434 for the UDP protocol.
Since I was running a named instance, I found that I did not need the 1433 TCP port rule at all, I only needed the 1434 UDP port rule (along with the two application rules) to get it working on my network.
Other steps to check
This post only covers the Windows Firewall. There are other steps required in configuring Sql Server itself:
- Enabling the correct protocols, and make sure Sql Server has the option “Allow remote connections to this server” checked on the Connections tab of the Server properties. You can access this setting in Sql Server Management Studio tool.
- Enable the correct protocols in the Sql Server Configuration Management Tool, under the Sql Server Network Configuration node. Honest, I wasn’t sure exactly which I needed, so I enabled all 3. I’m pretty sure TCP/IP was disable by default.
Some details pulled from these links:
https://technet.microsoft.com/en-us/library/ms165614%28v=sql.90%29.aspx
and a comment by user J_m on this Technet article: https://technet.microsoft.com/en-us/library/ms175043%28v=sql.110%29.aspx
Here is some more helpful info I found on this topic:
https://msdn.microsoft.com/en-us/library/ms175043.aspx
Here’s another good post which covers all the issues of this topic in greater detail:
https://www.mssqltips.com/sqlservertip/1929/configure-windows-firewall-to-work-with-sql-server/
After install is finished, there is a helpful System Configuration Check Report that you can review. It is locate at: C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\20161206_092131\SystemConfigurationCheck_Report.htm
And here is some more summary data:
Powershell scripts to enable Firewall rules:
https://blog.brankovucinec.com/2015/12/04/scripts-to-open-windows-firewall-ports-for-sql-server/
One more hint: If you cannot find SQL Server Configuration Manager in your Start menu (Windows 10), then check out this blog post: http://stackoverflow.com/questions/9844771/sql-server-configuration-manager-not-found