Security Features in SQL Server 2008

As most DBA's are aware, applications sometimes store sensitive information.  This may be Social Security numbers, credit card information, salary information... and data owners have a concern that prying eyes may be reviewing this sensitive data without cause.  While performing a little research on this topic, I came across two new features of SQL Server 2008 that absolutely blew me away, column level encryption and native auditing.  OK, so the concept of these features are nothing new, but the implementation knocked my socks off. 

I'll start with column level encryption.  Encryption has been around for a while and the story has continued to improve.  Encrypted File Systems (EFS) and BitLocker we are able to secure our data while at rest and ensure that even if someone boots the machine with an alternative file system, mounts the disk, and attempts to read the data, it is protected.  These are very important and great features, but what about the person who has access to the database?  DBA's traditionally have had access to review any data stored within the system.  To protect from this type of problem, and others who have the ability to select data, SQL Server 2008 has introduced column level encryption.

Here is the neat part.  As a DBA, if I query the data and the table I'm querying has an encrypted column, and I don't provide the key to decode it, all I will see is the encrypted text.  Now there are some parts that will be required to make this work, mostly on the application side, where the key will need to be passed to the database to allow for decoding the data.  The snag is,  encrypted columns cannot be indexed, or full text searched, but honestly, doesn't that make sense?  My experiments with column level encryption saw minimal performance impacts, providing I was not using the encrypted field in a where clause.  More information on how to encrypt a column of data in SQL 2008 can be found at http://msdn.microsoft.com/en-us/library/ms179331.aspx.

Well encryption is fantastic, protect my data from prying eyes, blah blah blah, but really, when an auditor comes to you and asks who has touched this data, you'll be left holding the bag.  Enter SQL Server Audit.  This feature allows for very granular auditing of not only changes made to the database and schema, but to individual objects within the database.  In previous versions, you could only audit insert, update, or delete statements against a particular table and that was with the use of triggers.  With a little more work and a rock solid security policy, you could cobble together a solution to audit data retrieval via stored procedures, but it would still not audit the sysadmin who directly queried the tables.  Using Database-Level Audit actions, an audit can be configured to audit Select, Insert, Update, Delete, Execute, Receive, and References on a particular table, thereby making sure whoever did anything with the data was known about.  All of the different objects that are eligible for audit can be found at http://msdn.microsoft.com/en-us/library/cc280663.aspx. 

This is only a quick review of two of the latest features available in SQL Server 2008, it is by no means an exhaustive list of the new security features.  A good reference to start reading about these new features can be found in the SQL Server 2008 Compliance Guide available at http://www.microsoft.com/downloads/details.aspx?FamilyId=6E1021DD-65B9-41C2-8385-438028F5ACC2&displaylang=en.  As with all security planning, this is only a facet of a total security plan, but I feel a very important step forward towards making customer data more secure.