Avatar of al4629740
al4629740
Flag for United States of America asked on

Repeated failed logins

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.
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
arnold

8/22/2022 - Mon
Dr. Klahn

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.
Andrei Fomitchev

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.
SOLUTION
David Favor

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Scott Fell

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
arnold

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
al4629740

ASKER
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



arnold

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 .....
al4629740

ASKER
What’s the next best thing if the VPN is not a convenient thing to setup?  Could I limit blocks of IP?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
arnold

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
al4629740

ASKER
sonic wall firewall
ASKER CERTIFIED SOLUTION
arnold

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
al4629740

ASKER
can I sort a list of offending IPs from the SQL log?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
arnold

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