CODEWITHSUNDEEP
sql-serversql-server-express
Cell Tech
Cell Tech

Reputation: 21

Remotely Connecting to SQLEXPRESS over the internet

I'm trying to experiment with sqlexpress, so I installed it on a computer, and I can successfully log into it when I'm inside the network (at work), using machinename\sqlexpress then username and password.

Problem: I can't connect from outside the network (from home). At work, I have a static ip. I went to myipaddress.com and used that ip address to connect remotely: \47.23.xx.xx,2301\sqlexpress, but still can't connect.

After researching this problem, I enabled tcp/ip, tried with port 1433, then changed to 2301 in case 1433 was blocked, Windows 8 firewall: added exception for sqlserver, I have sqlexpress and browser services running.

I guess my question is: am I right to assume that using the ip address that I got from myipaddress.com followed by configured port and server name sqlexpress should connect me? I'm a bit confused because if that static ip is for the whole router, how would it know to direct external calls to that machine on the network? and the sqlexpress running on it? Is all that accomplished by the port number?

Upvotes: 1

Views: 5808

Answers (2)

Ayomide Saliu
Ayomide Saliu

Reputation: 21

You are almost there, the public ip address is for the whole router as you have said, but you can configure a port forwarding feature, this will enable the router to know where exactly the traffic should be forwarded to. How to do this, log into your router, look for where port forwarding feature is, Then configure the port you have assingned for the sql express on the space for ports and infront of it there is ip address, input the ip address of the machine running the sql server, this way the router will be able to recognise which computer the application using the port number is and forward the traffic.

Note: You must configure a client list or dhcp reservation for the computer running the sql server so that the ip address doesn't change every time you restart your computer. How to do this: Log into your router, go to dhcp server, there should be either dhcp reservation or client list, depending on your type of router, put the mac id of the system running the sql server and your desired ip address in the required fields, save it.

This way you should be able to connect remotely. good luck.

Upvotes: 2

mcauth
mcauth

Reputation: 924

You're almost positively hitting a firewall which has no idea how to manage that traffic, and as such is rejecting it. Moreover, the static IP that you saw from work is a Network Address Translated (NAT) address, and is unlikely to accept connections.

Far better to use a VPN for this use case; no need to expose test systems to the internet, which could be a major security risk. Ask your admins for a VPN setup if you don't already have one...

Upvotes: 0

Related Questions