Encryption Examples

TDE -- Transparent Data Encryption

Below are the steps for TDE

1. Create master key for the database

2. Create certificate that protected by master key

3. Create a special key Database Encryption Key that used to protect database, and secure it using certificate.

4. Enable encryption on database.

use master
create master key encryption by password = 'calyansql@123'
create certificate encryption_cert with subject = 'KalyanDB_Certificate'

use KalyanDB
create database encryption key with algorithm = AES_128 encryption by server certificate encryption_cert
--Alogirthm can be AES_128, AES_192, AES_256, or Triple_DES_3Key

Alter database KalyandB set encryption on

-- Backup the certificate is very important step to move this to target server while restoring the Encrypted Database

use master

backup certificate encryption_cert to file ='c:\temp\encryption1_cert.cert'
with private key (file='c:\temp\encryptionprivatekey.key', encryption by password='calyansql@123')

backup database kalyandb to disk='c:\temp\kalyandb.bak'
select * from sys.certificates

How to restore encrypted database on target server?

1. Move database backup and certificate files to target server
2. Create master key
3. Create certificate using the transferred file from the source server

Example

use master
create master key encryption by password='calyansql@123'
create certificate encryption_cert from file='E:\temp\encryption_cert.cert'
with private key (file='E:\temp\encryptionprivatekey.key', Decryption by password='calyansql@123')

After creating certificate we can restore the database in normal way, without creating the certificate we cannot do restore.