I am Berke

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.

USE [master];
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
GO

USE [master];
GO
CREATE CERTIFICATE [BackupCert]
WITH SUBJECT = 'Backup cert', EXPIRY_DATE = '20201231';
GO

USE [master];
GO
BACKUP CERTIFICATE [BackupCert] TO FILE = 'c:\temp\sql.cer'      
WITH PRIVATE KEY 
(      
FILE = 'c:\temp\sql.pvk' ,       
ENCRYPTION BY PASSWORD = 'Pa$$w0rd' 
);  
GO

BACKUP DATABASE [TestDb] TO  DISK = N'C:\temp\testdb.bak' 
WITH NOFORMAT, INIT,  NAME = N'TestDb-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10,
ENCRYPTION 
(
	ALGORITHM = AES_256,
	SERVER CERTIFICATE = BackupCert
)
GO

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):

SELECT 
	backup_set_id, 
	name, 
	database_name,
	key_algorithm,
	encryptor_type
FROM 
	msdb..backupset
WHERE
	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.

USE [master]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd2';
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Pa$$w0rd2';
CREATE CERTIFICATE BackupCert FROM FILE = 'c:\temp\sql.cer'
WITH PRIVATE KEY (FILE = 'c:\temp\sql.pvk', 
    DECRYPTION BY PASSWORD = 'Pa$$w0rd');
CLOSE MASTER KEY;
GO

USE [master];
GO
RESTORE DATABASE [TestDb] FROM  DISK = N'C:\temp\testdb.bak' WITH  FILE = 1,  
NOUNLOAD,  STATS = 5
GO

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!