Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Natively Encrypting Social Security Numbers in SQL Server 2005


By:   |   Last Updated: 2007-11-30   |   Comments (3)   |   Related Tips: 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


next webcast button


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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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


Learn more about SQL Server tools