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: 
  1. AES_128
  2. AES_192
  3. AES_256
  4. 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

Popular posts from this blog

What is DACPAC and how to use it?

Backdoor in SQL server. Is this hacking, loop hole or Feature ?

Always on setup end to end - Part 1 || Pre-requisites