We help IT Professionals succeed at work.

Repeated failed logins

Medium Priority
35 Views
Last Modified: 2020-03-25
I was looking in our records and noticed some alarming login attempts on our SQL server.  Seems like its relentless.  Is there a way to limit the number of times someone tries to logon to our SQL server if it coming from the same IP address and being rejected.
Comment
Watch Question

Dr. KlahnPrincipal Software Engineer
CERTIFIED EXPERT

Commented:
a) If the IP address is outside of your LAN, then the firewall should be checked for problems.  It should not permit access to a critical server from outside the LAN.

b) If the IP address is from inside your LAN, your IT security team should be finding that system promptly and find out what is going on.

Dealing with the problem is more effective than dealing with the symptom.
CERTIFIED EXPERT

Commented:
1. Get failed login:
DECLARE @ErrorLogInfo TABLE ( LogDate DateTime, ProcessInfo NVarChar (50), [Text] NVarChar (Max))
INSERT INTO @ErrorLogInfo EXEC sp_readerrorlog -1,1
SELECT * FROM @ErrorLogInfo WHERE ProcessInfo = 'Logon'

2. Store failed attempts
CREATE TABLE FailedLogins(Id Int IDENTITY, IP VarChar(50), FailedAttempts Int)

3. Create login trigger:
CREATE TRIGGER LogonTrigger_RestrictIpAddresses
    ON ALL SERVER FOR LOGON
AS
BEGIN
    DECLARE @IP Varchar( 100 )
    SET @IP = EventData().value( '(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(100)' )
    IF Exists(SELECT NULL FROM FailedLogins WHERE IP = @IP)
         UPDATE FailedLogins SET FailedAttempts = FailedAttempts + 1
      ELSE INSERT INTO FailedLogins (IP, FailedAttempts) VALUES (@IP,1)
    -- If more than 3 attempts - rollback
    IF @IP IN ( SELECT IP FROM FailedLogins WHERE FailedAttempts > 3 )
    ROLLBACK TRANSACTION
END

4. To enable login again - remove IP from FailedLogins

Note: You can add the column of the date of the last failed login to the table FailedLogins. Schedule "remove IP" if the failure date older than 2 days (or some other value) for auto clean up.
David FavorFractional CTO
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
Options...

1) If your App code runs on the same machine as your database instance, only allowing connections from 127.0.0.1 will fix the problem.

2) If App code + database code live on separate machines, create a firewall ACL on your database machine only allowing connections from your App machine.

3) If many valid machines must connect to your database machine, use https://github.com/glasnt/wail2ban one of the many other Fail2Ban Windows ports.

Then with Fail2Ban create a rule which scans your database log file looking over a 10 second span, for any single login failure, then blocks the attacking IP for an hour.

This will reduce load on your database instance... because... 1,000,000s of attempts over short periods can take down a database instance, unless attackers are blocked at the OS Kernel. The take down might be a database crash or machine/container crash.

This also provides a self maintaining system.

Eventually constantly blocked attackers will eventually give up... or if they continue, their attacks will be meaningless (from a resource usage standpoint).
Scott FellDeveloper & EE Moderator
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013
Commented:
The options noted are both very good. Some way to temporarily ban an ip that looks like an attacker. It does need to be temporary as IP's for individuals change and you could end up banning somebody in the future you didn't mean to. For a web app, using localhost for connections is the best option.

In the end, looking at the logs can make you crazy. It is good to review, but if you have good set up, it would be hard for an outside person to log in unless they got a hold of the server. The logs for the db and server are always going to be filled with people trying to get in and there is not anything you can do about people attempting.  
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
As others posted much of the information about your setup is missing
1) is the SQL server exposed to the internet
2) base of your software web based or win32 client type
3) logon method SQL or integrated

Author

Commented:
My SQL server is exposed to the world because it needs to access an application that
win32
Sql and windows login.  But SA is used as the login from the outside



CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
It is unwise to expose SQL serve to the world.
Depending on your need, usually using a web server with other tools to mitigate such vulnerability/exposure.

Sa is a known login which is similar to root in UNIX/Linux or administrator, wreck hacks come in they use common known accounts.

To address your issue you gave to narrow the exposure of your SQL server
Setup a VPN connection .....

Author

Commented:
What’s the next best thing if the VPN is not a convenient thing to setup?  Could I limit blocks of IP?
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Do you gave a firewall?
You can allow inbound traffic from explicitly specified ip, ip ranges.

You can setup a web server with soap, predefined functions through which you can have your win32 client access the sata without exposing your SQL server to the outside.

The functions inthe web server would be lifting such attacks.

As to the mentioned ip restriction much depends on what you have

Author

Commented:
sonic wall firewall
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
Yes, on your port forwarding setup, instead of allowing from any, specify a group depending on your use, you can deny the group or permit the group and add IPs accordingly.

The decision on which to use depends on which are fewer.
Do you have a smaller list of IPs to authorize or IPs yo reject/deny.

A better approach is to explicitly define IPs from which you E pet connections and want to authorize.

Sonicwall includes VPN support.

Author

Commented:
can I sort a list of offending IPs from the SQL log?
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Why would you want to leave your SQL exposed to the internet. Do you have an IP range from which you need access to the SQL?

The answer to your question, these will not end, as your sql server will be accessed from all over the world with an attempt to hack it.

Limit it to the IPS you know need access.

You can

add a rule before the grant with the rule to deny access from the exclude_group
add IPs, ip ranges into the exclude group

access the gui, add a rule before the one grants access