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.