Search results matching tags 'SQL Server 2008', 'encryption', and 't-sql'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=SQL+Server+2008,encryption,t-sql&orTags=0Search results matching tags 'SQL Server 2008', 'encryption', and 't-sql'en-USCommunityServer 2.1 SP2 (Build: 61129.1)&quot;Cloning&quot; Symmetric Keyshttp://sqlblog.com/blogs/michael_coles/archive/2009/06/17/cloning-symmetric-keys.aspxThu, 18 Jun 2009 01:40:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:14741Mike C<P>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 <FONT face=courier>CREATE CERTIFICATE</FONT> to create or&nbsp;import a certificate or <FONT face=courier>DROP ASYMMETRIC KEY</FONT> to remove an asymmetric key from the database, for instance.&nbsp; One of the interesting ommissions from the T-SQL encryption statements is the statements necessary to backup and restore a symmetric key.&nbsp; Why would you want to do this?&nbsp; I can think of a couple of reasons off the top of my head:</P>
<OL>
<LI>You need to backup symmetric keys (and all other encryption keys, in fact) as part of an overall disaster recovery (DR) program.&nbsp; If a server needs to be rebuilt you obviously need a way to restore all encryption keys.</LI>
<LI>You need to implement the same symmetric keys on multiple servers.&nbsp; 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.</LI></OL>
<P>It seems like a bit of an oversight to not include <FONT face=courier>BACKUP</FONT> and <FONT face=courier>RESTORE SYMMETRIC KEY</FONT> options in T-SQL, but in practice you can effectively achieve the same end results with the standard <FONT face=courier>CREATE SYMMETRIC KEY</FONT> statement.&nbsp; Basically the <FONT face=courier>CREATE SYMMETRIC KEY</FONT> statement gives you an option to "clone" the exact same symmetric key on any SQL Server 2005 or 2008 instance, anywhere, at any time.&nbsp; To create a cloneable symmetric key you need to specify two special <FONT face=courier>CREATE SYMMETRIC KEY</FONT> options:</P>
<UL>
<LI>The <FONT face=courier>IDENTITY_VALUE</FONT> option, which SQL Server uses to generate a GUID (uniqueidentifier) for the key</LI>
<LI>The <FONT face=courier>KEY_SOURCE</FONT> option, which SQL Server uses as key material to generate the actual key</LI></UL>
<P>As long as you specify the same values for the <FONT face=courier>IDENTITY_VALUE</FONT> and <FONT face=courier>KEY_SOURCE</FONT> options (and the same <FONT face=courier>ALGORITHM</FONT>), your symmetric key will be exactly the same no matter where, when, or how many times&nbsp;you create it.&nbsp; To be honest, if I were creating a list, always use <FONT face=courier>IDENTITY_VALUE</FONT> and <FONT face=courier>KEY_SOURCE</FONT> would be listed in the top 10 list of SQL Server encryption best practices.</P>
<P>Here's a quick sample demonstrating the <FONT face=courier>CREATE SYMMETRIC KEY</FONT> statement with <FONT face=courier>IDENTITY_VALUE</FONT> and <FONT face=courier>KEY_SOURCE</FONT> specified:</P>
<P><FONT face=courier>CREATE SYMMETRIC KEY test_aes128_key<BR>WITH KEY_SOURCE = 'I am the very model of a modern major general',<BR>&nbsp;&nbsp;&nbsp; IDENTITY_VALUE = 'E pluribus unum',<BR>&nbsp;&nbsp;&nbsp; ALGORITHM = AES_128<BR>ENCRYPTION BY PASSWORD = <A href="mailto:'p@$$w0rd'">'p@$$w0rd'</A>;</FONT></P>
<P>This <FONT face=courier>CREATE SYMMETRIC KEY</FONT> statement will create the same symmetric encryption key on any SQL Server 2005 or 2008 instance on which you run it.&nbsp; This brings up another point, about security.&nbsp; If the same <FONT face=courier>KEY_SOURCE</FONT> and <FONT face=courier>IDENTITY_VALUE</FONT> 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.&nbsp; So once you've run your <FONT face=courier>CREATE SYMMETRIC KEY</FONT> statement, the <FONT face=courier>IDENTITY_VALUE</FONT> and <FONT face=courier>KEY_SOURCE</FONT> need to be handled like any other secure information.&nbsp; Don't leave them lying around where just anyone can access them.&nbsp; Store them with your certificates, key backups, and other confidential materials in a secure off-site location.</P>
<P>So what happens when you don't specify the <FONT face=courier>IDENTITY_VALUE</FONT> and <FONT face=courier>KEY_SOURCE</FONT> options?&nbsp; Well, basically SQL Server generates an unpredictable GUID to identify the symmetric key and the encryption key source material is randomly generated.&nbsp; Basically you'll never regenerate the exact same key again.&nbsp; Ever.&nbsp; There could be situations where this would be handy.&nbsp; The concept of "session keys" comes to mind.&nbsp; A session key is basically a "temporary" key that's only required to encrypt data for a user during a single session.&nbsp; Since it only exists for the life of the session, a totally randomly-generated key is just fine.</P>
<P>For my tastes, it would make more sense to require <FONT face=courier>IDENTITY_VALUE</FONT> and <FONT face=courier>KEY_SOURCE</FONT> 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.&nbsp; 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").</P>