Rob Kraft's Software Development Blog

Software Development Insights

SQL to Find Who Is Trying To Hack The SQL Server You Exposed To The Internet

Posted by robkraft on May 5, 2020

If you are running a SQL Server exposed to the Internet on the default port 1433 then hackers are probably try to hack in to it.  If the SQL Server is running on a different port it is less likely, but it could still be happening.  If you exposed a SQL Server to the world the hackers on the Internet will find it and will launch repeated login attempts against the ‘sa’ account.

That is a good reason for disabling the ‘sa’ account.

That is also a good reason for renaming the ‘sa’ account.

Most SQL Server admins record the Failed Login Attempts in the SQL Server Error Log.  If you have this option turned on, which is recommended, you may see entries like this:

Login failed for user ‘sa’. Reason: Could not find a login matching the name provided. [CLIENT: 113.64.210.68]

You can write an SQL to read the SQL Server ErrorLog and extract all the IP addresses that are attempting to login to your server using the ‘sa’ account.  You can then provide that list to your firewall to blacklist them, although a whitelist of known good addresses is a much better approach.  After you blacklist those IP Addresses, come back the next day and check your logs again because you will probably find a whole bunch of new IP addresses doing the same thing.  This game of ‘whack-a-hacker’ can be played for a long time before the hackers run out of IP addresses.  By the way, if you are going to try to blacklist the bad IP addresses, I recommend blocking entire blocks like 113.64.*.*, not a single IP Address at a time.

Here is an SQL to get you started with reading and filtering your SQL Server Error Logs.  Notice this script only looks for failed login attempts using the ‘sa’ account.  If you look at your logs, you will find failed login attempts for other accounts too.  Happy Hunting.

CREATE TABLE #TmpLog
(LogDate datetime, ProcessInfo VARCHAR(150), Text VARCHAR(max))
INSERT INTO #TmpLog
EXEC sp_readerrorlog
SELECT replace(Text,'Login failed for user ''sa''. Reason: Could not find a login matching the name provided. ',''), 
count(*)
FROM #TmpLog where Text like '%Client%' and Text like '%''sa''%' group by
replace(Text,'Login failed for user ''sa''. Reason: Could not find a login matching the name provided. ','')
having count(*)>10
order by 2 desc
DROP TABLE #TmpLog

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: