Natively Encrypting Social Security Numbers in SQL Server 2005

Problem 
I need to encrypt data in my SQL Server 2005 database and currently have a Database Master Key and Certificate installed. Can I encrypt using only a certificate? The answer is "yes". Symmetric and asymmetric keys are not required for the encryption/decryption process, although they do add another layer of security to the mix.

Solution 
When deciding on an encryption/decryption process, you must think about who needs to see what data. If there are different levels of security needed then multiple certificates will have to be created and permissions assigned to each one, preferably through the use of database roles or Active Directory groups. If, however, there is a dichotomy-either you need to see it or not, then one certificate should meet your needs.

In order for users to encrypt and decrypt information using a certificate they must have both the VIEW DEFINITION andCONTROL permissions assigned to them. VIEW DEFINITION will only allow the user to see the metadata for the certificate but not actually use it. Here is the syntax to grant these permissions:

USE MSSQLTIPS  GRANT VIEW DEFINITION ON CERTIFICATE :: "certificate name" TO "Database user/role"  GRANT CONTROL ON CERTIFICATE :: "certificate name" TO "Database user/role"  (Note that the scope qualifier "::" is used)

The Encryption Process 
As an example I created a table called "tblClients" with four fields: ClientID (identity), LastName (varchar(50)), FirstName (varchar(40)), and SocialSecurityNumber (varbinary(128)). We would like to encrypt the Social Security number. To encrypt it using the certificate the following syntax must be used:

insert into MSSQLTIPS.dbo.tblClients(LastName, FirstName, SocialSecurityNumber)  values('Doe', 'Jonathan', ENCRYPTBYCERT(Cert_ID('certMSSQLTIPS'),'1234567890'))

A straight SELECT statement on the encrypted information looks like a typical varbinary field:

If a user attempts to insert a record into the table and does not have permissions on the certificate then the field to be encrypted will be nulled. To decrypt the data, then the field to be decrypted is converted from varbinary to whatever data type you prefer (varchar(10) in this case):

If a user attempts to look at encrypted information and does NOT have permissions on the certificate, the encrypted information is returned as a NULL value:

It's as simple as that. The good news is that there is not an error when an attempt is made to use a certificate that you don't have permission to use, the data will be NULL. When using keys, you will receive an error if you attempt to use a key that is not open. More on that in the next series tip.

Source collected from mssqltips.com