SQL Server Data Encryption

Download

How do I download the videos?

• To download, right click the file type you would like and pick “Save target as…” or “Save link as…”

Why should I download videos from Channel9?

• It's an easy way to save the videos you like locally.
• You can save the videos in order to watch them offline.
• If all you want is to hear the audio, you can download the MP3!

Which version should I choose?

• If you want to view the video on your PC, Xbox or Media Center, download the High Quality WMV file (this is the highest quality version we have available).
• If you'd like a lower bitrate version, to reduce the download time or cost, then choose the Medium Quality WMV file.
• If you have a Zune, WP7, iPhone, iPad, or iPod device, choose the low or medium MP4 file.
• If you just want to hear the audio of the video, choose the MP3 file.

Right click “Save as…”

• Mid Quality WMV (Lo-band, Mobile)

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