Configure data access and auditing || a - iii
Qsn) How to Implement Encrypted backup
Ans :
To create an encrypted
backup an encryption algorithm as shown in the following steps:
- Create a master key.
- Create or obtain a
certificate protected by the master key.
- Create a database
backup and protect it with the certificate.
- The following
encryption algorithms can be used:
- AES_128
- AES_192
- AES_256
- TRIPLE_DES_3KEY
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD =
'XYZ@123;
CREATE CERTIFICATE BackupCertificate WITH SUBJECT = 'certificte_bkp';
BACKUP DATABASE [trans_DB]
TO DISK = N'D:\SQLBackupCRT\trans_DB_Certificate.bak'
WITH ENCRYPTION (ALGORITHM = AES_256,SERVER CERTIFICATE = certificte_bkp);
Qsn) How to
restore Encrypted backup on database?
Ans :
In order to
perform a successful restore, we'll need the database master key in the master
database in place and we'll need to restore the certificate used to encrypt the
database, but we'll need to make sure we restore it with the private key. In
checklist form:
- There's a database
master key in the master database.
- The certificate used
to encrypt the database is restored along with its private key.
- The database is restored.
Since we have
the database master key, let's do the final two steps. Of course, since we have
to clean up the previous certificate, we'll have a drop certificate in the
commands we issue:
-- Let's do
this one more time. This time, with everything,
-- Including
the private key.
DROP CERTIFICATE TDECert;
GO
-- Restoring the
certificate, but with private key.
CREATE CERTIFICATE TDECert
FROM FILE = 'C:\SQLBackups\TDECert.cer'
WITH PRIVATE KEY (
FILE = N'C:\SQLBackups\TDECert_key.pvk',
DECRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'
);
GO
-- We have the correct
certificate and we've also restored the
-- private key. Now
everything should work. Finally!
RESTORE DATABASE [RecoveryWithTDE]
FROM DISK = N'C:\SQLBackups\RecoveryWithTDE_Full.bak'
WITH MOVE 'RecoveryWithTDE' TO N'C:\SQLData\RecoveryWithTDE_2ndServer.mdf',
MOVE 'RecoveryWithTDE_log' TO N'C:\SQLData\RecoveryWithTDE_2ndServer_log.mdf';
GO
With everything
in place, we are finally successful!
Comments
Post a Comment