Search results matching tags 'sql', 'SQL Server 2008', and 'development'

"Cloning" Symmetric Keys

Mike C — Thu, 18 Jun 2009 01:40:00 GMT

It's well-known by now that SQL Server 2005 and 2008 include new encryption-related statements that allow you to create and administer encryption keys. You can use CREATE CERTIFICATE to create or import a certificate or DROP ASYMMETRIC KEY to remove an asymmetric key from the database, for instance. One of the interesting ommissions from the T-SQL encryption statements is the statements necessary to backup and restore a symmetric key. Why would you want to do this? I can think of a couple of reasons off the top of my head:

You need to backup symmetric keys (and all other encryption keys, in fact) as part of an overall disaster recovery (DR) program. If a server needs to be rebuilt you obviously need a way to restore all encryption keys.
You need to implement the same symmetric keys on multiple servers. There could be a couple of reasons for this -- you might encrypt data on one server and decrypt it on another, or you might be load-balancing across a server farm and need identical encryption keys on multiple servers simultaneously.

It seems like a bit of an oversight to not include BACKUP and RESTORE SYMMETRIC KEY options in T-SQL, but in practice you can effectively achieve the same end results with the standard CREATE SYMMETRIC KEY statement. Basically the CREATE SYMMETRIC KEY statement gives you an option to "clone" the exact same symmetric key on any SQL Server 2005 or 2008 instance, anywhere, at any time. To create a cloneable symmetric key you need to specify two special CREATE SYMMETRIC KEY options:

The IDENTITY_VALUE option, which SQL Server uses to generate a GUID (uniqueidentifier) for the key
The KEY_SOURCE option, which SQL Server uses as key material to generate the actual key

As long as you specify the same values for the IDENTITY_VALUE and KEY_SOURCE options (and the same ALGORITHM), your symmetric key will be exactly the same no matter where, when, or how many times you create it. To be honest, if I were creating a list, always use IDENTITY_VALUE and KEY_SOURCE would be listed in the top 10 list of SQL Server encryption best practices.

Here's a quick sample demonstrating the CREATE SYMMETRIC KEY statement with IDENTITY_VALUE and KEY_SOURCE specified:

CREATE SYMMETRIC KEY test_aes128_key
WITH KEY_SOURCE = 'I am the very model of a modern major general',
IDENTITY_VALUE = 'E pluribus unum',
ALGORITHM = AES_128
ENCRYPTION BY PASSWORD = 'p@$$w0rd';

This CREATE SYMMETRIC KEY statement will create the same symmetric encryption key on any SQL Server 2005 or 2008 instance on which you run it. This brings up another point, about security. If the same KEY_SOURCE and IDENTITY_VALUE options can create the exact same encryption key on any of your servers, they will create the exact same encryption key on any of my servers, or any server owned by any hacker anywhere in the world. So once you've run your CREATE SYMMETRIC KEY statement, the IDENTITY_VALUE and KEY_SOURCE need to be handled like any other secure information. Don't leave them lying around where just anyone can access them. Store them with your certificates, key backups, and other confidential materials in a secure off-site location.

So what happens when you don't specify the IDENTITY_VALUE and KEY_SOURCE options? Well, basically SQL Server generates an unpredictable GUID to identify the symmetric key and the encryption key source material is randomly generated. Basically you'll never regenerate the exact same key again. Ever. There could be situations where this would be handy. The concept of "session keys" comes to mind. A session key is basically a "temporary" key that's only required to encrypt data for a user during a single session. Since it only exists for the life of the session, a totally randomly-generated key is just fine.

For my tastes, it would make more sense to require IDENTITY_VALUE and KEY_SOURCE options by default. If you didn't want to specify both of these options there should be another option/indicator specifically to say that you want these options generated randomly. At any rate, it's a good idea to get into the habit of treating these options as if they are mandatory unless they have a very specific special-purpose requirement (e.g., "session keys").

Presenting in Louisville; Meeting in Cincinnati

Mike C — Mon, 01 Jun 2009 23:15:00 GMT

I'll be presenting "New SQL Server 2008 Features" in downtown Louisville, KY on June 22, in conjunction with the Louisville SQL Server Users Group thanks to group leader Malathi Mahadevan. We'll be giving away some SQL 2008 books and other goodies. I'll post further details (exact time and location) shortly.

Update: The address is for the Louisville meeting is:

Louisville Free Public Library
301 York Street
Louisville, KY 40202

We'll be meeting from 12 noon to 1 PM.

June 23rd I'll be stopping by the Cincinnati .NET User Group (for the UG meeting and a Geek Dinner -- hopefully at Skyline Chili! ;-) ) thanks to Mike Wood, director of the Cincy .NET Users Group. We'll be having an informal discussion of SQL Server and .NET technology and features after the main meeting, having some good food, and giving away a few more books! Visit http://cinnug.org for more info.

Practical SQL Server XML: Part 2

Mike C — Wed, 04 Mar 2009 02:28:00 GMT

In response to a challenge from Pinal Dave, a fellow MVP and a winner of an award for his blog at the MVP Summit 2009, I am writing a couple of articles to demonstrate practical uses for SQL Server’s XML capabilities. (Dave’s great blog is at http://blog.sqlauthority.com/).

Dave posted the first post in this series over at http://blog.sqlauthority.com/2009/03/17/sql-server-practical-sql-server-xml-part-one-query-plan-cache-and-cost-of-operations-in-the-cache/, describing how to shred the XML query plans in the SQL Server query plan cache. In this post I’ll talk about sending multiple parameters, and even parameter sets, to SQL Server via XML. SQL Server MVP Jacob Sebastian is finishing up the series with a post on his blog at http://blog.beyondrelational.com/2009/03/practical-sql-server-xml-part-three.html.

One of the most common requests in the SQL Server newsgroups is “how do I send a delimited list of values to my stored procedures?” There are many creative answers, including SQL MVP Erland Sommarskog’s popular article “Arrays and Lists in SQL Server” (http://www.sommarskog.se/arrays-in-sql-2005.html). Erland touches on the method of XML shredding that I’ll cover here.

For this example I’ll use the AdventureWorks 2008 database. This sample database is available for download from CodePlex (http://sqlserversamples.codeplex.com/) for SQL Server 2008. For this example I’ll first create a procedure that inserts a single record that must be put in two separate tables:

CREATE PROCEDURE Person.AddPerson

@PersonType nchar(2) = NULL,

@NameStyle dbo.NameStyle = NULL,

@Title nvarchar(8) = NULL,

@FirstName dbo.Name = NULL,

@MiddleName dbo.Name = NULL,

@LastName dbo.Name = NULL,

@Suffix nvarchar(10) = NULL,

@EmailPromotion int = NULL,

@EmailAddress nvarchar(50) = NULL

BEGIN

-- Get a new BusinessEntityID

DECLARE @BusinessEntityID int;

INSERT INTO Person.BusinessEntity(rowguid)

VALUES (NEWID());

SET @BusinessEntityID = SCOPE_IDENTITY();

-- Insert Person

INSERT INTO Person.Person

(

BusinessEntityID,

PersonType,

NameStyle,

Title,

FirstName,

MiddleName,

LastName,

Suffix,

EmailPromotion

)

VALUES

(

@BusinessEntityID,

@PersonType,

@NameStyle,

@Title,

@FirstName,

@MiddleName,

@LastName,

@Suffix,

@EmailPromotion

);

-- Insert Email Address

INSERT INTO Person.EmailAddress

(

BusinessEntityID,

EmailAddress

)

VALUES

(

@BusinessEntityID,

@EmailAddress

);

END

The procedure simply accepts several parameters that reflect a single Person record. The first step is to insert a row into the Person.BusinessEntity table to get a business entity ID. Then the remaining parameter values are inserted into the Person.Person and Person.EmailAddress tables. Simple enough. But what if you want to insert 2 Person records? You can round-trip to the server twice with two calls to this procedure, or you can double up the number of parameters and add the extra code in the procedure to deal with it.

But then again, what if you want to add 100 Person records? How about a thousand or a million? That’s a lot of round trips to the server or a lot of extra parameters to add (SQL Server actually maxes out at around 2,100 parameters). If you have SQL Server 2008 and you’re using a .NET client you can use table-valued parameters. But what if you’re not using a .NET client or SQL 2005 is your target database? In that case you can use XML to accomplish the same thing.

Using XML to add data to a single table is normally a pretty straightforward shredding operation, but we’re actually touching three different tables in this case. And one of the tables generates an identity value that the other two tables need. This means the code is not so simple as a straight single-table insert, especially if you decide not to use cursors. Here’s my XML version of adding a Person:

CREATE PROCEDURE Person.AddPersonXML

@People xml = NULL

BEGIN

-- Generate and store all new business entity IDs

DECLARE @TemporaryIDs TABLE

(

BusinessEntityID int NOT NULL

);

-- Number the XML nodes and generate business entity IDs

WITH NodeNumbers

(

SELECT

ROW_NUMBER() OVER (ORDER BY C.value('PersonType', 'nchar(2)')) AS NodeNumber

FROM @People.nodes('//Person') T(C)

)

INSERT INTO Person.BusinessEntity

(

rowguid

)

OUTPUT

inserted.BusinessEntityID

INTO @TemporaryIDs

(

BusinessEntityID

)

SELECT NEWID()

FROM NodeNumbers nn;

WITH PersonNodes

(

SELECT

ROW_NUMBER() OVER (ORDER BY C.value('PersonType[1]', 'nchar(2)')) AS JoinID,

C.query('.') AS PersonData

FROM @People.nodes('//Person') T(C)

BusinessEntityIDs

(

SELECT

ROW_NUMBER() OVER(ORDER BY BusinessEntityID) AS JoinID,

BusinessEntityID

FROM @TemporaryIDs

GetPersonData

(

SELECT

bei.BusinessEntityID,

pn.PersonData.value(N'(Person/PersonType)[1]', N'nchar(2)') AS PersonType,

pn.PersonData.value(N'(Person/NameStyle)[1]', N'dbo.NameStyle') AS NameStyle,

pn.PersonData.value(N'(Person/Title)[1]', N'nvarchar(8)') AS Title,

pn.PersonData.value(N'(Person/FirstName)[1]', N'dbo.Name') AS FirstName,

pn.PersonData.value(N'(Person/MiddleName)[1]', N'dbo.Name') AS MiddleName,

pn.PersonData.value(N'(Person/LastName)[1]', N'dbo.Name') AS LastName,

pn.PersonData.value(N'(Person/Suffix)[1]', N'nvarchar(10)') AS Suffix,

pn.PersonData.value(N'(Person/EmailPromotion)[1]', N'int') AS EmailPromotion

FROM PersonNodes pn

INNER JOIN BusinessEntityIDs bei

ON pn.JoinID = bei.JoinID

)

INSERT INTO Person.Person

(

BusinessEntityID,

PersonType,

NameStyle,

Title,

FirstName,

MiddleName,

LastName,

Suffix,

EmailPromotion

)

SELECT

BusinessEntityID,

PersonType,

NameStyle,

Title,

FirstName,

MiddleName,

LastName,

Suffix,

EmailPromotion

FROM GetPersonData;

WITH PersonNodes

(

SELECT

ROW_NUMBER() OVER (ORDER BY C.value('PersonType[1]', 'nchar(2)')) AS JoinID,

C.query('.') AS PersonData

FROM @People.nodes('//Person') T(C)

BusinessEntityIDs

(

SELECT

ROW_NUMBER() OVER(ORDER BY BusinessEntityID) AS JoinID,

BusinessEntityID

FROM @TemporaryIDs

GetPersonData

(

SELECT

bei.BusinessEntityID,

pn.PersonData.value(N'(Person/EmailAddress)[1]', N'nvarchar(50)') AS EmailAddress

FROM PersonNodes pn

INNER JOIN BusinessEntityIDs bei

ON pn.JoinID = bei.JoinID

)

INSERT INTO Person.EmailAddress

(

BusinessEntityID,

EmailAddress

)

SELECT

BusinessEntityID,

EmailAddress

FROM GetPersonData;

END

You notice in this example that we first have to get business entity IDs for each person. I use a table variable and the DML OUTPUT clause to grab the business entity IDs ahead of time. Then I shred the XML in two steps, once to put relevant data in the Person.Person table and another to put the email addresses in Person.EmailAddress. The procedure call looks like this:

EXEC Person.AddPersonXML N'<Person>

<FirstName>George</FirstName>

<LastName>Jetson</LastName>

<EmailAddress>George.Jetson@spacelysprockets.com</EmailAddress>

</Person>

<LastName>Flintstone</LastName>

<EmailAddress>Fred.Flintstone@sladegravelpit.com</EmailAddress>

</Person>';

As you can see, we can easily add as many people as you want with a single XML parameter. That is, so long as you respect the 2.1 GB size limit on SQL Server LOB data types like XML. Also since you are using XML you can use any client application in any language that can generate XML character data; you’re not limited to just any .NET-specific functionality.

You can verify that the people above were added with a few simple queries:

SELECT *
FROM Person.Person
WHERE BusinessEntityID > 20777;

SELECT *
FROM Person.EmailAddress
WHERE BusinessEntityID > 20777;

SELECT *
FROM Person.BusinessEntity
WHERE BusinessEntityID > 20777;

The results look like the following:

In the third article of this series at http://blog.beyondrelational.com/2009/03/practical-sql-server-xml-part-three.html, MVP and author Jacob Sebastian will show you how to add XML Schema validations to your xml data type parameter sets.