Forum FAQ: How do I configure Windows Firewall to allow remote connections using TCP/IP protocol for SQL Server?

  • Article

Questions

I could not connect to my SQL Server instance and I doubt that it is a Windows firewall issue. How do I configure Windows Firewall to allow the remote connection to my SQL Server.

Answer

On the server on which SQL Server runs, we need to find out which ports that SQL Server is listening and add which to Windows Firewall Inbound Exceptions.

Firstly, we need to add TCP port on which SQL Server service is listening to the Windows Firewall Inbound Exceptions so that the client can make connections to SQL Server over Windows Firewall.

Secondly, if we do not specify TCP port in the connection string and SQL Server is not listening on the TCP 1433 port, we need to add UDP 1434 port on which SQL Server Browser Services is listening to the Windows Firewall Inbound Exceptions. It is because:

a. If TCP port is specified in the connection string, the connection provider such as SQL Native Client will use this port to connect to SQL Server, so that the connection provider does not need SQL Server Browser Services to find the corresponding on which SQL Server is listening;

b. If TCP port is not specified in the connection string, the connection provider will send a UDP package to 1434 port to the server. So, if UDP 1434 port is not open in the Windows Firewall therefore the connection provider will not get the corresponding TCP port on which SQL Server is listening. As a result, the connection provider will use TCP 1433 port to connect to SQL Server, which may be failed if the SQL Server does not listen on TCP 1433 port. So as to make connection to SQL Server in this case, we need to add UDP 1434 port to Windows Firewall Inbound Exceptions.

To find out the TCP port on which SQL Server is listening, please follow the steps below:

1. Open SQL Server Configuration Manager from Start -> All Programs -> Microsoft SQL Server 2005/2008/2008 R2 -> Configuration Tools;

2. Click Protocols for <SQLInstanceName> under SQL Server Network Configuration node in the left panel of SQL Server Configuration Manager window; Double-click TCP/IP in the right panel, switch to IP Address tab and you could find out all ports that SQL Server is listening for each IP address or a specify port for all IP addresses.

Please note that if SQL Server is listening on dynamic ports, its ports may be changed after restarting each time. As a result, you need to find its current listening TCP port(s) and add all of them to Windows Firewall Inbound Exceptions.

References

SQL Server Browser Services
https://msdn.microsoft.com/en-us/library/ms181087.aspx

How to: Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager): https://msdn.microsoft.com/en-us/library/ms177440.aspx

Applies to

Microsoft SQL Server 2005 all editions;
Microsoft SQL Server 2008 all editions;
Microsoft SQL Server 2008 R2 all editions.