Re: Encrypting Data using SQL Server 2005

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)
  • Re: Encrypting Data using SQL Server 2005
    ... symmetric key password. ... create symmetric key signingKey with algorithm = triple_des encryption by ... open symmetric key signingkey decryption by password = 'theKey' ...
    (microsoft.public.sqlserver.security)
  • Re: SQL Server 2000 / 2005 Encryption
    ... This appears to be a "connection only" type of encryption. ... Can you encrypt the actual data in a database? ... SQL Server 2005 has the ability to generate self-signed certificates also to ... key, then create a certificate, then create a symmetric key. ...
    (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)