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

S'applique à : SQL Server

Cet article décrit comment protéger une base de données en utilisant Transparent Data Encryption (TDE), puis déplacer la base de données vers une autre instance de SQL Server en utilisant SQL Server Management Studio ou 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 base de données master 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 ne doit pas nécessairement être le même que celui de la clé principale de base de données.

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

autorisations

• La création de la clé principale de la base de données nécessite l’autorisation de CONTROL DATABASE sur la base de données master.

• La création du certificat qui protège la DEK nécessite l’autorisation de CREATE CERTIFICATE sur la base de données master.

• 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 de base de données et un certificat dans la base de données master. Pour plus d’informations, consultez Utilisation de Transact-SQL dans la suite de cet article.

2. Créer une sauvegarde du certificat de serveur dans la base de données master. Pour plus d’informations, consultez Utilisation de Transact-SQL dans la suite de 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 zone de dialogue Sélectionner le propriétaire de base de données. Pour plus d’informations sur la création d’une nouvelle base de données, consultez Créer une base de donnée.

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 ê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 ne disposez pas de l’autorisation VIEW DEFINITION sur les certificats de serveur, cette liste reste vide. Si une méthode de chiffrement de certificat est sélectionnée, cette valeur ne peut ê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 (TDE)

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, faites un clic droit sur la base de données que vous avez chiffrée précédemment. Ensuite, placez le curseur sur Tasks et sélectionnez Detach.....

   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.

Remarque

Vous ne pouvez pas détacher une base de données avec des connexions actives.

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 contient une ou plusieurs connexions actives, l’état indique Non 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 dans la suite de 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 dans la suite de cet article.

6. Dans l’Explorateur d’objets de SQL Server Management Studio, faites un clic droit sur le dossier Bases de données et sélectionnez Attach.....

7. Dans la zone de dialogue Attacher des bases de données, sous Bases de données à attacher, sélectionnez Ajouter.

8. Dans la zone de dialogue Localiser les fichiers de base de données : server_name, sélectionnez le fichier de base de données à attacher au serveur de news (groupes de discussion), puis cliquez sur 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 bases de données possibles dans laquelle vous pouvez sélectionner un autre propriétaire.

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

Icône	Texte d'état	Description
(Aucune icône)	(Aucun texte)	L’opération d’attachement n’a pas été lancée ou pourrait être en attente pour cet objet. Il s'agit de la valeur par défaut lorsque la boîte de dialogue est ouverte.
Triangle vert dirigé vers la droite	En cours	L’opération d’attachement a été lancée. Toutefois, elle n’est pas terminée.
Coche verte	Réussite	L’objet a bien été attaché.
Cercle rouge contenant une croix blanche	Erreur	L’opération d’attachement a rencontré une erreur et ne s’est pas bien terminée.
Cercle contenant deux quartiers noirs (à gauche et à droite) et deux quartiers blancs (en haut et en bas)	Arrêté	L’opération d’attachement n’a pas abouti, car l’utilisateur l’a interrompue.
Cercle contenant une flèche courbe pointant dans le sens inverse des aiguilles d'une montre	Restauré	L’opération d’attachement a réussi. Cependant, elle a été restaurée en raison d’une erreur survenue lors de l’attachement d’un autre objet.

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 «  <nom_base_de_données>  »
Affiche le nom des fichiers à attacher. Pour vérifier ou modifier le nom du chemin d’un fichier, sélectionnez le bouton Parcourir (...).

Remarque

Si un fichier n’existe pas, la colonne Message affiche « Introuvable ». Si un fichier d’historique n’est pas trouvé, il existe dans un autre annuaire ou a été supprimé. Vous devez mettre à jour le chemin d'accès du fichier dans la grille Détails de la base de données pour désigner l'emplacement correct ou supprimer le fichier journal de la grille. Si un fichier de données .ndf est introuvable, vous devez mettre à jour son chemin d’accès dans la grille pour désigner l’emplacement exact.

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, Données ou 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

• Attacher et détacher une base de données (SQL Server)
• Transparent Data Encryption avec Azure SQL Database
• sp_detach_db (Transact-SQL)