Assymetric Encryption – Remove TDE

ALTER DATABASE DatabaseName
SET ENCRYPTION OFF;
GO
/* Wait for decryption operation to complete, look for a
value of  1 in the query below. */

SELECT encryption_state, d.name, percent_complete
FROM sys.dm_database_encryption_keys e
INNER JOIN
sys.databases d ON
d.database_id = e.database_id;
GO
USE DatabaseName;
GO
DROP DATABASE ENCRYPTION KEY;
GO

USE master
go
DROP CERTIFICATE TDE_Certificate
go
DROP MASTER KEY
go

Note: IF All of tde is removed from the server, TEMPDB will still show up as encrypted. This is by design to make sure any residual data from an encrypted database within the TempDB is still encrypted and secured – even if the actual database no longer resides on that server. Tempdb will be free from any encryption on a restart of sql server.

 

Assymmetric Encryption – Useful DMVs

–Retrieving a list of certificates installed in the current database
SELECT
name,
certificate_id,
pvt_key_encryption_type_desc,
subject,
cert_serial_number,
start_date,
expiry_date,
thumbprint
FROM sys.certificates;

–Listing encrypted databases
SELECT
DB_NAME(database_id) AS database_name,
database_id,
CASE encryption_state
WHEN 0 THEN N’No database encryption key present, no encryption’
WHEN 1 THEN N’Unencrypted’
WHEN 2 THEN N’Encryption in progress’
WHEN 3 THEN N’Encrypted’
WHEN 4 THEN N’Key change in progress’
WHEN 5 THEN N’Decryption in progress’
END AS encryption_state,
key_algorithm,
key_length,
percent_complete
FROM sys.dm_database_encryption_keys;

 

Assymetric Encryption – Moving an encrypted database to another server

Step 1: Create Master Key - Only if one doesn't already exist in the 
destination.
Note: One Master Key per Instance
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1';
GO
Step 2: Restore/Recreate the certificate used to encrypt the database that 
needs to be moved.
-- Recreate the server certificate by using the original server certificate 
    backup file. 
-- The password must be the same as the password that was used when the backup
    was created.

CREATE CERTIFICATE TestSQLServerCert 
FROM FILE = 'TestSQLServerCert'
WITH PRIVATE KEY 
(
    FILE = 'SQLPrivateKeyFile',
    DECRYPTION BY PASSWORD = '*rt@40(FL&dasl1'
);
GO

Step 3:
Create the database on the destination server using the mdf file or restore 
from a backup.

From 
http://technet.microsoft.com/en-us/library/ff773063.aspx

 

Assymmetric encrpytion – Enabling TDE

What is TDE
Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. Encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory. TDE does not increase the size of the encrypted database.

TDE and TempDB
The tempdb system database will be encrypted if any other database on the instance of SQL Server is encrypted by using TDE. This might have a performance effect for unencrypted databases on the same instance of SQL Server.

Enabling TDE
The TDE model uses a new specialized symmetric key called the database encryption key (DEK) to encrypt your database. The DEK is protected by a server certificate—a certificate created in the master database. The server certificate is protected by the master database DMK, which must be protected in turn by the SMK.

The first step to enabling TDE is to create a DMK in the master database (if one doesn’t already exist).
USE master;
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = N’StrongPassword’;
GO

After you’ve created the master database DMK you need to create a server certificate in the master database. This is the certificate that will protect the DEK that you’ll create in subsequent steps.

USE master;
GO
CREATE CERTIFICATE TDE_Certificate
WITH SUBJECT = N’TDE Encryption Server Certificate’;
GO

After you create your server certificate you should immediately back it up using the BACKUP CERTIFICATE statement. Immediately store the certificate backup in a secure facility. You’ll need the server certificate if you want to restore the encrypted database from a backup or attach the database files to a different server in the future.

USE master;
GO
BACKUP CERTIFICATE TDE_Certificate
TO FILE = N’c:\Server_Certificate.cer’
WITH PRIVATE KEY
(
FILE = N’c:\Server_Certificate.pvk’,
ENCRYPTION BY PASSWORD = N’AnyStrongPassword’
);
GO
Now that you have a server certificate you can switch back to your user database and create a DEK with the CREATE DATABASE ENCRYPTION KEY statement. You can only create one DEK per user database.

USE AdventureWorksLT2008;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
GO

Finally enable encryption

ALTER DATABASE CustRecords
SET ENCRYPTION ON;
GO