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:
:CONNECT .\DEV01
USE [master]
GO
CREATE DATABASE TDEDB;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd1';
GO
CREATE CERTIFICATE ServerCert WITH SUBJECT = 'Server Certificate for TDE';
GO
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:
USE [TDEDB]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE ServerCert;
GO
ALTER DATABASE TDEDB SET ENCRYPTION ON;
GO
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).
SELECT is_encrypted FROM sys.databases WHERE [name] = 'TDEDB'
GO
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:
BACKUP CERTIFICATE ServerCert TO FILE = 'c:\Databases\Backup\dev01Server.cert'
WITH PRIVATE KEY
(
FILE = 'c:\Databases\Backup\dev01ServerCertprivate.key',
ENCRYPTION BY PASSWORD = 'Pa$$w0rd1'
);
Az adatbázist pedig a következő script segítségével mentem el:
BACKUP DATABASE TDEDB TO DISK = N'C:\Databases\Backup\tdedb.bak'
GO
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:
RESTORE 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:
RESTORE DATABASE TDEDB FROM DISK = N'C:\Databases\Backup\tdedb.bak'
WITH NORECOVERY,
MOVE 'TDEDB' TO 'c:\Databases\TDEDB.mdf',
MOVE '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:
USE [master]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd2';
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Pa$$w0rd2';
CREATE CERTIFICATE ServerCert FROM FILE = 'c:\Databases\Backup\dev01Server.cert'
WITH PRIVATE KEY (FILE = 'c:\Databases\Backup\dev01ServerCertprivate.key',
DECRYPTION BY PASSWORD = 'Pa$$w0rd1');
CLOSE MASTER KEY;
GO
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:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Pa$$w0rd2';
RESTORE FILELISTONLY FROM DISK = N'c:\Databases\Backup\tdedb.bak';
RESTORE DATABASE TDEDB FROM DISK = N'C:\Databases\Backup\tdedb.bak'
WITH NORECOVERY,
MOVE 'TDEDB' TO 'c:\Databases\TDEDB.mdf',
MOVE 'TDEDB_log' TO 'c:\Databases\TDEDB_log.LDF'
RESTORE DATABASE TDEDB WITH RECOVERY
CLOSE MASTER KEY;
SELECT is_encrypted FROM sys.databases WHERE [name] = 'TDEDB';
GO
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.