Partage via

Tutoriel : Créer et utiliser des index sur des colonnes prenant en charge les enclaves à l’aide d’un chiffrement aléatoire

  • Article

S’applique à : SQL Server 2019 (15.x) et versions ultérieures - Windows uniquement Azure SQL Database

Ce didacticiel vous apprend à créer et utiliser des index sur des colonnes prenant en charge les enclaves à l’aide de la prise en charge dans le chiffrement aléatoire Always Encrypted avec enclaves sécurisées. Il vous montre comment :

  • Comment créer un index lorsque vous avez accès aux clés (la clé principale de colonne et la clé de chiffrement de colonne) protection de la colonne.
  • Comment créer un index lorsque vous n’avez pas accès aux clés de protection de la colonne.

Prérequis

Téléchargez la dernière version de SQL Server Management Studio (SSMS).

Vérifiez que vous avez effectué un des tutoriels Bien démarrer dans l’utilisation d’Always Encrypted avec enclaves sécurisées avant de suivre les étapes ci-dessous de ce tutoriel.

Etape 1 : activer la récupération de base de données accélérée (ADR) dans votre base de données

Remarque

Cette étape s’applique uniquement à SQL Server. Si vous utilisez la base de données Azure SQL, ignorez cette étape. La récupération de base de données accélérée est automatiquement activée dans la base de données Azure SQL et sa désactivation n’est pas prise en charge.

Microsoft recommande fortement d'activer ADR dans votre base de données avant de créer le premier index sur une colonne prenant en charge les enclaves à l’aide d’un chiffrement aléatoire. Consultez la section Récupération de base de données dans Always Encrypted avec enclaves sécurisées.

  1. Fermez toutes les instances de SSMS utilisées dans le didacticiel précédent. La fermeture du SSMS permet de fermer les connexions de base de données que vous avez ouvert, ce qui est nécessaire pour activer ADR.

  2. Ouvrez une nouvelle instance de SSMS et connectez-vous à votre instance de SQL Server en tant que sysadmin sans Always Encrypted activé pour la connexion de base de données.

    1. Démarrer SSMS.
    2. Dans la boîte de dialogue Se connecter au serveur, spécifiez le nom de votre serveur, sélectionnez une méthode d’authentification et spécifiez vos informations d’identification.
    3. Sélectionnez Options >>, puis l’onglet Always Encrypted.
    4. Assurez-vous que la case Activer Always Encrypted (chiffrement de colonne) n’est pas cochée.
    5. Sélectionnez Se connecter.

  3. Ouvrez une nouvelle fenêtre de requête et exécutez l’instruction ci-dessous pour activer ADR.

    ALTER DATABASE ContosoHR SET ACCELERATED_DATABASE_RECOVERY = ON;

Etape 2 : créer et tester un index sans séparation des rôles

Dans cette étape, vous allez créer et tester un index sur une colonne chiffrée. Vous allez agir en tant qu’utilisateur unique qui assume les rôles de DBA, qui gère la base de données et de propriétaire des données qui a accès aux clés, en protection des données.

  1. Ouvrez une nouvelle instance SSMS et connectez-vous à votre instance SQL Server avec Always Encrypted activé pour la connexion de base de données.

    1. Démarrez une nouvelle instance SSMS.
    2. Dans la boîte de dialogue Se connecter au serveur, spécifiez le nom de votre serveur, sélectionnez une méthode d’authentification et spécifiez vos informations d’identification.
    3. Sélectionnez Options >>, puis l’onglet Always Encrypted.
    4. Cochez les cases Activer Always Encrypted (chiffrement de colonne) et Activer les enclaves sécurisées
    5. Si vous utilisez l’attestation pour votre base de données, sélectionnez une valeur d’Enclave Attestation Protocol qui représente votre service d’attestation (Service Guardian hôte ou Microsoft Azure Attestation) et remplissez l’URL d’attestation d’enclave. Sinon, sélectionnez Aucune.
    6. Sélectionnez Se connecter.
    7. Si vous êtes invité à activer la paramétrisation pour les requêtes Always Encrypted, sélectionnez Activer.

  2. Si vous n’êtes pas invité à activer le Paramétrage pour Always Encrypted, vérifiez qu’il est activé.

    1. Sélectionnez Outils dans le menu principal de SSMS.
    2. Sélectionnez Options... .
    3. Accédez à Exécution de la requête>SQL Server>Avancé.
    4. Vérifiez que la case Activer Paramétrage pour Always Encrypted est cochée.
    5. Cliquez sur OK.

  3. Ouvrez une fenêtre de requête et exécutez les instructions ci-dessous pour chiffrer la colonne LastName dans la table Employés. Vous allez créer et utiliser un index sur cette colonne dans les étapes suivantes.

    ALTER TABLE [HR].[Employees]
ALTER COLUMN [LastName] [nvarchar](50) COLLATE Latin1_General_BIN2 
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL;
GO   
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

  4. Créez un index sur la colonne LastName. Étant donné que vous êtes connecté à la base de données avec Always Encrypted activé, le pilote du client à l’intérieur de SSMS fournit en toute transparence CEK1 (la clé de chiffrement de colonne protégeant la colonne LastName) pour l’enclave, nécessaire pour créer l’index.

    CREATE INDEX IX_LastName ON [HR].[Employees] ([LastName])
INCLUDE ([EmployeeID], [FirstName], [SSN], [Salary]);
GO

  5. Exécutez une requête enrichie sur la colonne LastName et vérifiez que SQL Server utilise l’index lors de l’exécution de la requête.

    1. Dans la même fenêtre de requête ou dans une nouvelle fenêtre de requête, assurez-vous que le bouton de la barre d’outils Inclure les statistiques des requêtes actives est activé.

    2. Exécutez la requête ci-dessous.

      DECLARE @LastNamePrefix NVARCHAR(50) = 'Aber%';
SELECT * FROM [HR].[Employees] WHERE [LastName] LIKE @LastNamePrefix;
GO

    3. Dans l’onglet Live Query Statistics, observez que la requête utilise l’index.

Etape 3 : créer un index avec séparation des rôles

Dans cette étape, vous allez créer un index sur une colonne chiffrée, prétendant être deux utilisateurs différents. Un utilisateur est un DBA, qui doit créer un index, mais n’a pas accès aux clés. L’autre utilisateur est un propriétaire de données, qui a accès aux clés.

  1. À l’aide de l’instance SSMS sans Always Encrypted activé, exécutez l’instruction ci-dessous pour déposer l’index sur la colonne LastName.

    DROP INDEX IX_LastName ON [HR].[Employees]; 
GO

  2. En agissant en tant que propriétaire de données (ou une application qui a accès aux clés), vous remplissez le cache à l’intérieur de l’enclave avec CEK1.

    Remarque

    Sauf si vous avez redémarré votre instance SQL Server après Etape 2 : créer et tester un index sans séparation des rôles, cette étape est redondante car CEK1 est déjà présent dans le cache. Nous l’avons ajouté pour démontrer comment un propriétaire de données peut fournir une clé à l’enclave, si elle n’est pas déjà présente dans l’enclave.

    1. Dans l’instance de SSMS avec Always Encrypted activé, exécutez les instructions suivantes dans une fenêtre de requête. L’instruction envoie toutes les clés de chiffrement de colonne prenant en charge les enclaves à l’enclave. Consultez sp_enclave_send_keys pour plus d’informations.

      EXEC sp_enclave_send_keys;
GO

    2. Comme alternative à l’exécution de la procédure stockée ci-dessus, vous pouvez exécuter une requête DML qui utilise l’enclave contre la colonne LastName. Cela remplira l’enclave uniquement avec CEK1.

      DECLARE @LastNamePrefix NVARCHAR(50) = 'Aber%';
SELECT * FROM [HR].[Employees] WHERE [LastName] LIKE @LastNamePrefix;
GO

  3. Agissant en tant que DBA, créez l’index.

    1. Dans l’instance de SSMS sans Always Encrypted activé, exécutez les instructions suivantes dans une fenêtre de requête.

      CREATE INDEX IX_LastName ON [HR].[Employees] ([LastName])
INCLUDE ([EmployeeID], [FirstName], [SSN], [Salary]);
GO

  4. En tant que propriétaire des données, exécutez une requête enrichie sur la colonne LastName et vérifiez que SQL Server utilise l’index lors de l’exécution de la requête.

    1. Dans l’instance de SSMS avec Always Encrypted activé, sélectionnez une fenêtre de requête existante ou ouvrez une nouvelle fenêtre de requête et assurez-vous que le bouton de la barre d’outils Inclure les statistiques des requêtes actives est activé.

    2. Exécutez la requête ci-dessous.

      DECLARE @LastNamePrefix NVARCHAR(50) = 'Aber%';
SELECT * FROM [HR].[Employees] WHERE [LastName] LIKE @LastNamePrefix;
GO

    3. Dans Live Query Statistics, observez que la requête utilise l’index.

Étapes suivantes

Voir aussi