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.
Double click the TCP/IP status
Under the first tab, Protocol tab make sure Enabled is selected as Yes. Then go to 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.
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”
Let’s create a new Inbound Rules, called SQL Server
Under the Specific local ports add 1433 and any additional SQL named instance ports to the list.
Now you should be set for remote connection with SQL Server.