Trigger de connexion avec liste noire d'adresses IP
Refuser les connexions SQL Server venant d'adresses listées dans une table, avec journalisation des tentatives refusées.
Un trigger de connexion au niveau serveur peut refuser les connexions venant d'adresses clientes figurant dans une table de liste noire. Cet exemple crée une base d'audit avec un journal des connexions et une liste noire, puis un trigger de connexion qui annule les connexions issues des adresses listées et enregistre chaque tentative refusée. Les membres de la rôle sysadmin et un login applicatif sont exemptés.
Créer les objets d'audit
USE AuditDb;
go
use [AuditDb]
GO
GRANT CONNECT TO [guest]
GO
DROP TABLE AuditDb.dbo.logIP
go
DROP TABLE AuditDb.dbo.blackListIP
go
--DELETE AuditDb.dbo.logIP
go
CREATE TABLE AuditDb.dbo.blackListIP (
IP NVARCHAR(15),
CONSTRAINT PK_blackListIP PRIMARY KEY CLUSTERED (IP)
);
GO
GRANT SELECT ON AuditDb.dbo.blackListIP TO PUBLIC;
go
CREATE TABLE AuditDb.dbo.logIP (
IP NVARCHAR(15), login NVARCHAR(200),timeStamp DATETIME2 DEFAULT GETDATE(), Host_Name NVARCHAR(128), status VARCHAR(20)
);
GO
GRANT INSERT ON AuditDb.dbo.logIP TO PUBLIC;
go
--SELECT * FROM AuditDb.dbo.logIP
--SELECT * FROM AuditDb.dbo.blackListIP
-- Exemple : ajout d'une adresse à la liste noire (192.0.2.10 est une adresse de documentation)
INSERT INTO AuditDb.dbo.blackListIP (IP) VALUES ('192.0.2.10');
go
Créer le trigger de connexion
ALTER TRIGGER [tr_logon_CheckIP]
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF IS_SRVROLEMEMBER('sysadmin') <> 1
BEGIN
IF ORIGINAL_LOGIN() <> 'app_readonly'
BEGIN
DECLARE @IP NVARCHAR(15);
DECLARE @USER nvarchar(200);
DECLARE @Status NVARCHAR(15);
DECLARE @hostName NVARCHAR(200);
SET @Status = 'OK'
SET @USER = ORIGINAL_LOGIN()
SET @hostName = HOST_NAME()
SET @IP = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)'));
IF EXISTS(SELECT IP FROM AuditDb.dbo.blackListIP WHERE IP = @IP)
BEGIN
SET @Status = 'Denied'
ROLLBACK;
INSERT AuditDb.dbo.logIP (ip, LOGIN, HOST_NAME, STATUS)
SELECT @IP, @USER, @hostName, @Status
END
--INSERT AuditDb.dbo.logIP (ip,LOGIN ,HOST_NAME,status)
--select @IP,@USER,EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','NVARCHAR(128)'),@Status
END;
END
END;
GO
Remarques
- Les membres du rôle sysadmin ne sont jamais contrôlés. Le login
app_readonlyest exempté dans le trigger : le remplacer par le login applicatif qui doit toujours pouvoir se connecter, ou supprimer ce test. - Tester d'abord hors production. Un trigger de connexion qui annule les connexions peut bloquer toutes les connexions ; garder une connexion d'administration dédiée disponible (voir l'article sur la récupération après un trigger de connexion bloquant).
ALTER TRIGGERexige que le trigger existe déjà. Pour un premier déploiement, utiliserCREATE TRIGGERavec le même corps.- Le script accorde CONNECT à
guestet supprime puis recrée les deux tables, ce qui efface leurs données existantes. Relire ces deux instructions avant exécution. - Les placeholders de la source ont été remplacés : la base d'audit s'appelle
AuditDb, le login exemptéapp_readonly, etSERVER01()dans la source correspond àHOST_NAME(), la fonction qui renvoie le nom de l'hôte client.