Re: Encrypting Data using SQL Server 2005

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'),'beSureToDrinkYourOvaltine'))
select key_guid('enc_key'),data,cast(decryptByKey(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(decryptByKey(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 device
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 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 enc2.dbo.secrets values (encryptByKey(key_guid('enc_key'),'beSureToDrinkYourOvaltine'))
select key_guid('enc_key'),data,cast(decryptByKey(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 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
select key_guid('enc_key'),data,cast(decryptByKey(data) as varchar(255))
from enc2.dbo.secrets
close symmetric key enc_key
go



.

Relevant Pages

  • Re: private key encryption - doubts
    ... >> Suppose I finally got the symmetric key decided by the originator. ... >> its just a key that will be used for encryption. ... > SSL2 from the netscape web site ... > slicking on the rfc number brings up the RFC summary. ...
    (comp.security.ssh)
  • CREATE SYMMETRIC KEY
    ... Server 2005, with the main purpose to use the encryption capabilities of SQL ... CREATE SYMMETRIC KEY SSN_Key_01 ... DECRYPTION BY CERTIFICATE HumanResources037; ...
    (microsoft.public.sqlserver.security)
  • RE: CREATE SYMMETRIC KEY
    ... with the main purpose to use the encryption capabilities of SQL ... CREATE SYMMETRIC KEY SSN_Key_01 ... ENCRYPTION BY CERTIFICATE HumanResources037; ... DECRYPTION BY CERTIFICATE HumanResources037; ...
    (microsoft.public.sqlserver.security)
  • Re: Encrypting Email: What do I Need?
    ... > recipients have their own X.509 certificates. ... > all keeping our private keys to ourselves. ... but I am new to encryption. ... - you use the PUBLIC key of any recipientto encrypt THAT the symmetric key bytes ...
    (microsoft.public.dotnet.security)
  • Re: CREATE SYMMETRIC KEY
    ... AES is only supported by SQL Server on Windows 2003. ... To test it out using encryption, I created a database, TestEncrypt, ... CREATE SYMMETRIC KEY SSN_Key_01 ... DECRYPTION BY CERTIFICATE HumanResources037; ...
    (microsoft.public.sqlserver.security)