Pages

Men

rh

7/11/2013

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:

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.


Source collected from mssqltips.com

No comments :

Post a Comment