Trigger de connexion avec liste blanche d'adresses IP
N'autoriser les connexions SQL Server que depuis les adresses listées dans une table de la base master.
Un trigger de connexion peut n'autoriser les connexions que depuis les adresses clientes listées dans une table. La table ValidIPAddress dans master contient les adresses autorisées. Toute connexion depuis une autre adresse est annulée. Lire les remarques avant de l'activer.
Créer la table des adresses autorisées
USE [master]
GO
/****** Object: Table [dbo].[ValidIPAddress] Script Date: 10/12/2020 9:41:22 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ValidIPAddress](
[IP] [nvarchar](15) NOT NULL,
CONSTRAINT [PK_ValidAddress] PRIMARY KEY CLUSTERED
(
[IP] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Créer et activer le trigger de connexion
USE [master]
GO
/****** Object: DdlTrigger [tr_LogOn_CheckIP] Script Date: 10/12/2020 9:39:52 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [tr_LogOn_CheckIP] ON ALL SERVER
FOR LOGON
AS
BEGIN
DECLARE @IPAddress NVARCHAR(50) ;
SET @IPAddress = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]',
'NVARCHAR(50)') ;
IF NOT EXISTS ( SELECT IP
FROM master..ValidIPAddress
WHERE IP = @IPAddress )
BEGIN
-- If login is not a valid one, then undo login process
SELECT @IPAddress
ROLLBACK --Undo login process
END
END
GO
ENABLE TRIGGER [tr_LogOn_CheckIP] ON ALL SERVER
GO
Remarques
- La table est créée vide. Insérer les adresses qui doivent garder l'accès avant d'activer le trigger, y compris l'adresse depuis laquelle on se connecte. Sinon, le trigger refuse toute connexion non listée.
- Le trigger n'exempte pas les membres sysadmin. Garder une connexion d'administration dédiée disponible et un plan de retour arrière (voir l'article sur la récupération après un trigger de connexion bloquant).
- L'adresse cliente est celle que le serveur voit dans
EVENTDATA(). Les connexions passant par un proxy ou un NAT apparaissent avec l'adresse de cet équipement. - La ligne
SELECT @IPAddressrenvoie l'adresse refusée au client avant l'annulation. La supprimer si elle n'est pas utile. - La source est un export de script SSMS avec ses commentaires d'en-tête.