Trigger de connexion limitant le nombre de sessions par login
Refuser une nouvelle connexion SQL Server quand un login a déjà atteint sa limite de sessions, limite lue dans une table.
Un trigger de connexion au niveau serveur peut refuser une nouvelle connexion lorsqu'un login a déjà trop de sessions utilisateur. La limite de chaque login est lue dans une table ; les logins absents de la table ont une limite par défaut de 200 sessions.
Créer la table de configuration
USE AuditDb;
go
-- Create LIMITEDLOGINS Table
CREATE TABLE dbo.limitedLogins
(
limitedLoginsId INT IDENTITY(100,1) PRIMARY KEY
,loginName VARCHAR (50) NOT NULL
,loginID VARCHAR (50) NOT NULL
,num_ALLOWED TINYINT NOT NULL
,Date_Added Date NULL DEFAULT GETDATE()
);
-- Add values to the LimitedLogins table
INSERT INTO dbo.limitedLogins
(loginName , loginID, num_ALLOWED)
VALUES
('Example User 1', 'user1','200')
,('Example User 2', 'user2','200')
,('Example User 2', 'user3','200')
;
Créer le login utilisé par le trigger
USE master
go
CREATE LOGIN app_logon_trigger WITH PASSWORD = <HASHED_PASSWORD> HASHED;
GO
USE AuditDb;
go
CREATE USER app_logon_trigger FROM LOGIN app_logon_trigger WITH DEFAULT_SCHEMA = [dbo];
GO
GRANT SELECT ON dbo.LimitedLogins TO app_logon_trigger;
GO
USE master
go
GRANT VIEW SERVER STATE TO app_logon_trigger
GO
GRANT VIEW SERVER STATE TO [Public]
GO
GRANT VIEW SERVER STATE TO [Public]
GO
Créer et activer le trigger de connexion
USE master
GO
CREATE TRIGGER [connection_limit_trigger] ON ALL SERVER
WITH EXECUTE AS 'app_logon_trigger'
FOR LOGON
AS
BEGIN
DECLARE @maxAllowed int
DECLARE @CurrentConnection int
DECLARE @strMsg varchar(100)
SELECT @maxAllowed = NUM_ALLOWED
FROM AuditDb.dbo.LimitedLogins
WHERE loginID = ORIGINAL_LOGIN()
IF @maxAllowed IS NULL
SET @maxAllowed = 200 -- Default value
SELECT @CurrentConnection = COUNT(*)
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
AND original_login_name = ORIGINAL_LOGIN()
SET @strMsg = 'The max of connection has been reached( @CurrentConnection ' + CAST(@CurrentConnection AS varchar) + ' @maxAllowed '+ CAST (@maxAllowed AS varchar)
IF ( @maxAllowed < @CurrentConnection )
BEGIN
RAISERROR (@strMsg,16, 1 ); -- Second argument.
ROLLBACK
END
END;
GO
ENABLE TRIGGER [connection_limit_trigger] ON ALL SERVER
GO
Version simplifiée avec une limite unique
CREATE TRIGGER TR_logonMax
ON ALL SERVER
FOR LOGON
AS
BEGIN
if (
SELECT COUNT(*)
FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND original_login_name = ORIGINAL_LOGIN()) > 200
ROLLBACK;
END
SELECT *
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
Requêtes utiles
SELECT COUNT(*)
FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND original_login_name = ORIGINAL_LOGIN()
-- Scripting of existing triggers
SELECT
SSM.definition
FROM
sys.server_triggers AS ST JOIN
sys.server_sql_modules AS SSM
ON ST.object_id = SSM.object_id
Remarques
- Le trigger compte les sessions utilisateur du login dans
sys.dm_exec_sessions, puis annule la connexion si ce nombre dépasse la limite. Vérifier la limite exacte par un test avant de s'y fier. - Le trigger n'exempte pas les membres sysadmin : un administrateur qui dépasse la limite est aussi bloqué. Garder une connexion d'administration dédiée disponible (voir l'article sur la récupération après un trigger de connexion bloquant).
GRANT VIEW SERVER STATE TO [Public]est présent dans la source et accorde ce droit à tous les logins. Si possible, l'accorder uniquement au login du trigger.- Le mot de passe est un placeholder.
WITH PASSWORD = ... HASHEDattend une valeur de mot de passe déjà hachée, pas un mot de passe en clair. - La version « simple » ne lit pas la table des limites et applique une limite unique de 200 à tous les logins.
- Placeholders remplacés : la base s'appelle
AuditDb, le login du triggerapp_logon_trigger, et les utilisateurs d'exemple sont génériques.