Natively Encrypting Social Security Numbers in SQL Server 2005

By:   |   Comments (3)   |   Related: > Encryption

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.

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 and CONTROL 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:

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:

Straight select statement without using a Certificate

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):

Successful decryption of information using a Certificate

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:

Unsuccessful decryption of information using a Certificate

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.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Tim Cullen Tim Cullen has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, December 3, 2014 - 6:04:34 AM - Gabriel Back To Top (35474)

Based on the result of the output after running "select * from tblClients" an encrypted output was displayed on the column "SocialSecurityNumber". what encryption algorithm was used? is it AES_128, AES_256 which? if it is using a default algorithm i would like to know the strength.



Saturday, January 5, 2008 - 6:21:44 PM - timothyrcullen Back To Top (193)

Good evening.  It looks like you have the right syntax.  How did you encrypt the data when it was inserted?  Thanks-TC

Wednesday, December 19, 2007 - 6:04:52 PM - Riskworks Back To Top (166)

Great intro artical.  I tried the sample code and used the follow grants to test.  (I know never grant to public).  When I decrypt I still getting ascii character in the decryptbycert column.  Did I miss something?

GRANT CONTROL ON CERTIFICATE :: certabclients TO public

 select  ss_plain,ss_cert 'ss_cert',
 decryptbycert(cert_id('certabclients'),ss_cert)) 'decryptbycert'
from tblClients

 Plain Text     ss_cert                                                        SS_decrypt
1234567890 0xF1642E9549F9B281D31F0D8BA6C77165 ?????



get free sql tips
agree to terms