SQL Server TDE, Encrypted Backup Interview Questions

by

Recently we worked with one of customer who wanted to implement TDE and Encrypted backups on his production SQL Servers. He had so many questions on encryption and decryption process. Some questions might look silly but we had to test things to be able to provide answers.

Refer existing articles for TDE and Encrypted backups:

Before putting those few questions, let me first put TDE encryption/decryption and Encrypted backup/restore process below:

We follow below process at source server to implement TDE or to take encrypted backups.

--Below steps are common for encrypted backups as well as TDE. 

-- create master key and certificate

USE MASTER;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Encrypt@2023';
GO

CREATE CERTIFICATE EncryptBackup
    WITH SUBJECT = 'Backup Encryption Certificate';
GO

-- Take backup of certificate to a file, keep password in repository

BACKUP CERTIFICATE EncryptBackup TO FILE = 'D:\EncryptBackup.cert'
WITH PRIVATE KEY (
FILE = 'D:\EncryptBackup.key',
ENCRYPTION BY PASSWORD = 'Encrypt@2023')
-- This step is to be followed only for encrypted backups at source server 
-- Take encrypted backup of any database using above encryption & certificate.
-- You can use different algorithm as well here 

BACKUP DATABASE DBNAME
TO DISK = 'D:\DBNAME.bak'
WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = EncryptBackup)

--This step is to be followed only for enabling TDE on any database at source server 
--Enable TDE on any database using above encryption & certificate.

USE <DBNAME>
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE EncryptBackup;
GO

ALTER DATABASE <DBNAME>
SET ENCRYPTION ON;
GO

everyday we need to restore encrypted backup or backup of TDE enabled database on another database server. Normally , we follow below process at target database server to do restores.

-- create master key and certificate on target instance
 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Encrypt@2023';
GO

-- restore the certificate using certificate backups taken on source server.

CREATE CERTIFICATE EncryptBackup
FROM FILE = 'D:\EncryptBackup.cert'
WITH PRIVATE KEY (FILE = 'D:\EncryptBackup.key',
DECRYPTION BY PASSWORD = 'Encrypt@2023');
GO

--Now run restore , it will work without any issues be it of TDE or encrypted backup.

Lets come to questions :

  1. Can we restore certificate on target database server using different name and still perform restores?
CREATE CERTIFICATE OtherName
FROM FILE = 'D:\EncryptBackup.cert'
WITH PRIVATE KEY (FILE = 'D:\EncryptBackup.key',
DECRYPTION BY PASSWORD = 'Encrypt@2023');
GO

2. Can we create master key on source and target database servers using different passwords?

3. Can we take encrypted backup of TDE enabled database ? If yes, what need to be done to restore that on another database server?

4. How does enabling TDE impact performance?

5. How does enabling TDE on a database impact tempdb database ?

Please post your answers in comment sections. I will post my answers after a week.

Post Views: 132