How to Allow Microsoft SQL Server Express through the Windows Firewall, Dynamically
If you’ve just installed Microsoft SQL Server (MSSQL), and you’re looking to connect to it from outside of the server. You’re going to need to allow access in to the instance of the SQL Engine. The most common way to do this is by just allowing Port 1433 and optionally 1434 if you want to use server browser. I’m honestly not the biggest fan of that method, because it prohibits the use of Dynamic Ports. Firstly, I should probably say, although I rarely use Dynamic Ports, the one time that someone else seems to enable it, having it configured in the firewall tends to prevent a trouble ticket. To do this, we need to allow the SQL Server Executable, rather than the specific port, access through the Windows Firewall.
Firstly, you need to open the Windows Firewall with Advanced Security Snapin. If you don’t know where that is, chances are you shouldn’t be working on SQL Server. It’s under Administrative Tools.
Next, we’re going to want to create a new rule Inbound Rule. Go to Inbound Rules and press New Rule on the top right.
Select Program from the List, and press next.
Next up, enter the following path into your “this program path” section.
%ProgramFiles%Microsoft SQL ServerMSSQL11.SQLEXPRESSMSSQLBinnsqlservr.exe
This may be slightly different depending on your SQLExpress version or Instance name, just check the Microsoft SQL Server directory for your instance name.
Finally press allow connection, and on the next window apply the profiles you want to have access to the SQL server. If you don’t know the answer, just apply it to all profiles. Finally, name it something memorable, like ‘MSSQL Server”.
This will allow users access to any port that sqlservr.exe wants access too. However, it may not necessarily allow SQL Browser to function. If you would also like SQL Browser to be exposed to the internet, you can either allow just that port, or you can find it at this path:
C:Program Files (x86)Microsoft SQL Server90Sharedsqlbrowser.exe