SQL Server Data Encryption

SQL Server 2005 includes some new encryption capabilities that all administrators, programmers and database analyst should be aware of.  Key and Certificate creation and management is now an integral part of SQL Server 2005.  You have the flexibility to create your own X.509 certificates, use Windows Server 2003 CA issued certs, or use other certs purchased from a trusted certificate authority.  

There are a number of levels in the key hierarchy, but you’ll spend most of your time with database level certs and symmetric keys.  See the SQL Server 2005 Encryption Hierarchy article for a detailed description of certificates, asymmetrical and symmetrical keys.  You’ll also notice at the bottom of that article is a link to the SQL Server 2005 Permissions Hierarchy.  I would recommend reading and digesting both.  It’s a short read and will be a good use of your time as we review the following scripts and demos.

Watching the Demos

My team is now disseminating information using a variety of publishing techniques.  See the following scripts demonstrated using Windows Media Video format.  That video is in full color.  I had to sacrifice some colors for the video here, but it should not matter much for the demos.  See the full screen launch buttons below.

Setting Up To Use Encryption

In our first script and demo, we are going to create a number of objects.  We’re going to create a user id, login id, a sample database,  and a sample table.  Later, we’ll create and use some views into the data along with a helper function that will allow us to control access to the data.  I’m going to cut a lot of the comments from the original script(s).  I will however highlight or link important function calls and features in the scripts.  You’ll notice that this first script is fairly straight forward.  You’ll also notice that the definition for CardNumber doesn’t reveal anything out of the ordinary.  It certainly doesn’t indicate the contents of that column will be encrypted.  This provides a little bit of stealth but not much.  The real power is in the encryption which you see soon.

See the entire blog post complete with full scripts at http://blogs.technet.com/keithcombs/archive/2005/11/24/415079.aspx