Sql-server – connect to server with IP address running sql-express instance through sql-management studio

sql serversql-server-2008-r2

I have my VPS server in a remote location with ip address 172.16.11.2 (for example) having two instances of sql-server express running.

one instance is Microsoft SQL Server 2008 R2 (SP2) where I connect from my local PC using remote desktop with the following credentials:

 servername : 172.16.11.2
 authentication : sql - server authentication
 userid: xx
 password:xx

the other instance is:

Microsoft SQL Server 2008 R2 (SP2) with advanced services. To connect to this machine I remote desktop to the server and type in 

 servername : machinename\sql-express
 authentication : sql - server authentication
 userid: xx
 password:xx

now using remote desktop from my local machine how will I connect to this instance of sql-server.

I tried 

 172.16.11.2\sqlexpress

but this failed. I also enabled sharedmemory/namedpipes/tcp-ip but still I am not able to connect. When I go to the machine where sql-express is installed and then type machinename\sqlexpress I am able to connect the sql-express instance, but from my local machine using sql-management studio if i type in ipaddress\sqlexpress I am not able to connect. It gives me network related issue error.

Best Answer

Ensure that the secondary instance (in this case SQL Express) has a TCP port set.

  1. Under SQL Server Configuration Manager, go to SQL Server Network Configuration, then Protocols for [INSTANCENAME].
  2. Right click TCP/IP and under the IP Addresses tab make sure the field "TCP Port" has a value (for example 1533).

Then try to connect to the instance with the newly assigned port:

172.16.11.2\sqlexpress,1533

Voila!

Related Question