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:
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.
Next Steps
- Review information on the Encryption Hierarchy for SQL Server 2005.
- Read about all of the Cryptographic Functions available in SQL Server 2005
- Find out how to get information on SQL Server 2005 security components using the Security Catalog Views
- Read more Security tips on http://www.MSSQLTIPS.com/
