Tech Off Thread

17 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Exposing sql server with alternate port and dynamic dns

Back to Forum: Tech Off
  • User profile image
    ScanIAm

    I fumbled around for a few hours this weekend trying to figure out how to do this, and I suspect I just don't know the right terminology to find it myself.  (note: I have spent time with www.connectionstrings.com, but the question still remains unanswered):

     

    I want to be able to expose an instance of sql server that I can access remotely through a NAT device.  I also want to be able to access it using a dynamic dns service.  Lastly, I want to use an alternate port to hit the server so I don't get hundreds of failed 'sa' log attempts.

    Assuming that this is a single instance (MyServerInstance1), I'm not using dynamic tcp ports, so I had hoped I could port forward my NAT device port (12345) to the server and port (1434).  Then, I thought I could use my dynamic dns url (example.com), the exposed port (12345) and connect to the sql server.  No luck, however. 

    How do I this server correctly in sql server management studio?  I've tried "example.com,12345/MyServerInstance" and that fails.  I suspect that the combination of needing a port AND a named instance is causing problems.  I've tried using a colon to specify the port as well and a number of other iterations.  As best as I can tell from the server logs, the request is never making it to the sql server so I'm wondering if I'm missing some security piece that would be obvious to smarter people than I.

  • User profile image
    W3bbo

    I've done what you're trying to do before, but not with named instances, only the default instance.

    Note that SSMS always uses a comma to specify the port number and never a colon (looks like Microsoft is falling behind on the RFC front again).

    Having said that, I thought named instances multiplexed on the same port. Check your SSCU layout, maybe post it here?

  • User profile image
    ScanIAm

    I can guess the first two letters, but what's an SSCU ?

  • User profile image
    davewill

    @ScanIAm: 1433 is the port number you want if you are attempting to connect to the default sql server (i.e. no instances).

  • User profile image
    fabian

    try example.com/MyServerInstance,12345

  • User profile image
    W3bbo

    , ScanIAm wrote

    I can guess the first two letters, but what's an SSCU ?

    Sorry, I meant SSCM: SQL Server Configuration Manager. Buried under Start Menu > SQL Server 2008 > Configuration Tools > SQL Server Configuration Manager

  • User profile image
    blowdart

    Maybe start with the obvious.

    1) Do you have SQL configured to use TCP/IP

    2) Without an instance configured can you connect with 127.0.0.1,XXXXX where XXXXX is the port number in SQL Management Studio?

    3) Do you have the ports punched in the Windows Firewall?

    4) Can you connect from another machine in your internal network?

    5) What happens if you force TCP use? tcp:FQDN\INSTANCE,XXXXX ?

  • User profile image
    ScanIAm

    , blowdart wrote

    Maybe start with the obvious.

    1) Do you have SQL configured to use TCP/IP

    2) Without an instance configured can you connect with 127.0.0.1,XXXXX where XXXXX is the port number in SQL Management Studio?

    3) Do you have the ports punched in the Windows Firewall?

    4) Can you connect from another machine in your internal network?

    5) What happens if you force TCP use? tcp:FQDN\INSTANCE,XXXXX ?

    1) yes

    2) I assume you mean to try this on the machine it self.  I haven't tried, but I will this evening.

    3) I do have 1434 specifically opened up, and I also added a Windows Firewall rule for that instance of sqlsvr.exe.  Hmmm...this is all running on a VM on my desktop and the network connection is bound to the desktop nic.  I also have a 3rd party firewall running on that desktop, so I wonder if it is actually blocking the inbound connection. 

    4) yes, as long as I use the machine name/instance or local network ip/instance. 

    5) I'll try that too, this evening. 

    I'll also try the syntax fabian suggested to see if it works. 

    I'm wondering, though, does all of the traffic go through the single port assigned to the instance or are there multiple ports used for communication.  My uneducated suspicion was that the initial request was done on that port, but then further communication would occur on other ports.  That's fine if the client machine is within the network and can open those other ports, but it won't work from a client outside of the network.

  • User profile image
    blowdart

    Oh, if it's in a VM, then, if the VM is NAT-ing through that may be a problem. If the VM is getting an IP address from the router that's one less thing to worry about, but yes, the firewall on the desktop may stop it. It may even get confused by having what is effectively two IPs bound.

    However, it's not 1434 you need to open, it's 1433. 1434 UDP is the server browser, 1433 TCP is the actual server itself.

  • User profile image
    ScanIAm

    Interesting.  I initially opened 1433, but switched to 1434 based on some info found elsewhere on line.  I'll have to try 1433 again to see if that works.

    Thanks for the clues.

  • User profile image
    figuerres

    , ScanIAm wrote

    I fumbled around for a few hours this weekend trying to figure out how to do this, and I suspect I just don't know the right terminology to find it myself.  (note: I have spent time with www.connectionstrings.com, but the question still remains unanswered):

     

    I want to be able to expose an instance of sql server that I can access remotely through a NAT device.  I also want to be able to access it using a dynamic dns service.  Lastly, I want to use an alternate port to hit the server so I don't get hundreds of failed 'sa' log attempts.

    Assuming that this is a single instance (MyServerInstance1), I'm not using dynamic tcp ports, so I had hoped I could port forward my NAT device port (12345) to the server and port (1434).  Then, I thought I could use my dynamic dns url (example.com), the exposed port (12345) and connect to the sql server.  No luck, however. 

    How do I this server correctly in sql server management studio?  I've tried "example.com,12345/MyServerInstance" and that fails.  I suspect that the combination of needing a port AND a named instance is causing problems.  I've tried using a colon to specify the port as well and a number of other iterations.  As best as I can tell from the server logs, the request is never making it to the sql server so I'm wondering if I'm missing some security piece that would be obvious to smarter people than I.

    i *NEVER* open sql ports to an outside connection.

    use a VPN , then connect, that way the only hole is the VPN one and you can use IPSEC VPN's or SSL VPN's to make the connection secure.

    just my 0.02 worth.

    I have been working with MSSQL and others for a little while.

  • User profile image
    W3bbo

    i *NEVER* open sql ports to an outside connection.

    use a VPN , then connect, that way the only hole is the VPN one and you can use IPSEC VPN's or SSL VPN's to make the connection secure.

    just my 0.02 worth.

    I have been working with MSSQL and others for a little while.

    I disagree. Well, I agree in principle, in reality a well-configured SQL Server shouldn't be a viable attack-vector. If you run SQL Server under a standard user token and require authentication then at most an attack during the authentication stage can do damage to SQL Server's own files or brute-force insecure passwords.

    And if you have adequate backups and continuous protection then that shouldn't be a problem. And brute-force attacks can be avoided by publishing SQL Server to a non-standard port.

    SQL Server itself has a pretty good security track record:

    SQL Server 2008: 0 security issues
    SQL Server 2005: 4 issues, all patched
    SQL Server 2000: 1 issue unpatched, 11 patched, and that issue only affects SQL Server Agent if it's running as SYSTEM.

    Let's compare to rival DBMSs:

    MySQL 5.x = 66 vulnerabilities documented in 26 advisories, all but 1 patched
    Oracle Database 11.x = 271 vulnerabilities documented in 12 advisories, all patched
    IBM DB2 9.x = 94 vulnerabilities in 22 advisories

    I'd say that's pretty good, especially given the time-range for SQL Server's good history goes back to 2005, whereas MySQL, Oracle, and DB2's histories are much shorter.

  • User profile image
    davewill

    @ScanIAm:  1433 is the default sql server port for the default instance of sql server.  1434 is where instance sql servers connect with to find out what the port for a specific instance is running against.  instance sql servers don't have hard set ports.  its a first start first get.

    Run "netstat -ano" at a cmd prompt.  Match the PID to the PID in task manager for the sql server that is running.  That is your port.  However, if you are using an instance of sql server instead of the default instance then i don't think the port is guaranteed to be the same with each restart.

    I'd suggest uninstalling and installing sql server as the default MSSQLSERVER and not an instance if you will be needing fixed port access.

  • User profile image
    ScanIAm

    OK, so you all gave me some clues as to what was going on, and I found that the instance was using dynamic ports.  Just for testing, I changed the port forwarding to the specific port listed and it connected fine.

    It turns out that by default, the port numbers are dynamic, but you can set a specific port for the instance, so I did that, restarted the vm, and testing locally, it seems to work.  The real test will be tomorrow when I try remotely.

    As for using VPN, that is certainly an option, but I've had some troubles recently where the onboard NIC will need to be disabled/enabled after a VPN connection disconnects.  Further, some of the remote locations where I log in seem to disable outbound VPN connections and I have no real control over that.  I may try to set up an alternate port for the VPN, too and if that works, I won't have to expose the sql server to the perils of the web.

    Thanks so much for all of your input, though.  I never would have figured this out without it.

  • User profile image
    davewill

    @ScanIAm: oh yea.  helped me as well.  i had completely forgotten about the SQL Server Configuration Manager -> SQL Server network configuration -> Protocols -> TCP/IP properties.

  • User profile image
    ScanIAm

    Just for followup, this configuration work as well when the client is coming from an extranet location.

    Thanks again.

  • User profile image
    blowdart

    , ScanIAm wrote

    Just for followup, this configuration work as well when the client is coming from an extranet location.

    Thanks again.

    Woohoo!

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.