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