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