SQL doesn’t really give us too many tools out of the box to allow us to spot when someone may be up to no good.
We can look at the number of failed login attempts in SQL’s error log. If you start seeing multiple login attempts, especially for SA or any other suspicious looking user name that could indicate a brute force attack. If you’ve got a SQL Server that’s open to the internet (I know, you really shouldn’t be doing that but let’s face it, sometimes in the real world it’s unavoidable. Especially if you’ve inherited an application written by someone who didn’t know better.) you’ll probably see quite a few of this sort of thing in your logs.
I’ve got a SQL Server which is internet facing, it’s purely for my own testing and totally cut off from anything that I actually care about so no big deal if someone does get into it but I see login failures constantly on known accounts such as SA but also on ones that don’t exist on my system where the attacker is having a guess, ‘DBA’, ‘DBAdmin’ and ‘SQLAdmin’ seem fairly common.
These are easy to watch for and if you’re using our Undercover Inspector, failed logins are part of the report it produces.
Brute Force Hackers are Opportunists, what about the Serious Bandidos?
But let’s be honest, the above attacks are just going to be opportunistic hackers probably using an automated tool to hunt out open servers and try to exploit them, a bit like the guy walking down the road and trying all the car doors in the hope that someone will have left their unlocked. It someone’s specifically targeting your servers, the chances are that they’ll be a bit more sophisticated.
This is where things get a little more troublesome and where SQL doesn’t really give us any tools to help.
But there are warning signs that we can look out for and these can be certain error messages in the logs. There are three in particular that I see as red flags that something amiss may be going on.
Err 17810 Could not connect because the maximum number of dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process.
Err 17832 The login packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library.
Err 17836 Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library.
I’ve recently been involved in a couple of security incidents where seeing email alerts on these errors allowed me to very quickly identify a potential attack, shut the servers down and scramble the relevant security teams to prevent further damage to the systems. The first incident was a genuine cyber attack, the second turned out to be a penetration test that I was unware of happening. I did cause a bit of a kerfuffle with that second one but I’d much rather take the heat for disruption to a customer from my calling a false alarm, than not act and have to deal with the consequences of a successful cyber attack (and if I’d have been told that there were pen testers poking around, I wouldn’t have gone into panic mode).
Alerting
It was having email alerts on these errors that allowed us to act quickly. I’d always recommend having email alerts set up on any error severity 17 or above as I spoke about here https://sqlundercover.com/2020/01/30/your-sql-servers-screaming-but-is-anyone-listening-high-severity-alert-notifications/ and as the alerts we’re looking at today are all severity 20 you should be getting notified about them.
But, as I’ve already done with certain corruption related errors, I’ve been starting to think that it’s probably worth having specific alerts set up for these errors so they’re more noticeable when they hit my inbox.
The below code will create the alerts and notifications on those errors. Please see the above mentioned link on setting up high severity alerts if you need information on creating an alert category or operator.
USE MSDB
--Corruption Errors
EXEC msdb.dbo.sp_add_alert @name=N'URGENT POTENTIAL ATTACK: Error 17810',
@message_id=17810,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'High Severity Error'
GO
EXEC dbo.sp_add_notification
@alert_name = N'URGENT POTENTIAL ATTACK: Error 17810', @operator_name = N'AHCDBA', @notification_method = 1 ;
GO
EXEC msdb.dbo.sp_add_alert @name=N'URGENT POTENTIAL ATTACK: Error 17832',
@message_id=17832,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'High Severity Error'
GO
EXEC dbo.sp_add_notification
@alert_name = N'URGENT POTENTIAL ATTACK: Error 17832', @operator_name = N'AHCDBA', @notification_method = 1 ;
GO
EXEC msdb.dbo.sp_add_alert @name=N'URGENT POTENTIAL ATTACK: Error 17836',
@message_id=17836,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'High Severity Error'
GO
EXEC dbo.sp_add_notification
@alert_name = N'URGENT POTENTIAL ATTACK: Error 17836', @operator_name = N'AHCDBA', @notification_method = 1 ;
GO
Thanks for reading and I hope you found it useful.