Login Failed – IP Adressen Zugriff auf SQL Server verweigern

Normalerweise würde man den Weg umgekehrt gehen: nur bestimmte Adresse bekommen Zugriff auf den SQL Port. Allerdings bin ich häufiger unterwegs und beziehe per DHPC verschiedenste Adressen. Daher steht der SQL offen im Netz und wird gerne Ziel verschiedenster Angriffe. Das Problem ist für mich zunächt der Versuch oder die Möglichkeit Accounts zu knacken, sondern eher, daß die Bruteforce Attacken jede Menge Ressourcen kosten.

Hier mal ein kleiner Ausschnitt der TOP 10 Versuche. Gelistet pro IP Adresse:

Um diesen Problem Herr zu werden, gibt es 2 Methoden:

1.) Auslesen des Logfiles, Herausfinden der IP Adressen der Login Failed Einträge. Hinzufügen der IP Adressen zur Firewall Ausnahme des SQL Servers.

2.) Überwachen der Login Versuche per DDL. Vergleich der IP Adresseb mit einer Tabelle, in der nur zugelassene IP Adressen vorhanden sind.

Methode 1: Bannen einer IP Adresse mit Hilfe des SQL Log und Login Failed Einträgen

set nocount on
--Anlegen der temo tabelle für Logfile
create table #IPlog (Logdate datetime, Prozess nvarchar(100), text nvarchar(250))
go

--Logfile in tempTabelle
insert into #IPLOG (logdate, prozess, text) 
exec xp_readerrorlog 0, 1,'Login failed'
go

declare @ip as varchar(200)
set @ip=''

select  @ip=ip+','+ @ip
from 
        (select    SUBSTRING(text,    (CHARINDEX('[',text)+8),
        (CHARINDEX(']',text)-(CHARINDEX('[',text)+8))) as IP
         
     from 
           (select text, count(*) as anzahl  from #iplog 
    where Logdate > getdate() -14 and text not like '%local%' group by text having count(*) > 5 ) t1
 ) t

set @IP=replace(@ip,' ','')
select @ip –später für Windows Batch auskommentieren

drop table #iplog

Mit Hilfe der undokumentierten Prozedur xp_readerrorlog werden alle Einträge ausgelesen, die ein “Login Failed” enthalten und in eine temporäre Tabelle gelesen.

Mit den Funkionen Substring und Charindex werden nun aus der temporären Tabelle alle IP Adressen ausgelesen, die mehr als 5 fehlerhafte Logins erzeugten und in eine Variable @IP geschrieben.

Für das folgende Windows Skript werden die IP Adressen kommasepariert benötigt.

Anlegen der Firewallausnahme für bestimmte IP Adressen

Um die IP Adresse automatisiert zu bannen, verwende ich den Befehl netsh. Die Syntax ist unter Windows 2003 leider anders als unter Windows 2008/7. Hier das Listening für Windows 2008/7

Hier ein Beispiel:

C:UsersUser>netsh advfirewall firewall add rule name=SQL protocol=TCP dir=in action=block
remoteip=222.222.222.222 remoteport=1433

C:UsersUser>netsh advfirewall firewall set rule name=SQL  
new remoteip=222.222.222.223 remoteport=1433

Wie kann man das nun automatisieren? Per sqlcmd!

Man ergänzt das TSQL Script einfach um folgende Zeilen:

declare @cmd varchar(200)
set @cmd='netsh firewall add portopening protocol=TCP port=1433 name="SQL"  addresses='+@IP+' scope=CUSTOM profile=Standard'
select @cmd

Den select @IP im Script auskommentieren! Wir lassen uns den netsh Befehl, der in unserem Script zusammengebastelt wird in eine Batch-Datei schreiben. Das SQL Skript speichern wir unter ipsuche.sql.

C:sqlcmd -i ipsuche.sql -o netshregel.bat

..so, und nun zum Schluss eine Batchdatei, in der alles regelt wird und im Windows Aufgabenplaner integriert werden kann.

fw.bat:

sqlcmd -i ipsuche.sql -o netshellregel.bat
netshellregel.bat

Methode 2: Nur bestimmte IP Adressen zulassen

Das ist für mich persönlich der angenehmere fall und deutlich komfortablere Lösung. Anhand einer Tabelle in der nur die erlaubten IP Adressen gelistet werden, untersucht unser DDL Trigger bei jedem Loginversuch, ob die Client IP Adresse in der Liste vorhanden ist.

Sofern diese nicht geklistet ist, wird der Loginversuch rückgängig gemacht.

create table ipliste (ip varchar(15));
Go

insert into ipliste (ip) values ('127.0.0.1'),('192.168.178.42'), ('192.168.178.45');


alter trigger trg_loginfailed
on ALL Server
for Logon
AS 
BEGIN 
   DECLARE @IP VARCHAR(15)
   SET @IP = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'VARCHAR(15)'))
   --Prinzip:  IF @IP='192.168.178.40'  Rollback
    --Hier Überprüfung auf eine bestehende IP in einer Tabelle
    if not exists (select * from iplist where ip =@ip) rollback
END
  
GO  

Zwei Anmerkungen dazu:

– Man wird auch dann ausgesperrt, wenn man ein gültiges Login hat, da nur bestimmte IP Adressen zugelassen werden

– Jede Anmeldung geht an der Firewall vorbei. Das heißt, dass die Belastung durch Bruteforce Attacken nicht verhindert wird.

Viel Spaß damit