1

We are building a cloud-based application that relies on a login database as well as many individual client databases. Using rackspace we have setup a server (windows server 2012) with SQL Management Studio 2012 (Web) and are in the testing/QA procedures before launch.

Using the servers event viewer to look for some debug information, I noticed that a few IP's have been attempting to make a connection with our SQL database every minute or so. We are using the default 'sa' user as the super user.

My questions are:

  • Can we set a maximum number of requests for any individual IP before a 'ban' is placed on it somehow?

  • Will changing the user account name to something rather random be helpful?

  • Are there any other steps we can take to protect our database?

6
  • 6
    For one thing, you should not be using the sa account for anything and it should be locked down to localhost access only. SQL shouldn't be exposed to the Internet if possible.
    – Nathan C
    Jan 29, 2014 at 13:24
  • @NathanC well that's easily fixed. Is there any inherent way to block incoming requests via SMSS?
    – Mike H.
    Jan 29, 2014 at 13:32
  • 1
    How do you know it's people using SMSS to connect? More likely, it's general network noise and probes scanning IP ranges so there's nothing dangerous going on. Use a firewall to restrict who can access your SQL Server.
    – Nathan C
    Jan 29, 2014 at 13:36
  • Well I don't necessarily believe it's a person physically typing in random passwords. The event viewer is throwing a very specific error "Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: 89.19.14.98]". Which leads me to believe that be it a program or person, they are trying to login to management studio.
    – Mike H.
    Jan 29, 2014 at 13:46
  • @mhome9 Why do you say "to login to management studio"? NOONE tries that. They try to login into your sql SERVER, not the client application you use to connect to it.
    – TomTom
    Jan 29, 2014 at 14:01

4 Answers 4

1

As others have mentioned your security structure is quite wrong... That being said here are some best practices to look at.... also see link to full article below. I would suggest rethinking your structure and security approach from the ground up.

http://www.serverwatch.com/tutorials/article.php/3554051/Lock-Down-IIS-and-SQL-Server.htm

Basic SQL Server Security Checklist

Set a password on your SA account and restrict its use. Also, change the password periodically to keep it from 'propagating' and being used by developers or too many administrators. Change the SA password if anyone who knows it leaves the company. Use eEye's tool to scan your network for SQL servers with no SA password.

Place your SQL Server behind a firewall, separate from your IIS or Web servers. Only allow connections to the SQL server from those designated web servers. Your SQL server should never be Internet-facing or publicly accessible.

Remove BUILTIN/Administrators from the sysadmin role and give sysadmin rights in SQL to specific domain accounts that need it.

Use Windows Authentication and Windows Only Mode if possible. This way, a potential hacker must authenticate to the domain first instead of just to SQL Server.

Do not run SQL Server on a Domain Controller.

Change the SQL Server service startup account to something besides LocalHost.

Enable the Failed Login option (Server Properties | Security tab) so you can look for failed logins to see if an unauthorized person is trying to access the server. Monitor the SQL logs and set up alerts in SQL using NETSEND or email, if possible.

Keep up to date on patches and service packs for the operating system and SQL. See Tools for Securing IIS for some options.

Protect any Extended Stored Procedures. Control all data access through stored procedures and grant access to those instead of giving blanket db_datareader and db_datawriter permissions to the data itself. See Part I of this article for more information.

Change the standard SQL Server port under the Server Network Configuration Utility and block the default port of 1433. Have your network administrator allow the new port instead.

Make sure the Everyone group doesn't have write access to SQL Server registry keys

2
  • Excellent answer, thank you for your objective and pointed response. I knew the 'access layer' of the application was not finished, I just never realized how much we should be working on it. I appreciate your respectful advice.
    – Mike H.
    Jan 29, 2014 at 14:35
  • I am glad I could help :)
    – DaffyDuc
    Jan 29, 2014 at 15:00
4

You should really take SQL Server off the Internet and firewall it so external access isn't allowed. Then, use a VPN to each client site. Yes, this isn't the easiest of setups but it's the only way to block random connection attempts at all hours of the day. Also, the "attacks" tend to increase in speed as more probes find your SQL instance open to the outside world.

The implementation of what's being done needs to be looked at from a security perspective. Also, this is access to SQL Server itself; SSMS simply speaks SQL to it so you can manage it.

2
  • A VPN is a great idea but the premise of the application is mobility. The connections will not necessarily come from the 'client site', and may come from a phone/tablet/device in another state.
    – Mike H.
    Jan 29, 2014 at 14:10
  • 4
    @mhome9 Hello Premise, here is reality. NOT GOOD. Then you need to put a high end firewall in front - one that can track connection attempts to the port and block the IP in case. Someone REALLY made some not smart design decisions for the application. Standard approach is to use a web service as application gateway into a hidden database.
    – TomTom
    Jan 29, 2014 at 14:12
2

First of all, you shouldn't be using SA, as others have pointed out.

Secondly, this server shouldn't be exposed to the Internet, for reasons that you've seen. What Nathan C said; set up a VPN.

Lastly, unless you've specifically configured SQL with a certificate, traffic is unencrypted by default and subject to sniffing. (Not the username/password, but your actual data.)

Seriously, this is almost certainly a violation of PCI. Please, firewall that off.

2
  • Yea the sa user will not be used. A VPN is not a valid option. Encryption will be paramount and we are working on it, that is not an issue. As for compliance, this data isn't particularly sensitive...I suppose I'll look into the specifics more but I doubt it really even matters if we comply with standards.
    – Mike H.
    Jan 29, 2014 at 14:16
  • 1
    Then perhaps you should consider a web service, as TomTom suggested. Jan 29, 2014 at 14:17
1

I would block TCP port 1433 (port used by SQL Management Studio) in the firewall for external ips, and only allow localhost or a set of trusted ips.

4
  • 1
    Restricting to a set of trusted ips is a bit troublesome. Our databases are sync'd with our clients local db copies. Each db has a unique connection string which they must pull from a master login db. Their IP must be allowed access to management studio...and there will be hundreds and eventually into the thousands of users. Manually adding permissions for each one seems impractical.
    – Mike H.
    Jan 29, 2014 at 13:48
  • 3
    Put them into an internal network, use a VPN for clients to connect to it. SImple and standard setup.
    – TomTom
    Jan 29, 2014 at 13:56
  • @MJV you may want to edit ot. Port 1433 is not used by sql management studio but by SQL SERVER.
    – TomTom
    Jan 29, 2014 at 14:02
  • @TomTom yes I meant server, not management studio. Cannot edit comment for some reason.
    – Mike H.
    Jan 29, 2014 at 14:12

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .