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
- The table is created empty. Insert the addresses that must keep access before enabling the trigger, including the address you connect from. Otherwise the trigger refuses every connection that is not listed.
- The trigger has no exemption for sysadmin members. Keep a dedicated administrator connection available and a rollback plan (see the article on recovering from a blocking logon trigger).
- The client address is the one the server sees in
EVENTDATA(). Connections through a proxy or NAT device appear with the address of that device. - The
SELECT @IPAddressline returns the refused address to the client before the rollback. Remove it if it is not needed. - The source is an SSMS script export with its header comments.