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.
- Under SQL Server Configuration Manager, go to SQL Server Network Configuration, then Protocols for [INSTANCENAME].
- 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.