I’m sure there are a billion articles out there talking about using database encryption but I didn’t really find one that simply has a quick and simple guide.

Step 0: What do we need

The simplest approach is to use: a Master key on the database, a certificate and a symmetric key.

Step 1: Create Master Key

MSDN: The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Some Really long and complex password'

If you have no idea for a long and complex password you can use a free online password generator. Use a length of 28 or longer.

Step 2: Generate a Certificate

MSDN: […] when SQL Server generates a self-signed certificate, the private key is always created. By default, the private key is encrypted using the database master key. If the database master key does not exist and no password is specified, the statement will fail.

CREATE CERTIFICATE MyDatabaseCertificate
   WITH SUBJECT = 'Certificate For My Database',
   EXPIRY_DATE = '10/31/2011';

Step 3: Create the symmetrical key used for the encryption

MSDN: When a symmetric key is created, the symmetric key must be encrypted by using at least one of the following: certificate, password, symmetric key, asymmetric key, or PROVIDER.

CREATE SYMMETRIC KEY MyDatabase_Key WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE MyDatabaseCertificate;

Step 4: Give rights to the user(s) that will encrypt and decrypt using the certificate and key

In order to use (to open) the certificate for encryption and decryption you need to give view definition and control rights on the certificate to the user(s) that will do the operations.

GRANT VIEW DEFINITION ON CERTIFICATE :: MyDatabaseCertificate TO "MyDbUser"
GRANT CONTROL ON CERTIFICATE :: MyDatabaseCertificate TO "MyDbUser"

NOTE: the usage of quotation marks instead of normal apostrophes around the user name.

If you use Integrated Security your user would look like “DOMAIN\UserName”:

GRANT VIEW DEFINITION ON CERTIFICATE :: MyDatabaseCertificate TO "SERVER\MyWebUser"
GRANT CONTROL ON CERTIFICATE :: MyDatabaseCertificate TO "SERVER\MyWebUser"

Step 5: Encrypting and Decrypting Data

To encrypt and decrypt data you need to be able to open the symmetric key. In order to open a symmetric key you need to be in a stored procedure. You can’t open the certificate from a function or from code via TSQL calls.

Procedure to encrypt credit card numbers:

CREATE PROCEDURE [dbo].[usp_EncryptCreditCardNumber]
(
    @CreditCardNumber AS VARCHAR(16)
    , @Result VARBINARY(32) OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON

    OPEN SYMMETRIC KEY MyDatabase_Key
    DECRYPTION BY CERTIFICATE MyDatabaseCertificate

    SET @Result = EncryptByKey(Key_GUID('MyDatabase_Key'), @CreditCardNumber)

    CLOSE SYMMETRIC KEY MyDatabase_Key
END

Procedure to decrypt credit card numbers:

CREATE PROCEDURE [dbo].[usp_DecryptCreditCardNumber]
(
    @EncryptedCreditCardNumber VARBINARY(32)
    , @CreditCardNumber AS VARCHAR(16) OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON

    OPEN SYMMETRIC KEY MyDatabaseKey
    DECRYPTION BY CERTIFICATE MyDatabaseCertificate

    SET @CreditCardNumber = CONVERT(VARCHAR(16), DecryptByKey(@EncryptedCreditCardNumber))

    CLOSE SYMMETRIC KEY MyDatabaseKey
END

Step 6: Creating the table that will contain the encrypted data

Note: The encrypted column(s) have to be of type VarBinary of a length enough to keep the encrypted value of the data. Length depends on the algorithm used which in our scenario is AES_256. The AES works on blocks of 16 bytes so you should align your data to the next 16 size than the one that you have to encrypt.

So, lets just say you want to encrypt credit card details: You could create a table like:

CREATE TABLE [dbo].[CreditCardDetails](
    [CreditCardDetailsId] [int] IDENTITY(1,1) NOT NULL,
    [CreditCardNumber] [varbinary](32) NULL, -- Encrypted Credit Card
    [CreditCardLast4Digits] [char](4) NULL,
    [CreditCardExpMonth] [tinyint] NULL,
    [CreditCardExpYear] [int] NULL,
    [CreditCardName] [varchar](255) NULL,
    [CreditCardTypeId] [int] NULL,
 CONSTRAINT [PK_CreditCardDetails] PRIMARY KEY CLUSTERED
([CreditCardDetailsId] ASC)
)

You really want to keep the Last4Digits of the card in a separate column so you can present them in different UI items without having to decrypt the real credit card number.

Step 7: Inserting credit card details

Once we have the encrypt and decrypt procedure, the insert and retrieve become simple two step processes.

Save and encrypt:

CREATE PROC [dbo].[usp_CreditCardDetailsInsert]
    @CreditCardExpMonth tinyint,
    @CreditCardExpYear int,
    @CreditCardLast4Digits nchar(4),
    @CreditCardName varchar(255),
    @CreditCardNumber VARCHAR(16),
    @CreditCardTypeId int
AS
    SET NOCOUNT ON
    SET XACT_ABORT ON

    BEGIN TRAN

    DECLARE @EncryptedCreditCardNumber VARBINARY(32)
    EXECUTE dbo.usp_EncryptCreditCardNumber @CreditCardNumber, @EncryptedCreditCardNumber OUTPUT

    INSERT INTO [dbo].[CreditCardDetails] ([CreditCardExpMonth], [CreditCardExpYear], [CreditCardLast4Digits], [CreditCardName], [CreditCardNumber], [CreditCardTypeId])
    SELECT @CreditCardExpMonth, @CreditCardExpYear, @CreditCardLast4Digits, @CreditCardName, @EncryptedCreditCardNumber, @CreditCardTypeId

    COMMIT
GO

Retrieve and decrypt:

CREATE PROC [dbo].[usp_CreditCardDetailsSelectForPayment]
    @CreditCardDetailsId INT
AS
    SET NOCOUNT ON
    SET XACT_ABORT ON

    -- retrieve the encrypted credit card
    DECLARE @EncryptedCreditCardNumber VARBINARY(32)
    SELECT @EncryptedCreditCardNumber = CreditCardNumber
    FROM dbo.CreditCardDetails
    WHERE  [CreditCardDetailsId] = @CreditCardDetailsId

    DECLARE @CreditCardNumber varchar(16)
    -- decrypt
    EXECUTE [dbo].[usp_DecryptCreditCardNumber] @EncryptedCreditCardNumber, @CreditCardNumber OUTPUT

    -- select the other bits
    SELECT [CreditCardDetailsId], [CreditCardExpMonth], [CreditCardExpYear], [CreditCardLast4Digits], [CreditCardName], @CreditCardNumber AS CreditCardNumber, [CreditCardTypeId]
    FROM   [dbo].[CreditCardDetails]
    WHERE  [CreditCardDetailsId] = @CreditCardDetailsId

GO

PLEASE  make sure that you have another procedure for retrieving the non-critical data like name and last 4 digits of the card for display purposes without having to decrypt the credit card.

The only reason why you would need to decrypt the credit card is to send it to a payment gateway to process a transaction.