generate qr code c# .net Transparent Data Encryption in C#.NET

Creator QR Code in C#.NET Transparent Data Encryption

Transparent Data Encryption (TDE) allows us to encrypt the entire database without requiring any changes to the structure of the database or the applications that access it. It protects the database in situations where someone breaches physical and login security and obtains access to the .mdf (data) files or .bak (backup) files. Without TDE or another third-party encryption solution, the files could be taken offsite and attached or restored. Later in this section we ll look at some of the restrictions of TDE that may limit its usefulness in certain situations. For the moment, though, let s take a look at implementing TDE with T-SQL scripts. ENCRYPTING A DATABASE The first step in implementing TDE is in creating a master key. Intended to protect the private keys of certificates and other keys, the master key is created as a symmetric key using the Triple DES algorithm along with a password supplied by the user creating it:

-- Create a Master Key USE MASTER GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'jGKhhg6647##tR'; GO

Next, we create a certificate, used to protect the database encryption key, which we ll create shortly:

-- Create a Certificate USE MASTER GO CREATE CERTIFICATE tdeCertificate WITH SUBJECT = 'TDE Certificate'; GO

At this point, it s crucial that we back up the certificate. When a TDE-encrypted database is backed up, the backup itself is encrypted. If we want to restore an encrypted database to another server, the certificate used to encrypt the database needs to be loaded to the other server to enable the database to be restored. Further, should we suffer a catastrophic server failure, the newly installed server will also require the certificate in order to restore the database.

The certificate backup should be stored in a secure location, and ideally separated from both the database backups and private key backup. We can back up the certificate and private key as follows:

-- Backup the certificate -- Required if restoring encrypted databases to another server -- Also required for server rebuild scenarios USE MASTER GO BACKUP CERTIFICATE tdeCertificate TO FILE = 'g:\cert\tdeCertificate.backup' WITH PRIVATE KEY (FILE = 'e:\cert\tdeCertificatePrivateKey.backup', ENCRYPTION BY PASSWORD = 'jjKiid_%%4-9') GO

Now, let s change focus from the master database to the database we want to encrypt (AdventureWorks in this example) and create the database encryption key (DEK), used for encrypting the database with Transparent Data Encryption:

-- Create a Database Encryption Key USE [AdventureWorks2008] GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE tdeCertificate GO

In this example, we used the AES encryption algorithm with a 128-bit key. In addition, 192- and 256-bit keys are supported, as well as Triple DES. Now that we ve created our DEK, we can encrypt the database:

-- Encrypt the database using Transparent Database Encryption (TDE) -- Encryption will proceed as a background task -- Use the sys.dm_database_encryption_keys DMV to check progress ALTER DATABASE [AdventureWorks2008] SET ENCRYPTION ON GO

The encryption process will now start as a background task. During this time, some functions, such as modifying the database files and detaching the database, won t be available. The sys.dm_database_encryption_keys Dynamic Management View (DMV), fully described in BOL, can be used to inspect the progress of the encryption process. Finally, earlier we discussed the need to back up the certificate for recovery purposes and to enable encrypted databases to be restored to another server. Attempting to restore a backup of a TDE-encrypted database to another server that doesn t have the appropriate certificate installed will result in failure of the restore process, resulting in an error like that shown in figure 6.15. Let s take a quick look at the process of restoring a certificate on another server in preparation for restoring an encrypted database: