Sql-server – Microsoft SQL Server 2005 Encryption Options

Since you are using Standard Edition, you cant use TDE. So other options are

Using encryption keys at instance/database level :

SQL Server has two kinds of keys: symmetric and asymmetric. Symmetric keys use the same password to encrypt and decrypt data. Asymmetric keys use one password to encrypt data (called the public key) and another to decrypt data (called the private key).

SQL Server has two primary applications for keys: a service master key (SMK) generated on and for a SQL Server instance, and a database master key (DMK) used for a database.

Also, you can have encryption at column level by creating a MASTER KEY ENCRYPTION along with CREATE CERTIFICATE and then CREATE SYMMETRIC KEY.

An example of how this can be done is described at Encrypt a Column of Data

Reference : SQL Server and Database Encryption Keys (Database Engine)

At Drive level :

Using BitLocker as it is a Drive Encryption data protection feature available Windows Server 2008 R2. Refer to : BitLocker Drive Encryption Overview There are many opensource or third party software to do the same job but at additional cost.

Note: The most important bit is ALWAYS backup your encryption keys.

You can use third party software like Redgate's sql backup which allows you to encrypt backups using passwords.

Depending on what level you need encryption will determine if it is worth upgrading to enterprise edition or not. You have to evaluate native TDE encryption vs encryption keys and certificates vs open source vs disk encryption.

OK, In case anyone wants to know, I got to the bottom of this.

The chain is pretty easy to work out:

Create Service Master Key (automatically done upon install)

Create Database Master Key (user creates their own key)

SymetricEncrypt(Service Key,Database Master Key) -> Store Encrypted Master Key into Master DB

Create Certificate

SymetricEncrypt(Database Master Key, Certificate Private Key) -> Store Encrypted private key in Master DB

Create Database Encryption Key (system creates a random key, but must be manually added to database)

ASymetricEncrypt(Certificate, Database Encryption Key) -> Store Encrypted DEK into database.

So, to decrypt the database data, you'll need to:

Use the SMK to decrypt the DMK

Use the DMK to decrypt the cert private key

Use the certificate to decrypt the DEK

Use the DEK to decrypt the database

The starting point is the Service Master Key... how is this stored (if you encrypt it, you can't retrieve data, and if left in plaintext your system is exposed).

The answer is, the SMK is encrypted with Data Protection API, which is a service built into windows.

Basically, data is encrypted with "secrets" from your user profile. So the same user profile must be used to decrypt the SMK.

Now that I know this, I can use DPAPI in my applications to encrypt my encryption keys :)