LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon