go backarticles

Articles of SQLschool.gr Team

Encrypting SQL Server Backup & Restore with Certificate

Kyriakos Petsalaris

Στην σημερινή εποχή η ασφάλεια των δεδομένων είναι και πρέπει να αποτελεί υψηλή προτεραιότητα για κάθε 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 μερικές εγγραφές.

Για να κάνουμε encrypt την βάση μας χρειαζόμαστε ένα certificate ή ένα asymmetric key.

Σε αυτό το 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 που σχετίζεται με αυτό.


Image-1
image

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 αυτές τις εγγραφές;

Σε αυτή την περίπτωση θα πρέπει να πάρουμε ένα Tail-Log Backup με το παρακάτω script πάλι με το certificate που δημιουργήσαμε στο step-3

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

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


Kyriakos Petsalaris

Kyriakos Petsalaris

Kyriakos is a Multifaceted Information Technology professional with a background in Information Technology Management. An experienced database and systems administrator, who also establishes clear objectives for team members and enforces deadlines to keep projects on track. Well versed in personnel training and supporting servers,Azure based Systems and Virtual Enviroments. He can do a bit of everything, but he has a passion for SQL Server and love troubling resolving live site incidents as well engineering continuous improvements to avoid repeat incidents. He is also skilled with writing, performance-tuning and debugging T-SQL, and developing automated and scalable maintenance/monitoring procedures to ensure optimal uptime across multiple systems. He has 20+ years experience as a Systems Engineer with specific expertise in SQL Server (from SQL 2005 to 2019). He works with both on-premise and Azure-based, customer-facing services including several Microsoft’s Products and have experience collaborating remotely with global teams in Bahrein,South Africa, Egypt and England.

  

Comments

user-gravatar

On 28 Apr 2020 @ 1:32 PM Athos K. MCP,MCSA wrote:

Thank you, great Article

Leave your comment

COMMENT

FULL NAME

EMAIL ADDRESS

We use Gravatar

WEB SITE



captcha


 

Newsletter

If you want to receive updates from us subscribe below with your email.
Follow us in
PASS chapter logo

The Official PASS Local Group for Greece

About us Contact us Terms of Use Privacy Sing in Register
sql school greece logo
© 2010-2020 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.