Le Common Language Runtime (CLR)

1. Le Transact-SQL ou le CLR ?

En introduisant le code CLR dans SQL Server, un nouveau problème se pose alors au développeur. Quand choisir de travailler avec un langage du Framework .NET et quand lui préférer le Transact-SQL ?

Contrairement à ce qui pourrait être perçu par un premier regard trop rapide sur SQL Server, le Transact-SQL n’est en aucun cas menacé par le code CLR. Au contraire, chacun possède ses avantages et ses inconvénients. En fonction du travail à réaliser, le choix se portera tout naturellement sur l’un ou l’autre.

Quelques-uns des principaux avantages de chaque langage sont énumérés ci-dessous :

Le Transact-SQL sera privilégié pour :

1. L’activation de la prise en charge du code CLR

Avant de commencer à écrire du code CLR pour SQL Server, il est nécessaire d’activer la prise en charge de ce type de code par SQL Server. Il ne s’agit pas d’une option ou d’un composant à installer mais d’une option de configuration à activer. En effet, pour des raisons de sécurité cette option est désactivée par défaut. Elle peut être activée depuis le Transact-SQL à l’aide de la procédure stockée sp_configure. Il s’agit alors de modifier la valeur de l’option de configuration CLR_ENABLED. Cette configuration via le Transact-SQL peut être réalisée depuis SQL Server Management Studio ou bien depuis SQLCMD dans une invite de commandes.

EXEC sp_configure 'CLR_ENABLED', '1'; 
RECONFIGURE WITH OVERRIDE; 

Affichage :

L’option de configuration CLR_ENABLED est passée de 0 à 1. Pour installer, exécutez l’instruction RECONFIGURE.

Il est également nécessaire d’accepter que l’assembly ne soit pas signée :

EXEC sp_configure 'SHOW_ADVANCED_OPTIONS', '1'; 
RECONFIGURE; 
 
EXEC sp_configure 'CLR_STRICT_SECURITY', '0'; 
RECONFIGURE WITH OVERRIDE; 

2. Le travail dans Visual Studio

Bien qu’il soit théoriquement possible d’écrire le code source depuis n’importe quel éditeur, même le bloc-notes, il est très fortement recommandé d’utiliser Visual Studio pour définir des éléments en code CLR gérés pour SQL Server. En effet, depuis Visual Studio, le déploiement sur l’instance SQL Server et le mappage CLR-Transact-SQL sont faits de façon automatique.

Après avoir lancé Visual Studio, il s’agit donc de créer un projet de type Projet de base de données SQL Server.

Ensuite, Visual Studio demande les informations de création du projet.

Les différents types de développement proposés par le CLR sont exposés et illustrés par un exemple. Les exemples sont indiqués en C#, mais il est tout à fait possible de réaliser le même travail...

1. L’installation de SMO

Depuis SQL Server 2017, plus aucun module PowerShell n’est installé de facto. Il faut l’installer en ouvrant une console PowerShell en tant qu’administrateur et en utilisant la commande suivante :

Install-Module -Name SqlServer -AllowClobber 

Il faut répondre "O" pour l’installation de NuGet et pour l’utilisation d’un référentiel non approuvé.

2. L’utilisation de SMO

La commande...

1. Un mot de passe pour les clients

Les clients peuvent maintenant accéder à leur compte à distance. Pour cela, un mot de passe est ajouté à leur compte. Pour des raisons de sécurité, il est bien évidemment hors de question de stocker le mot de passe : uniquement son empreinte (hash) est stockée. L’empreinte est calculée à l’aide de l’algorithme SHA-512. Une complexité de mot de passe est exigée : au moins 8 caractères, au moins une lettre minuscule, au moins une lettre majuscule et au moins un chiffre.

Créez un type personnalisé permettant de gérer ces mots de passe. Ajoutez une colonne mdpHash à la table Clients afin de pouvoir exécuter les instructions suivantes :

UPDATE clients SET mdpHash = 'SQL4ever!' WHERE noCli=1;  
UPDATE clients SET mdpHash = 'Ilove5QL' WHERE noCli=2;  
SELECT * FROM clients;  
  
SELECT mdpHash.Hash FROM Clients;  
   
SELECT  
       CASE mdpHash.Tester('SQL4ever!')  
             WHEN 1 THEN 'ok connecté'  
             ELSE 'erreur de connexion'  
       END Connexion FROM Clients WHERE...

1. Un mot de passe pour les clients

using System;  
using System.Data.SqlTypes;  
using System.IO;  
using System.Security.Cryptography;  
using System.Text;  
using System.Text.RegularExpressions;  
using Microsoft.SqlServer.Server;  
   
  
[Serializable]  
[SqlUserDefinedType(Format.UserDefined, MaxByteSize = 8000)]  
public struct MotDePasse: INullable, IBinarySerialize  
{  
    private bool _null;  
    private string _hash;  
   
    private static SHA512 SHA512 => SHA512.Create();  
   
    public override string ToString()  
    {  
        return _hash;  
    }  
   
    public bool IsNull => _null;  
      
    public static MotDePasse Null  
    {  
        get  
        {  
            MotDePasse h = new MotDePasse();  
            h._null = true;  
            return h;  
        }  
    }  
      ...