Logon trigger with an IP address blacklist
Refuse SQL Server logons from client addresses listed in a table, and log the refused attempts.
A server-level logon trigger can refuse connections from client addresses listed in a blacklist table. This example creates an audit database with a logon log and a blacklist, then a logon trigger that rolls back logons from blacklisted addresses and records each refused attempt. Sysadmin members and one application login are exempt.
Create the audit objects
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
-- Example: blacklist one client address (192.0.2.10 is a documentation address)
INSERT INTO AuditDb.dbo.blackListIP (IP) VALUES ('192.0.2.10');
go
Create the logon trigger
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
Notes
- Sysadmin members are never checked. The login
app_readonlyis exempt in the trigger: replace it with the application login that must always connect, or remove the test. - Test in a non-production environment first. A logon trigger that rolls back can block every connection; keep a dedicated administrator connection available (see the article on recovering from a blocking logon trigger).
ALTER TRIGGERrequires that the trigger already exists. For a first deployment, useCREATE TRIGGERwith the same body.- The script grants CONNECT to
guestand drops and recreates the two tables, which deletes their existing data. Review both statements before running it. - Placeholders from the source were replaced: the audit database is named
AuditDb, the exempt login isapp_readonly, andSERVER01()in the source isHOST_NAME(), the function that returns the client host name.