SQL Server 2005 includes new encryption capabilities that all administrators, programmers and database analyst should be aware of.  Key and Certificate creation and management functions are 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.  The videos of the demos are now posted at http://channel9.msdn.com/Showpost.aspx?postid=139794 in both Windows Media Video format as well as Macromedia Shockwave Video format.  See the buttons at the bottom of that post for the full screen versions.

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 (links are in red) 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.

Setup.sql – it’s purpose is to create a sample database, id and table to use.  Nothing fancy.

CREATE DATABASE [DataEncryptDemo]
go

USE [DataEncryptDemo]
go

CREATE LOGIN [login_low_priv] WITH PASSWORD = 'Login1 Password!'
CREATE USER [user_low_priv] FOR LOGIN [login_low_priv]
go

CREATE TABLE [dbo].[CreditCards]( CardId INT PRIMARY KEY ,
                                  CardNumber varbinary(256) )
go

Key  and Certificate Creation 

CreateSecrets.sql – now we are starting to get into the good stuff.  You’ll notice we are going to create a master symmetrical key to start things off (hyper linked below).  The next few lines of the script create the certificate and symmetric key we’ll use to encrypt and decrypt data we’ll add or retrieve from the demo table.

USE [DataEncryptDemo]
go

-- Create the DB master key.
-- Notice that the password may be subject to password policy verification, depending on your system.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'DB Master key password!'
go

CREATE CERTIFICATE [cert_SecretTable_SecretData_Key]
  WITH SUBJECT = 'SecretTable_SecretData_Key protection'
go


-- You can also use other encryption algorithms like AES_128 if your system supports it
CREATE SYMMETRIC KEY [SecretTable_SecretData_Key]
  WITH ALGORITHM = TRIPLE_DES
  ENCRYPTION BY CERTIFICATE [cert_SecretTable_SecretData_Key]
go

Encrypting Data and Testing Encrypted Contents

EncryptData.sql – in this script we start to do the fun stuff. You’ll notice we first open the keys created in the prior script. We are going to call key_guid() to get the symmetric key GUID from the database.  Now that we have it, we’ll call the encryptbykey() function with that GUID and the data to be encrypted on the two inserts into the table.  After that occurs, the credit card values are safely locked inside the table rows.  You can see in the script we try to retrieve those rows using a standard select.  This fails to display the credit card numbers because we didn’t call a decryption function.  The next test in the script calls the decryptbykey() function.  This of course works nicely.

USE [DataEncryptDemo]
go

-- In order to use the synmmetric key, you need to open it first
OPEN SYMMETRIC KEY [SecretTable_SecretData_Key]
    DECRYPTION BY CERTIFICATE [cert_SecretTable_SecretData_Key]
go

-- Now insert some secret data into the table
DECLARE @KeyGuid AS UNIQUEIDENTIFIER
SET @KeyGuid = key_guid( 'SecretTable_SecretData_Key')
IF( @KeyGuid is not null )
BEGIN
 INSERT INTO [dbo].[CreditCards] VALUES ( 1, encryptbykey( @KeyGuid, N'4388-1234-1234-1234'))
 INSERT INTO [dbo].[CreditCards] VALUES ( 2, encryptbykey( @KeyGuid, N'4549-5678-5678-5678'))
END
ELSE
BEGIN
 PRINT 'Failed to obtain the symmetric key GUID'
END

SELECT * FROM [dbo].[CreditCards]

SELECT CardId,
       convert( NVARCHAR(100), decryptbykey( CardNumber )) as 'Card Number'
FROM   [dbo].[CreditCards]
go

-- A good recommendation is to close the key after you have finish to encrypt data
CLOSE SYMMETRIC KEY [SecretTable_SecretData_Key]
go

-- Without the key open, the unencrypt function returns NULL
SELECT CardId,
       convert( NVARCHAR(100), decryptbykey( CardNumber )) as 'Card Number'
FROM   [dbo].[CreditCards]
go

Using A View to Access Encrypted Data

CreateView.sql – this script creates a view that allows our lowly privileged user access to the encrypted data.  The view calls the cert_id() function to retrieve our cert id number.  That number is then used to open and use the symmetric key for decryption of the credit card number.  The problem with this script is that it grants way too much authority and access to the keys used in the decryption process.  We’re going to change that in a minute when we revoke those permissions but use a helper function to provide access to the data.

USE [DataEncryptDemo]
go

CREATE VIEW [dbo].[CreditCardsView]
AS
 SELECT CardId as CardId,
 convert( nvarchar(50), decryptbykeyautocert( cert_id( 'cert_SecretTable_SecretData_Key' ),
 null, CardNumber )) as CardNumber
FROM [dbo].[CreditCards]
go

GRANT SELECT ON [dbo].[CreditCardsView] TO [user_low_priv]
go
GRANT CONTROL on certificate::[cert_SecretTable_SecretData_Key]
 TO [user_low_priv]
go
GRANT VIEW DEFINITION on symmetric key::[SecretTable_SecretData_Key]
 TO [user_low_priv]
go

Using the View to Access Data

UseView.sql – this is a simple script that impersonates the user we created.  Keep in mind for the moment, the user has too much authority over the cert and key.  However, you’ll also notice the view is doing all of the work to decrypt the data and present it.  Nothing fancy here.

USE [DataEncryptDemo]
go

EXECUTE AS USER = 'user_low_priv'
SELECT * FROM [dbo].[CreditCardsView]
REVERT
go

Creating Helper Functions to Access Encrypted Data

CreateView2.sql – now things are starting to get interesting.  Here we start things off by creating a helper function called Cards_decrypted_Helper.  This functions executes temporarily with elevated privilege as DBO.  You’ll notice this function takes the data passed to it and decrypts it using the cert and symmetric key.  So how does the helper function get called?  By the new view of course!!!  The new view is called CreditCardView2.  The view iterates through the rows in the table, calls the helper, decrypts the data, then shows the result.

You’ll also notice we revoke the permissions for user_low_priv to the cert and symmetric key.  After the revocations, we’ll grant access to the view.

USE [DataEncryptDemo]
go

CREATE FUNCTION [dbo].[Cards_decrypted_Helper] ( @SecretData VARBINARY(256))
RETURNS NVARCHAR(50)
WITH EXECUTE AS 'DBO'
AS
BEGIN
RETURN convert( NVARCHAR(50), decryptbykeyautocert( cert_id( 'cert_SecretTable_SecretData_Key' ), null, @SecretData ))
END
go

CREATE VIEW [dbo].[CreditCardsView2]
AS
 SELECT CardID as CardID, [dbo].[Cards_decrypted_Helper](CardNumber) as CardNumber FROM [dbo].[CreditCards]
go

REVOKE CONTROL on certificate::[cert_SecretTable_SecretData_Key]
 TO [user_low_priv]
go
REVOKE VIEW DEFINITION on symmetric key::[SecretTable_SecretData_Key]
 TO [user_low_priv]
go

GRANT SELECT ON [dbo].[CreditCardsView2] TO [user_low_priv]

Testing the Helper Function

UseView2.sql – now that we have all of the access controls in place, we can test CreditCardView2 to see if it really works.  In the first test we are using View2.  It of course works correctly and displays the data.  Our little helper function did all the work and more importantly, we reduced the permission footprint for our lowly user.  The second test, the one using our first view fails due to the fact there is no access to the certificate and key used to decrypt the data. HA!!!

USE [DataEncryptDemo]
go

-- access through new view works without unneeded permissions
EXECUTE AS USER = 'user_low_priv'
SELECT * FROM [dbo].[CreditCardsView2]
REVERT
go

-- no access through the old view
EXECUTE AS USER = 'user_low_priv'
SELECT * FROM [dbo].[CreditCardsView]
REVERT
go

Summary

As you can see, there are some powerful capabilities in SQL Server 2005.  This was a brief look into the encryption functions, view usage, permission models, and help functions.  That ought to get your appetite going. 

A number of changes and improvements have been made to SQL Server 2005.  Did I really need to state that?  One of the big improvements is in the management and security area.  The SQL Management Object (SMO) infrastructure replaces much of what used to be accomplished using SQL-DMO.  SMO is tightly integrated with Windows Management Instrumentation (WMI).  This linkage is so tight, new capabilities now exist to take advantage of the eventing taking place.

One way to take advantage of this instrumentation is to use Data Definition Language (DDL) or Data Manipulation Language (DML) via T-SQL.  Last week, Kai Axford delivered a webcast on SQL Security as part of the “A More Secure and Well-Managed Infrastructure” series.  In his webcast (Part 11), he did some DDL and DML demos.  A number of people wanted me to post the scripts, so look down at the bottom of this post for those.

My team is also delivering live seminars as part of the “Best Of SQL Server 2005 Launch.”  One of those demos is DDL.  It’s a pretty simple demo, but to understand the power, you really need to look as the available events and build some automation around them.  The SQL Server 2005 books are now online.  If you look at the DDL_EVENTS hierarchy, you’ll notice we have database and server levels events we can trap.  Once we trap an event, we can setup a tripwire to take the appropriate “corrective” action.

In our demo, we use DDL_DATABASE_LEVEL_EVENTS to see what is occuring on the virtual machine demo environment.  Our script will detect unapproved actions and roll them back while logging the time, date, user ids and stuff to an audit table.  Our script will also post a nice little message indicating to the user that action was prohibited.  In reality, you might take advantage of other automation like sending a page to a pager/cellphone, sending an email message to the DBA’s or security professionals, etc.  The automation is only limited by your imagination and programming skills. 

See the full Data Definition Language (DDL) demo to get an idea on how to do this.

The script we demo is as follows:

USE AdventureWorks;
GO

CREATE TABLE AuditDDLOperations
(
 OpID  int    NOT NULL identity 
        CONSTRAINT AuditDDLOperationsPK
         PRIMARY KEY CLUSTERED,
 LoginName sysname   NOT NULL,
 UserName sysname   NOT NULL,
 PostTime datetime  NOT NULL,
 EventType nvarchar(100) NOT NULL,
 DDLOp  nvarchar(2000) NOT NULL
);
GO

CREATE TRIGGER PreventAllDDL
ON DATABASE
WITH ENCRYPTION
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
SET @data = EVENTDATA()
RAISERROR ('DDL Operations are prohibited on this production database. Please contact ITOperations for proper policies and change control procedures.', 16, -1)
ROLLBACK
INSERT AuditDDLOperations
  (LoginName,
   UserName,
   PostTime,
   EventType,
   DDLOp)
VALUES   (SYSTEM_USER, CURRENT_USER, GETDATE(),
   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') )
RETURN;
GO

--Test the trigger.
CREATE TABLE TestTable (col1 int);
GO

DROP TABLE AuditDDLOperations;
GO

SELECT * FROM AuditDDLOperations;
GO

--Drop the trigger.
DROP TRIGGER PreventAllDDL
ON DATABASE;
GO

DROP TABLE AuditDDLOperations;
GO

As you can see from the example and demo above, it’s a simple script but has a lot of power.  If you are interested in other examples and procedures, see the “Designing DDL Triggers” online help page.  Of course you should also have a local copy of this page since I know you were a good doggie and installed SQL Server 2005.  If you don’t have a copy, shame on you.  Come to one of the remaining launch events if you can and get one.  If registration is blocked because it is full, you can still come by.  However, walkins aren’t guaranteed to get in and you’ll only get a free copy of SQL Server 2005 and Visual Studio 2005 if you get in. 

Here are the scripts from Kai’s webcast:

--Step 1: Create a table to log audited events to
USE LucernePublishing
GO
CREATE TABLE DDL_Audit (id INT PRIMARY KEY IDENTITY,
  AuditText VARCHAR(MAX))
GO

--Step 2: Create a table for testing the trigger
CREATE TABLE LPTriggerTest (id INT PRIMARY KEY)
GO

--Step 3: Create a trigger to fire when
-- an ALTER TABLE command is issued
CREATE TRIGGER LPTableAlter
ON DATABASE
AFTER ALTER_TABLE
AS
INSERT INTO DDL_Audit VALUES('A database table was altered.')
GO

--Step 4:Add a new column to the test table
ALTER TABLE LPTriggerTest
   ADD TestText VARCHAR(MAX)
GO

--Step 5:Verify that the trigger fired
SELECT * FROM DDL_Audit
GO

============

--Step 1: Create a trigger to prevent
-- tables from being dropped.
USE LucernePublishing
GO

CREATE TRIGGER Safety
ON DATABASE
FOR DROP_TABLE
AS
 PRINT 'You must disable the Safety trigger
       before you drop a table.'
 ROLLBACK
GO

--Step 2: Test the trigger by trying to drop a
-- table from the database
DROP TABLE dbo.LPBuildVersion
GO

--Step 3: Drop the trigger to allow database
-- tables to be dropped
DISABLE TRIGGER [Safety] ON DATABASE
GO

=============

--Step 1: Create a new table for auditing
--with the eventdata() function
USE LucernePublishing
GO
CREATE TABLE DDL_Eventdata (
 PostTime datetime,
 DB_User nvarchar(100),
 Event nvarchar(100),
 TSQL nvarchar(2000)
)
GO

--Step 2: Modify the trigger
-- to use eventdata() function
ALTER TRIGGER LPTableAlter
ON DATABASE
AFTER ALTER_TABLE
AS
DECLARE @data XML
SET @data = EVENTDATA()
INSERT INTO DDL_EventData
   (PostTime, DB_User, Event, TSQL)
 VALUES   
 (GETDATE(),   
 CONVERT(nvarchar(100), CURRENT_USER),   
 @data.value('(/EVENT_INSTANCE/EventType)[1]',
  'nvarchar(100)'),   
 @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]',
  'nvarchar(2000)') ) ;
GO

--Step 3: Modify the test table again to
-- cause the trigger to fire
ALTER TABLE LPTriggerTest
   ADD TestText2 VARCHAR(MAX)
GO

--Step 4: Verify that the trigger fired and wrote
-- eventdata() information to the table.
SELECT * FROM DDL_EventData
GO

 

 

 

Today the SST TechNet team launched the pilot of a new weekly blogcast interview program IT Heroes: Stories from the Trenches, the real stories of men and women in IT making a difference everyday. We talk with authors, innovators and implementers about emerging technologies, troubleshooting and remediation of common infrastructure issues and charitable contributions in the community.  We seek to educate and provide a forum for open discussion of the many uses for and specific ways in which people are everyday exploiting technology to create opportunities for themselves and others.

The announcement and links to stream the first interview are at http://blogs.technet.com/mjmurphy .

In keeping with the SQL 2005 launch we feature a 20 minute interview with Bill Zack, one of the co-authors of Programming SQL 2005 due out from MS Press early 2006. We spend the first five minutes discussing Bill's many other projects including his software architects User Group in Manhattan and get into a discussion of SQL 2005 at about the 4:45 mark.

http://wm.microsoft.com/ms/inetpub/mjmurphy/mp3/bzacksql.mp3 is the direct link.

You would think that http://www.microsoft.com/technet would have the SQL Server 2005 books, right?  GONG!!! Nope.  So where do you get the goods?  Well, this gets you into the age old debate about the audience for the product.  Is the audience IT Pro, DBA, Developer or all of the above?  That answer is pretty obvious.  DEVELOPER !!! 

So head over to MSDN website for the SQL Server 2005 Documentation.

The underlying ideas of the Web can be traced as far back as 1980, when Tim Berners-Lee and Robert Cailliau built ENQUIRE (short for Enquire Within Upon Everything, a book Berners-Lee recalled from his youth). While it was rather different from the Web we use today, it contained many of the same core ideas (and even some of the ideas of Berners-Lee's next project after the WWW, the Semantic Web).

In March 1989, Tim Berners-Lee wrote "Information Management: A Proposal", which referenced ENQUIRE and described a more elaborate information management system. [3] He published a more formal proposal for the actual World Wide Web on November 12, 1990 [4]. Implementation began on November 13, 1990 when Berners-Lee wrote the first Web page [5] on a NeXT workstation.

from http://en.wikipedia.org/wiki/World_Wide_Web

I got a message from Kevin Remde that a few of you were complaining I’ve been MIA on my blog.  Well, that is soo true, but don’t think for a second it hasn’t been the top of my mind.  In fact, I’ve been setting up some stuff to take this blog and others MUCH further.

I’ve been building out some infrastructure that will allow the SST TechNet and MSDN teams to stream audio, video and podcast.  We are taking some baby steps, but the first one is a dozeee.  Think of it like the first baby step for the Jolly Green Giant.  In order to accomplish some of our goals, we need a way to stream audio and video.  The status on that is complete as of today.

Fellow comrade, Michael J Murphy, is the first person on my team to put something into production using the service we are building out.  See his IT Hero Series blog post at http://blogs.technet.com/mjmurphy/archive/2005/11/07/413884.aspx. 

So what else has been consuming my time?  Uh, anyone heard about SQL Server 2005?  Check out all of the new information at http://www.microsoft.com/sql/default.mspx.  That page has been completely updated with all of the launch information.  Today is launch day!!!  Yaaaa!!!  My team has been very heads down working with the product so we can do a killer job of delivering our live and webcast events over the next couple of months.

But Keith, surely there’s something else?  Uh, yea.  You may have seen that I’m the Master of Ceremonies for the “Secure And Well Managed Infrastructure” webcast series. This is an eighteen part series running through my birthday in December.  Actually, it concludes the day before my birthday but whos counting?

Uh, dude, you’re still jerking our chain.  Surely that isn’t all?  Ok, you got me.  If the above wasn’t sucking up over 60–70 hours per week, maybe my new toy is.  As you might recall from previous posts, I’ve been evaluating high definition DLP TVs.  I made the plunge and purchased the HP 58” md5880n.  I have my Media Center 2005 PC connected and am driving the HDTV at 1080p resolutions via a DVI->HDMI cable.  I have an upscaling DVD player connected via HDMI driving the HDTV at 1080i via HDMI.  I have my Charter Communications HD DVR and Xbox connected via the component video connections.  It is working wonderfully.  The picture quality is awesome.  The speakers sound great.

Now, I promise to start posting more to my blog.  I’m currently learning to become a Camtasia expert.  I will be delivering some really kewl blog entries soon with some great demos.

FYI, the ISP I use to store pictures is apparently on vacation and the server went down.  Hopefully all of the pretty pictures will be back tomorrow. :(

I was surfing around looking at various blogs.  One of them had me rolling on the floor because it was totally empty.  I’m sure you’ve seen others. 

See http://blogs.technet.com/bryanvonaxelson/

Now in Bryan’s defense, he was probably pretty busy doing stuff like the SQL Server 2005 series when he created it.  That was several months ago.    Ok, can anyone knock off the King Of The Hill?