Στην σημερινή εποχή η ασφάλεια των δεδομένων είναι και πρέπει να αποτελεί υψηλή προτεραιότητα για κάθε DBA/IT Pro.

Η ολοένα και αυξανόμενη τάση να μεταφέρουμε τα δεδομένα μας στο Cloud πρέπει να μας κάνει ιδιαίτερα προσεκτικούς στο πώς ανεβαίνει αυτή πληροφορία .Αν αποκτήσει κάποιος μη εξουσιοδοτημένη πρόσβαση σε αυτά τα δεδομένα μπορεί πολύ εύκολα να τα κάνει restore και να πάρει την πληροφορία που θέλει.

Δεν αρκεί λοιπόν μια βάση να τρέχει αποδοτικά και να βρίσκεται σε ένα ασφαλές περιβάλλον Με το Backup τι γίνεται;

Υπάρχει ένα γνωμικό που λέει ‘DBAs get paid for performance but keep their job with recovery!

Από το SQL Server 2014 και πάνω μας δίνεται η δυνατότητα να παίρνουμε κρυπτογραφημένο Backup.

Σε αυτό το άρθρο θα σας παρουσιάσω end to end πως να κρυπτογραφήσετε το Backup των βάσεων σας σας και πώς να κάνετε restore το κρυπτογραφημένο Backup από το storage στο οποίο φυλάσσεται.

Για το παράδειγμά μου θα χρησιμοποιήσω ένα SQL Server 2017 instance αλλά μπορείτε να κάνετε χρήση των script και του συγκεκριμένου Feature από τον SQL Server 2014 και πάνω.

Αρχικά με το step -1 ας δημιουργήσουμε μια sample database και ένα πίνακα όπου σε αυτόν κάνουμε Insert μερικές εγγραφές.

Σε αυτό το Demo θα κάνουμε χρήση του Certificate.

Στην συνέχεια και στο step-2 δημιουργώ ένα MASTER KEY.Το MASTER KEY χρειάζεται γιατί ο SQL Server κρυπτογραφεί με αυτό το CERTIFICATE κατά την δημιουργία του.

Στο step -3 τρέχω το script για την δημιουργία του Certificate και του δίνουμε το όνομα του SUBJECT.

Με το script στο step-4 μπορούμε να δούμε τα metadata του certificate κάνοντας select στον πίνακα sys.certificates που βρίσκεται στην master Database. O πίνακας sys.certificates περιέχει τρείς κολόνες.

Η κολόνα pvt_key_encryption_type έχει τον τύπο της κρυπτογράφησης του CERTIFICATE που είναι το ΜΚ (Master Key) ,το name και το subject είναι τα ονόματα που δώσαμε κατά την δημιουργία του script στο step-3.

Στο step-5 εκτελώ το backup με την παράμετρο WITH ENCRYPTION που συνοδεύεται μέσα σε παρένθεση από τον αλγόριθμο κρυπτογράφησης AES256 και το όνομα του Certificate που δημιουργήσαμε στο Step-4.

Αν την τρέξετε και εσείς θα δείτε ότι σας εμφανίζει ένα Warning ότι δεν έχουμε πάρει Backup το certificate και το private key που σχετίζεται με αυτό.

sos-sos-sos

Αυτό είναι και το ποιο σημαντικό step. Το certificate πρέπει να το κάνετε export σε ένα αρχείο σε περίπτωση που χρειαστεί να κάνετε Restore την βάσης σας σε έναν άλλο Server ή σε κάποιο άλλο instance.Αν δεν υπάρχει το certificate το restore της database σας δεν θα μπορέσει να γίνει.Να σημειώσω εδώ ότι για να εκτελεστεί η BACKUP CERTIFICATE το Login πρέπει να έχει CONTROL PERMISSION δικαιώματα.

Στο step-6 εκτελώ το script για να κάνουμε export το certificate με την παράμετρο WITH PRIVATE KEY που θα κρυπτογραφήσει το certificate file.Στο private key δίνουμε ένα password με το όνομα του και το path στο οποίο θα αποθηκευτεί.

Στο επόμενο step από ένα άλλο instance θα κάνουμε το Restore της encrypted database.

Πριν από αυτό το σημείο θα κάνουμε μια παύση και θα θέσω ένα ερώτημα.

Τί γίνεται στην περίπτωση που αρκετά δεδομένα εισαχθούν στην βάση μετά το encrypted Full Backup που πήραμε προηγουμένως και η βάση μας εντωμεταξύ γίνει Corrupt και πρέπει να κάνουμε recover αυτές τις εγγραφές;

USE master;
GO
BACKUP LOG BackupDBEncryption
TO DISK = 'c:\backup\BackupDBEncryption.log'
WITH CONTINUE_AFTER_ERROR,ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = BackupDBEncryptionCert)

Recreate certificates and Master key & Restore Database to a New Instance

Συνεχίζουμε στο step-7 που από ένα άλλο instance θα πρέπει να κάνουμε recreate το master key και τα certificates.Στο script θα δείτε ότι για την δημιουργία του certificate γίνεται χρήση της παραμέτρου FROM FILE συνοδευόμενο από το Path που είναι αποθηκευμένο το αρχείο με το extension .key

Τέλος στο step-8 θα κάνουμε restore την βάση με την RESTORE DATABASE όπως θα κάναμε σε οποιαδήποτε άλλη περίπτωση και επίσης με την RESTORE LOG το t-log backup που πήραμε μετά το database corruption για να φέρουμε και τις τελευταίες εγγραφές που μας λείπουνε .

Έχοντας όλα τα παραπάνω υπόψιν δεν μας εμποδίζει τίποτα από το να κάνουμε backup encryption και να το εντάξουμε στις καθημερινές μας διαδικασίες.

--Step1

CREATE DATABASE BackupDBEncryption;
GO
USE BackupDBEncryption;
GO
CREATE TABLE T1 (
    ID int IDENTITY(1,10000) PRIMARY KEY NOT NULL,
    value int
);
GO
CREATE PROCEDURE T1_insert
AS
DECLARE @i int = 1
WHILE @i <100
    BEGIN
        INSERT t1 (value) VALUES (@i)
        Set @i +=1
    END
GO
EXECUTE T1_insert;
GO
SELECT * FROM T1;
GO

-- Step-2

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '(MasterKeyEncryption!!SQLschool)'

-- Step-3

CREATE CERTIFICATE BackupDBEncryptionCert
    WITH SUBJECT = 'BackupDBEncryption Backup Certificate';
GO
-- Step-4

SELECT name, pvt_key_encryption_type, subject
FROM sys.certificates

-- Step-5

BACKUP DATABASE BackupDBEncryption
TO DISK = 'C:\backup\BackupDBEncryption.bak'
WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = BackupDBEncryptionCert)

-- Step-6

BACKUP CERTIFICATE BackupDBEncryptionCert TO FILE = 'c:\BackupCert\BackupDBEncryptionCert.cert'
WITH PRIVATE KEY (
FILE = 'c:\backup\ BackupDBEncryptionCert.key',
ENCRYPTION BY PASSWORD = 'SqlBackschool123!!!!@')

-- Step-7

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '(MasterKeyEncryption!!SQLschool)'
CREATE CERTIFICATE BackupDBEncryptionCert
FROM FILE = 'c:\restorecert\BackupDBEncryptionCert.cert'
WITH PRIVATE KEY (FILE = 'c:\restorecert\BackupDBEncryptionCert.key.key',
DECRYPTION BY PASSWORD = 'SqlBackschool123!!!!@');
GO

-- Step-8

RESTORE DATABASE [BackupDBEncryption] FROM  DISK = N'C:\restore\BackupDBEncryption.bak' WITH NORECOVERY,
MOVE BackupDBEncryption TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\ BackupDBEncryption.mdf', 
MOVE BackupDBEncryption _Log TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\ BackupDBEncryption_log.ldf', 
REPLACE, STATS = 10;
GO
RESTORE LOG BackupDBEncryption
FROM DISK = 'c:\Program Files\ BackupDBEncryption.log';
GO

Εύχομαι Καλό Πάσχα σε όλους σας!