SQL Server 2005: column encryption demo part 2

I modified the last column encryption demo by protecting the encryption key with a certificate instead of using a password. The advantage to this approach is that a password is no longer needed when opening the key. The advantage of the password approach was the additional protection coming from the fact that only the user who knew the password could use the key. In this version of the demo, any db_owner can use the key.

— Goals of this demo:
— Show how a column can be encrypted and decrypted without using passwords
— The encryption key will be encrypted using a certificate instead of a password

create database demo;

use demo;

— create the database master key
create master key encryption by password = ‘Pufd&s@))%’;

— create a simple employee table
create table t_employees (id int primary key, name varchar(300), salary varbinary(300));

— create a certificate to protect the symmetric key that will encrypt the data
— the certificate will be encrypted by the database master key
create certificate cert_sk_admin with subject = ‘Certificate for accessing symmetric keys – for use by admin’;
— create a second certificate that will be used by another user (Charles) to get access to the key
create certificate cert_sk_charles with subject = ‘Certificate for accessing symmetric keys – for use by Charles’;

— create a key to protect the employee sensitive data, in this case – the salary
create symmetric key sk_employees with algorithm = aes_192 encryption by certificate cert_sk_admin;

— open the key so that we can use it
open symmetric key sk_employees decryption by certificate cert_sk_admin;

— verify key was opened
select * from sys.openkeys;

— add another encryption to the key using the certificate we created for Charles
alter symmetric key sk_employees add encryption by certificate cert_sk_charles;

— check the catalog to see the list of certificates that encrypt our key
select c.name from sys.key_encryptions ke, sys.certificates c, sys.symmetric_keys sk where ke.thumbprint = c.thumbprint and sk.name = ‘sk_employees’ and ke.key_id = sk.symmetric_key_id;

— insert some data
— we will use the id as an authenticator value to tie the salary to the employee id
insert into t_employees values (101, ‘Alice Smith’, encryptbykey(key_guid(‘sk_employees’), ‘$200000’, 1, ‘101’));
insert into t_employees values (102, ‘Bob Jones’, encryptbykey(key_guid(‘sk_employees’), ‘$100000’, 1, ‘102’));

— see the result; salary is encrypted
select * from t_employees;

— create a view to automatically do the decryption
— note that when decrypting we specify that the id should be used as authenticator
create view v_employees as select id, name, convert(varchar(10), decryptbykey(salary, 1, convert(varchar(30), id))) as salary from t_employees;

— create another view for charles that will automatically open the key using his certificate and do the decryption
create view v_employees_auto as select id, name, convert(varchar(10), decryptbykeyautocert(cert_id(‘cert_sk_charles’), NULL, salary, 1, convert(varchar(30), id))) as salary from t_employees;

— see the result, the decrypted data is available
select * from v_employees;

— now close the key
close all symmetric keys;

— verify key was closed
select * from sys.openkeys;

— use the view that will automatically open the key
select * from v_employees_auto;

— note that the key is also automatically closed by the above query
— it is only opened for the duration of the query
select * from sys.openkeys;

— also note that if the key is not opened, the v_employees view will not be able to decrypt the data
select * from v_employees;

— create a login and user for Charles
create login charles with password = ‘Pufd&s@))%’;
create user charles;

— grant control on the certificate to charles
grant control on certificate::cert_sk_charles to charles;
grant view definition on symmetric key::sk_employees to charles;
grant select on v_employees to charles;
grant select on v_employees_auto to charles;

— impersonate charles
execute as login = ‘charles’;

— verify context
select suser_sname();
select user_name();

— select from view that will open the key
select * from v_employees_auto;

— open key
open symmetric key sk_employees decryption by certificate cert_sk_charles;

— now we can use the v_employees view as well
select * from v_employees;

— close key
close all symmetric keys;

— revert impersonation of charles

— if we ever want to prevent charles from accessing the data,
— we can just remove the encryption of the symmetric key
— and revoke view definition on the key
— any of these is sufficient, but doing both is a better cleanup
— charles may continue to use the certificate for other purposes
— (maybe to access some other key)
open symmetric key sk_employees decryption by certificate cert_sk_admin;
alter symmetric key sk_employees drop encryption by certificate cert_sk_charles;
close symmetric key sk_employees;
revoke view definition on symmetric key::sk_employees to charles;
— we can also revoke permission to access the views if we don’t want Charles to use them anymore
revoke select on v_employees to charles;
revoke select on v_employees_auto to charles;

— cleanup
drop user charles;
drop login charles;
drop view v_employees;
drop view v_employees_auto;
delete from t_employees;
drop table t_employees;
drop symmetric key sk_employees;
drop certificate cert_sk_charles;
drop certificate cert_sk_admin;

use master;

drop database demo;

Comments (16)

  1. Chris says:

    An extremely good demonstration of Encryption with Server 2005

  2. Leandro Carvalho says:

    Can this be done with Express Edition? When I try to put the follow code:

    create master key encryption by password = ‘Pufd&s@))%’;

    I receive the erro message: Incorrect syntax near ‘master’.

    Could you help me?


    Leandro Carvlaho

  3. lcris says:

    Yes, this works with Express editions as well. Please check that you are indeed connected to a recent build of SQL Server 2005.

  4. Leandro Carvalho says:

    When I tryed to create a symmetric key with the follow code:

    create symmetric key sk_employees with algorithm = aes_192 encryption by certificate cert_sk_admin;

    I’m receiving the following error message:

    Either no algorithm has been specified or the bitlength and the algorithm specified for the key are not available in this installation of Windows.

    Could you help me?

  5. Leandro Carvalho says:

    I’m using the Windows 2000 Professional with SP4.


    Leandro Carvalho

  6. lcris says:

    AES encryption is not available on all Windows platforms. To make the demo work on a platform that doesn’t support AES, you can replace the reference to AES_192 with TRIPLE_DES.

  7. Sam Bendayan says:


    Question:  we want to encrypt a small amount of data in a database so that the db_owner CANNOT decrypt it.  It seems to me that we need to encrypt the data by NOT using Certificates at all; we should just use a key pair that is encrypted by a password.  That way, only the user that knows the password can decrypt the data and not anyone else.  Does this make sense?



  8. lcris says:

    Yes, if you protect your key using a password, you will prevent a mere db_owner from decrypting the encrypted data.

    However, there is no restriction on certificate usage: you can use a certificate to protect the encryption key. Here are a few examples of how you could setup encryption, in which “->” should be read as “is encrypted by”:

    data -> symmetric key -> password
    data -> symmetric key -> symmetric key -> password
    data -> symmetric key -> certificate -> password

    For additional information on protecting the keys, you can have a look at: http://blogs.msdn.com/lcris/archive/2005/10/14/481434.aspx

  9. Cihat says:

    How do you know what the lenght of the encrypted column must be when creating it?

    In this example you have create the encryption column varbinary(300), why 300? Is it randomly chosen?

  10. lcris says:

    I actually chose 300 to be much bigger than what was needed for this example. There are two ways to determine the length of data you should reserve: (1) encrypt the largest piece of data and note the resulting size of the ciphertext, or (2) use the formula from this blog post: http://blogs.msdn.com/yukondoit/archive/2005/11/24/496521.aspx.

  11. prinks says:


    I want to encrypt one whole column in my database. It should be stored in the DB in the encrypted form bt during queries i need to do comparisons based on this column which should be done with the actual data (means it should be decrypted at that time). Please tell me whats the best way of doing it?



  12. Lalita says:

    Am trying to implement Encryption in our application in order to meet the HIPAA compliance. Once of the regulation states that the key encrypting the data cannot reside on the database. That leaves me with the only option of using EncryptByPassPhrase. I have few questions regarding this:

    1. In one of the blogs i read EncryptByPassPhrase internally creates a symmetric key using TripleDES Algorithm to encrypt the data. (http://www.pluralsight-training.net/…/21375.aspx) Does SQL Server cache this generated key or keep it in memory beyond the current transaction boundary ?

    2. FIPS Compliance says that Triple DES (2 Key) will no longer be compliance from Dec 2010 onwards. Would you have any idea what Algorithm Microsoft plans to use for EncryptByPassPhrase behind the scenes going forward ?

    3. Do i have any alternative option other than EncryptByPassPhrase where the key wont be stored on the server ?

  13. lcris says:

    Regarding HIPAA, note that the keys necessary to decrypt the data are not stored in the database – the keys stored in the database are always stored encrypted by another key. I am not sure whether HIPAA refers to storing the key in clear or not.

    If you want to move key management outside the database, then also have a look at this feature: EKM – Extensible Key Management – msdn.microsoft.com/…/bb895340(SQL.100).aspx.

    Answers to your questions below:

    1. The key is created during the command, used for encryption, then it is destroyed using CryptDestroyKey. There is no caching.

    2. I can't answer this because I am no longer working on SQL Server. I suggest asking this question on the security forum on MSDN (social.msdn.microsoft.com/…/threads). Your speculation is as good as mine on this point 🙂

    3. EKM as mentioned above.

  14. Lalita says:

    Thanks for the info. Just to clarify further, am working on SQL Server 2008. The Answer 1 still holds true ?

    Also Could you confirm if the TripleDES used in EncryptByPassPhrase is a 2 key or 3 key ?

  15. lcris says:

    Yes, answer 1 holds true in all releases. SQL Server doesn't keep keys around if it doesn't need them. For the keys kept around, you get facilities to see them and close them.

    It's the 2-key version of TripleDES that is being used by encryptbypassphrase. See the clarification in the CREATE SYMMETRIC KEY page at technet.microsoft.com/…/ms188357.aspx:


    DESX was incorrectly named. Symmetric keys created with ALGORITHM = DESX actually use the TRIPLE DES cipher with a 192-bit key. The DESX algorithm is not provided. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

    Symmetric keys created with ALGORITHM = TRIPLE_DES_3KEY use TRIPLE DES with a 192-bit key.

    Symmetric keys created with ALGORITHM = TRIPLE_DES use TRIPLE DES with a 128-bit key.


    TRIPLE_DES is the algorithm used when no algorithm choice is given in the DDL, so encryptbypassphrase, like the DbMK/SMK, is using a TRIPLE DES 128bit key (I had mentioned the master keys algorithm here: blogs.msdn.com/…/437048.aspx).