locked
Access SQL Server from inside DMZ (which is a different domain) RRS feed

  • Question

  • Hey guys!

    I am not quite sure what's the best way to do this. We have two domains:

    - Internal

    - DMZ

    We want to keep the DMZ as seperated as possible, that's why it's running its own domain, uses another IP segment and so on. However, as always, some holes cannot be avoided. No suprise I guess.

    However it now came up that an application running inside the DMZ needs access to an SQL Server database in our internal domain. Not good. From what I was able to gather so far is that it's a DB on an instance on one of our primary SQL 2005 clusters.  So that means it's probably possible to change the settings for that agent in terms of IP and what not.

    I wonder what has to be done to allow connectivity to the database. From what I remember there's a default port on which the agent listens and communication then flows over a socket that's chosen dynamically. Opening an entire range of ports from the DMZ to internal sounds ... well, quite terrible actually. So:

    - Is there some way to limit the holes needed to connect from the DMZ to the internal SQL Server?

    -- Please name 3rd party tools if they exist, something like an "SQL Proxy Tunnel" maybe?

    - If there is no "good" way - which is the range of ports that we would need to open?

     

    Thank you guys!

    Regards,

    Chris

    Friday, January 27, 2012 2:35 PM

Answers

  • Chris, if you did not change the port number on install, your clustered instance will be listening on port 1433 by default.

    Your firewall configuration would be just like that. Remember you can use a port number other than 1500 for the new endpoint if you wish.

    The new endpoint can only be accessed if you provide a login and password, or use windows authentication, just like the default TCP endpoint. That means if anyone takes over your application server, he will need to authenticate to have access to SQL Server using that new endpoint. Be careful to protect the login and password your app uses to connect. You should also encrypt the channel between your applications and SQL Server. See my previous post about it.

    At first, I believe your endpoint will allow connections from any user, but you can change that. See this article on how to do grant endpoint permissions, and this one on how to deny. You should deny connections to EVERYONE and then allow connections from specific logins only.

    Your application should allow you to edit the connection string somewhere. Every time you connect to any DBMS system using TCP, you need to provide an IP and port, unless it is hard coded, which will be a big problem.

    The reason you create a new endpoint to allow connections from the DMZ is to narrow down the risks. It will not be the default TCP port SQL Server listens to, and you can restrict access to that new endpoint using firewall rules.

    If I helped you, please Mark As Answer.

    Good luck.


    SQL Server Database Administrator
    • Proposed as answer by Marcelo Farinelli Thursday, February 2, 2012 6:57 PM
    • Marked as answer by Maggie Luo Monday, February 6, 2012 4:00 PM
    Monday, January 30, 2012 4:11 PM

All replies

  • Chris, assuming your application can reach the SQL Server 2005 cluster nodes through proper network and firewall configuration...

    And assuming your SQL Server instance is listening on port, say, 1433...

    I think you should create an ENDPOINT for TCP transport with a TSQL payload type, listening on a different port, dedicated to connections coming from the application. You should then be able to configure your firewall to accept connections to that port on the cluster nodes, only from that application alone, not the entire DMZ. You can read about creating an endpoint here.

    You should also worry about encrypting the channel between the applications and SQL Server. You can read more about it here.


    Senior SQL Server Database Administrator
    Java JEE Software Developer
    Friday, January 27, 2012 7:17 PM
  • Chris, this link is related to my previous post, and could also help you.
    SQL Server Database Administrator
    Sunday, January 29, 2012 6:41 PM
  • Marcelo, thanks for the tip. I'm not sure I understood this right, maybe you can check?

    Let's assume the clustered instance has the IP (10.1.1.10) and the instance is named "dbcluster\instance2" - which would normally listen on a dynamically assigned port. By creating an endpoint I basically create an addidional, fixed port on which this server also listens. I can then assign security settings to this enpoint, for example only allow the application's db user to connect. Say the port is 1500.

    So basically, I would need to punch holes in the firewall like this:

    DMZ -> Intranet

    ApplicationServer:<ANY_TCP_PORT> -> 10.1.1.10:1500

    Intranet -> DMZ

    10.1.1.10:1500 - ApplicationServer:<ANY_TCP_PORT>

    So because I have an endpoint with specified security, even if someone takes over the application server, only if he also get's the application db user + password could he access the SQL server.

    But what is the benefit over assigning these firewall rules for the default port? From what I understand the default IP 1433 is also used to resolve the correct instance, so 

     

    ApplicationServer:<ANY_TCP_PORT> ->10.1.1.10:1433

    10.1.1.10:1433 - ApplicationServer:<ANY_TCP_PORT>

     

    would be pretty much the same? Except that an attacker could now also try and use other database accounts like SA and such. However I would have to "hope" the application supports setting the connection to a specific port (don't know the application so no info here)?

     

    Thank you very much! 

     

     

     


    Monday, January 30, 2012 1:55 PM
  • Chris, if you did not change the port number on install, your clustered instance will be listening on port 1433 by default.

    Your firewall configuration would be just like that. Remember you can use a port number other than 1500 for the new endpoint if you wish.

    The new endpoint can only be accessed if you provide a login and password, or use windows authentication, just like the default TCP endpoint. That means if anyone takes over your application server, he will need to authenticate to have access to SQL Server using that new endpoint. Be careful to protect the login and password your app uses to connect. You should also encrypt the channel between your applications and SQL Server. See my previous post about it.

    At first, I believe your endpoint will allow connections from any user, but you can change that. See this article on how to do grant endpoint permissions, and this one on how to deny. You should deny connections to EVERYONE and then allow connections from specific logins only.

    Your application should allow you to edit the connection string somewhere. Every time you connect to any DBMS system using TCP, you need to provide an IP and port, unless it is hard coded, which will be a big problem.

    The reason you create a new endpoint to allow connections from the DMZ is to narrow down the risks. It will not be the default TCP port SQL Server listens to, and you can restrict access to that new endpoint using firewall rules.

    If I helped you, please Mark As Answer.

    Good luck.


    SQL Server Database Administrator
    • Proposed as answer by Marcelo Farinelli Thursday, February 2, 2012 6:57 PM
    • Marked as answer by Maggie Luo Monday, February 6, 2012 4:00 PM
    Monday, January 30, 2012 4:11 PM