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

Posted on

Question :

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 : machinenamesql-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.2sqlexpress 

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 machinenamesqlexpress I am able to connect the sql-express instance, but from my local machine using sql-management studio if i type in ipaddresssqlexpress I am not able to connect. It gives me network related issue error.

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.2sqlexpress,1533

Voila!

type in run command -> mstsc

then remote desktop connection window will be pop-up. That window will
ask your Computer and User Name

In “Computer Name” field there will be your “Remote Desktop IP address”

and “User Name” field type your local computer UserID, from which userid your did login in your local computer.

After again new “Windows Security” window will be pop-up. It will ask your credential details because these credentials will be used to connect to your Remote Server IP address.

Here UserID & password will be your local computer userid & password
through which you had logged in your computer

UserID automatically comes from Windows and your
password will be your local computer password

After providing these credential again new windows will be popup for “certificate authentication”. That window will ask you “The identify of the remote computer can not be verified. Do you want to connect anyway?” click “yes”.

Hope your remote connection will be established.

Leave a Reply

Your email address will not be published. Required fields are marked *