Agregar una base de datos con encriptación TDE a un grupo de disponibilidad Always On SQL Server

Imagen:Cifrado de datos transparente (TDE) Always On SQL Server

Problema:

Al agregar una base de datos encriptada con "Transparent Data Encryption" (TDE) a un grupo de disponibilidad Always On de SQL Server, con el asistente de SQL Server Management Studio (SSMS) muestra el siguiente mensaje:

--Microsoft SQL Server Management Studio:
--This wizard cannot add a database containing a database encryption key to an availability group. Use the CREATE or ALTER AVAILABILITY GROUP Transact-SQL statement instead. For more information see SQL Server Books Online.

Solución:

Se tendrá que agregar la base de datos al grupo Disponibilidad manualmente.

Para lo cual se realizará una copia de seguridad completa y una copia de seguridad del registro de transacciones para la base de datos con encriptaciòn TDE, copiarla y restaurar con NORECOVERY en la réplica secundaria.

La restauración fallará enel servidor secundario, por que la base de datos está encriptada usando TDE, el proceso de restauración fallará y obtendrá el siguiente error:

--No se encuentra el servidor certificado con la huella digital '0xCF3F424E8362C9540C2C6E5BACCB2DB067D4DFDF'.
--Fin anómalo de RESTORE FILELIST. (.Net SqlClient Data Provider)

El error indica que la base de datos está cifrada con una clave de cifrado de base de datos que está protegida por un certificado y este certificado no está disponible en la instancia de SQL Server.

Para restaurar la copia de seguridad de la base de datos cifrada en una instancia de SQL Server, el certificado que cifra la clave de cifrado de la base de datos debe crearse en ese servidor. Es por eso que creamos una copia de seguridad del certificado y su clave privada cuando habilitamos el TDE en nuestra base de datos.

Debemos crear una clave maestra en el servidor secundario con una contraseña compleja, usando el comando CREATE MASTER KEY T-SQL:

  Use Master;
  go
  Create master key encryption by password='PasswordEncriptacion2020S2';
  go

Tiene la opción de usar la misma contraseña para la clave maestra anterior o usar una nueva contraseña, ya que la clave maestra se usa para cifrar el certificado y no la base de datos en sí para mantenerla protegida. En todos los casos, la clave en sí será diferente debido a cómo se realiza el proceso de generación de claves.

El siguiente paso es copiar el certificado y los archivos de respaldo de la clave privada generados previamente desde la réplica primaria al servidor secundario. Puede guardarlo en la carpeta de datos predeterminada en el servicio secundario, o en una carpeta personalizada donde debe proporcionar ese baño en la declaración CREATE CERTIFICATE T-SQL. La clave privada del certificado debe ser descifrada por la misma contraseña que se usó para cifrarla cuando se creó la copia de seguridad:

Necesitamos realizar una copia de seguridad completa y una copia de seguridad del registro de transacciones para la base de datos que se agregará al grupo de disponibilidad, copiarla y restaurarla en modo NORECOVERY en la réplica secundaria.

Recuperar el certificado para el cifrado de base de datos

Para esto se requiere de la copia de seguridad del certificado de cifrado. Desde la consola de SQL Server, ejecutamos el siguiente comando para crear la clave maestra:

  Use Master;
  go
  Create master key encryption by password='PasswordEncriptacion2020S2';
  go

Generar el nuevo certificado:

Generar el nuevo certificado a partir de la copia de seguridad que se realizó en la réplica primaria, indicando la ruta del certificado, la ruta del archivo con la clave privada y la contraseña que se puso cuando se hizo la copia de seguridad del certificado:

--Use Master;
  go
  Create Certificate Certificado_TDE1 from file = 'C:\Certificado_TDE\Certificado_TDE' with Private key (File = 'C:\Certificado_TDE\Certificado_TDE.pri', Decryption By Password ='PasswordBackupCertificado2020')
--El servidor ha recuperado el certificado en la base de datos, lo comprobamos:
  Select * from sys.certificates;

ALTER DATABASE DatabaseName  SET HADR AVAILABILITY GROUP = AG_GroupName
GO