Column Level Two-way Encryption

To use encryption and later be able to decrypt data in a column using symmetric encryption involves the following: 1. Service Master Key - unique to each sql server instanced 2. Database Master Key - unique to each database, generated using password and service master key 3. One or more Symmetric Database Certificates 4. One or more Symmetric Encryption Keys that use an algorithm and are tied to a certificate ---------------------------------------------------------- -- This only needs to be run once after database creation ---------------------------------------------------------- USE [database] GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyEncryptionPassword' GO CREATE CERTIFICATE SSN_CERT_01 WITH SUBJECT = 'SSN_CERT_01' GO CREATE SYMMETRIC KEY SSN_KEY_01 WITH ALGORITHM = AES_128 ENCRYPTION BY CERTIFICATE SSN_CERT_01 GO GRANT CONTROL ON CERTIFICATE::[SSN_CERT_01] TO [APPLICATION_USER] GO GRANT CONTROL ON SYMMETRIC KEY::[SSN_KEY_01] TO [APPLICATION_USER] GO ---------------------------------------------------------- -- This is how to encrypt a value to be inserted into -- column of database table -- This needs to be encapsulated in stored procedures -- that are called from the application, so as not to -- expose encrypt/decrypt process within application code -- NOTE: Will need to 'OPEN' the key once per session ---------------------------------------------------------- OPEN SYMMETRIC KEY SSN_KEY_01 DECRYPTION BY CERTIFICATE SSN_CERT_01; UPDATE t_person SET ssn = EncryptByKey( Key_GUID('SSN_KEY_01'), @ssn ) WHERE person_id = @person_id CLOSE SYMMETRIC KEY SSN_KEY_01 ---------------------------------------------------------- -- This is how to decrypt a value to select from -- column of database table -- This needs to be encapsulated in stored procedures -- that are called from the application, so as not to -- expose encrypt/decrypt process within application code -- NOTE: The cast needs to be the same datatype as the -- data was before it was encrypted ---------------------------------------------------------- OPEN SYMMETRIC KEY SSN_KEY_01 DECRYPTION BY CERTIFICATE SSN_CERT_01; SELECT TOP 1 @ssn = cast( DecryptByKey(A.ssn) as nvarchar(10) ) FROM t_person A WITH (NOLOCK) WHERE A.person_id = @person_id CLOSE SYMMETRIC KEY SSN_KEY_01 MOVING FROM INSTANCE TO INSTANCE By default, a database's master key is encrypted using the server's service master key. When you move a database to a new server using backup/restore or detach/attach, the database master key can not be decrypted automatically when you attempt to use the symmetric key. This is because the "service master key" is used to automatically open the database's master key. At this point there are a couple of options in order to get encryption working again in the database: 1) Decode all data on source instance; create backup; restore backup; recreate master key, certificate, and encryption key, then re-encrypt the data... yeh, right. 2) Explicitly OPEN MASTER KEY every time you want to use it... this is problematic as it will require code changes. 3) BACKUP SERVICE MASTER KEY on the original instance and use RESTORE SERVICE MASTER KEY on the target server... This can be bad as it hoses up the MASTER KEYS in all the databases on the instance. 4) You can back up the certificates on the original database; create database backup; restore database on target server; drop the keys, certificates, and master key in restored database; recreate the master key; restore the certificate from the backup file; recreate the encryption key. Probably a good practice to backup the certificate, but still is a lot of work. --TO BACK UP CERTIFICATE ON SOURCE INSTANCE BACKUP CERTIFICATE SB_CERT TO FILE = 'C:\SANDBOX_CERT.cer' WITH PRIVATE KEY ( FILE = 'C:\SANDBOX_CERT_Key.pvk' , ENCRYPTION BY PASSWORD = 'SandBox_P@$$w0rd' ) --ON TARGET INSTANCE AFTER DB RESTORE --AND COPY CERT FILES TO TARGET'S FILE SYSTEM CREATE CERTIFICATE SB_CERT FROM FILE = 'C:\SANDBOX_CERT.cer' WITH PRIVATE KEY ( FILE = 'C:\SANDBOX_CERT_Key.pvk' , DECRYPTION BY PASSWORD = 'SandBox_P@$$w0rd' ) 5) Easiest solution is use the following command on the restored database: USE dbname GO OPEN MASTER KEY DECRIPTION BY PASSWORD = 'MyEncryptionPassword' GO ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'MyEncryptionPassword' GO NOTE: The master key password needs to match the password you used to create master key on original database. You also need CONTROL permission on database to fiddle with master key.