Tuer des sessions utilisateur sans débordement

Lors de plusieurs audits chez différents clients d’un même éditeur de logiciel, nous avons découvert que la manière résoudre certains problèmes, consistait tout simplement à « tuer » les sessions des utilisateurs plutôt que de chercher à résoudre les problèmes sans violence !

La mauvaise pratique qui consiste à utiliser la commande KILL pour tuer une session bloquée dans SQL Server est une manie dommageable que certains éditeurs de logiciels pratiquent de manière systématique dès qu’il ne comprennent pas ce qui se passe dans le SGBDR…
Mais, économie oblige, de plus en plus de clients mutualisent leurs bases sur un seul et même serveur, quitte à retrouver différents logiciels dans une seule et même instance de SQL Server ce qui ne pose en théorie aucun problème, et même en pratique, pourvu qu’on y applique les bonnes manières… SQL Server sait encaisser des dizaines de bases et des dizaines de To sans broncher. Le tout est une question de bonne administration ainsi que de dimensionnement des ressources.

Chez un des clients que nous avons traité (en l’occurrence Rhenus France), le challenge a consisté à réunir une trentaine de serveurs SQL en 2 machines de production. L’une pour l’OLTP (les bases relationnelles, applicatives des différents éditeurs) et l’autre pour le décisionnel fait maison (OLAP).
Aujourd’hui les trois quart du chemin sont fait et l’économie réalisée, ainsi que le fonctionnel d’infrastructure s’avère plus facile à administrer, moins couteux, et plus fiable (solution de PCA/PRA AlwaysOn en place).

Un des problèmes auquel nous avons été confronté vient d’un éditeur (Hardis pour ne pas le nommer) et de son logiciel Reflex « web ». Hardis fait partit de ses éditeurs qui ont commencé à livrer des solution du temps de bases hiérarchique, puis monté dans les bases relationnelles (Oracle à l’origine) en tentant de ne pas changer grand chose à leur manière de faire, c’est à dire sans aucun code centralisé (pas de procédure stockées en particulier…) et donc à coup de curseurs ouverts dans touts les sens et de traitement « client » itératifs et poussifs…
Les clients ne voulant pas payer les sommes exorbitantes des licences Oracle, ni subir les foudres des audits intrusifs d’Oracle, et encore moins d’éventuels redressement de prix liées aux options payantes d’Oracle qu’ils auraient malencontreusement et involontairement activées, se tournent de plus en plus vers des solutions alternatives et notamment vers SQL Server capable de supporter des charges aussi lourde qu’Oracle, voir mieux (meilleure fiabilité et facilité d’administration) avec des performances accrues (notamment grâce aux index columstore et aux tables « in memory »).
Le problème est que certains éditeurs font un portage sans régler certains paramètres ni modifier certaines pratiques, toutes choses rendues nécessaires du fait de la différence de comportement des deux moteurs. Et cette absence de réaction pénalise les nombreux clients de ces éditeurs de logiciels.

Chez Hardis, entres autres, on a donc décidé de tuer les sessions bloquées plutôt que de tenter de comprendre les blocages et d’y remédier.

Le problème est que la commande KILL, utilisé pour tuer un process, nécessite ordinairement des pouvoirs très élevés (sysadmin / CONTROL SERVER) et donc peut tuer n’impoter quel processus, y compris ceux des éditeurs concurrent tournant sur la même machine ! Impensable, bien évidemment en terme de sécurité.

Il nous a donc fallut trouver une solution pour :

  • ne permettre que la visualisation des processus Hardis aux équipes Hardis
  • ne permettre de tuer une session que si elle est identifiée en tant que session d’un logiciel Hardis d’une base de données Reflex.

Une procédure pour lister les sessions Hardis et tuer les sessions Hardis

Nous avons donc écrit une procédure qui liste les sessions Hardis avec l’ensemble des paramètres nécessaires à l’utilisation de cette fonction, y compris son infrastructure de sécurité.
Pour cela la procédure a été écrite dans le contexte de la base msdb dont le niveau de sécurité est « trustworthy », c’est à dire « digne de confiance », ce qui descend son niveau de sécurité afin de permettre l’exécution de module de code faisant référence à d’autres bases….
En outre, pour isoler cette procédure nous avons décidé de créer un schéma SQL spécifique de nom _HARDIS_KILL (notez bien le premier caractère qui est un blanc souligné (underscore) destiné, dans ma nomenclature particulière à indiquer que ce schéma contient des objets d’administration spécifique.

La procédure, de nom P_HARDIS_DIAGNOSE_SESSION possède deux paramètres spécifiques :

  • @ACTION de type CHAR(4) qui doit prendre la valeur LIST ou KILL (toute autre valeur annule l’exécution)
  • @SESSION_ID de type SMALLINT qui identifie le n° de session (session_id) à tuer

L’exécution de la procédure doit se faire comme suit :

EXEC _HARDIS_KILL.P_HARDIS_DIAGNOSE_SESSION 'LIST', NULL;
… pour lister les sessions Hardis Reflex.

EXEC _HARDIS_KILL.P_HARDIS_DIAGNOSE_SESSION 'KILL', 168;
… pour tuerune session Hardis Reflex encore vivante.

Deux impératifs

  • Le nom des bases doit commencer par « HARDIS_ »
  • Le compte de connexion doit être « reflex »

Sans cela la procédure ne remonte aucune session et ne permet en aucun cas de tuer une quelconque session.

USE msdb;
GO

CREATE SCHEMA _HARDIS_KILL;
GO

CREATE OR ALTER PROCEDURE _HARDIS_KILL.P_HARDIS_DIAGNOSE_SESSION 
   @ACTION       CHAR(4), 
   @SESSION_ID   SMALLINT
WITH EXECUTE AS OWNER, ENCRYPTION
AS
/******************************************************************************
* PROCEDURE msdb._HARDIS_KILL.P_HARDIS_DIAGNOSE_SESSION            2020-05-13 *
* VERSION 1.0                                                                 *
*******************************************************************************
* Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *
* Architecte de données :  expertise, audit, conseil, formation, modélisation *
* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *
* blog: http://blog.developpez.com/sqlpro  site: http://sqlpro.developpez.com *
*******************************************************************************
* Liste les sessions HARDIS REFLEX ou tue une session HARDIS REFLEX           *
*******************************************************************************
* PARAMÈTRES EN ENTRÉE :                                                      *
*   @ACTION CHAR(4)                                                           *
*       Nature de l''action à entreprendre. Ne peut être que LIST ou KILL.    *
*   @SESSION_ID                                                               *
*       N° de la session à tuer                                               *
* PARAMÈTRE DE SORTIE                                                         *
*    (aucun)                                                                  *
* RETOUR :                                                                    *
*    si paramètre @ACTION = 'LIST', table des sessions HARDIS reflex          * 
*    si paramètre @ACTION = 'KILL', message d'exécution ou d'erreur           *
*******************************************************************************
* EXEMPLES D''UTILISATION :                                                   *
*    USE msdb;                                                                *
*    EXEC _HARDIS_KILL.P_HARDIS_DIAGNOSE_SESSION 'LIST', NULL;                *
*       fournit la liste des sessions HARDIS Refelx avec toutes               *
*       les informations nécessaires                                          *
*    EXEC _HARDIS_KILL.P_HARDIS_DIAGNOSE_SESSION 'KILL', 168;                 *
*       tue la session considérée si elle appartient aux sessions HARDIS      *
*       Reflex                                                                *
*******************************************************************************
* NOTA : En cas de KILL, la procédure vérifie que la session existe toujours  *
*        et qu''elle appartient bien à un processus HARDIS reflex.            *
*******************************************************************************
*   ©   Tous droits réservés : frédéric Brouard / SQL SPOT - 2020-05-13   ©   *
******************************************************************************/
IF @ACTION NOT IN ('LIST', 'KILL') 
BEGIN
   RAISERROR('Unknown value for "action" parameter (@ACTION = %s). must be "LIST" or "KILL"', 16 , 1, @ACTION);
   RETURN;
END;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
-- renvoie la liste des session HARDIS
IF @ACTION = 'LIST'
BEGIN
   SELECT c.*, s.*,
          DB_NAME(s.database_id) AS CONTEXT_DATABASE,
          lq.text AS LAST_QUERY_EXECUTED, q.text AS QUERY_RUNING,
          r.*
   FROM   sys.dm_exec_connections AS c
          JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id
          LEFT OUTER JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id
          OUTER APPLY sys.dm_exec_sql_text(sql_handle) AS q
          OUTER APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS lq
   WHERE  c.session_id <> @@SPID
     AND  s.login_name = 'reflex'
     AND  DB_NAME(s.database_id) LIKE 'HARDIS?_%' ESCAPE '?'
   ORDER  BY blocking_session_id DESC, COALESCE(r.session_id, 66666);
END;

-- demande un KILL
IF @ACTION = 'KILL'
BEGIN
   --> vérifier que la session existe bien
   IF NOT EXISTS(SELECT * 
                 FROM   sys.dm_exec_sessions 
                 WHERE  session_id = @SESSION_ID)
   BEGIN
      RAISERROR('Session id %d is not alive. You cannot kill this session.', 
                16 , 1, @SESSION_ID);
      RETURN;
   END;
   -- vérifier que la session à tuer a bien une connexion "reflex"
   IF EXISTS(SELECT * 
             FROM   sys.dm_exec_sessions 
             WHERE  session_id = @SESSION_ID 
               AND  login_name <> 'reflex')
   BEGIN
      RAISERROR('Session id %d is logged under another login account than reflex. You are not authorized to kill this session.', 
                16 , 1, @SESSION_ID);
      RETURN;
   END;
   -- vérifier que la session à tuer porte bien sur une base HARDIS
   IF EXISTS(SELECT * 
             FROM   sys.dm_exec_sessions 
             WHERE  session_id = @SESSION_ID 
               AND  login_name = 'reflex' 
               AND  DB_NAME(database_id) NOT LIKE 'HARDIS?_%' ESCAPE '?')
   BEGIN
      RAISERROR('Session id %d is executing queries under a non HARDIS database. You are not authorized to kill this session.', 
                16 , 1, @SESSION_ID);
      RETURN;
   END;
   --> tout va bien, on tue la session
   DECLARE @KILL_COMMAND VARCHAR(32) = 'KILL ' + CAST(@SESSION_ID AS VARCHAR(6)) + ';'
   BEGIN TRY
      EXEC (@KILL_COMMAND);
   END TRY
   BEGIN CATCH
      DECLARE @ERROR_MSG NVARCHAR(1048)
      SET @ERROR_MSG = 'HARDIS_KILL (ERREUR) : ' + ERROR_MESSAGE();
      RAISERROR(@ERROR_MSG, 16, 1) WITH LOG;
   END CATCH
   --> on ajoute cet information au journal d'événement
   DECLARE @LOGIN sysname = SYSTEM_USER
   RAISERROR('HARDIS_KILL (EXECUTE) : Session %d tué via procédure sp__HARDIS_DIAGNOSE_SESSION par le compte de connexion %s.', 
             10, 1, @SESSION_ID, @LOGIN) WITH LOG;
END
GO

Sécurité

Il faut maintenant donner les privilèges aux entités de sécurité Hardis pour pouvoir l’exécuter, ce qui est fait à l’aide du script suivant :

USE master;
GO

CREATE LOGIN CNX_REFLEX_ADMIN 
   WITH PASSWORD = N'H@rd1s-Reflex', 
        DEFAULT_DATABASE=[master], 
        CHECK_EXPIRATION=OFF, 
        CHECK_POLICY=OFF;
GO

USE msdb
GO

CREATE USER USR_REFLEX_ADMIN 
   FOR LOGIN CNX_REFLEX_ADMIN;
GO

GRANT EXECUTE ON _HARDIS_KILL.P_HARDIS_DIAGNOSE_SESSION 
   TO USR_REFLEX_ADMIN
GO

Nous avons préféré créer un compte de connexion spécifique afin d’éviter tout confusion.

Le code de la procédure et son environnement d’exécution est disponible ici HARDIS PROC KILL

Merci à Arian papillon de Datafly qui m’a aider à trouver cette solution !


Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
* Le site sur les SGBD relationnels et le SQL : https://sqlpro.developpez.com *
* le blog SQL, SQL Server, SGBDR... sur : https://blog.developpez.com/sqlpro/ *
* Expert Microsoft SQL Server, MVP (Most valuable Professional) depuis 14 ans *
* Entreprise SQL SPOT : modélisation, conseil, audit, optimisation, formation *
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

Ce contenu a été publié dans Procédure de maintenance, Procédures MS SQL Server, avec comme mot(s)-clé(s) , , , , . Vous pouvez le mettre en favoris avec ce permalien.