SQL Server 2005 Encryption

This posting shows how to set up a column to be encrypted. These steps were derived from instructions found in the SQL Server 2005 Implementation and Maintenance book. This exercise was done on WinXP.

/* SQL Server has automatically created a Service Master Key, and we need to back that up so our data can be recovered if we end up restoring on a different server */

/* SQL Server will use the given password to encrypt a backup file */
BACKUP SERVICE MASTER KEY TO FILE='G:\Backup\SQL_Server\ServiceMasterKey.bak'
ENCRYPTION BY PASSWORD='MySecretPassword'

/* Now create a Database Master Key */
CREATE MASTER KEY ENCRYPTION BY PASSWORD='AnotherSecretPassword'

/* this shows that the Database Master Key exists */
SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id=101

/* the key could be created again, with this */
IF EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id=101)
DROP MASTER KEY
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD='aoeusnth;qjkzvwm,.pyrgcf'

/* Create a Public Key Certificate to be used when creating a Symmetric Key */
CREATE CERTIFICATE jvzCert WITH SUBJECT = 'JVZ Custom Password Certificate'

/* Create a Symmetric Key for encryption
NOTE: AES encryption is not supported on Windows XP or Windows Server 2000 */
CREATE SYMMETRIC KEY jvzKey WITH ALGORITHM=RC4_128 ENCRYPTION BY CERTIFICATE jvzCert
--DROP SYMMETRIC KEY jvzKey --I used this to switch the above line from DES to RC4_128

/* create some password fields; one for a readable password, the other for an encrypted version of it */
ALTER TABLE tblUser ADD UserPwd varchar(50), UserPwdEncrypted varchar(50)

/* populate the readable password with some random garbage */
UPDATE tblUser SET UserPwd = SUBSTRING(CAST(Rand(ascii(substring(UserName,2,1)))AS Varchar(50)),6,4) + 'HelloWorld'

/* show what's in there now */
SELECT UserName, UserPwd, UserPwdEncrypted FROM tblUser

/* open the symmetric key and use it to encrypt the password into the encrypted password column */
OPEN SYMMETRIC KEY jvzKey
DECRYPTION BY CERTIFICATE jvzCert
UPDATE tblUser SET UserPwdEncrypted = EncryptByKey(Key_GUID('jvzKey'), UserPwd)

/* the symmetric key is already open in this session, so I don't need to open it again */

/* show the contents of the encrypted column...in decrypted text */
SELECT UserName, UserPwd,
CAST(DecryptByKey(UserPwdEncrypted) AS varchar) AS UnencryptedPwd,
UserPwdEncrypted,
CASE
WHEN UserPwd = CAST(DecryptByKey(UserPwdEncrypted) AS varchar) THEN 'Match'
ELSE 'No Match'
END AS MatchCheck
FROM tblUser