Scenario 1
SQL Server instance is running on default port 1433 TCP/IP. So you need to add TCP/IP 1433 in exception list of Windows Firewall.
Access Windows Firewall exception frame, go to exceptions tab and click Add Port… button. A frame will appear as shown below, provide parameters with any descriptive name as shown in below figure and click OK.
Now you can access your SQL server instance on the default port without any customization in connection parameters. Although it would have no effect on your connection parameters since you are using the default port, you could also add SQL Server Browser in the exception list. For this, go to exceptions tab and click Add Port… button and add 1434 port number for UDP and click OK.
Scenario 2
In this scenario you are using a port other than the default 1433 and SQL Server Browser Service is running.
Suppose you have configured SQL Server to run on port 55120. So you would add the custom port in the exception list as shown below.
To make use of the SQL Server Browser service, you would also add port 1434 UDP in exception list as shown below.
If you do not include port 1434 UDP in the exception list then there would be no use for the SQL Server Browser service even if it was started. Now to access the instance just enter IP of machine with instance name without any port number. SQL Server Browser service would do the rest for you and it will connect you to the instance even if a custom port is being used.
Scenario 3
In cases where the instance to be accessed is using the default port TCP 1433 and SQL Server Browser Service is not running, you can connect to an instance without specifying the port. You may note that in the case where instance to be accessed is on default TCP/IP port, SQL Server Browser Service status would not effect the way you are connecting to the instance.
Scenario 4
If instance is on any custom defined port and SQL Server Browser service is not running, then just add SQL Server instance custom port in exception list.
Now you have to provide IP + InstanceName + Port# for connection. If you provide just IP along with instance name, there is no mechanism to get the port number. As a result the connection would fail.
Rules for working with firewall and TCP/IP
Above mentioned scenarios may be used to build more complex configurations where more than one instances is installed on the same machine with different port configurations. Following are rules that may be deducted from above four scenarios
- Make sure that your instance is configured to use a static port either default or custom while configuring firewall access through adding ports in exception list.
- If you need to use SQL Server Browser service, include UDP 1434 in exception list
- Include 1433 in exception list, if your instance is running on default port
- Include custom port in exception list, if instance is running on custom port
- If you are not using SQL Server Browser service then provide IP + InstanceName + Port for connection to custom port other than default (1433).
Configuring access for Named Pipes
If you have configured named pipes as a network protocol, then to allow access through Windows Firewall, go to configuration frame and enable File and Printer Sharing through the firewall as shown below.
This will allow you to access the SQL Server engine on named pipes behind the firewall.
Using firewall with SQL Server on dynamic ports
If you have configured SQL Server to listen on dynamic ports, even then you can use the firewall for your SQL Server instance. To do this you include the “sqlservr.exe” program in your firewall exception list. Just go to exceptions tab and click on ‘Add Program’ button.
Browse to “sqlservr.exe”, it is in the bin directory of your SQL Server instance installation. Click OK and now you can access this instance of SQL Server without adding specific ports in the exception list.
This configuration would work for only one instance for which you have browsed to sqlservr.exe.
To configure multiple instances along with dynamic ports, add “sqlservr.exe” from each instance in the exception list.
Adding sqlsrvr.exe works for static ports also.
Configuring more complex scenarios
Once you have worked with the above mentioned simple scenarios, you may join these pieces to build more complex configurations of instances, ports and SQL Server Browser services.
