Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

As you know you can create a database with SQL Server using the SQL Server configuration manager and SQL Server Management Studio. You know this - just typing that to tell that I checked the whole internet but didn't find answer to my problem. Let's go back to question.

I installed servers - one with default instance (MSSQLSERVER) and one with my own instance name. I want to configure that server so I can connect to it over the internet - from everywhere I am. I did some changes as I see on the internet - TCP Ports are enable and Named Pipes too. I set same ports on my server everywhere - to execute chance to be changed after restart (Many people say it - so I did it). OK good. When I open SQL Server Management Studio, I need to type in the Server name - here are three ways to do it - that is what I know:

  1. Typing: Computer name\SQL server name

  2. Typing: Local IP(given by the router)\SQL server name

  3. This is what I need - network one: Typing: IP Adress(check in internet on one of many websites that says your real IP that comes to router\SQL SERVER NAME

And my question is - why can I do first and 2nd connections, but in the 3rd one I get an error... The one that says there is no such a server. I did changes to router that allow ports on SQL Server and SQL Browser and nothing ... but everyone says i need to find port forwarding on router settings. I'm using Tenda W541R and I don't have such as "Port forwarding" but I have some kind of that - "Virtual Server" - I think that must be the same.

Thanks in advance !

share|improve this question

closed as off topic by Remus Rusanu, Jon Seigel, dezso, RolandoMySQLDBA, Mark Storey-Smith May 6 '13 at 12:59

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

    
I'd say it deserves moving to ServerFault. –  Marian May 6 '13 at 13:08

1 Answer 1

I think this is a routing question, not a DBA question.

Your router needs to be configured so that if traffic comes in its external interface on port 1433 (for the default instance) or on whatever port your named instance is using then the router sends that traffic to the SQL Server machine on the internal network. This is known as "port forwarding" or "server publishing" or "virtual server", depending on the router (it looks like your router uses "virtual server").

Warning: If you are not familiar with configuring routers then be very careful. Misconfiguring a router can break an entire network.

The big steps for what you ned to do are as follows.

  1. Configure both of your SQL Server machines with static ip addresses.
  2. Configure your SQL Server named instance with a static port, for example tcp45001, using SQL Server Configuration Manager. The default instance is already using a static port of tcp1433.
  3. Open the static port numbers using Firewall with Advanced Protection on both your SQL Server machines.
  4. Publish the port numbers on your firewall. Incoming traffic on port tcp1433 should be forwarded to port tcp1433 on the default instance machine; incoming traffic on port tcp45001 should be forwarded to port tcp45001 on the named instance machine.

Make sure your SQL Server services and machines are well secured - anything published on the Internet will get attacked. You should have good pass phrases, renamed administrative accounts (if possible), regularly-updated anti-malware software, etc.

share|improve this answer
    
Thanks a lot for the answer. I did all the things you said - changing ip to static, making ports, firewall and all the things you said, but here is the problem with router. As you said mine must be "virtual server" i did it - i set the port on the server that must be allowed and ip on the machine (there must be the local ip gived by the router) i did it and still i can't connect to my server true the global IP :{ –  Nikola Obretenov May 6 '13 at 0:15
    
Possibly a silly question, but when you say you can't connect, are you trying from outside the router? Trying to connect to the external ip address from a machine on the internal network won't work - the port forwarding only forwards incoming traffic. –  Greenstone Walker May 6 '13 at 1:38
    
Hmm, maybe this is the reason! I'm asking because i watched a video where they show us those steps - 1)with pc machine name/ 2)with local ip given by router/ 3) And the external ip - and there it works, that was i asked. Now I'll try it with another machine. Let's see. I'll tell if it is working. Thanks again ! –  Nikola Obretenov May 6 '13 at 9:16
    
Grr, I did it from the another machine and nothing happened again the same error. I tried to turn off the firewall(i already set them in allowed but ...) - nothing again. There must be another options to allow it. I have enabled all ports and etc. and nothing. –  Nikola Obretenov May 6 '13 at 9:38
    
@GreenstoneWalker when you try to connect to external ip address (internet address) your connection will do roundtrip to your isp and back, thus connecting from the "outside" of the router. –  Justicator May 6 '13 at 11:55

Not the answer you're looking for? Browse other questions tagged or ask your own question.