Connecting to a 2014 SQLEXPRESS database on an externally hosted computer through TCP/IP

Question

I am working on a system whereby I can update a small database from several locations. I wish to host the database on my home computer and allow connections to be made to the database from two other locations through tcp/ip.

I have done the following thus far:

1. Enabled TCP/IP for SQLEXPRESS
2. Allowed port 1433 tcp on my firewall
3. Allowed port 1434 udp on my firewall
4. Enabled SQLEXPRESS server to allow remote connections

I was hoping to be able to connect to the database by opening sql management studio on the other pcs and using the following format [ExternalIP]\SQLEXPRESS but as of yet I have been unable to do so. I have looked at what my external IP might be and i am getting two different values:

86.XX.X.XXX 66.XXX.XX.XX

Should I be using either of these values with this format to connect from the other sql management studios when everything has been correctly configured? Are there any steps that I am missing?

Solution 1

Named instances of SQL Server don't listen by default on port 1433, but instead on a random port determined at each service startup. It's the function of the sql browser service to provide clients with actual ports of each running instance (and that's done using the fixed 1434 udp port).

The necesary steps would be to configure the server to use a fixed port, then setup the firewall and router to allow that port. When connecting, just use the external IP address with the port but not the instance name (as that would invoke the browser service, which is unneeded with fixed ports).

To resume, necesary steps would be these:

1. Enable remote connections on the server.
2. Setup users and permissions as needed (Windows authentication is a pain over internet, so SQL authentication is strongly recommended)
3. Enable TCP/IP for the instance.
4. Configure the server to use a fixed port for connections (1433 being the standard). Look here for instructions.
5. Restart the database server.
6. Give permissions on the firewall to allow the server executable incoming connections on your selected port. At this point other computers in the LAN should connect.
7. On your router forward the port you opened to your local machine private IP (note that in Windows you must configure your computer to use a fixed local IP instead of DHCP).
8. Test with a client over internet or just using your external IP.

Note that for the connection from the remote client, as server name you must use 86.XX.X.113,1433 (the port number is separated with a comma, optional if it's 1433). Notice that there is NO instance name, even if you installed it as SQLEXPRESS or whatever. This is due having a fixed listening port (like the default instance by default). That eliminates the need of the browser service and udp 1434, and the pain of a dynamic port forwarding.

You should also check that your ISP most likely will give you a dynamic external IP, so clients might be reconfigured when that changes. Or you can use some dynamic DNS service.

Solution 2

Config MSSQL Express 2014 connect remoto PC or PHP, etc..

View Video Tutorial Easy Config Server to MSSQL Express:

https://www.youtube.com/watch?v=5UkHYNwUtCo

Code Test Cliente Remote PHP:

$serverName = '192.168.1.64,49170'; //only the server name and ip MSSQL SERVER EXPRESS 
 $connectionInfo = array( "Database"=>"mydbName", "UID"=>"myUserId", "PWD"=>"myPass");
 $conn = sqlsrv_connect( $serverName, $connectionInfo);

 if( $conn ) {
   echo "Connection established";
 }else{
   echo "Connection could not be established.";
   die( print_r( sqlsrv_errors(), true));
 }

Comments

[Alejandro]

Are other clients over the same LAN or over internet?

[Jay]

They are over the internet

[Alejandro]

Note that over internet you also need to do port forwarding on any router you have. Some ISPs don't allow incoming connections for home connections, so watch out for that too.

[Jay]

was related to port forwarding had everything else configured ok, thanks for your help alejandro