TDE - ENCRIPTAR BASE DE DATOS

ENCRIPTAR BASE DE DATOS CON TDE

To use TDE to encrypt a database, you must perform the following steps:

  MASTER
    1.- Create the master encryption key.

    2.- Create the Server Certificate protected by the master key.

    3.- Backup the Server Certificate

  USER DATABASE

    4.- Create Database Encryption Key

    5.- Encrypt the database.

--     1:     CREATE THE MASTER ENCRYPTION KEY

USE MASTER;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword'

GO

-- 2: CREATE THE SERVER  CERTIFICATE PROTECTED BY THE MASTER KEY

CREATE CERTIFICATE MyMasterServerCertificate WITH SUBJECT = 'Master Server Certificate'

GO

--  3:  BACKUP MASTER SERVER CERTIFICATE

--Warning:

--The certificate used for encrypting the databases encryption key has

--not been backed up. You should immediately back up the server certificate and the private

--key associated with the certificate. If the certificate ever becomes unavailable or

--if you must restore or attach the database on another server,

--you must have backups of both the certificate and the private key or you will

--not be able to open the database anymore.

 BACKUP CERTIFICATE MyMasterServerCertificate

 TO FILE = 'C:\BASURA\MyMasterServerCertificate.cer'

 WITH PRIVATE KEY (

                    FILE = 'C:\BASURA\MyMasterServerCertificate.pvk', 

                    ENCRYPTION BY PASSWORD = 'PrivateKeyPassword'

                   );

 GO

--  4: CREATE DEK (DATABASE ENCRYPTION KEY) AND PROTECT IT BY USING THE MASTER SERVER CERTIFICATE, THIS CERTIFICATE MUST BE CREATED IN THE USER DATABASE

USE MyDataBase;

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE MyMasterServerCertificate

GO

-- 5:  ENCRYPT THE DATABASE

ALTER DATABASE MyDataBase SET ENCRYPTION ON;

GO

 --                          ELIMINAR POR COMPLETO TDE 

 --                                            use MyDataBase;

-- 1.- QUITAR LA ENCRIPTACIÓN

ALTER DATABASE MyDataBase SET ENCRYPTION OFF

-- 2.-

DROP DATABASE ENCRYPTION KEY

 -- 3.- (ONLY IF YOU HAVE ONE)

DROP SYMMETRIC KEY PasswordTableKey;   -- llave simétrica que pertenece al certificado

-- Para borrar el certificado de base de datos de usuario borre no deben existir llaves simétricas

-- 4.-

DROP CERTIFICATE [Remate2G_Data_Certificate];   -- certificado de la base de datos

-- 5.- (ONLY IF YOU HAVE ONE)

-- para borrar la MASTER KEY antes debe borrar el certificado

DROP MASTER KEY;   

------------------------------------- eliminar el certificado de la MASTER

USE master;

GO

 -- Para eliminar el certificado, de la MASTER DB, no deben existir Bases de datos de usuario utilizando TDE

 -- Borrar certificados de otras bases antes de borrar el certificado de la master

DROP CERTIFICATE [MyMasterServerCertificate]; 

-- para borrar la MASTER KEY antes debe borrar el certificado

DROP MASTER KEY;

 -- SOME HELP QUERIES

SELECT * FROM SYS.dm_database_encryption_keys

TDE - DESENCRIPTAR DB

Cuando intentas restaurar una base de datos que está encriptada (TDE) en un servidor secundario, debes instalar el certificado que se encuentra en el servidor primario, de lo contrario, al intentar restaurar la base de datos te arrojará el siguiente error:

Cannot find server certificate with thumbprint '0xD6270CD6DBA0437B0054685723BAEB12B19D17EF'.

Msg 3013, Level 16, State 1, Line 2

RESTORE DATABASE is terminating abnormally.

• CREAR MASTER KEY
• CREATE CERTIFICATE
• RESTORE DATABASE

1.- CREATE MASTER KEY

Para implementar el certificado se debe crear una Master Key en el servidor secundario sobre la base de datos MASTER

USE MASTER;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**********';

GO

El password de la Master Key no necesariamente debe ser el mismo que el del servidor primario, preferentemente hágalo con el mismo.

2.- CREATE CERTIFICATE (restaurar certificado en servidor secundario)

A continuación se muestra el código SQL-T para restaurar el certificado que obtuvo del servidor primario.

Tome el backup del certificado del servidor primario, cópielo al servidor secundario y restaurelo como en el siguiente ejemplo:

USE MASTER;

GO

CREATE CERTIFICATE CertificateServerLevel

FROM FILE = 'G:\Authority\SERVER_Certificate.cer'

WITH PRIVATE KEY (FILE = 'G:\Authority\SERVER_CertificateKey.pvk',DECRYPTION BY PASSWORD = '********')

- CertificateServerLevel: Es un Nombre X del Certificado del MASTER DB (Certificado a nivel servidor), preferentemente que se llamen igual que tiene en el servidor primario

- Para abrir e implementar el certificado necesitará la contraseña que generó en el servidor primario.

3.- RESTORE DATABASE

Ahora ya puede restaurar su base de datos Encriptada por TDE como lo hace normalmente.

En este caso particular, se va a restaurar un FULL y un DIFFERENTIAL

Puede hacerlo mediante SQL-T o utilizando el Management Studio

USE [master]

RESTORE DATABASE [REMATE2G]

FROM  DISK = N'G:\Backups\BACKUP 2015 SEPTEMBER WEEK 39  -  REMATE2G.BAK'

WITH  FILE = 3, 

MOVE N'LOG_TRANSACTION' TO N'J:\LDF\REMATE2G_LOG.ldf', 

NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5

RESTORE DATABASE [REMATE2G]

FROM  DISK = N'G:\Backups\BACKUP 2015 SEPTEMBER WEEK 39  -  REMATE2G.BAK'

WITH  FILE = 7,  NOUNLOAD,  STATS = 5

Mover una base de datos protegida por TDE a otra instancia de SQL Server

Restaurar una base de datos Encriptada (Microsoft)

Qué es el TDE?

NOTAS: (SYMMETRIC KEY) Cuando se tiene una llave simétrica dentro de la DB

DEJAR ABIERTA LA MASTER KEY PARA EVITAR COLOCAR EL PASSWORD CONTINUAMENTE AL ABRIR LA LLAVE SIMÉTRICA

USE REMATE2G;

GO

-- AL RESTAURAR LA BASE DE DATOS EN OTRO SERVIDOR

-- EL MASTER KEY DEBE ABRIRSE PARA PODER ABRIR SU LLAVE SIMÉTRICA

-- CADA VEZ QUE SE DESEE ABRIR LA LLAVE SIMÉTRICA, SERÁ NECESARIO ABRIR LA MASTER KEY COLOCANDO SU PASSWORD

-- SIN EMBARGO, PUEDE ALTERARSE LA MASTER KEY PARA QUE ABRA EN AUTOMÁTICO

-- PRIMERO ABRIR LA MASTER KEY

OPEN MASTER KEY DECRYPTION BY PASSWORD = '*********'

-- CLOSE MASTER KEY

-- PARA PONER EN AUTOMÁTICO LA MASTER KEY, ES NECESARIO TENERLA SIEMPRE ABIERTA

-- CON EL SIGUIENTE CÓDIGO AUNQUE SE CIERRE LA MASTER KEY, ABRIRA AUTOMÁTICAMENTE

USE Remate2G;

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = '**********';

GO

AHORA YA SE PUEDE MANDAR ABRIR LA LLAVE SIMÉTRICA...

       OPEN SYMMETRIC KEY PasswordTableKey

       DECRYPTION BY CERTIFICATE Nombre_Certificado

La SYMETRIC KEY se utiliza para encriptar datos, información dentro de las tablas.

Cannot find server certificate with thumbprint