Encrypting Data using SQL Server 2005

If you encrypt some data using a symmetric key with a password. It appears
that the database master key is not used at all to encrypt the data. Is thi
s
true? Also it appears you can backup the database and move it to another
server, and retrain the password for the symmetric key from the old server.
Meaning that after you restore the database to a new server you can use the
symmetric key password from the old server to open the symmetric key in the
database on the new server and decrypt the data.
My basic question if you create a symmetric key with a password, and encrypt
data with that symmetric key, then is there any reason you would need to
create a master key for the database?
--
If you are looking for SQL Server examples check out my Website at
http://www.sqlserverexamples.comHello Greg,
GL> If you encrypt some data using a symmetric key with a password. It
GL> appears that the database master key is not used at all to encrypt
GL> the data. Is this true?
Strictly speaking, yes. But recall that the symmetric's key decryption devic
e
is stored encrypted by the Service Master (SMK) in its absence. So while
the SMK isn't part the encryption vector per se, you aren't using going to
be able to decrypt encrypted data without the correct SMK if you don't use
a Database Master Key (DBMK).
GL> Also it appears you can backup the
GL> database and move it to another server, and retrain the password for
GL> the symmetric key from the old server. Meaning that after you
GL> restore the database to a new server you can use the symmetric key
GL> password from the old server to open the symmetric key in the
GL> database on the new server and decrypt the data.
Yes, you wouldn't want to unrecoverable data, but you will still need to
regenerate off that instance's SMK.
GL> My basic question if you create a symmetric key with a password, and
GL> encrypt data with that symmetric key, then is there any reason you
GL> would need to create a master key for the database?
Consider the following example. Although both DBs have the same keys, they
really don't because the keys have different GUIDs. And if you look at the
encrypted data carefully enough, its pretty obvious that the key guid is
part of the encrypted data.
use master
go
create database enc1
create database enc2
go
use enc2
create table dbo.secrets(data varbinary(255))
go
use enc1
create symmetric key signingKey with algorithm = triple_des encryption by
password = 'theKey'
open symmetric key signingkey decryption by password = 'theKey'
create symmetric key enc_Key with algorithm = triple_des encryption by symme
tric
key signingKey
close symmetric key signingKey
go
open symmetric key signingkey decryption by password = 'theKey'
open symmetric key enc_key decryption by symmetric key signingKey
close symmetric key signingKey
select name,key_guid,algorithm_desc from sys.symmetric_keys
insert into enc2.dbo.secrets values (encryptByKey(key_guid('enc_key'),'beSur
eToDrinkYourOvaltine'))
select key_guid('enc_key'),data,cast(decryptByK
ey(data) as varchar(255))
from enc2.dbo.secrets
close symmetric key enc_key
go
use enc2
create symmetric key signingKey with algorithm = triple_des encryption by
password = 'theKey'
open symmetric key signingkey decryption by password = 'theKey'
create symmetric key enc_Key with algorithm = triple_des encryption by symme
tric
key signingKey
close symmetric key signingKey
go
open symmetric key signingkey decryption by password = 'theKey'
open symmetric key enc_key decryption by symmetric key signingKey
close symmetric key signingKey
select name,key_guid,algorithm_desc from sys.symmetric_keys
select key_guid('enc_key'),data,cast(decryptByK
ey(data) as varchar(255))
from enc2.dbo.secrets
close symmetric key enc_key
go|||So if I understand you correctly the encrypted data can not be decrypted
without the appropriate Service Master Key, even if you have the correct
symmetric key password. Meaning you can't move a dataase backup of the
encrypted data from one server to another and decrypt it using the only the
symmetric key. Is this true?
I'm guessing I don't have this right because I can copy a database backup
from one server to another and still decrypt the encrypted data. Here is a
script I tested it with:
-- on server 1 do this:
use master
go
if exists (select * from master.sys.databases where name = 'enc1')
drop database enc1
create database enc1
go
use enc1
create table dbo.secrets(data varbinary(255))
go
create symmetric key signingKey with algorithm = triple_des encryption by
password = 'theKey'
open symmetric key signingkey decryption by password = 'theKey'
create symmetric key enc_Key with algorithm = triple_des encryption by
symmetric
key signingKey
close symmetric key signingKey
go
open symmetric key signingkey decryption by password = 'theKey'
open symmetric key enc_key decryption by symmetric key signingKey
close symmetric key signingKey
select name,key_guid,algorithm_desc from sys.symmetric_keys
insert into dbo.secrets values
(encryptByKey(key_guid('enc_key'),'beSur
eToDrinkYourOvaltine'))
select key_guid('enc_key'),data,cast(decryptByK
ey(data) as varchar(255))
from dbo.secrets
close symmetric key enc_key
backup database enc1 to disk = 'C:\temp\enc1.bak'
-- copy C:\temp\enc1.bak from server 1 to server 2
-- server 2 do this:
use master
go
if exists (select * from master.sys.databases where name = 'enc1')
drop database enc1
go
restore database enc1 from disk='c:\temp\enc1.bak'
go
use enc1
go
open symmetric key signingkey decryption by password = 'theKey'
open symmetric key enc_key decryption by symmetric key signingKey
close symmetric key signingKey
select name,key_guid,algorithm_desc from sys.symmetric_keys
select key_guid('enc_key'),data,cast(decryptByK
ey(data) as varchar(255))
from dbo.secrets
close symmetric key enc_key
Now so I'm wondering why I can move a database that has encrypted data from
one server to another by just doing a database backup and restore and then
issuing the open symmetric key using the password from the target server,
like so.
If you are looking for SQL Server examples check out my Website at
http://ww.sqlserverexamples.com
"Kent Tegels" wrote:

> Hello Greg,
> GL> If you encrypt some data using a symmetric key with a password. It
> GL> appears that the database master key is not used at all to encrypt
> GL> the data. Is this true?
> Strictly speaking, yes. But recall that the symmetric's key decryption dev
ice
> is stored encrypted by the Service Master (SMK) in its absence. So while
> the SMK isn't part the encryption vector per se, you aren't using going to
> be able to decrypt encrypted data without the correct SMK if you don't use
> a Database Master Key (DBMK).
> GL> Also it appears you can backup the
> GL> database and move it to another server, and retrain the password for
> GL> the symmetric key from the old server. Meaning that after you
> GL> restore the database to a new server you can use the symmetric key
> GL> password from the old server to open the symmetric key in the
> GL> database on the new server and decrypt the data.
> Yes, you wouldn't want to unrecoverable data, but you will still need to
> regenerate off that instance's SMK.
> GL> My basic question if you create a symmetric key with a password, and
> GL> encrypt data with that symmetric key, then is there any reason you
> GL> would need to create a master key for the database?
> Consider the following example. Although both DBs have the same keys, they
> really don't because the keys have different GUIDs. And if you look at the
> encrypted data carefully enough, its pretty obvious that the key guid is
> part of the encrypted data.
> use master
> go
> create database enc1
> create database enc2
> go
> use enc2
> create table dbo.secrets(data varbinary(255))
> go
> use enc1
> create symmetric key signingKey with algorithm = triple_des encryption by
> password = 'theKey'
> open symmetric key signingkey decryption by password = 'theKey'
> create symmetric key enc_Key with algorithm = triple_des encryption by sym
metric
> key signingKey
> close symmetric key signingKey
> go
> open symmetric key signingkey decryption by password = 'theKey'
> open symmetric key enc_key decryption by symmetric key signingKey
> close symmetric key signingKey
> select name,key_guid,algorithm_desc from sys.symmetric_keys
> insert into enc2.dbo.secrets values (encryptByKey(key_guid('enc_key'),'beS
ureToDrinkYourOvaltine'))
> select key_guid('enc_key'),data,cast(decryptByK
ey(data) as varchar(255))
> from enc2.dbo.secrets
> close symmetric key enc_key
> go
> use enc2
> create symmetric key signingKey with algorithm = triple_des encryption by
> password = 'theKey'
> open symmetric key signingkey decryption by password = 'theKey'
> create symmetric key enc_Key with algorithm = triple_des encryption by sym
metric
> key signingKey
> close symmetric key signingKey
> go
> open symmetric key signingkey decryption by password = 'theKey'
> open symmetric key enc_key decryption by symmetric key signingKey
> close symmetric key signingKey
> select name,key_guid,algorithm_desc from sys.symmetric_keys
> select key_guid('enc_key'),data,cast(decryptByK
ey(data) as varchar(255))
> from enc2.dbo.secrets
> close symmetric key enc_key
> go
>
>|||Hello Greg,
GL> So if I understand you correctly the encrypted data can not be decrypted
without the appropriate Service Master Key, even if you have the correct sy
mmetric key password. Meaning you ca
n't move a dataase backup of the encrypted data from one server to another a
nd decrypt it using the only the symmetric key. Is this true?
It was certainly the understanding I had from reading BOL and the testing I
did. I couldn't get your backup example to work and wondered if there wasn't
maybe so vodoo getting done during
the restore process so a did a dettach/attach insead (attachment #1.)
GL> Now so I'm wondering why I can move a database that has encrypted data f
rom one server to another by just doing a database backup and restore and th
en issuing the open symmetric key=2
0using the password from the target server, like so.
There's a note in BOL that gave me a different understanding of this:
"When a symmetric key is encrypted with a password instead of the public key
of the database master key, the TRIPLE_DES encryption algorithm is used. Be
cause of this, keys that are created20with a strong encryption algorithm, su
ch as AES, are themselves secured by a weaker algorithm."
This was added in December 2006. So when you sign a symmetric key with a pas
sword, it looks like it just internalizes the key under 3DES and makes it tr
ansportable. That sucks because n
ow its way easier to brute force attack that key. UGH!
Even more annoyingly, the same behavior seems to apply to symmetic keys at a
re encrypted by asymmetric keys where that key is encrypted by a password. S
ee attachment #2.|||Can't seem to see those attachments. But I think from your reply you
confirmed what I was saying.
Now what I wonder is why are you encrypting a symmetric key with another
symmetric key. What exactly does this accomplish?|||"Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
news:1DB4AD7C-69C7-4BD8-B48F-8166CD35DABE@.microsoft.com...
> Can't seem to see those attachments. But I think from your reply you
> confirmed what I was saying.
> Now what I wonder is why are you encrypting a symmetric key with another
> symmetric key. What exactly does this accomplish?
It provides layered protection for your keys. You can theoretically replace
any key in the mid- to upper-levels of your key hierarchy and only need to
decrypt and re-encrypt the keys it protects, until you reach the
bottom-level keys. The result is that you can theoretically change
intermediate and top-level keys on very often with very little effect on
your server or processes, and you can change bottom-level keys much less
often.
Of course if you change the bottom-level keys you have to decrypt and
re-encrypt all your protected data, which can be a resource-intensive
operation.sql