LLI Wiki — My Digital Knowledge Base

Logon trigger with an IP address whitelist

Allow SQL Server logons only from the addresses listed in a table in the master database.

A logon trigger can allow connections only from the client addresses listed in a table. The table ValidIPAddress in master holds the allowed addresses. Any logon from another address is rolled back. Read the Notes before enabling it.

Create the table of allowed addresses

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

Create and enable the logon trigger

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

Notes

Mastodon LLIMastodon SocialFosstodon