SQL – Etienne ZINZINDOHOUE

DÉCLENCHEUR LOGON ET SES PROBLÈMES

Depuis la version 2005 de SQL SERVER, Microsoft a introduit le trigger (déclencheur) de connexion (LOGON). Chaque fois qu’un utilisateur ou une application se connecte à une instance SQL SERVER, l’évènement LOGON est levé et provoque ainsi l?activation du déclencheur de connexion.

Ce couple évènement-déclencheur de connexion peut être utilisé pour :

- mettre en place l’historique des accès à une instance SQL SERVER

- gérer autrement un compte particulier.

- imposer un nombre maximal de session que peut ouvrir un compte (au-delà de ce nombre, toute tentative de connexion à SQL SERVER par ce compte sera rejetée)

- etc

Dans cet article, nous allons voir comment utiliser le trigger LOGON pour :

- Auditer par exemple le compte sa SQL SERVER

- Limiter le nombre de sessions que peut ouvrir un compte

- Examiner enfin les problèmes courants à l’utilisation du trigger LOGON

Historique (Audit) de login à l’aide du trigger LOGON

Nous envisageons ici d’avoir l’historique de connexion du compte ’sa’ à une instance SQL SERVER.

Pour ce faire, nous allons créer dans la base master une table audit_loginsa qui va collecter l’historique des connexions du compte ’sa’.

• Création de la table de collecte

– Table de collecte (audit)

USE master

GO

CREATE TABLE [dbo].[audit_loginsa](

[loginName] [varchar](50) NULL,

[loginType] [varchar](50) NULL,

[loginTime] [datetime] NULL,

[hostUser] [varchar](50) NULL

) ON [PRIMARY]

GO

Colonne	Description	Commentaire
loginName	Nom du login	Dans notre exemple cette colonne a pour valeur ’sa’
loginType	Type d’authentification	De façon standard SQL SERVER permet deux types d’authentification: - Authentification Windows - Authentification SQL
loginTime	Date et heure de connexion
hostUser	Nom ou adresse IP du poste utilisateur	Nom ou l’IP de la machine à partir duquel la connexion est établie

• Création du trigger de connexion

– Creation du trigger : l’idée ici c’est de tracer le compte ’sa’

CREATE TRIGGER TR_audit_loginsa

ON ALL SERVER

FOR LOGON

AS

BEGIN

DECLARE @DataTrigger XML

SET @DataTrigger = EVENTDATA() ;

IF ORIGINAL_LOGIN()= ‘sa’

INSERT INTO master..audit_loginsa

SELECT @DataTrigger.value(‘(/EVENT_INSTANCE/LoginName)[1]’, ‘varchar(50)’),

@DataTrigger.value(‘(/EVENT_INSTANCE/LoginType)[1]’, ‘varchar(50)’),

@DataTrigger.value(‘(/EVENT_INSTANCE/PostTime)[1]’, ‘datetime’),

@DataTrigger.value(‘(/EVENT_INSTANCE/ClientHost)[1]’, ‘varchar(50)’) END

Quelques explications

–> TR_audit_loginsa : nom du trigger

–> ON ALL SERVER : la portée(l’étendue) du déclencheur

–> FOR LOGON : déclenchement à la connexion

–> DECLARE @DataTrigger XML :pour capturer les données XML renvoyées par EVENTDATA()

–> L’événement LOGON retourne le schéma des données XML suivant :

<EVENT_INSTANCE>

<EventType>event_type</EventType>

<PostTime>post_time</PostTime>

<SPID>spid</SPID>

<ServerName>server_name</ServerName>

<LoginName>login_name</LoginName>

<LoginType>login_type</LoginType>

<SID>sid</SID>

<ClientHost>client_host</ClientHost>

<IsPooled>is_pooled</IsPooled>

</EVENT_INSTANCE>

 

• Comment visualiser le trigger créé ?

Comment peut-on visualiser ce trigger qui a une portée (étendue) SERVER (ON ALL SERVER)

Le premier réflexe consiste à aller voir dans la base master, dans le dossier Déclencheur ; non ce n’est pas là qu’il faut le chercher ! Ce trigger est visible dans le dossier Déclencheurs sous Objets serveur

La requête suivante permet d’afficher les caractéristiques du trigger créé :

SELECT name,

parent_class_desc ‘Class’,

tr.Type,

tr_ev.Type_desc + ‘_’ + tr.Type_desc ‘Trigger_Type_Desc’,

is_ms_shipped,is_disabled

FROM master.sys.server_triggers tr

Inner Join master.sys.server_trigger_events tr_ev

on tr.object_id = tr_ev.object_id

Cette requête interroge bien sûr la base master.

Quelque soit le contexte de la base de données, (c’est-à-dire la base de donnée sur laquelle, vous vous positionnez dans SSMS), la requête ci-dessus, renvoie toujours le même résultat.

• Test de connexion du compte ’sa’

–> Essayons de se connecter avec le compte ’sa’ en utilisant SSMS

La compte ’sa’ est bien connecté au serveur

–> Déconnectons-nous et essayons de se connecter à nouveau à la même instance SQL SERVER, mais cette fois si avec un autre compte (authentification Windows ou un autre compte SQL). Dans mon cas j’utilise un autre compte SQL.

• Testons le trigger TR_audit_loginsa : Audit du login ’sa’

Nous allons maintenant auditer le compte de connexion ’sa’ à l’aide de la requête suivante :

– Audit du login ’sa’

SELECT * FROM master.dbo.audit_loginsa

ORDER BY loginTime DESC

On voit donc l’historique de connexion du compte ’sa’.

Et seul le compte ’sa’ est tracé.

Limiter le nombre de sessions ouvertes à l’aide du trigger LOGON

Le trigger LOGON permet également de limiter le nombre de session ouverte avec un compte donné.

Pour mettre ceci en évidence, créons d’abord un compte qu’on va nommer ’logon3Max’.

– Créer le compte ’logon3Max’

USE master;

GO

CREATE LOGIN logon3Max WITH PASSWORD = ‘pwdlogon3Max’

GRANT VIEW SERVER STATE TO logon3Max;

GO

Créons ensuite un trigger LOGON qui va limiter à trois le nombre de sessions que peut ouvrir ‘logon3Max’

– Limiter le nombre de connexion au server à 3 pour le compte ‘logon3Max’

CREATE TRIGGER TR_logon3Max

ON ALL SERVER

FOR LOGON

AS

BEGIN

IF ORIGINAL_LOGIN()= ‘logon3Max’ AND

(SELECT COUNT(*)

FROM sys.dm_exec_sessions

WHERE is_user_process = 1 AND original_login_name = ‘logon3Max’) > 3

ROLLBACK;

END

• Testons le trigger TR_logon3Max

– première session avec le compte ‘logon3Max’

SELECT COUNT(*) Nbconnexion

FROM sys.dm_exec_sessions

WHERE is_user_process = 1 AND original_login_name = ‘logon3Max’

– 2^ème session ouverte avec le compte ‘logon3Max’

– 3^ème session ouverte avec le compte ‘logon3Max’

– 4^ème tentative d’ouverture de session avec le compte ‘logon3Max’

A la quatrième ouverture de session, il est impossible à un utilisateur (ou une application) de se connecter à

l’instance en utilisant le compte ‘logon3Max’ !

Alors comment peut-on débloquer à nouveau le compte de connexion ‘logon3Max’ ?

Cette question nous amène à examiner quelques problèmes qu’on peut rencontrer lorsqu’on utilise le déclencheur LOGON

PROBLEMES COURANTS AVEC LOGON

Je me souviens de la première fois où j’ai rencontré un problème avec le trigger LOGON. Le problème se situait sur mon PC portable lorsque j’avais créé un déclencheur LOGIN avec une erreur de frappe. J’ai passé des heures à m’arracher les cheveux .

Le principal problème avec le trigger LOGON (ON ALL SERVER) est le blocage de la session SQL SERVER. Ce qui a pour conséquence, l’impossibilité aux applications et aux utilisateurs d’accéder aux bases de données. Ceci peut arriver si :

à Vous faites une erreur dans la requête de création du trigger LOGON ayant une portée SERVER (ON ALL SERVER)

à Vous faites une erreur dans lors de la mise à jour du trigger LOGON

VOICI UN CAS CONCRET DE PROBLEME AVEC LOGON

Voici notre requête initiale de création du trigger TR_logon3Max

CREATE TRIGGER TR_logon3Max

ON ALL SERVER

FOR LOGON

AS

BEGIN

IF ORIGINAL_LOGIN()= ‘logon3Max’ AND

(SELECT COUNT(*)

FROM sys.dm_exec_sessions

WHERE is_user_process = 1 AND original_login_name = ‘logon3Max’) > 3

ROLLBACK;

END

Supposons qu’il y a erreur de frappe sur de la vue sys.dm_exec_sessions

Comme ci-dessous

ALTER TRIGGER TR_logon3Max

ON ALL SERVER

FOR LOGON

AS

BEGIN

IF ORIGINAL_LOGIN()= ‘logon3Max’ AND

(SELECT COUNT(*)

FROM sys.dm_exec_session

WHERE is_user_process = 1 AND original_login_name = ‘logon3Max’) > 3

ROLLBACK;

END

A l’exécution de cette commande SSMS affiche « commande réussie » ! Et c’est à partir de là que tous les problèmes commencent !

Essayons maintenant de nous connecter à l’instance

Avec le compte ’sa’

Avec le compte ’logon3Max’

Avec le compte Windows

Impossible donc de se connecter à l’instance ! Si vous avez ce problème de connexion à SQL Server en production, les conséquences seront désastreuses : aucune application et aucun utilisateur ne peuvent accéder au serveur !!! Vous devez donc savoir très rapidement comment remédier à ce problème d?accès à SQL SERVER.

COMMENT REGLER CE PROBLEME ?

Il faut tout d’abord accéder à l?instance en « Connexion Administrateur dédié » (DAC)

Lancer ensuite la console DOS avec la commande suivante : sqlcmd -E -A -S NomInstance

Afficher ensuite la liste des triggers LOGON de l’instance en utilisant les vues sys.server_triggers et sys.server_events

SELECT name,

parent_class_desc ‘class’,

tr.Type,tr_ev.Type_desc + ‘_’ + tr.Type_desc ‘Trigger_Type_Desc’,

is_ms_shipped,is_disabled

FROM master.sys.server_triggers tr

Inner Join master.sys.server_trigger_events tr_ev

on tr.object_id = tr_ev.object_id

On voit bien les deux triggers qu’on avait créés : TR_audit_loginsa et TR_logon3Max

Une solution rapide consiste à désactiver TOUS LES TRIGGERS si on ne connaît pas le trigger qui est la cause du problème.

Disable Trigger All ON ALL Server;

Dans notre cas on sait que c’est le trigger TR_logon3Max qui est la source du problème

On va donc juste le désactiver

Disable Trigger TR_logon3Max ON ALL Server;

à La méthode violente consiste à faire un DROP du trigger qui pose problème

DROP TRIGGER TR_logon3Max ON ALL SERVER ;

Voyons si on peut maintenant se connecter à SQL SERVER

La connexion est à nouveau possible

à Une autre méthode plus fine consiste à éditer le trigger pour voir l’erreur et le modifier

SELECT definition

FROM master.sys.server_sql_modules sq

Inner Join master.sys.server_triggers tr on sq.object_id = tr.object_id