Titkosított adatbázisok helyreállítása

Régóta érik ez a bejegyzés, de sajnos sem időm, sem energiám nem volt eddig erre.

Feladat

Transparent Data Encryption által titkosított adatbázis(ok) helyreállítása másik szerveren az alábbi lehetséges okokból kifolyólag:

• Mirroring kialakítása,
• AlwaysOn Availability Group kialakítása,
• helyreállítás hardver hiba miatt,
• biztonsági mentés helyreállítási tesztje okán. Milyen könnyűnek tűnik a dolog, és egyébként az is, de sajnos ez az SQL Server 2008 R2 dokumentációjából kimaradt.

Az egyszerűség kedvéért 2 helyi instance – DEV01 és DEV02 – segítségével fogom bemutatni a helyreállítás lépéseit. A DEV01 szerveren létrehozunk egy adatbázist TDEDB néven, majd ezt titkosítom, illetve a DEV02-ön fogom helyreállítani.

TDE beállítás

Az alábbi lépések segítségével hozzunk létre egy adatbázist, majd a MASTER KEY-re és egy tanúsítványra lesz szükségünk a titkosításhoz:

1:CONNECT .\DEV01
2USE [master]
3GO
4CREATE DATABASE TDEDB;
5GO
6CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd1';
7GO
8CREATE CERTIFICATE ServerCert WITH SUBJECT = 'Server Certificate for TDE';
9GO

Az adatbázis titkosításához az instance szintű lépésekkel kész vagyunk. Ezek után a TDEDB adatbázisban kell létrehozni egy kulcsot, amely a tikosításhoz lesz használva. Ezt a kulcsot a fentebb létrehozott tanúsítvánnyal, majd ezzel a kulccsal az adatbázist fogom titkosítani:

1USE [TDEDB]
2GO
3CREATE DATABASE ENCRYPTION KEY
4WITH ALGORITHM = AES_128
5ENCRYPTION BY SERVER CERTIFICATE ServerCert;
6GO
7ALTER DATABASE TDEDB SET ENCRYPTION ON;
8GO

Ha megnézem a sys.databases DMV-t látni fogom, hogy az adatbázisom titkosítva van (megjegyzem, hogy itt azért ilyen gyors a tikosítás, mert szinte üres adatbázisra állítottam be).

1SELECT is_encrypted FROM sys.databases WHERE [name] = 'TDEDB'
2GO

Mentés és helyreállítás

Mostmár van egy titkosított adatbázisunk, ami a mentés során is tikosítva marad. Ez egy tök jó dolog, mert nem lehet csak úgy helyreállítani az adatbázist, tehát egy esetleges mentési médium eltűnése nem okozhat túlzott izgalmat :-).

Mit kell menteni

Ahhoz, hogy egy ilyen TDE tikosított adatbázist a későbbiekben is helyre lehessen állítani, szükséges az alábbi objektumok mentése:

Master Key (a visszaállításhoz ugyan nem kell, de nem baj, ha mentjük), titkosításhoz használt tanúsítvány a privát kulccsal együtt, a titkosított adatbázis (meglepő, de igaz ;-), adatbázis mentés nélkül nincs lehetőség helyreállításra). Ezeket lehetőleg ne tartsuk egy helyen, értelemszerűen, a biztonság további fokozása érdekében.

Tanúsítvány mentéséhez az alábbi script nyújt segítséget:

1BACKUP CERTIFICATE ServerCert TO FILE = 'c:\Databases\Backup\dev01Server.cert'
2WITH PRIVATE KEY
3(
4    FILE = 'c:\Databases\Backup\dev01ServerCertprivate.key',
5    ENCRYPTION BY PASSWORD = 'Pa$$w0rd1'
6);

Az adatbázist pedig a következő script segítségével mentem el:

1BACKUP DATABASE TDEDB TO DISK = N'C:\Databases\Backup\tdedb.bak'
2GO

Most, hogy ezekkel kész vagyunk, elkezdhetjük helyreállítani az adatbázist egy másik szerveren.

Helyreállítás

Próbáljuk meg az adatbázis mentésünk tartalmát megnézni a FILELISTONLY opcióval a DEV02 szerveren:

1RESTORE FILELISTONLY FROM DISK = N'c:\Databases\Backup\tdedb.bak';

Az eredmény várható volt, nem lehet:

Msg 33111, Level 16, State 3, Line 1 Cannot find server certificate with thumbprint '0x3B500000000000000000000000000000000000F6'. Msg 3013, Level 16, State 1, Line 1 RESTORE FILELIST is terminating abnormally.

Most próbáljuk meg helyreállítani:

1RESTORE DATABASE TDEDB FROM DISK = N'C:\Databases\Backup\tdedb.bak'
2WITH NORECOVERY,
3MOVE 'TDEDB' TO 'c:\Databases\TDEDB.mdf',
4MOVE 'TDEDB_log' TO 'c:\Databases\TDEDB_log.LDF'

Ebben az esetben is az előző hibaüzenetet kapjuk, hogy nem találja a tikosításhoz használt tanúsítványt.

Hát, ha nincs meg, akkor állítsuk helyre a mentésünkből, miután létrehoztunk egy új MASTER KEY-t:

 1USE [master]
 2GO
 3CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd2';
 4GO
 5OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Pa$$w0rd2';
 6CREATE CERTIFICATE ServerCert FROM FILE = 'c:\Databases\Backup\dev01Server.cert'
 7WITH PRIVATE KEY (FILE = 'c:\Databases\Backup\dev01ServerCertprivate.key',
 8    DECRYPTION BY PASSWORD = 'Pa$$w0rd1');
 9CLOSE MASTER KEY;
10GO

Szándékosan hoztam létre egy új MASTER KEY-t. Ezzel is bizonyítva, hogy a megfelelő tanúsítvány mentések segítségével, a különböző szerverekről hozott TDE titkosított adatbázisok megférnek egymás mellett.

Ezek után már sima ügy az adatbázis visszaállítása:

 1OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Pa$$w0rd2';
 2RESTORE FILELISTONLY FROM DISK = N'c:\Databases\Backup\tdedb.bak';
 3 
 4RESTORE DATABASE TDEDB FROM DISK = N'C:\Databases\Backup\tdedb.bak'
 5WITH NORECOVERY,
 6MOVE 'TDEDB' TO 'c:\Databases\TDEDB.mdf',
 7MOVE 'TDEDB_log' TO 'c:\Databases\TDEDB_log.LDF'
 8 
 9RESTORE DATABASE TDEDB WITH RECOVERY
10CLOSE MASTER KEY;
11 
12SELECT is_encrypted FROM sys.databases WHERE [name] = 'TDEDB';
13GO

Ismétlésként a legfontosabbak:

• Az adatbázis titkosításhoz használt tanúsítványt mindig a privát kulccsal együtt mentsük el és állítsuk helyre,
• ne tároljuk a mentésekkel együtt a tanúsítványt és a privát kulcsot,
• különböző szerverekről mentett TDE adatbázisokat is vissza lehet állítani egy szerveren.