LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon