9

I am having an issue and could not figure out how to handle it. I have a SQL Server on a Windows 2008 R2 server. This SQL Server 2005 is being used to receive DB subscriptions from another SQL Server located elsewhere on the Internet. I have the sql server port open through the firewall, however in the scope, I have entered the IP of the other SQL Server. Doing so I hoped that connection requests through that port will not reach the SQL Server unless the requests are coming from the other SQL Server (whose IP is listed in the scope in the firewall rule). But, when I see the log, there are hundreds of "login failed user sa" entries (and they are coming each passing second). It appears some hacker is trying brute force to guess user sa password. But the question is, why Windows is letting these request reach the SQL Server even though they are not coming from the IP address that is listed in the firewall scope? What is the right way to protect this SQL Server. No other IP than the IP of the other SQL Server needs to connect to this sql server.

EDIT - Further Info:

I ran telnet on the sql server port from different machines. Telnet fails except when it is run from the machine that is specifically mentioned in the firewall scope. So it appears that the firewall is blocking the sql server port fine. But then why I am seeing those failed login requests to user "sa" from different IP addresses in SQL Server log? Is it possible the hacker is entering the machine via port 80 and then somehow trying to connect to the sql server? Port 80 and 443 are open to everybody. All other ports are closed with the exception of the sql server port (and that is open only for one specific IP). There is nothing running on the Webserver at port 80 that may lead a visitor to SQL server. In fact, there is only one index.html (pure HTML with no connection to SQL) file in the Web server. This is just a test server being setup for future use. Only test data in SQL Server.

EDIT:

I turned on firewall tracing to include both drop and success connections. It is now tracing everything. Then I go to SQL Server log where I see these failed login attempts from different IP addresses in China. But there are no entries for these IP addresses in the firewall log. How is this possible? Can they get to SQL server bypassing firewall completely? If suppose some firewall port was open that they could enter through, the firewall log should show an entry for that IP address. I am at a complete loss.

  • How about using Logon Trigger ? Also, SQL Server should not be exposed to Internet (public facing). Also, you can use Program Rules in Windows Firewall to limit range of IP addresses. – Kin Shah Dec 24 '13 at 1:43
  • Yes I could, but deep down this is not really about SQL Server. I am trying to find out why Windows firewall is not doing what it is supposed to be doing, that is, not letting these requests pass through because they are not coming from the only allowed IP address. – Allen King Dec 24 '13 at 1:45
  • 1
    Yes it sounds like your Windows Firewall is misconfigured. Maybe you have another more general rule that is allowing port 1433 (or allowing everything), or maybe Windows Firewall is disabled on the internet-facing NIC. It's hard to say without seeing the configuration. But this is really more of a question for ServerFault like Max says. Did you mean Windows 2008 R2, btw? 'RC' is Release Candidate, pre-release software for testing. – James L Dec 24 '13 at 6:31
  • 1
  • 1
    Are you really using an old release candidate operating system?! Fix that first. – Michael Hampton Dec 24 '13 at 15:40
12

Sounds like your firewall isn't configured correctly. This is the first step.

Normally I wouldn't pimp a book that I wrote, but in this case I'll make an exception. It's called Securing SQL Server and it'll give you a good start.

  • Well, your book is an exact fit for this question :-) – mfinni Dec 24 '13 at 13:38
  • Cool! Does it have prescriptive guidance about using certificates instead of local/Windows auth for internet-facing servers? ;-) – Greg Askew Dec 24 '13 at 19:19
  • See my edits. Firewall seems to be working ok. It is some other trick these Chinese hackers are playing to get to the SQL Server. – Allen King Dec 25 '13 at 1:14
  • 1
    If someone is connecting to the SQL port from a public IP Address, then the firewall isn't configured correctly. If they are coming from an internal IP then you've got some other kind of problem. SQL by it self doesn't support certificates. You have to do this via AD and certificate mappings. – mrdenny Dec 26 '13 at 1:51
  • Back to the drawing indeed for the firewall. I would pull the SQL server off the internet until you get your firewall situation resolved. That way you'll be able to IP ban entire ranges of IP addresses with relative ease. – Techie Joe Dec 28 '13 at 0:15
4

SImply said - you do not. I would not use afirewall etc. - a SQL server has no right to be on the internet. VERY VERY few exceptions.

For replication, set up a proper VPN.

  • I am a little confused. If the DB server is not on the Internet, how will a Web App backend server reach the DB server? – Allen King Dec 24 '13 at 19:35
  • Through the internal network. Basic setup. THe web app sits in both networks. – TomTom Dec 24 '13 at 19:50
3

Besides configuring the firewall properly, here are some general recommendations to keep SQL Server safe from brute force attacks:

  • Disable the 'sa' account. Knowing the exact login name will make attacks easier

    ALTER LOGIN sa DISABLE
    

Another option is to rename the 'sa' account to a less obvious name

ALTER LOGIN sa WITH NAME = SimonXZY
  • Use Windows authentication instead of mixed mode authentication. The Windows authentication enforces the Windows password policy and it locks the login in case of successive failed logon attempts
  • Audit failed logons. The easiest way to do this is to set the Login auditing option in the Server properties, Security tab to Failed logins only or Both failed and successful logins. This will not help you protect against brute force attacks, but will help you be aware of the attacks

More useful recommendations here: Preventing a Brute Force or Dictionary Attack: How to Keep the Brutes Away from Your Loot

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.