Remote login to SQL Server

Question

Remote login to SQL Server

michaeljharper

SSC-Addicted

Points: 406
More actions
June 13, 2012 at 8:36 am

#259757

Hi guys, I've configured my router and firewalls so that I can connect to my SQL server from any computer in my home network, how do I access it from a remote computer? I've tried my IP:1433\SERVERNAME at the connection screen but it doesn't find it.
I also use dyndns and have http://www.theweatherproject.net connected to my dynamic IP address, could I use this address at the connection screen?
Many thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Sounds like you need to do port forwarding, so when I packet comes into your router for port 1433 it knows to pass it to the LAN IP of 192.168.0.10 for example.

Also you say its a named instance, is it actually using port 1433, as named instances use dynamic ports so the port number can change.

michaeljharper SSC-Addicted Points: 406 More actions · Answer 2

I have forwarded 1433 TCP in and out and 1434 UDP in and out, can I check which port SQL Server is using from Management Studio?

Thanks

Jared SSC Guru Points: 61793 More actions · Answer 3

michaeljharper (6/13/2012)
Hi guys, I've configured my router and firewalls so that I can connect to my SQL server from any computer in my home network, how do I access it from a remote computer? I've tried my IP:1433\SERVERNAME at the connection screen but it doesn't find it.
I also use dyndns and have http://www.theweatherproject.net connected to my dynamic IP address, could I use this address at the connection screen?
Many thanks

I'm not 100% sure... but I believe when I had to do this I actually used the naming convention of:

IP\servername:portno

However, have you simply tried IP\servername? You should not need the port if it is 1433.

Jared
CE - Microsoft

Ant-Green SSC Guru Points: 113507 More actions · Answer 4

michaeljharper (6/13/2012)
I have forwarded 1433 TCP in and out and 1434 UDP in and out, can I check which port SQL Server is using from Management Studio?
Thanks

You will need to open up SSCM (SQL Server Configuration Manager) and under the TCP/IP protocol you should find the port number being used by SQL, if its dynamic, I would change it to a static port so that you never have to reconfigure your router and port forwarding when you restart SQL.

Jared is right in saying it should be IP\InstanceName,Port, not IP,Port\InstanceName.

heymiky SSCarpal Tunnel Points: 4875 More actions · Answer 5

If your specifying the port number you don't need the instance name eg,

yourserver, 1433 or 192.168.5.5, 1433 should work

EDIT: made up my own ip address so, not that I think you will, don't use it to try your SQL Server 😉

heymiky SSCarpal Tunnel Points: 4875 More actions · Answer 6

Not always a best practice but starting the SQL Browser Service can remove the need to use ports # if your not using 1433, 1434.

michaeljharper SSC-Addicted Points: 406 More actions · Answer 7

Thanks for all the help guys, I was probably entering the IP and port in the wrong order, I later found out I could use my dyndns domain name and it works great 😀

Nadrek SSC-Insane Points: 20039 More actions · Answer 8

While the other answers will help you do what you're currently doing, I would suggest that what you're doing is a horrifically bad idea security-wise.

What I would suggest instead is setting up a VPN, so you can connect to your home VPN from somewhere else, and _then_ over the VPN it's as if you're on your home network (or more restricted, if you like). Preferably use certificates for the VPN (pfSense has wizards for that, and there are more advanced ways), but at least use a long, complex password.

I've done this very successfully with pfSense (http://www.pfsense.org/index.php?option=com_frontpage&Itemid=1). You can install it on a suitable machine if you have one, or, for most home grade (or even small office grade) internet connections, even a tiny little device is fine. Example: One of the small ones at Netgate.com.

Jared SSC Guru Points: 61793 More actions · Answer 9

Nadrek (6/15/2012)
While the other answers will help you do what you're currently doing, I would suggest that what you're doing is a horrifically bad idea security-wise.
What I would suggest instead is setting up a VPN, so you can connect to your home VPN from somewhere else, and _then_ over the VPN it's as if you're on your home network (or more restricted, if you like). Preferably use certificates for the VPN (pfSense has wizards for that, and there are more advanced ways), but at least use a long, complex password.
I've done this very successfully with pfSense (http://www.pfsense.org/index.php?option=com_frontpage&Itemid=1). You can install it on a suitable machine if you have one, or, for most home grade (or even small office grade) internet connections, even a tiny little device is fine. Example: One of the small ones at Netgate.com.

Reread the first post by the OP. 🙂

Jared
CE - Microsoft

Nadrek SSC-Insane Points: 20039 More actions · Answer 10

SQLKnowItAll (6/15/2012)

Reread the first post by the OP. 🙂

I did; my post stands.

Yes, the other posts did help the question posed by the OP in a simple way, based on how the OP was originally implementing it. That particular implementation remains horrifically bad from a security perspective, and the VPN suggestion still accomplishes the original goal of "how do I access it from a remote computer" - i.e. you connect to your new VPN with the remote computer, and then the steps to allow SQL Server access on the home network (which the OP had already succeeded at) would allow access from the remote computer on the VPN as well.