How to Encrypt Data in SQL Express

You can protect the content of your databases from prying eyes.

SQL Server Express is a free and scaled-down version of Microsoft's SQL Server relational database manager. SQL Express includes a rich set of functions for database creation, backup, storage and retrieval. In some scenarios, the storage systems that contain the databases are at risk of unauthorized access. You can encrypt data using SQL Express primitive functions to protect your databases against this risk. Even if intruders manage to access the encrypted data (the "cyphertext"), they will not be able to convert it into the original, unencrypted version (the "plaintext") unless they know a symmetric encryption key (the "passphrase") that you only communicate to legitimate users.

Instructions

1. Store the data to be encrypted in a SQL Express variable. For example, you can declare and initialize a string variable as follows:

   DECLARE @plaintext NVARCHAR(50)

   SET @plaintext = N'This is the data you want to encrypt';

2. Create a passphrase that you must keep secret from anybody who does not have a legitimate reason to access the plaintext. For example, you can create a passphrase as follows:

   DECLARE @passphrase NVARCHAR(50)

   SET @passphrase = M'This is the encryption key';

3. Invoke the EncryptByPassPhrase() SQL Express primitive to perform the encryption. This sample code works for the variables created in the previous Steps:

   DECLARE @cyphertext VARBINARY(MAX)

   SET @cyphertext = EncryptByPassPhrase(@passphrase,@plaintext);

   Note that, unlike @plaintext and @passphrase, @cyphertext is not a regular string--it is a block of binary data created by the encryption algorithm.

References

• 4 Guys from Rolla: Using Symmetric Encryption in a SQL Server 2005 Database