Tutoriel : bien démarrer avec Always Encrypted

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance

Ce tutoriel vous apprend à bien démarrer avec Always Encrypted. Il vous montre comment :

• Comment chiffrer les colonnes sélectionnées dans votre base de données.
• Comment interroger des colonnes chiffrées.

Notes

Si vous cherchez des informations sur Always Encrypted avec les enclaves sécurisées, consultez plutôt les tutoriels suivants :

• Bien démarrer dans l’utilisation d’Always Encrypted avec enclaves sécurisées
• Tutoriel : Bien démarrer dans l’utilisation d’Always Encrypted avec enclaves sécurisées dans SQL Server

Prérequis

Pour ce didacticiel, vous avez besoin des éléments suivants :

• Une base de données vide dans la base de données Azure SQL, Azure SQL Managed Instance ou SQL Server. Les instructions ci-dessous supposent que le nom de la base de données est ContosoHR. Vous devez être propriétaire de la base de données (membre du rôle db_owner ). Pour plus d’informations sur la création d’une base de données, consultez Démarrage rapide : Créer une base de données unique – base de données Azure SQL ou Créer une base de données dans SQL Server.
• Facultatif, mais recommandé, en particulier si votre base de données se trouve dans Azure : un coffre de clés dans Azure Key Vault. Pour plus d’informations sur la création d’un coffre de clés, consultez Démarrage rapide : créer un coffre de clés avec le portail Azure.
  • Si votre coffre de clés utilise le modèle d’autorisations de stratégie d’accès, vérifiez que vous disposez des autorisations de clé suivantes dans le coffre de clés : get, list, create, unwrap key, wrap key, verify, sign. Consultez Attribuer une stratégie d’accès Key Vault.
  • Si vous utilisez le modèle d’autorisation RBAC (contrôle d’accès en fonction du rôle) Azure, assurez-vous que vous êtes membre du rôle Agent de chiffrement Key Vault pour votre coffre de clés. Consultez Donner accès aux clés, certificats et secrets du coffre de clés avec un contrôle d’accès en fonction du rôle Azure.
• La dernière version de SQL Server Management Studio (SSMS) ou encore celle des modules SqlServer et Az PowerShell. Le module Az PowerShell est obligatoire uniquement si vous utilisez Azure Key Vault.

Étape 1 : créer et remplir le schéma de la base de données

Dans cette étape, vous allez créer le schéma RH et la table Employees. Ensuite, vous remplirez la table en y ajoutant des données.

SSMS

1. Connectez-vous à votre base de données. Pour obtenir des instructions sur la façon de se connecter à une base de données à partir de SSMS, consultez Démarrage rapide : Se connecter et interroger une base de données Azure SQL ou Azure SQL Managed Instance à l’aide de SQL Server Management Studio (SSMS) ou démarrage rapide : Se connecter et interroger une instance SQL Server à l’aide de SQL Server Management Studio (SSMS).

2. Ouvrez une nouvelle fenêtre Requête pour la base de données ContosoHR.

3. Collez et exécutez les instructions ci-dessous pour créer une table nommée Employees.

   SQL

   CREATE SCHEMA [HR];
   GO
   
   CREATE TABLE [HR].[Employees]
   (
       [EmployeeID] [int] IDENTITY(1,1) NOT NULL
       , [SSN] [char](11) NOT NULL
       , [FirstName] [nvarchar](50) NOT NULL
       , [LastName] [nvarchar](50) NOT NULL
       , [Salary] [money] NOT NULL
   ) ON [PRIMARY];
   

4. Collez et exécutez les instructions ci-dessous pour ajouter quelques enregistrements employés à la table Employees.

   SQL

   INSERT INTO [HR].[Employees]
   (
       [SSN]
       , [FirstName]
       , [LastName]
       , [Salary]
   )
   VALUES
   (
       '795-73-9838'
       , N'Catherine'
       , N'Abel'
       , $31692
   );
   
   INSERT INTO [HR].[Employees]
   (
       [SSN]
       , [FirstName]
       , [LastName]
       , [Salary]
   )
   VALUES
   (
       '990-00-6818'
       , N'Kim'
       , N'Abercrombie'
       , $55415
   );
   

PowerShell

Dans une session PowerShell, exécutez les commandes suivantes. Veillez à mettre à jour le chaîne de connexion en indiquant l’adresse de votre serveur et des paramètres d’authentification valides pour votre base de données.

PowerShell

Import-Module "SqlServer"

# Set your database connection string
$connectionString = "Server = myServerAddress; Database = ContosoHR; ..."

# Create a new table, named Employees.
$query = @'
    CREATE SCHEMA [HR];
    GO
    
    CREATE TABLE [HR].[Employees]
    (
        [EmployeeID] [int] IDENTITY(1,1) NOT NULL
        , [SSN] [char](11) NOT NULL
        , [FirstName] [nvarchar](50) NOT NULL
        , [LastName] [nvarchar](50) NOT NULL
        , [Salary] [money] NOT NULL
    ) ON [PRIMARY];
'@
Invoke-SqlCmd -ConnectionString $connectionString -Query $query

# Add a few rows to the Employees table.
$query = @'
    INSERT INTO [HR].[Employees]
    (
        [SSN]
        , [FirstName]
        , [LastName]
        , [Salary]
    )
    VALUES
    (
        '795-73-9838'
        , N'Catherine'
        , N'Abel'
        , $31692
    );

    INSERT INTO [HR].[Employees]
    (
        [SSN]
        , [FirstName]
        , [LastName]
        , [Salary]
    )
    VALUES
    (
        '990-00-6818'
        , N'Kim'
        , N'Abercrombie'
        , $55415
    );
'@
Invoke-SqlCmd -ConnectionString $connectionString -Query $query

Étape 2 : chiffrer les colonnes

Dans cette étape, vous fournirez une clé principale de colonne et une clé de chiffrement de colonne pour Always Encrypted. Ensuite, vous chiffrerez les colonnes SSN et Salary dans la table Employees.

SSMS

SSMS propose un assistant qui vous aide à configurer facilement Always Encrypted en définissant une clé principale de colonne, une clé de chiffrement de colonne, et en chiffrant les colonnes sélectionnées.

1. Dans l’Explorateur d’objets, développez Bases de données>ContosoHR>Tables.

2. Faites un clic droit sur la table Employees et sélectionnez Chiffrer les colonnes pour ouvrir l’assistant Always Encrypted.

   

3. Sélectionnez Suivant sur la page d’introduction de l’Assistant.

4. Sur la page de sélection des colonnes.

   1. Sélectionnez les colonnes SSN et Salary. Choisissez le chiffrement déterministe pour la colonne SSN et le chiffrement aléatoire pour la colonne Salary. Le chiffrement déterministe prend en charge les requêtes, telles que les recherches de points qui impliquent des comparaisons d’égalité sur des colonnes chiffrées. Le chiffrement aléatoire ne prend pas en charge les calculs sur les colonnes chiffrées.
   2. Conservez CEK-Auto1 (Nouveau) comme clé de chiffrement de colonne pour les colonnes SSN et Salary. Cette clé n’existe pas encore et sera générée par l’Assistant.
   3. Cliquez sur Suivant.

   

5. Dans la page Configuration de la clé principale, configurez une nouvelle clé principale de colonne qui sera générée par l’Assistant. Tout d’abord, vous devez sélectionner l’emplacement où vous souhaitez stocker votre clé principale de colonne. L’Assistant prend en charge deux types de magasins de clés :

   • Azure Key Vault : recommandé si votre base de données se trouve dans Azure
   • Magasin de certificats Windows

   En général, Azure Key Vault est l’option recommandée, surtout si votre base de données se trouve dans Azure.

   • Pour utiliser Azure Key Vault :

     1. Sélectionnez Azure Key Vault.
     2. Sélectionnez Se connecter et poursuivez la connexion à Azure.
     3. Une fois connecté, la page affiche la liste des abonnements et des coffres de clés auxquels vous avez accès. Sélectionnez un abonnement Azure contenant le coffre de clés que vous souhaitez utiliser.
     4. Sélectionnez votre coffre de clés.
     5. Cliquez sur Suivant.

     

   • Pour utiliser le magasin de certificats de Windows :

     1. Sélectionnez le magasin de certificats de Windows.

     2. Conservez la sélection par défaut de l’utilisateur actuel : cette option indique à l’assistant de générer un certificat (votre nouvelle clé principale de colonne) dans le magasin de l’utilisateur actuel.

        

     3. Cliquez sur Suivant.

6. Sur la page Paramètres de chiffrement local, aucune configuration supplémentaire n’est requise, car la base de données n’a pas d’enclave activée. Cliquez sur Suivant.

7. Sur la page Paramètres d’exécution, il vous est demandé si vous souhaitez procéder au chiffrement ou générer un script PowerShell qui sera exécuté ultérieurement. Conservez les paramètres par défaut et sélectionnez Suivant.

8. Dans la page Résumé, l’Assistant vous informe des actions qu’il exécutera. Vérifiez que toutes les informations sont correctes et sélectionnez Terminer.

9. Dans la page Résultats, vous pouvez surveiller la progression des opérations de l’Assistant. Patientez jusqu’à ce que toutes les opérations soient terminées, puis sélectionnez Fermer.

   

10. (Facultatif) Explorez les modifications apportées à l’Assistant dans votre base de données.

    1. Développez ContosoHR>Security>Always Encrypted Keys pour explorer les objets de métadonnées pour la clé principale de colonne et le chiffrement de colonne que l’Assistant a créés.

    2. Vous pouvez également exécuter les requêtes ci-dessous sur les affichages catalogue du système qui contiennent des métadonnées clés.

       SQL

       SELECT * FROM sys.column_master_keys;
       SELECT * FROM sys.column_encryption_keys
       SELECT * FROM sys.column_encryption_key_values
       

    3. Dans l’Explorateur d’objets, faites un clic droit sur la table Employees et sélectionnez Table de script comme>CREATE To>Nouvelle éditeur de requête Window. Une nouvelle fenêtre Requête s’ouvre alors avec l’instruction CREATE TABLE pour la table Employees. Notez la clause ENCRYPTED WITH qui apparaît dans les définitions des colonnes SSN et Salary.

    4. Vous pouvez également exécuter la requête suivante sur sys.columns pour récupérer les métadonnées de chiffrement au niveau de la colonne pour les deux colonnes chiffrées.

       SQL

       SELECT
       [name]
       , [encryption_type]
       , [encryption_type_desc]
       , [encryption_algorithm_name]
       , [column_encryption_key_id]
       FROM sys.columns
       WHERE [encryption_type] IS NOT NULL;
       

PowerShell

1. Créer une clé principale de colonne dans votre magasin de clés.

   • Si vous utilisez Azure Key Vault, exécutez les commandes ci-dessous pour créer une clé asymétrique dans votre coffre de clés. Assurez-vous de fournir l’ID exact de votre abonnement, le nom du groupe de ressources contenant votre coffre de clés, ainsi que celui de votre coffre de clés.

     PowerShell

     Import-Module "Az"
     Connect-AzAccount
     $subscriptionId = "<your Azure subscription ID"
     $resourceGroup = "your resource group name containing your key vault"
     $keyVaultName = "your vault name"
     $keyVaultKeyName = "your key name"
     
     # Switch to your subscription.
     Set-AzConteXt -SubscriptionId $subscriptionId
     
     # To validate the above key vault settings, get the key vault properties.
     Get-AzKeyVault -VaultName $keyVaultName -ResourceGroupName $resourceGroup 
     
     # Create a key in the key vault.
     $keyVaultKey = Add-AzKeyVaultKey -VaultName $keyVaultName -Name $keyVaultKeyName -Destination "Software"
     $keyVaultKey
     

   • Si vous utilisez le magasin de certificats Windows, exécutez les commandes ci-dessous pour créer un certificat dans votre magasin d’utilisateurs actuel.

     PowerShell

     $cert = New-SelfSignedCertificate -Subject "HRCMK" -CertStoreLocation Cert:CurrentUser\My -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage DataEncipherment -KeySpec KeyExchange
     

2. Connectez-vous à votre base de données à l’aide du module SqlServer PowerShell. Assurez-vous de fournir une chaîne de connexion valide pour votre base de données.

   PowerShell

   $database = Get-SqlDatabase -ConnectionString $connectionString
   $database
   

3. Fournissez un objet de métadonnées de clé principale de colonne (qui fait référence à la clé principale de colonne physique que vous avez créée dans votre magasin de clés) dans votre base de données.

   • Si vous utilisez Azure Key Vault, exécutez les commandes ci-dessous.

     PowerShell

     # Sign in to Azure for the SqlServer PowerShell module
     Add-SqlAzureAuthenticationContext -Interactive
     
     # Create a SqlColumnMasterKeySettings in-memory object referencing the key you've created in your key vault. 
     $cmkSettings = New-SqlAzureKeyVaultColumnMasterKeySettings -KeyURL $keyVaultKey.Key.Kid
     
     # Create column master key metadata object (referencing your certificate), named CMK1, in the database.
     $cmkName = "CMK1"
     New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings
     

   • Si vous utilisez le magasin de certificats Windows, exécutez les commandes ci-dessous.

     PowerShell

     # Create a SqlColumnMasterKeySettings in-memory object referencing your certificate.
     $cmkSettings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation "CurrentUser" -Thumbprint $cert.Thumbprint
     
     # Create column master key metadata object, named CMK1, in the database.
     $cmkName = "CMK1"
     New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings
     

4. Générez une clé de chiffrement de colonne, chiffrez-la en utilisant la clé principale de colonne que vous avez créée et créez un objet de métadonnées de clé de chiffrement de colonne dans la base de données.

   PowerShell

   $cekName = "CEK1"
   New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName
   

5. Chiffrez les colonnes SSN et Salary dans la table Employees. Choisissez le chiffrement déterministe pour la colonne SSN et le chiffrement aléatoire pour la colonne Salary. Le chiffrement déterministe prend en charge les requêtes, telles que les recherches de points qui impliquent des comparaisons d’égalité sur des colonnes chiffrées. Le chiffrement aléatoire ne prend pas en charge les calculs sur les colonnes chiffrées.

   PowerShell

   # Encrypt the SSN and Salary columns 
   $ces = @()
   $ces += New-SqlColumnEncryptionSettings -ColumnName "HR.Employees.SSN" -EncryptionType "Deterministic" -EncryptionKey $cekName
   $ces += New-SqlColumnEncryptionSettings -ColumnName "HR.Employees.Salary" -EncryptionType "Randomized" -EncryptionKey $cekName
   Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -LogFileDirectory .
   
   

6. (Facultatif) Examinez les modifications que vous avez apportées à votre base de données.

   • Exécutez les commandes ci-dessous pour interroger les affichages catalogue du système qui contiennent des métadonnées sur la clé principale de colonne et la clé de chiffrement de colonne que vous avez créée.

     PowerShell

     $query = @'
     SELECT * FROM sys.column_master_keys;
     SELECT * FROM sys.column_encryption_keys
     SELECT * FROM sys.column_encryption_key_values
     '@
     
     Invoke-SqlCmd -ConnectionString $connectionString -Query $query
     

   • Exécutez les commandes ci-dessous pour interroger sys.columns afin de récupérer les métadonnées de chiffrement au niveau des colonnes pour les deux colonnes chiffrées.

     PowerShell

     $query = @'
     SELECT
     [name]
     , [encryption_type]
     , [encryption_type_desc]
     , [encryption_algorithm_name]
     , [column_encryption_key_id]
     FROM sys.columns
     WHERE [encryption_type] IS NOT NULL;
     '@
     
     Invoke-SqlCmd -ConnectionString $connectionString -Query $query
     

Étape 3 : interroger des colonnes chiffrées

SSMS

1. Connectez-vous à votre base de données en désactivant la fonction Always Encrypted pour votre connexion.

   1. Ouvrez une fenêtre de nouvelle requête.
   2. Faites un clic droit sur n’importe quel point de la fenêtre Requête et sélectionnez Connexion>Changer la connexion. La boîte de dialogue Se connecter au moteur de base de données s’ouvre.
   3. Cliquez sur Options<<. Cette option affiche des onglets supplémentaires dans la boîte de dialogue Se connecter au Moteur de base de données.
   4. Sélectionnez l’onglet Always Encrypted.
   5. Assurez-vous que l’option Activer Always Encrypted (chiffrement de la colonne) n’est pas sélectionnée.
   6. Sélectionnez Connecter.

   

2. Collez et exécutez la requête suivante. La requête doit retourner des données chiffrées binaires.

   SQL

   SELECT [SSN], [Salary] FROM [HR].[Employees]
   

   

3. Connectez-vous à votre base de données en activant la fonction Always Encrypted pour votre connexion.

   1. Faites un clic droit sur n’importe quel point de la fenêtre Requête et sélectionnez Connexion>Changer la connexion. La boîte de dialogue Se connecter au moteur de base de données s’ouvre.
   2. Cliquez sur Options<<. Cette option affiche des onglets supplémentaires dans la boîte de dialogue Se connecter au Moteur de base de données.
   3. Sélectionnez l’onglet Always Encrypted.
   4. Sélectionnez Activer Always Encrypted (chiffrement de colonne).
   5. Sélectionnez Connecter.

   

4. Exécutez à nouveau la même requête. Étant donné que la connexion aux bases de données est activée sur Always Encrypted, le gestionnaire client de SSMS tentera de déchiffrer les données stockées dans les deux colonnes chiffrées. Si vous utilisez Azure Key Vault, vous pouvez être invité à vous connecter à Azure.

   

5. Activez le paramétrage pour Always Encrypted. Cette fonctionnalité vous permet d’exécuter des requêtes qui filtrent les données par colonnes chiffrées (ou insèrent des données dans des colonnes chiffrées).

   1. Sélectionnez Requête dans le menu principal de SSMS.
   2. Sélectionnez Options de requête….
   3. Accédez à Exécution>Avancé.
   4. Assurez-vous que l’option Activer le paramétrage pour Always Encrypted est cochée.
   5. Cliquez sur OK.

   

6. Collez et exécutez la requête ci-dessous, qui filtre les données par la colonne SSN chiffrée. La requête doit retourner une ligne contenant des valeurs de texte en clair.

   SQL

   DECLARE @SSN [char](11) = '795-73-9838'
   SELECT [SSN], [Salary] FROM [HR].[Employees]
   WHERE [SSN] = @SSN
   

7. Par ailleurs, si vous utilisez Azure Key Vault configuré selon le modèle de autorisations de la stratégie d’accès, suivez les étapes ci-dessous pour voir ce qui se passe lorsqu’un utilisateur tente de récupérer des données de texte en clair à partir de colonnes chiffrées sans avoir accès à la clé principale de la colonne protégeant les données.

   1. Supprimez l’autorisation de la clé unwrap pour vous-même dans la stratégie d’accès à votre coffre de clés. Pour plus d’informations, consultez Attribuer une stratégie d’accès Key Vault.
   2. Étant donné que le gestionnaire client dans SSMS met en cache les clés de chiffrement de colonne acquises à partir d’un coffre de clés pendant 2 heures, fermez SSMS et ouvrez-la à nouveau. Cette option garantit que le cache de clés est vide.
   3. Connectez-vous à votre base de données en activant la fonction Always Encrypted pour votre connexion.
   4. Collez et exécutez la requête suivante. La requête devrait échouer et afficher un message d’erreur indiquant qu’il manque l’autorisation unwrap requise.
   SQL

   SELECT [SSN], [Salary] FROM [HR].[Employees]
   

PowerShell

1. Connectez-vous à votre base de données en désactivant la fonction Always Encrypted et exécutez une requête pour lire les données des colonnes chiffrées. La requête doit retourner les données chiffrées sous forme de tableaux binaires.

   PowerShell

   $query = "SELECT [SSN], [Salary] FROM [HR].[Employees]"
   Invoke-SqlCmd -ConnectionString $connectionString -Query $query
   

2. Connectez-vous à votre base de données en activant la fonction Always Encrypted activée et exécutez une requête pour lire les données des colonnes chiffrées. Étant donné que vous avez accès à la clé principale de colonne protégeant vos colonnes chiffrées, la requête doit retourner des données de texte en clair.

   PowerShell

   $query = "SELECT [SSN], [Salary] FROM [HR].[Employees]"
   Invoke-SqlCmd -ConnectionString "$connectionString; Column Encryption Setting = Enabled" -Query $query
   

Notes

Invoke-SqlCmd ne prend pas en charge les requêtes qui peuvent filtrer ou insérer des données dans des colonnes chiffrées. De telles requêtes doivent être paramétrables, et Invoke-SqlCmd ne prend pas en charge les requêtes paramétrables.

Étapes suivantes

• Développer des applications avec Always Encrypted

Voir aussi

• Documentation Always Encrypted
• Always Encrypted avec une documentation sur les enclaves sécurisées
• Provisionner des clés Always Encrypted à l’aide de SQL Server Management Studio
• Configurer Always Encrypted à l’aide de PowerShell
• Assistant Chiffrement intégral
• Interroger des colonnes en utilisant Always Encrypted avec SQL Server Management Studio