Natively Encrypting Social Security Numbers in SQL Server 2005


By:   |   Updated: 2007-11-30   |   Comments (3)   |   Related: More > Encryption

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

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:

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

 



Last Updated: 2007-11-30


get scripts

next tip button



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.

View all my tips





Comments For This Article




Wednesday, December 03, 2014 - 6:04:34 AM - Gabriel Back To Top

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.

 

Regards


Saturday, January 05, 2008 - 6:21:44 PM - timothyrcullen Back To Top

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

 Hi,
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 VIEW DEFINITION ON CERTIFICATE :: certabclients TO public
GRANT CONTROL ON CERTIFICATE :: certabclients TO public

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

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

 

Thanks
Riskworks



download


Recommended Reading

Storing passwords in a secure way in a SQL Server database

SQL Server Column Level Encryption Example using Symmetric Keys

Encrypting and Decrypting SQL Server Stored Procedures, Views and User-Defined Functions

Where Does SQL Server Store Its Certificates

Updating an expired SQL Server TDE certificate





get free sql tips
agree to terms


Learn more about SQL Server tools