Xifrat de base de dades SQL Server per TDE

19 setembre 2017
Josep Ma Solanes 0

En aquesta entrada veurem com xifrar una base de dades amb SQL Server mitjançant el Transparent Data Encryption (TDE), ja sigui per seguretat com per donar compliment a alguna normativa que aplica, com per exemple el Reglament General de Protecció de Dades (RGPD o GDPR, pels amics) de la Unió Europea, aprovat el 27 d’abril de 2016 i que serà aplicable a partir del 25 de maig de 2018. Amb multes molt considerables en cas d’incompliment del mateix que poden ser fins els 20 milions d’euros o bé el 4% del volum de negoci total anual global de l’exercici financer anterior. Per tant, no és una cosa que s’hagi d’obviar.

Quasi tots els fabricants de programari que conec van com a bojos per aplicar les noves mesures demanades, que no són poques i que en molts casos afecten el nucli de les aplicacions que requeriran canvis profunds. però també sé de moltes aplicacions que no es tornaran a dissenyar, ja sigui perquè tenen els seus anys i només es troben en manteniment, perquè el fabricant ha desaparegut o pels costos que això suposa.

Mitjançant el xifrat de base de dades minimitzem l’impacte negatiu que hi pugui haver en les aplicacions que no podran aplicar la pseudonimització de les dades, així com un complement al que demana la nova reglamentació en els articles 6, 32 i 34.

El més recomanable avui dia és utilitzar tecnologies de xifrat fort per defecte, sobretot en entorns on és més fàcil accedir directament a les dades o servidors.

Vídeo de l’article a Youtube.

 

Què és el xifrat de base de dades TDE?

El xifrat de base de dades SQL Server mitjançant el Transparent Data Encryption (TDE) es troba disponible en les versions 2008, 2008 R2, 2012, 2014 i 2016, però només en les edicions ENTERPRISE (també Datacenter de 2008 R2 o Developers que no són per producció). Per tant, la versió STANDARD no permet el xifrat de base de dades mitjançant Transparent Data Encryption (TDE).

Consisteix en xifrar les dades dins els fitxers físics de la base de dades mitjançant un certificat i clau mestra. Si no es disposa d’aquest certificat i clau mestra, encara que es robin els fitxers, les dades no són accessibles.

Amb Microsoft SQL Server 2016 Service Pack 1 apareix la característica Always Encrypted més avantatjosa i suportada en totes les edicions: Standard i Enterprise, aquesta serà tractada en una altra entrada específica.

Per tenir clar on s’ubiquen les claus mestra i els certificats podeu donar un cop d’ull al següent gràfic que mostra la jerarquia que cal seguir per generar les claus mestres i els certificats per poder xifrar la base de dades d’usuari:

  • El sistema operatiu xifra la clau mestra del servei que es guarda a nivell d’instància de SQL Server.
  • La clau mestra del servei xifra la clau mestre de la base de dades MASTER.
  • La clau mestra de la base de dades MASTER crea un certificat per la mateixa base de dades.
  • El certificat de la base de dades MASTER xifra la clau de xifrat de base de dades de l’usuari (on tenim les dades).
  • La base de dades de l’usuari es protegeix amb la clau de xifrat mitjançant la tecnologia Transparent Data Encryption (TDE).

 

Naturalment, si es perd la clau mestra o el certificat tindreu un bon problema. Feu sempre còpia de seguretat d’aquestes dades.

 

Implementació del xifrat de base de dades SQL Server

Abans de començar, assegurar que tenim una versió Microsoft SQL Server que està entre la 2008 i la 2016, i que l’edició és la ENTERPRISE. En cas que no fos correcta, tot i que ens deixarà fer els processos aquí descrits, a l’arribar al punt d’activar el xifrat a la base de dades de l’usuari aquest se’ns denegarà.

Primer pas, crear la clau mestra a la base de dades Master. Des de la consola de SQL Server Management Studio o qualsevol altra aplicació que permeti executar comandes a la base de dades SQL Server, creem una nova consulta i executem la següent comanda amb la contrasenya mestra per la base de dades MASTER:

Use Master;
Go
Create Master Key Encryption by Password='HolaEstoEsLaClaveDeCifrado#17';
Go

Executem la consulta (prémer la tecla F5, per exemple). En cas que la clau no compleixi els requisits de complexitat establerts en el sistema operatiu es denegarà la creació demanant una clau més forta. Penseu que és una clau que protegirà les bases de dades, és prou important perquè no sigui la típica 1234.

Continuem amb la creació del certificat (li poso el nom Certificat_TDE) que protegirà la contrasenya mestra de la base de dades MASTER:

Use Master;
Go
Create Certificate Certificat_TDE With Subject='Cifrado_bases_de_datos';
Go

Executem la consulta (prémer la tecla F5, per exemple).

Comprovem que s’ha generat un nou certificat al SQL Server amb la següent comanda:

Select * from sys.certificates

Tenim la base de dades MASTER xifrada, anem a crear la clau de xifrat per la base de dades d’usuari (la que té les dades i que volem xifrar) amb la següent comanda:

Use Nom_Base_de_dades;
Go
Create Database Encryption Key With Algorithm=AES_256 Encryption by Server Certificate Certificat_TDE;
Go

Primer ens col·loquem dins la base de dades on s’ha de crear la clau (Use Nom_Base_de_dades) i generem una clau amb un algoritme de xifrat utilitzant el certificat que hem creat anteriorment al servidor. Sobre  els algoritmes de xifrat recordeu que són tant bons en quan costa aplicar força bruta per obtenir la clau. Els que teniu disponibles són els AES_128, AES_192 i AES_256, la resta, com bé sabeu, ja no s’han d’utilitzar perquè són dèbils. La meva recomanació, actualment, és utilitzar el AES_256.

Es crea el certificat amb una advertència:

Fes una còpia de seguretat del CERTIFICAT!!!!

En cas contrari, no es podrà accedir a les dades des d’un altre servidor o el mateix en cas de desastre.

 

 

Còpia de seguretat del certificat pel xifrat de base de dades

Consisteix en copiar el certificat (en el meu cas Certificat_TDE) i la clau privada del mateix en dos fitxers (C:\Certificats\Certificat_TDE i C:\Certificats\Certificat_TDE.pri) protegits per una contrasenya forta. Assegureu que la carpeta on voleu guardar els certificats s’hi pot escriure per part del compte de servei del SQL Server:

Use Master;
Go
Backup Certificate Certificat_TDE to File = 'C:\Certificats\Certificat_TDE'
    with Private Key (file='c:\Certificats\Certificat_TDE.pri', Encryption by Password='ContraseñaCopiaCertificado#17')

Explorant la carpeta on s’han d’haver creat els fitxers de còpia de seguretat del certificat: el certificat i clau privada. Guardeu aquests dos fitxers, amb la contrasenya de protecció que heu establert per poder recuperar-los, en un lloc segur i que no sigui en el mateix servidor SQL Server, és clar.

 

 

Xifrat de base de dades d’usuari

Tenim el servidor SQL Server i la base de dades d’usuari preparats per xifrar bases de dades, però encara no s’ha començat a xifrar res. Per fer-ho, cal habilitar el procés de xifrat amb la següent comanda o modificant les propietats de la base de dades des de la consola gràfica (Propietats  a la base de dades > Opcions > Estat > Xifrar base de dades):

Alter database Base_de_dades_X set encryption on;
Go

Ara si ha començat el procés de xifrat de base de dades d’usuari. Depenent de la quantitat de dades tardarà més o menys. Es pot comprovar el seu estat fent la següent consulta:

Select * from sys.dm_database_encryption_keys

Cal identificar la base de dades concreta, com que només en tenim una, apareix la MASTER i la d’usuari. Heu de comprovar el camp encryption_state que segons els seu valor indica:

  • 0 = No existeix una clau de xifrat a la base de dades, no està xifrat
  • 1 = No xifrat
  • 2 = S’està xifrant la base de dades
  • 3 = Xifrat
  • 4 = S’està canviant la clau de xifrat
  • 5 = S’està desxifrant
  • 6 = S’està canviant la protecció. El certificat o la clau asimètrica que ha xifrat la clau de xifrat de base de dades s’està modificant.

Felicitats, la foto indica un valor de 3 en les dues bases de dades, això vol dir que el seu contingut està xifrat!!!

No obstant, no vol dir que si hi tinc permisos no hi pugui accedir, al contrari, tot funciona amb normalitat i es pot treballar amb el seu contingut en clar:

El xifrat de base de dades TDE protegeix de la còpia dels fitxers de la base de dades a altres equips.

 

 

Còpia de seguretat de la base de dades xifrada

Amb la base de dades xifrada, heu d’assegurar que les còpies que se’n fan també van xifrades, sinó no té gràcia. Tot i què encara que el feu sense xifrar, com la base de dades sí està xifrada, veureu el contingut de la còpia, però NO la recuperació de la base de dades sense les claus i certificats corresponents.

A l’hora de de configurar una còpia de seguretat, per exemple, botó dret damunt la base de dades, seleccionar Tasques i Còpia de seguretat:

Cal eliminar qualsevol fitxer de còpies de seguretat anterior que hi pugui haver configurat, seleccionant-lo i clicant el botó d’esborrar.

Afegir un nou fitxer, a la ruta que desitgeu, on guardar la còpia de seguretat. Assegureu que poseu l’extensió .bak per no tenir després problemes amb els filtres de les extensions de fitxers que fa el SQL Server Management Studio.

En el menú de l’esquerra, clicar a Opcions del Medi. Seleccionar Fer còpia de seguretat en un conjunt de medis i esborrar tots els conjunts de còpies existents. Indicar un nom descriptiu pel conjunt de còpia de seguretat.

Seleccionar els piscus de:

  • verificar la còpia de seguretat quan acabi. És una bona pràctica.
  • fer un checksum abans d’escriure al dispositiu. Per si les mosques el què es guarda no és el mateix que l’original.
  • No recomano marcar continuar en cas d’error. Podria ser que es fes una còpia de seguretat corrupte i a l’hora de recuperar, poca cosa tindríem, vaja que no ens serviria de res.

Clicar l’opció Opcions de Còpia de seguretat.

Seleccionar el mètode de compressió de la còpia de seguretat. Marcar el piscu de xifrar la còpia de seguretat.

Seleccionar l’algoritme i el certificat o clau asimètrica. Es pot utilitzar el mateix certificat que el què s’ha utilitzat per xifrar la base de dades.

Clicar el botó OK per fer la còpia de seguretat.

Si tot es correcte, s’acaba de fer una còpia de seguretat xifrada de la base de dades.

 

Què passa si intentem recuperar aquesta còpia de seguretat en un altre servidor?

No obstant, la pregunta és si podem emportar-nos aquesta còpia de seguretat a un altre servidor, que no disposa de la clau mestra ni el certificat, i visualitzar-ne el contingut. Anem a provar a fer-ho. En un altre servidor, mitjançant la consola SQL Server Management Studio, botó dret damunt de la carpeta bases de dades i clicar a recuperar base de dades.

A l’opció General, marcar l’opció de dispositiu, clicar el botó per indicar el nom del fitxer, clicar el botó Afegir fitxer i seleccionar el fitxer de la còpia de seguretat que s’ha fet anteriorment:

Sorpresa, no es pot seleccionar cap base de dades que contingui aquest fitxer.

Per comprovar que tot funciona segons el què esperem que faci, repetim el procés amb una còpia de seguretat no xifrada, de la mateixa base de dades que sí està xifrada. Aquest cop sí se’ns permet seleccionar la base de dades i el punt de recuperació que conté la còpia de seguretat.

Però què passa a l’intentar restaurar-la? Simplement que, al no tenir la clau mestra ni el certificat de xifrat, no es pot recuperar la base de dades.

I, si en comptes de la recuperació de les dades mitjançant una còpia de seguretat, s’intenta fer pel mètode de desenganxar i enganxar (attach-desattach)? Amb la base de dades xifrada original, aturar el servei de SQL Server o desenganxar-les i copiar els DOS fitxers (mdf i ldf) de la base de dades en un altre servidor. Des de la carpeta de bases de dades del nou servidor, botó dret i clicar a Adjuntar.

Seleccionar el fitxer de base de dades amb extensió .mdf.

Mala sort, o no. No tenim la clau mestra de xifrat, per tant, tampoc podem accedir a les dades de la base de dades per aquest mètode.

Repetim el procés en el servidor origen, el què si tenia la clau de xifrat?

Això és una altra cosa, la base de dades xifrada es llegeix correctament el seu contingut i, per tant, es pot afegir com qualsevol altre.

Un cop afegida al servidor de base de dades, s’opera correctament, com si res hagués passat.

 

Eliminar certificat pel xifrat de bases de dades

En cas que no necessitem més el certificat pel xifrat de base de dades d’usuari és molt aconsellable eliminar-lo del sistema. Si una cosa no la necessites, no la guardes en un racó perquè un dia es giri en contra teva. Per fer-ho, des de la consola de SQL Server Management Studio obrim una nova consulta amb la següent comanda:

Use master;
go
Select * from sys.certificates;
Drop certificate Certificat_TDE
Select * from sys.certificates;

Tenint la base de dades xifrada original. La tornem a adjuntar a la base, però no hi ha el certificat que necessita per desxifrar-la, per tant, no es pot adjuntar de nou.

 

Recuperar el certificat pel xifrat de base de dades

Com a bons minyons, tenim una còpia de seguretat del certificat de xifrat guardada en un lloc segur. Des de la consola de SQL Server, executem la següent comanda per crear la clau mestra:

Use Master;
go
Create master key encryption by password='HolaEstoEsLaClaveDeCifrado#17';
go

El següent pas és generar el nou certificat a partir de la còpia de seguretat, indicant la ruta del certificat, la ruta del fitxer amb la clau privada i la contrasenya que es va posar quan es va fer la còpia de seguretat del certificat:

Use Master;
go
Create Certificate Certificat_TDE from file = 'c:\Certificat\Certificat_TDE' with Private key (File = 'c:\Certificat\Certificat_TDE.pri', Decryption By Password ='ContraseñaCopiaCertificado#17')

El servidor ha recuperat el certificat a la base de dades, ho comprovem:

Select * from sys.certificates;

Ara sí ja es pot adjuntar la base de dades xifrada. Botó dret damunt bases de dades, clicar a adjuntar. Seleccionar el fitxer .mdf amb la base de dades i clicar el botó OK.

Tornem a tenir la base de dades operativa!

 

NO Recuperar la base de dades xifrada en una edició estàndard

Què passa quan intentem recuperar la base de dades en una edició estàndard que no suporta el xifrat de bases de dades?

Tot i que ens deixa crear la clau, importar el certificat, carregar la còpia de seguretat xifrada de la base de dades i fins hi tot recuperar la base de dades, però no executar-la.

La base de dades quedarà restaurada però no servirà per a res al quedar marcada com a sospitosa.

Tingueu ben present en les instal·lacions!

 

Desxifrar base de dades xifrada

Si us heu cansat del xifrat de base de dades, o ha canviat la naturalesa de la mateixa, es pot desxifrar accedint a les propietats de la base de dades, Opcions > Estat > Xifrat activat; o bé amb la següent comanda:

Alter database Nom_Base_De_Dades
Set encryption off

Per comprovar que s’ha desxifrat correctament, el camp encryption_state ha de tenir com a valor el número 1:

Select * from sys.dm_database_encryption_keys

Finalment, per no deixar rastre del xifrat, toca esborrar la clau de xifrat de base de dades:

Drop database encryption key

Fetes aquestes operacions, la base de dades ja torna a estar en un format pla i accessible per a qualsevol.

 

 

Per acabar aquesta entrada i com a recomanació, us he de dir que no utilitzeu el mateix certificat per xifrar totes les bases de dades del servidor o servidors, que ens coneixem.

Un certificat per a cada base de dades, no un certificat que els governi a tots!

 

T’ha agradat l’article? El pots compartir a les xarxes socials. També pots deixar la teva opinió, comentari o suggeriment. Gràcies!

Similar Posts by The Author: