SQL Server Configuration Settings for remote TCP/IP connections

In SQL Server Express you will need to enable remote TCP/IP communications for a SQL client to connect to your SQL server. 

In SQL Server Configuration Manager, open the protocols for SQL Express (or your instance) and double-click on the TCP/IP protocol.

Make sure the IP addresses are ACTIVE and the correct TCP port is entered.


More explanation on TCP port settings from the SQL 2008 HELP file:

Use the TCP/IP Properties (IP Addresses Tab) dialog box to configure the TCP/IP protocol options for a specific IP address. Only TCP Dynamic Ports and TCP Port can be configured for all addresses at once by selecting IP All.

Changes take effect when Microsoft SQL Server is restarted. For information about starting and stopping the SQL Server Browser service, see How to: Start and Stop the SQL Server Browser Service in 'SQL Server Books Online' <http://technet.microsoft.com/en-US/library/ms130214(v=sql.110).aspx>

Static vs. Dynamic Ports

The default instance of SQL Server listens for incoming connections on port 1433. The port can be changed for security reasons or because of a client application requirement. By default, named instances (including SQL Server Express) are configured to listen on dynamic ports. To configure a static port, leave the TCP Dynamic Ports box blank and provide an available port number in the TCP Port box. For more information about opening ports in the firewall, see Configuring the Windows Firewall to Allow SQL Server Access in Books Online.

Dynamic Ports

At startup, when an instance of SQL Server is configured to listen on dynamic ports, it checks with the operating system for an available port, and opens an endpoint for that port. Incoming connections must specify that port number to connect. Since the port number can change each time SQL Server starts, SQL Server provides the SQL Server Browser Service to monitor the ports and direct incoming connections to the current port for that instance. Using dynamic ports complicates connecting SQL Server through a firewall because the port number may change when SQL Server is restarted, requiring changes to the firewall settings. To avoid connection problems through a firewall, configure SQL Server to use a static port.



Indicates that the IP address is active on the computer. Not available for IPAll.


If the Listen All property on the TCP/IP Properties (Protocol Tab) is set to No, this property indicates whether SQL Server is listening on the IP address. If the Listen All property on the TCP/IP Properties (Protocol Tab) is set to Yes, the property is disregarded. Not available for IPAll.

IP Address

View or change the IP address used by this connection. Lists the IP address used by the computer, and the IP loopback address, Not available for IPAll. The IP address can be in either IPv4 or IPv6 format.

TCP Dynamic Ports

Blank, if dynamic ports are not enabled. To use dynamic ports, set to 0.

For IPAll, displays the port number of the dynamic port used.

TCP Port

View or change the port on which SQL Server listens. By default, the default instance of Database Engine listens on port 1433.

SQL Server Database Engine can listen on multiple ports on the same IP address. Simply list the ports, separated by commas, in the format 1433,1500,1501. This field is limited to 2047 characters.

To configure a single IP address to listen on multiple ports, the Listen All parameter must also be set to No in the Protocols Tab of the TCP/IP Properties dialog box. For more information, see "How to: Configure the Database Engine to Listen on Multiple TCP Ports" in SQL Server Books Online.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request


Please sign in to leave a comment.