SQL Server 2014 CTP2: Backup encryption

Ma, az SQL Summit-on bejelentették az SQL Server 2014 CTP2-t. Ez az utolsó publikus verzió az RTM előtt. Túl sok újdonságot nem hozott a CTP1 óta, sőt akár azt is mondhatnánk, hogy az adatbázis motor nem nagyon fog változni, befejezték a fejlesztését. Természetesen azért van pár újdonság a 2014-es verzióban, de ami számomra most a legérdekesebb volt, az a mentés titkosítása.

Eddig a Transparent Data Encryption bekapcsolásával lehetett elérni azt, hogy a mentés is titkosítva legyen. A mentés visszaállítása ebben az esetben sem egy egyszerű dolog, van pár lépés, amit nem lehet kihagyni; lásd egy régebbi bejegyzésem: Titkosított adatbázisok helyreállítása.

A mostani backup encryption hasonló módon működik, azzal a különbséggel, hogy az éles adatbázis nincs titkosítva a szerveren, csak a mentés.

Lássunk erre egy példát:

• Először létrehozom a service master key-t,
• létrehozom a tanúsítványt, amit a titkosításhoz fogok használni, majd
• elmentem a privát kulccsal együtt a tanúsítványt (erre szükségem van, ha vissza kell állítanom egy új szerveren az adatbázist),
• A BACKUP DATABASE parancs ENCRYPTION opciójával elmentem az adatbázist.

 1USE [master];
 2GO
 3CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
 4GO
 5
 6USE [master];
 7GO
 8CREATE CERTIFICATE [BackupCert]
 9WITH SUBJECT = 'Backup cert', EXPIRY_DATE = '20201231';
10GO
11
12USE [master];
13GO
14BACKUP CERTIFICATE [BackupCert] TO FILE = 'c:\temp\sql.cer'     
15WITH PRIVATE KEY
16(     
17FILE = 'c:\temp\sql.pvk' ,      
18ENCRYPTION BY PASSWORD = 'Pa$$w0rd'
19); 
20GO
21
22BACKUP DATABASE [TestDb] TO  DISK = N'C:\temp\testdb.bak'
23WITH NOFORMAT, INIT,  NAME = N'TestDb-Full Database Backup',
24SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10,
25ENCRYPTION
26(
27   ALGORITHM = AES_256,
28   SERVER CERTIFICATE = BackupCert
29)
30GO

A vicces része a dolognak, hogy az SSMS (SQL Server Management Studio) segítségével nem tudom ezt megtenni, mivel nem működik ez az opciója ezt még nem fejezték be.

Honnan látom, hogy titkosítva van a mentésem? Sajnos se a RESTORE HEADERONLY, se a RESTORE FILELISTONLY parancsok nem mutatják ezt meg nekem. Az egyetlen hely, ahol ez elérhető az az msdb adatbázisban a dbo.backupset tábla, amelyből az alábbi lekérdezés segítségével megkapjuk az információt (ha ezt nem töröljük ki rendszeres időközönként):

 1SELECT
 2    backup_set_id,
 3    name,
 4    database_name,
 5    key_algorithm,
 6    encryptor_type
 7FROM
 8    msdb..backupset
 9WHERE
10    key_algorithm IS NOT NULL

Az eredmény az alábbi lett:

Mi van akkor, ha vissza kell állítanom az adatbázist? Semmi különös, ugyan azokra a lépésekre van szükség, mint a TDE adatbázisok helyreállításánál:

• service master key létrehozása,
• szükség van a titkosításhoz használt kulcsra, ennek a visszaállítására,
• végül az adatbázis helyreállításra.

 1USE [master]
 2GO
 3CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd2';
 4GO
 5OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Pa$$w0rd2';
 6CREATE CERTIFICATE BackupCert FROM FILE = 'c:\temp\sql.cer'
 7WITH PRIVATE KEY (FILE = 'c:\temp\sql.pvk',
 8    DECRYPTION BY PASSWORD = 'Pa$$w0rd');
 9CLOSE MASTER KEY;
10GO
11 
12USE [master];
13GO
14RESTORE DATABASE [TestDb] FROM  DISK = N'C:\temp\testdb.bak' WITH  FILE = 1, 
15NOUNLOAD,  STATS = 5
16GO

Ha valamit rosszul csináltunk vagy csak a nem megfelelő tanúsítvány lett helyreállítva, akkor az alábbihoz hasonló hibaüzenetet kapunk:

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

Fontos megemlíteni, hogy ezzel a megoldással csak a mentés kerül titkosításra, az adatbázis file a szerveren nem. Ezáltal pár kellemetlen meglepetéstől és teljesítménybeli problémától kímélhetjük meg magunkat. Mindaddig, amíg a tanúsítványokról is megvan a mentés és ezt külön tároljuk az adatbázistól, nyugodtak lehetünk, hogy csak azok tudják visszaállítani, akik erre jogosultak, illetve egyáltalán lesz lehetőség a visszaállításra. Ne feledjük el: mentés nélkül nem lehet visszaállítani adatbázist!