How To Setup SQL Server Remote Connection and Why it’s not Listening on Port 1433

This guide will help you to set up MS SQL Server (express and standard editions) with remote connection enabled via TCP port 1433 or any other port of your choice. By default, when you install MS SQL Server Express edition, the remote connection feature is disabled. You can connect to the DB locally via SQL Server Management Studio but you won’t be able to connect the database remotely via third party integration tool sets.

Firstly, you can check to see if SQL Server has remote connection via TCP port enabled or not by going to Start Menu > SQL Server Configuration Manager. Under SQL Server Network Configuration section, expand and find the SQL Server instance you are trying to config, select the server and on the right side you will see the TCP/IP status is enabled or disabled. As I previous mentioned, by default TCP/IP remote connection is disabled. You can enable it, and there are couple steps you need to do in order for the connection to fully enabled and remotely accessible.

SQL Server Configuration manager

Double click the TCP/IP status

SQL TCP/IP enable

Under the first tab, Protocol tab make sure Enabled is selected as Yes. Then go to IP Address tab.

SQL Server IP address tab

There will be a list of IP <numbers> listed there. What you need to do is find the corresponding IP <number> section that matches the real machine IP address. Once you do, change the Enabled status to yes, as well as empty out TCP Dynamic Ports.

SQL IP address all

Then scroll all the way to the bottom of the IP Address tab, under IP All section, make sure TCP Dynamic Ports is empty. And under TCP Port, you can give it a port number. The common convention is to have SQL Server port running on 1433. 

Lastly you need to make sure your Windows Firewall has port 1433 opened for inbound traffic. Go to Start Menu > type “Firewall” and go to “Windows Firewall with Advanced Security”

Windows Firewall

Let’s create a new Inbound Rules, called SQL Server

Windows Firewall Inbound Rule

Select Port

Windows Firewall Inbound Rules

Under the Specific local ports add 1433 and any additional SQL named instance ports to the list.

Windows Firewall Inbound Rules

Now you should be set for remote connection with SQL Server.

Jonathan Hu

Programming by day, Web Development, Canucks & Movies for spare time!
Co-founder of Next of Windows and a cool geek 🙂

Last updated: 06/17/2014

Posted in: IT Pro
Discover more: , ,