Déclencheurs de connexion

  • Article

S’applique à :SQL ServerAzure SQL Managed Instance

Les déclencheurs de connexion lancent des procédures stockées en réponse à un événement LOGON. Cet événement est déclenché lorsqu'une session utilisateur est établie avec une instance de SQL Server. Les déclencheurs de connexion sont activés au terme de la phase d'authentification de connexion, mais avant l'établissement de la session utilisateur. Par conséquent, tous les messages provenant du déclencheur qui parviendraient normalement à l'utilisateur, tels que les messages d'erreur et les messages de l'instruction PRINT sont redirigés vers le journal des erreurs du serveur SQL. Les déclencheurs de connexion ne sont pas activés si l'authentification échoue.

Vous pouvez utiliser les déclencheurs de connexion pour auditer et contrôler les sessions du serveur, par exemple en suivant l'activité de connexion, en restreignant les connexions à SQL Server ou en limitant le nombre de sessions pour une connexion spécifique. Par exemple, dans le code suivant, le déclencheur de connexion refuse les tentatives de connexion au serveur SQL initiées par le login login_test s'il y a déjà trois sessions utilisateur créées par ce login.

USE master;
GO

CREATE LOGIN login_test
WITH PASSWORD = N'3KHJ6dhx(0xVYsdf' MUST_CHANGE,
CHECK_EXPIRATION = ON;
GO

GRANT VIEW SERVER STATE TO login_test;
GO

CREATE TRIGGER connection_limit_trigger ON ALL SERVER
WITH EXECUTE AS N'login_test'
FOR LOGON AS BEGIN
    IF ORIGINAL_LOGIN() = N'login_test'
    AND (
        SELECT COUNT(*)
        FROM sys.dm_exec_sessions
        WHERE is_user_process = 1
            AND original_login_name = N'login_test') > 3
    ROLLBACK;
END;

L'événement LOGON correspond à l'événement SQL TraceAUDIT_LOGIN, qui peut être utilisé dans les notifications d'événements. La différence principale entre les déclencheurs et les notifications d'événement est que le lancement des déclencheurs est synchronisé avec celui des événements alors que les notifications d'événement sont asynchrones. Cela signifie, entre autre, que si vous souhaitez arrêter l'établissement d'une session, vous devez faire appel à un déclencheur de connexion. Une notification d'événement sur un événement AUDIT_LOGIN ne peut pas être utilisée à cette fin.

Spécifier les premier et dernier déclencheurs

Il est possible de définir plusieurs déclencheurs sur l'événement LOGON. Ces déclencheurs peuvent être désignés comme le premier ou dernier déclencheur à être activé sur un événement à l’aide de la procédure stockée système sp_settriggerorder . SQL Server ne garantit pas l'ordre d'exécution des déclencheurs restants.

Gérer les transactions

Avant que SQL Server ne déclenche un trigger de connexion, SQL Server crée une transaction implicite qui est indépendante de toute transaction utilisateur. Par conséquent, à l'exécution du premier déclencheur de connexion, le nombre de transactions est 1. Après l'exécution de tous les déclencheurs de connexion, la transaction est validée. En ce qui concerne les autres types de déclencheurs, SQL Server retourne une erreur si l'exécution d'un déclencheur de connexion se termine avec un nombre de transactions de 0. L'instruction ROLLBACK TRANSACTION rétablit le nombre de transactions à 0, même si l'instruction est émise au sein d'une transaction imbriquée. COMMIT TRANSACTION peut décrémenter le nombre de transactions à 0. Il est donc déconseillé d'insérer des instructions COMMIT TRANSACTION dans les déclencheurs de connexion.

Tenez compte de ce qui suit lorsque vous utilisez une instruction ROLLBACK TRANSACTION dans des déclencheurs de connexion :

  • Toutes les modifications de données effectuées jusqu'à ROLLBACK TRANSACTION sont annulées. Ces modifications comprennent les changements apportés par le déclencheur actuel et par les déclencheurs précédents qui se sont exécutés sur le même événement. Les déclencheurs restants pour l'événement spécifique ne sont pas exécutés.

  • Le déclencheur actif continue d'exécuter les instructions restantes qui suivent l'instruction ROLLBACK. Si l'une de ces instructions modifie les données, les modifications ne sont pas restaurés.

Une session utilisateur n'est pas établie si l'une des conditions suivantes se produit pendant l'exécution d'un déclencheur sur un événement LOGON:

  • La transaction implicite d'origine est restaurée ou échoue.
  • Une erreur dont la gravité est supérieure à 20 est levée dans le corps du déclencheur.

Désactiver un déclencheur de connexion

Un déclencheur de connexion peut empêcher les connexions au Moteur de base de données pour tous les utilisateurs, notamment les membres du rôle serveur fixe sysadmin . Quand un déclencheur de connexion empêche les connexions, les membres du rôle serveur fixe sysadmin peuvent se connecter à l’aide de la connexion administrateur dédiée, ou en démarrant le Moteur de base de données en mode de configuration minimale (-f). Pour plus d’informations, consultez Options de démarrage du service moteur de base de données.

Contenu connexe