locked
Secure Developer Access to SQL Server RRS feed

  • Question

  • My organization is running SQL Server 2008 and I currently access the database through a local copy of enterprise manager that connects to the db through the standard port (1433). I also us RDP to go the server directly at times. 

    My organization just had an outside party conduct a security audit and we were dinged both for having an open port (and the fact that is was the default port) and also for using RDP.

    Is there a more secure method to connect to a SQL Server DB for developoment than either RDP or through TCI/IP via an open port?

    Thanks!

    Friday, June 6, 2014 7:22 PM

Answers

  • So the audit complained on that it was possible for other machines to access SQL Server? So how did they think that applications would access SQL Server? Smoke signals?

    OK, if all you have is a web app and the web server runs on the same box as SQL Server, SQL Server only has to accept local connections. But there is still the question how to do maintenance. Walking up to the physical machine in a cold computer room is not appealing.

    So it is not really clear what the security audit dinged you for. Or are you accessing SQL Server from home over the Internet? Now, that is bad, and that should not occur over any port. If you expose SQL Server on the Internet, there will be a lot of people knocking on the door, and one day they may crack your password by brute force.

    If you need to access SQL Server from outside your corporate network, you should get a VPN solution in place.

    It is possible to change the port SQL Server is listening to. You do this in SQL Server Configuration Manager. This will affect all connection applications, unless you have the Browser service running. But it will not take very long time of scanning for an intruding piece of software to find that there is an SQL Server running on that port, so I am not sure it is worth the hassle.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, June 6, 2014 9:29 PM
  • Security isn't black and white, it comes in many shades of gray.  As Erland pointed out, having a SQL server exposed to any random traffic from the internet is a really bad idea.

    If your auditing company is competent what they might have found is that your server has port 1433 open to any address.  That's certainly something you can and should tighten up.  There's little reason for a SQL server which is accessed through a middle tier such as a web server to be open to traffic from any old IP address.

    A reasonable plan would be to close the port off to all IP addresses other than your web server(s), report server(s), and developers.  Why would you allow traffic from one of your users PCs to port 1433 of your SQL server?

    Monday, June 9, 2014 12:55 AM
  • Hello,

    I must agree with the responses so far.

    My organization just had an outside party conduct a security audit and we were dinged both for having an open port (and the fact that is was the default port) and also for using RDP.

    Security through obscurity won't help. If they were competent they would port scan (find the now different port) and check the service type. Just now you had to change all of your application connection strings. They will still ding you next time. For reference this comes up in PCI compliance a good bit and every time I ask if they port scan and what the difference would be to which I get "it'd just not be on the standard port". So if I have the browser service running, it's going to do port translation for me and it won't matter.

    RDP access is needed to do *some* items. Even if you could use remoting through something such as powershell, wmi, etc, you'd still get dinged for that being open. They are going to "do an evaluation" and find as much as they can because they want it to look good that they did something. You can challenge their results for security (I wouldn't in this case) and I often do when it's something petty that fills up line items. For example, I'd argue the port change, especially due to the change in all connection strings needed and if browser is used for port translation then nothing changes except more work for you.

    As others have already pointed out, your networking team should restrict access to the services through the inside firewall and (if no talking to other servers located elsewhere) should be completely blocked at the perimeter/outside firewalls.

    To make it more secure you could use IPsec and then be super redundant and enforce encrypted connected through SQL Server. Obviously the firewall changes other and I have stated will help immensely more.


    Sean Gallardy | Blog | Twitter

    Monday, June 9, 2014 1:34 AM

All replies

  • So the audit complained on that it was possible for other machines to access SQL Server? So how did they think that applications would access SQL Server? Smoke signals?

    OK, if all you have is a web app and the web server runs on the same box as SQL Server, SQL Server only has to accept local connections. But there is still the question how to do maintenance. Walking up to the physical machine in a cold computer room is not appealing.

    So it is not really clear what the security audit dinged you for. Or are you accessing SQL Server from home over the Internet? Now, that is bad, and that should not occur over any port. If you expose SQL Server on the Internet, there will be a lot of people knocking on the door, and one day they may crack your password by brute force.

    If you need to access SQL Server from outside your corporate network, you should get a VPN solution in place.

    It is possible to change the port SQL Server is listening to. You do this in SQL Server Configuration Manager. This will affect all connection applications, unless you have the Browser service running. But it will not take very long time of scanning for an intruding piece of software to find that there is an SQL Server running on that port, so I am not sure it is worth the hassle.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, June 6, 2014 9:29 PM
  • Security isn't black and white, it comes in many shades of gray.  As Erland pointed out, having a SQL server exposed to any random traffic from the internet is a really bad idea.

    If your auditing company is competent what they might have found is that your server has port 1433 open to any address.  That's certainly something you can and should tighten up.  There's little reason for a SQL server which is accessed through a middle tier such as a web server to be open to traffic from any old IP address.

    A reasonable plan would be to close the port off to all IP addresses other than your web server(s), report server(s), and developers.  Why would you allow traffic from one of your users PCs to port 1433 of your SQL server?

    Monday, June 9, 2014 12:55 AM
  • Hello,

    I must agree with the responses so far.

    My organization just had an outside party conduct a security audit and we were dinged both for having an open port (and the fact that is was the default port) and also for using RDP.

    Security through obscurity won't help. If they were competent they would port scan (find the now different port) and check the service type. Just now you had to change all of your application connection strings. They will still ding you next time. For reference this comes up in PCI compliance a good bit and every time I ask if they port scan and what the difference would be to which I get "it'd just not be on the standard port". So if I have the browser service running, it's going to do port translation for me and it won't matter.

    RDP access is needed to do *some* items. Even if you could use remoting through something such as powershell, wmi, etc, you'd still get dinged for that being open. They are going to "do an evaluation" and find as much as they can because they want it to look good that they did something. You can challenge their results for security (I wouldn't in this case) and I often do when it's something petty that fills up line items. For example, I'd argue the port change, especially due to the change in all connection strings needed and if browser is used for port translation then nothing changes except more work for you.

    As others have already pointed out, your networking team should restrict access to the services through the inside firewall and (if no talking to other servers located elsewhere) should be completely blocked at the perimeter/outside firewalls.

    To make it more secure you could use IPsec and then be super redundant and enforce encrypted connected through SQL Server. Obviously the firewall changes other and I have stated will help immensely more.


    Sean Gallardy | Blog | Twitter

    Monday, June 9, 2014 1:34 AM