Déplacer une base de données protégée par TDE vers un autre serveur SQL

S’applique à :SQL Server

Cet article explique comment protéger une base de données à l’aide du chiffrement transparent des données (TDE), puis déplacer la base de données vers une autre instance de SQL Server à l’aide de SQL Server Management Studio ou de Transact-SQL. TDE effectue le chiffrement et le déchiffrement d’E/S en temps réel des données et des fichiers journaux. Le chiffrement utilise une clé de chiffrement de base de données stockée dans l’enregistrement de démarrage de base de données à des fins de disponibilité lors de la récupération. La clé de chiffrement de base de données est une clé symétrique sécurisée à l'aide d'un certificat stocké dans la base de données master du serveur ou une clé asymétrique protégée par un module de gestion de clés extensible.

Limites

• Lors du déplacement d'une base de données protégée par chiffrement transparent des données, vous devez également déplacer le certificat ou la clé asymétrique qui sert à ouvrir la clé DEK. Le certificat ou la clé asymétrique doit être installé dans la master base de données du serveur de destination, afin que SQL Server puisse accéder aux fichiers de base de données. Pour plus d’informations, consultez Transparent Data Encryption (TDE).

• Vous devez conserver des copies du fichier de certificat et du fichier de clé privée pour permettre la récupération du certificat. Le mot de passe de la clé privée n’a pas besoin d’être identique au mot de passe de clé principale de la base de données.

• SQL Server stocke les fichiers créés ici C:\Program Files\Microsoft SQL Server\MSSQL<xx>.MSSQLSERVER\MSSQL\DATA par défaut, où <xx> est le numéro de version.

Autorisations

• Nécessite CONTROL DATABASE l’autorisation sur la master base de données pour créer la clé principale de la base de données.

• Nécessite CREATE CERTIFICATE l’autorisation sur la master base de données pour créer le certificat qui protège la clé DEK.

• Requiert l'autorisation CONTROL DATABASE sur la base de données chiffrée et l'autorisation VIEW DEFINITION sur le certificat ou la clé asymétrique qui sert à chiffrer la clé de chiffrement de la base de données.

Créer une base de données protégée par Transparent Data Encryption

Les procédures suivantes vous montrent comment créer une base de données protégée par TDE avec SQL Server Management Studio et Transact-SQL.

Utiliser SQL Server Management Studio

1. Créez une clé principale et un certificat de base de données dans la master base de données. Pour plus d’informations, consultez Utilisation de Transact-SQL plus loin dans cet article.

2. Créez une sauvegarde du certificat de serveur dans la master base de données. Pour plus d’informations, consultez Utilisation de Transact-SQL plus loin dans cet article.

3. Dans l’Explorateur d’objets, cliquez avec le bouton droit sur le dossier Bases de données et sélectionnez Nouvelle base de données.

4. Dans la boîte de dialogue Nouvelle base de données , dans la zone Nom de la base de données , entrez le nom de la nouvelle base de données.

5. Dans la zone Propriétaire , entrez le nom du propriétaire de la nouvelle base de données. Vous pouvez également sélectionner les points de suspension (...) pour ouvrir la boîte de dialogue Sélectionner le propriétaire de la base de données. Pour plus d’informations sur la création d’une base de données, consultez Créer une base de données.

6. Dans l’Explorateur d’objets, sélectionnez le signe plus pour développer le dossier Bases de données .

7. Cliquez avec le bouton droit sur la base de données que vous avez créée, pointez sur Tâches, puis sélectionnez Gérer le chiffrement de base de données.

   Les options suivantes sont disponibles dans la boîte de dialogue Gérer le chiffrement de base de données .

   Algorithme de chiffrement
   Affiche ou définit l'algorithme à utiliser pour le chiffrement de la base de données. AES128 est l'algorithme par défaut. Ce champ ne peut pas être vide. Pour plus d’informations sur les algorithmes de chiffrement, consultez Choisir un algorithme de chiffrement.

   Utilisez un certificat de serveur
   Définit le chiffrement à sécuriser par un certificat. Sélectionnez une option dans la liste. Si vous n’avez pas l’autorisation sur les VIEW DEFINITION certificats de serveur, cette liste est vide. Si une méthode de chiffrement de certificat est sélectionnée, cette valeur ne peut pas être vide. Pour plus d'informations sur les certificats, consultez SQL Server Certificates and Asymmetric Keys.

   Utilisez une clé asymétrique de serveur
   Définit le chiffrement à sécuriser par une clé asymétrique. Seules les clés asymétriques disponibles sont affichées. Seule une clé asymétrique protégée par un module EKM peut chiffrer une base de données en utilisant le chiffrement transparent de données.

   Définir le chiffrement de la base de données sur
   Modifie la base de données pour activer ou désactiver le chiffrement transparent des données.

8. Lorsque vous avez terminé, sélectionnez OK.

Utiliser Transact-SQL

1. Dans l' Explorateur d'objets, connectez-vous à une instance du Moteur de base de données.

2. Dans la barre d’outils standard, sélectionnez Nouvelle requête.

3. Copiez et collez l’exemple suivant dans la fenêtre de requête, puis sélectionnez Exécuter.

   -- Create a database master key and a certificate in the master database.
   USE master;
   GO
   
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1';
   GO
   
   CREATE CERTIFICATE TestSQLServerCert
       WITH SUBJECT = 'Certificate to protect TDE key'
   GO
   
   -- Create a backup of the server certificate in the master database.
   -- The following code stores the backup of the certificate and the private key file in the default data location for this instance of SQL Server
   -- (C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA).
   BACKUP CERTIFICATE TestSQLServerCert TO FILE = 'TestSQLServerCert'
   WITH PRIVATE KEY (
       FILE = 'SQLPrivateKeyFile',
       ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1'
   );
   GO
   
   -- Create a database to be protected by TDE.
   CREATE DATABASE CustRecords;
   GO
   
   -- Switch to the new database.
   -- Create a database encryption key, that is protected by the server certificate in the master database.
   -- Alter the new database to encrypt the database using TDE.
   USE CustRecords;
   GO
   
   CREATE DATABASE ENCRYPTION KEY
       WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE TestSQLServerCert;
   GO
   
   ALTER DATABASE CustRecords
   SET ENCRYPTION ON;
   GO
   

Pour plus d’informations, consultez l’article suivant :

• CREATE MASTER KEY (Transact-SQL)
• CREATE CERTIFICATE (Transact-SQL)
• BACKUP CERTIFICATE (Transact-SQL)
• CREATE DATABASE
• CREATE DATABASE ENCRYPTION KEY (Transact-SQL)
• ALTER DATABASE (Transact-SQL)

Déplacer une base de données protégée par Transparent Data Encryption

Les procédures suivantes vous montrent comment déplacer une base de données protégée par TDE avec SQL Server Management Studio et Transact-SQL.

Utiliser SQL Server Management Studio

1. Dans l’Explorateur d’objets, cliquez avec le bouton droit sur la base de données que vous avez chiffrée précédemment, pointez sur Tâches et sélectionnez Détacher....

   Les options suivantes sont disponibles dans la boîte de dialogue Détacher la base de données .

   Bases de données à détacher
   Répertorie les bases de données à détacher.

   Database Name
   Spécifie le nom de la base de données à détacher.

   Supprimer les connexions
   Permet de déconnecter les connexions à la base de données spécifiée.

Mettre à jour les statistiques
Par défaut, l'opération de détachement conserve toutes les statistiques d'optimisation obsolètes avant de procéder au détachement ; pour actualiser les statistiques existantes, activez cette case à cocher.

Conserver les catalogues de texte intégral
Par défaut, l'opération de détachement conserve tous les catalogues de texte intégral associés à la base de données. Pour les supprimer, décochez la case Conserver les catalogues de texte intégral . Cette option s’affiche uniquement lorsque vous mettez à niveau une base de données à partir de SQL Server 2005 (9.x).

État
Affiche l’un des états suivants : Prêt ou Non prêt.

Message
La colonne Message peut indiquer des informations sur la base de données, comme suit :

• Lorsqu'une base de données est impliquée dans la réplication, l' État est Non prêt et la colonne Message indique Base de données répliquée.

• Lorsqu’une base de données a une ou plusieurs connexions actives, l’état n’est pas prêt et la colonne Message affiche <number_of_active_connections>connexion active ( par exemple : 1 connexion active). Avant de détacher la base de données, vous devez déconnecter toutes les connexions actives en cliquant sur Supprimer les connexions.

Pour obtenir plus d'informations sur un message, sélectionnez le texte du lien hypertexte pour ouvrir le Moniteur d'activité.

1. Cliquez sur OK.

2. À l'aide de l'Explorateur Windows, déplacez ou copiez les fichiers de base de données depuis le serveur source vers le même emplacement sur le serveur de destination.

3. À l'aide de l'Explorateur Windows, déplacez ou copiez la sauvegarde du certificat de serveur et le fichier de clé privée depuis le serveur source vers le même emplacement sur le serveur de destination.

4. Créez une clé principale de base de données sur l’instance de destination de SQL Server. Pour plus d’informations, consultez Utilisation de Transact-SQL plus loin dans cet article.

5. Recréez le certificat de serveur à l'aide du fichier de sauvegarde du certificat de serveur d'origine. Pour plus d’informations, consultez Utilisation de Transact-SQL plus loin dans cet article.

6. Dans l’Explorateur d’objets dans SQL Server Management Studio, cliquez avec le bouton droit sur le dossier Bases de données et sélectionnez Attacher....

7. Dans la boîte de dialogue Attacher des bases de données, sous Bases de données à joindre, sélectionnez Ajouter.

8. Dans la boîte de dialogue Localiser les fichiers de base de données -server_name , sélectionnez le fichier de base de données à joindre au nouveau serveur, puis sélectionnez OK.

   Les options suivantes sont disponibles dans la boîte de dialogue Attacher des bases de données .

   Bases de données à attacher
   Affiche des informations sur les bases de données sélectionnées.

   <aucun en-tête de colonne>
   Affiche une icône indiquant l'état de l'opération d'attachement. Les icônes possibles sont décrites dans la section État.

   Emplacement du fichier MDF
   Affiche le chemin d'accès et le nom du fichier MDF sélectionné.

   Database Name
   Affiche le nom de la base de données.

   Attacher en tant que
   Permet de spécifier éventuellement un autre nom sous lequel la base de données doit être attachée.

   Propriétaire
   Fournit une liste déroulante des propriétaires de base de données possibles à partir desquels vous pouvez éventuellement sélectionner un autre propriétaire.

   État
   Affiche l'état de la base de données conformément au tableau ci-après.

Message
Affiche un message vierge ou un lien hypertexte «Fichier introuvable».

Ajouter
Permet de rechercher les principaux fichiers de base de données nécessaires. Lorsque l'utilisateur sélectionne un fichier .mdf, les informations applicables sont automatiquement remplies dans les champs respectifs de la grille Bases de données à attacher .

Remove
Supprime le fichier sélectionné de la grille Bases de données à attacher .

Détails de la base de données «< database_name »>
Affiche le nom des fichiers à attacher. Pour vérifier ou modifier le nom du chemin d’un fichier, sélectionnez le bouton Parcourir (...).

Nom du fichier d'origine
Affiche le nom du fichier attaché appartenant à la base de données.

Type de fichier
Indique le type de fichier, de données ou de journal.

Chemin d'accès au fichier actuel
Affiche le chemin d'accès au fichier de base de données sélectionné. Le chemin d'accès peut être modifié manuellement.

Message
Affiche un message vierge ou un lien hypertexte «Fichier introuvable».

Utiliser Transact-SQL

1. Dans l' Explorateur d'objets, connectez-vous à une instance du Moteur de base de données.

2. Dans la barre d’outils standard, sélectionnez Nouvelle requête.

3. Copiez et collez l’exemple suivant dans la fenêtre de requête, puis sélectionnez Exécuter.

   -- Detach the TDE protected database from the source server.
   USE master;
   GO
   
   EXEC master.dbo.sp_detach_db @dbname = N'CustRecords';
   GO
   
   -- Move or copy the database files from the source server to the same location on the destination server.
   -- Move or copy the backup of the server certificate and the private key file from the source server to the same location on the destination server.
   -- Create a database master key on the destination instance of SQL Server.
   USE master;
   GO
   
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1';
   GO
   
   -- Recreate the server certificate by using the original server certificate backup file.
   -- The password must be the same as the password that was used when the backup was created.
   CREATE CERTIFICATE TestSQLServerCert
   FROM FILE = 'TestSQLServerCert'
   WITH PRIVATE KEY (
       FILE = 'SQLPrivateKeyFile',
       DECRYPTION BY PASSWORD = '*rt@40(FL&dasl1'
   );
   GO
   
   -- Attach the database that is being moved.
   -- The path of the database files must be the location where you have stored the database files.
   CREATE DATABASE [CustRecords] ON (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\CustRecords.mdf'),
       (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\CustRecords_log.LDF')
   FOR ATTACH;
   GO
   

Pour plus d’informations, consultez l’article suivant :

• CREATE MASTER KEY (Transact-SQL)
• CREATE CERTIFICATE (Transact-SQL)
• CREATE DATABASE

Contenu connexe

• Détachement et attachement de base de données (SQL Server)
• Transparent Data Encryption avec Azure SQL Database
• sp_detach_db (Transact-SQL)