Tutoriel : Prise en main d’Always Encrypted

S’applique à :SQL ServerAzure SQL DatabaseAzure 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.

Note

Si vous recherchez des informations sur Always Encrypted avec enclaves sécurisées, consultez les didacticiels suivants à la place :

• 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

Prerequisites

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

• Base de données vide dans Azure SQL Database, 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 - Azure SQL Database 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 la dernière version 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 base de données

Dans cette étape, vous allez créer le schéma RH et la table Employees . Ensuite, vous allez remplir la table avec certaines 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 Database ou une instance Managée Azure SQL à 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 de 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.

   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 .

   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 la chaîne de connexion avec l’adresse de votre serveur et des paramètres d’authentification valides pour votre base de données.

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 allez approvisionner une clé principale de colonne et une clé de chiffrement de colonne pour Always Encrypted. Ensuite, vous chiffrez les colonnes SSN et Salary dans la table Employees .

SSMS

SSMS fournit un Assistant qui vous permet de configurer facilement Always Encrypted en configurant une clé principale de colonne, une clé de chiffrement de colonne et en chiffrer les colonnes sélectionnées.

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

2. Cliquez avec le bouton droit sur la table Employees et sélectionnez Chiffrer les colonnes pour ouvrir l’Assistant Always Encrypted.

   

3. Sélectionnez Suivant dans la page Présentation de l’Assistant.

4. Dans la page Sélection de colonne.

   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 recherche de point 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. Laissez CEK-Auto1 (Nouveau) comme clé de chiffrement de colonne pour les deux colonnes. 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 magasin 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, en particulier 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 terminer 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 Windows :

     1. Sélectionnez le magasin de certificats Windows.

     2. Laissez la sélection par défaut de l’utilisateur actuel . Cela indique à l’Assistant de générer un certificat (votre nouvelle clé principale de colonne) dans le magasin d’utilisateurs actuel.

        

     3. Cliquez sur Suivant.

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

7. Dans la page Paramètres d’exécution, vous êtes invité à procéder au chiffrement ou à générer un script PowerShell à exécuter ultérieurement. Conservez les paramètres par défaut, puis 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. Attendez que toutes les opérations se terminent correctement, puis sélectionnez Fermer.

   

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

    1. Développez les clés Always Encrypted de sécurité>ContosoHR>pour explorer les objets de métadonnées de 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 vues de catalogue système qui contiennent des métadonnées clés.

       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, cliquez avec le bouton droit sur la table Employees et sélectionnez Table de script comme>fenêtre CREATE To>New Query Editor. Cette opération ouvre une nouvelle fenêtre de requête 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 ci-dessous sur sys.columns pour récupérer les métadonnées de chiffrement au niveau des colonnes pour les deux colonnes chiffrées.

       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éez 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. Veillez à fournir l’ID correct de votre abonnement, le nom du groupe de ressources contenant votre coffre de clés et le nom de votre coffre de clés.

     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.

     $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. Veillez à fournir une chaîne de connexion valide pour votre base de données.

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

3. Provisionnez 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.

     # 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.

     # 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 avec 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.

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

5. Chiffrer les colonnes SSN et Salaire dans la table Employés . 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 recherche de point 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.

   # 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) Explorez les modifications que vous avez apportées dans votre base de données.

   • Exécutez les commandes ci-dessous pour interroger les vues de catalogue 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.

     $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 pour récupérer les métadonnées de chiffrement au niveau des colonnes pour les deux colonnes chiffrées.

     $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 avec Always Encrypted désactivé pour votre connexion.

   1. Ouvrez une fenêtre de nouvelle requête.
   2. Cliquez avec le bouton droit n’importe où dans la fenêtre de requête et sélectionnez Connexion>modifier la connexion. La boîte de dialogue Se connecter au moteur de base de données s’ouvre.
   3. Cliquez sur Options<<. Cela 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. Vérifiez que l’option Activer Always Encrypted (chiffrement de colonne) n’est pas sélectionnée.
   6. Sélectionnez Se connecter.

   

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

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

   

3. Connectez-vous à votre base de données avec Always Encrypted activé pour votre connexion.

   1. Cliquez avec le bouton droit n’importe où dans la fenêtre de requête et sélectionnez Connexion>modifier la connexion. La boîte de dialogue Se connecter au moteur de base de données s’ouvre.
   2. Cliquez sur Options<<. Cela 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 Se connecter.

   

4. Réexécutez la même requête. Étant donné que vous êtes connecté à Always Encrypted activé pour votre connexion de base de données, le pilote client dans SSMS tente 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. Activer 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. Vérifiez 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 en texte clair.

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

7. Si vous utilisez Éventuellement Azure Key Vault configuré avec le modèle d’autorisations de 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 en texte clair à partir de colonnes chiffrées sans avoir accès à la clé principale de colonne protégeant les données.

   1. Supprimez l’autorisation de clé unwrap pour vous-même dans la stratégie d’accès de votre coffre de clés. Pour plus d’informations, consultez Attribuer une stratégie d’accès Key Vault.
   2. Étant donné que le pilote 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. Cela garantit que le cache de clés est vide.
   3. Connectez-vous à votre base de données avec Always Encrypted activé pour votre connexion.
   4. Collez et exécutez la requête suivante. La requête doit échouer avec le message d’erreur indiquant que vous ne disposez pas de l’autorisation requise unwrap .

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

PowerShell

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

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

2. Connectez-vous à votre base de données avec Always Encrypted activé et exécutez une requête pour lire des données à partir de 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 en texte clair.

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

Note

Invoke-SqlCmd ne prend pas en charge les requêtes qui peuvent filtrer par ou insérer des données sur des colonnes chiffrées. Ces requêtes doivent être paramétrables et Invoke-SqlCmd ne prennent pas en charge les requêtes paramétrables.

Étapes suivantes

• Développer des applications avec Always Encrypted

Voir aussi

• Documentation Always Encrypted
• Documentation Always Encrypted avec 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