April 20, 2010 at 12:47 pm
I have a SQL Server open to the internet which various of my clients connect to remotely. I've noticed recently that I'm running out of disk space and found that 6gb was taken up by sql server error log. It's full of login attempts from the same ip, many times a second. This is obviously costing performance and bandwitdh and eventually the attack might guess my sa password correctly by brute force.
I was supprised that I could find no article on securing a SQL Server to prevent many failed login attempts from the same IP.
Anybody got advice? Should I stop my SQL Server browser service (of course it's now too late, it's already discovered.) I can change my port, but a port scanner will find it eventually and I'll have to manually help all my clients again.
Many thanks.
April 20, 2010 at 12:54 pm
If you have any servers open to the Internet be prepared to spend a lot of time and effort on security. If you don't know what to do (such as using a firewall and blocking offending IP ranges as a basic start), it would probably be very wise to pay for someone to come in and get you started.
April 20, 2010 at 1:01 pm
bteraberry (4/20/2010)
If you have any servers open to the Internet be prepared to spend a lot of time and effort on security. If you don't know what to do (such as using a firewall and blocking offending IP ranges as a basic start), it would probably be very wise to pay for someone to come in and get you started.
I have to agree. You really need to pull your server behind a firewall and put policies in place that will only allow your clients to access the SQL Server system through the firewall.
A better solution would be to implement a VPN solution between your clients and your SQL Server system.
The best thing you could do if you don't feel you can do all of this, is hire someone to come in and help you set it up and train you you to maintain it.
April 20, 2010 at 1:28 pm
Thanks. Seems a glaring hole in functionality though :/
I'll have to look at the firewall route for blocking the ranges, but then some clients connect from anywhere in the world !
April 20, 2010 at 1:30 pm
I would echo what Lynn and bterraberry have said. Having a SQL Server open to the world is asking for trouble. You should have an idea of what the IP ranges of your clients are or they should be able to tell you, a firewall should drop traffic from all other inbound clients and all ports but the one SQL is on.
CEWII
April 20, 2010 at 1:40 pm
hein-1120388 (4/20/2010)
Thanks. Seems a glaring hole in functionality though :/I'll have to look at the firewall route for blocking the ranges, but then some clients connect from anywhere in the world !
It's not a hole in functionality. Generally your network architechture would provide the security layer that you require. Provided the same thing in SQL Server would be redundant, especially since a great deal of SQL Server implementations are never exposed externally.
You might find simple network security features in other databases, like IP blocking, but they will never be as robust as a firewall or VPN. This is true for all databases. Databases provide security features, but they are not security tools.
If you need your users to access it from any IP, your best bet is a VPN.
I really feel that I need to reiterate what everyone else is saying. If network security is over your head, pay someone to do it. It will cost your company a great deal more if your database is compromised. Since you are already being targeted, it should be fairly easy to state your case to management. Also, don't let pride get in the way. As a dba at my company, network security is not my job. We actually hire a contractor to do such things. I can't be expected to do everything, and, as you've already learned, network security does not fall under the scope of database administration.
April 20, 2010 at 1:58 pm
hein-1120388 (4/20/2010)
Thanks. Seems a glaring hole in functionality though :/I'll have to look at the firewall route for blocking the ranges, but then some clients connect from anywhere in the world !
If they are roving all over the world then a VPN solution for them might be a better resolution. If you have some fairly static it is easier to use a simple drop all but X list.
I have a larger question, how do your customers feel about their data being sent clear-text over the internet?
On reflection a VPN solution might be good for all of your clients..
CEWII
April 21, 2010 at 1:08 am
It's not a hole in functionality. Generally your network architechture would provide the security layer that you require. Provided the same thing in SQL Server would be redundant, especially since a great deal of SQL Server implementations are never exposed externally.
You might find simple network security features in other databases, like IP blocking, but they will never be as robust as a firewall or VPN. This is true for all databases. Databases provide security features, but they are not security tools.
I beg to differ. Banning a failed login attempt from an IP for a while is something a firewall cannot do. It's definately a security feature to protect itself. It's an elemetary feature available in FTP servers, SSH servers and others since the 80s. There's such a focus on security in Microsoft, yet this simple measure would prevent the bulk of breaches.
If you need your users to access it from any IP, your best bet is a VPN.
Setting up a VPN is a complex procedure for my clients, they just want my app to run from their pc. Reaching them over the phone explaining how to install OpenVPN on their various versions of windows is a headache. We all know users have trouble with the most basic of tasks. Best case scenario is they'll use my app less because of the extra p.t.
I really feel that I need to reiterate what everyone else is saying. If network security is over your head, pay someone to do it. It will cost your company a great deal more if your database is compromised. Since you are already being targeted, it should be fairly easy to state your case to management. Also, don't let pride get in the way. As a dba at my company, network security is not my job. We actually hire a contractor to do such things. I can't be expected to do everything, and, as you've already learned, network security does not fall under the scope of database administration.
I run my own small business, but I don't mind paying an consultant to look at my security, but I don't see a security technology that's going to work for me. I'm going to try a few things with SQL Server, such as renaming the sa account, moving the port, etc.
Thanks for your time & suggestions, it's really appreciated.
April 21, 2010 at 5:13 am
You absolutely must use a non-standard port if publishing a SQL Server to the internet and SQL Browser should always be disabled. I've no idea why you'd allow UDP access in to the SQL Browser port in the first place.
There's also a large amount of work needed to do with reducing the surface area of attack if a password is brute force hacked. SQL Server is not designed to be a web facing service in the same way an ftp site is, which is why these sort of features are absent.
If you cannot restrict by IP address, I would strongly suggest implementing a VPN solution, especially if you're dealing with any confidential/sensitive data at all.
Depending on the complexity of your solution, database size and whether it's a .Net app consuming the data, you could also look at SQL Azure as a possible alternative where there's more protection against these kind of attacks, although Microsoft still strongly recommends putting IP address restrictions in...
April 21, 2010 at 5:25 am
HowardW (4/21/2010)
You absolutely must use a non-standard port if publishing a SQL Server to the internet and SQL Browser should always be disabled. I've no idea why you'd allow UDP access in to the SQL Browser port in the first place.
I have some client applications that connect to my server to do synchronizing. Non-standard port it is.
There's also a large amount of work needed to do with reducing the surface area of attack if a password is brute force hacked. SQL Server is not designed to be a web facing service in the same way an ftp site is, which is why these sort of features are absent.
If you cannot restrict by IP address, I would strongly suggest implementing a VPN solution, especially if you're dealing with any confidential/sensitive data at all.
I have no sensitive info on the database, it's more a concern of general security as well as performance loss and wasted bandwitdh.
Depending on the complexity of your solution, database size and whether it's a .Net app consuming the data, you could also look at SQL Azure as a possible alternative where there's more protection against these kind of attacks, although Microsoft still strongly recommends putting IP address restrictions in...
Thanks, but as I said, my clients ar roaming the world and can connect form any IP address. Not keen on Azure at the moment since my web app would need to connect to a remote db, also not happy to pay by usage.
April 21, 2010 at 8:53 am
hein,
Not to put too fine a point on it, but the fact is that SQL doesn't do this, hole or not. A VPN solution would be the best, but you shot that down. A firewall solution is the next best solution, I'm not sure whether you shot that down or not. You SHOULD use your firewall to drop the traffic from the offending IP address and there is not a single situation where your SQL server should be hooked directly to the internet. With that said your firewall may allow a lot of traffic to your SQL server but it should ONLY be passing traffic to a specific port unless that traffic comes from a trusted IP or range of IPs. I understand wanting to make it easy for your users to connect, and quite frankly the firewall solution could easily be implemented in a way that is transparent to the users but still give you the ability to restrict traffic from offending IP addresses. It would also likely give YOU access statistics that might be useful.
We can debate all we want about whether such features should be in the product but they aren't and they won't be in the near future. So the only useful discussion is what we are going to do with what is available NOW. Drop a firewall in and configure it to drop the traffic. Build a process to detect the failed logins and give you a report so you can periodically update your firewall. Create a new user, sa2 perhaps, and set the sa password to some REALLY long completely random password and then don't use that account AT ALL. What that does is make the password harder to guess and you can change it at anytime without any issues since you aren't using it.
CEWII
April 21, 2010 at 9:25 am
Configuring a VPN client is only done once. Once configured, you simply start it and use it. One of our sysadmins even has his start automatically when he fires up his laptop so he doesn't have to even do that.
I do agree, however, that you need to at least configure a firewall between the internet and your SQL Server.
April 21, 2010 at 9:28 am
Elliott W (4/21/2010)
hein,Not to put too fine a point on it, but the fact is that SQL doesn't do this, hole or not. A VPN solution would be the best, but you shot that down. A firewall solution is the next best solution, I'm not sure whether you shot that down or not. You SHOULD use your firewall to drop the traffic from the offending IP address and there is not a single situation where your SQL server should be hooked directly to the internet. With that said your firewall may allow a lot of traffic to your SQL server but it should ONLY be passing traffic to a specific port unless that traffic comes from a trusted IP or range of IPs. I understand wanting to make it easy for your users to connect, and quite frankly the firewall solution could easily be implemented in a way that is transparent to the users but still give you the ability to restrict traffic from offending IP addresses. It would also likely give YOU access statistics that might be useful.
We can debate all we want about whether such features should be in the product but they aren't and they won't be in the near future. So the only useful discussion is what we are going to do with what is available NOW. Drop a firewall in and configure it to drop the traffic. Build a process to detect the failed logins and give you a report so you can periodically update your firewall. Create a new user, sa2 perhaps, and set the sa password to some REALLY long completely random password and then don't use that account AT ALL. What that does is make the password harder to guess and you can change it at anytime without any issues since you aren't using it.
CEWII
Thanks again for your advice. I'm going to do the following:
1. Move SQL to a non-standard port.
2. Create a very long password for sa.
I just dont see why I need a second login? Or at least why should I not use sa?
Again, your advice is appreciated.
April 21, 2010 at 9:31 am
Lynn Pettis (4/21/2010)
Configuring a VPN client is only done once. Once configured, you simply start it and use it. One of our sysadmins even has his start automatically when he fires up his laptop so he doesn't have to even do that.I do agree, however, that you need to at least configure a firewall between the internet and your SQL Server.
Perhaps I'm using the wrong type. With openVPN I need to generate a client certificate and get the user to install the adapter driver, load the parameters and modify the text file according to his setup. Then when he connects, he is unable to reach the internet unless ther are some DNS changes too, which differ depending on the type of internet setup.
Can you suggest a good, pay-for one? Except Hamachi. That just chows bandwidth 24/7.
April 21, 2010 at 9:41 am
hein-1120388 (4/21/2010)
Thanks again for your advice. I'm going to do the following:1. Move SQL to a non-standard port.
2. Create a very long password for sa.
I just dont see why I need a second login? Or at least why should I not use sa?
Again, your advice is appreciated.
sa should NOT be used by any process, because it generally prevents the password for sa from being changed without significant pain. The only way to disable the sa login is to switch to integrated security ONLY. sa is a well known login so when a SQL server is trying to be breached IT is the login that will be attempted. By setting up a second user and then using it you can easily change the sa password at will and there will be no ill effects. By using a crazy obscenely long password you don't prevent but delay hack attempts. The idea is to make it harder to break in without adding significant complexity to your maintenance.
A non-standard port is a good start, it makes it more difficult to find the server, but it is also what is called security through obscurity. Which is basically if they don't know where it is they can't hack it. A port scanner takes care of that problem..
Putting a firewall in front of it, even a cheap one is a must and gives you a lot more security options than you have now. Just to be clear, I'm not talking about the firewall on the machine but an external one that sits between the machine and the internet.
I have several Linksys RV042 VPN routers that I just love and I think they can do what we are talking about. I have acquired them from ebay for less than $100 each, you might look into them on the linksys site.
CEWII
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply