index > SQL Server Security > Encryption concepts

Encryption concepts

I've been following the How-to topics from the books online

Creating a Database Master Key protected by a password ( not SMK)
Creating a Certificate protected by the Database Master key ( not specifying password)
Creating a symmetric key protected by the Certificate

Using the symmetric key to encrypt the data

At last I use the following query to read the result

OPEN SYMMETRIC KEY SSN_Key_01

DECRYPTION BY CERTIFICATE HumanResources001

SELECT EncryptedNationalIDNumber AS ‘Encrypted ID Number�?

CONVERT (nvarchar, DecryptByKey(EncryptedNationalIDNumber))

AS ‘Decrypted ID Number�?/span>

FROM HumanResources.Employee

Of coz, I got the correct result.

What I keep thinking is, During the query, I use the Certificate which is protected by the Database Master key

Why shouldn't I use OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password' before using the certificate to decrypt the symmetric key?

And at the end, when should I use the OPEN MASTER KEY ?

Edited by新手陳先�?/span> Tuesday, August 04, 2009 9:08 AM
Edited by新手陳先�?/span> Tuesday, August 04, 2009 7:55 AM

新手陳先� Tuesday, August 04, 2009 7:48 AM

Your post was clear. When you created a database master key, its *automatically* encrypted by the service master key. So you *don't* have to explicitly open it unless you specifically drop the encryption by the SMK (which you say you haven't).

>Should I use OPEN MASTER KEY too?

>is the DMK automatically opened? (I only encrypt it by password)

Yes, its automatically opened.

Cheers

Bob Beauchemin

SQLskills

"æ–°æ‰‹é™³å…ˆç”Ÿ" wrote in message news:eea71c3b-2e28-46d 3-9682-9c05f215d2ee...
um sorry maybe my post isn't clear enough

the symmetric key is protected by the certificate
and the certificate is protected by the DMK

When I OPEN SYMMETRIC KEY, decryption by certificate

Should I use OPEN MASTER KEY too?

is the DMK automatically opened? (I only encrypt it by password, but not dropping the SMK encrypt)

Marked As Answer byRaul Garcia - MSModeratorWednesday, August 05, 2009 9:33 PM

Bob Beauchemin Wednesday, August 05, 2009 4:12 AM

Exactly!

Cheers,
Bob

Marked As Answer by新手陳先�?/span> Thursday, August 06, 2009 4:41 AM

Bob Beauchemin Wednesday, August 05, 2009 2:54 PM

>Creating a Database Master Key protected by a password ( not SMK)

Are you sure it's not protected by service master key? When you create a database master key protected by a password, SQL Server automatically adds an encryption by service master key. You can check the encryptions by executing select * from sys.key_encryptions. Unless you execute "alter master key drop decryption by service master key", you'll have one.

If you have the (automatic) encryption by SMK, you will need to open the database master key before you can use the cert. You'll get an error on your "OPEN SYMMETRIC KEY" statement, otherwise.

You usually leave the SMK encryption on the master key, so that you don't have to specify the password each time. If you need to restore to another instance (where SMK is different) then you need to:

1. alter master key drop encryption by service master key.

2. After restoring you'd

a. open master key decryption by password = '...'

b. alter masterkey add encryption by service master key (to generate an encryption withthe new instance's SMK)

Hope this helps,

Bob Beauchemin

SQLskills

"æ–°æ‰‹é™³å…ˆç”Ÿ" wrote in message news:467d37cb-ca36-487 8-bab2-19db159e8bf2...
I've been following the How-to topics from the books online

Creating a Database Master Key protected by a password ( not SMK)
Creating a Certificate protected by the Database Master key ( not specifying password)
Creating a symmetric key protected by the Certificate

Using the symmetric key to encrypt the data

At last I use the following query to read the result

OPEN SYMMETRIC KEY SSN_Key_01

DECRYPTION BY CERTIFICATE HumanResources001

SELECT EncryptedNationalIDNumber AS â€˜Encrypted ID Numberâ€™,

CONVERT (nvarchar, DecryptByKey(EncryptedNationalIDNumber))

AS â€˜Decrypted ID Numberâ€™

FROM HumanResources.Employee

Of coz, I got the correct result.

What I keep thinking is, During the query, I use the Certificate which is protected by the Database Master key

Why shouldn't I use OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password' before using the certificate to decrypt the symmetric key?

And at the end, when should I use the OPEN MASTER KEY ?

Bob Beauchemin Tuesday, August 04, 2009 8:24 PM

um sorry maybe my post isn't clear enough

the symmetric key is protected by the certificate
and the certificate is protected by the DMK

When I OPEN SYMMETRIC KEY, decryption by certificate

Should I use OPEN MASTER KEY too?

is the DMK automatically opened? (I only encrypt it by password, but not dropping the SMK encrypt)

新手陳先� Wednesday, August 05, 2009 2:58 AM

>Should I use OPEN MASTER KEY too?

>is the DMK automatically opened? (I only encrypt it by password)

Yes, its automatically opened.

Cheers

Bob Beauchemin

SQLskills

"æ–°æ‰‹é™³å…ˆç”Ÿ" wrote in message news:eea71c3b-2e28-46d 3-9682-9c05f215d2ee...
um sorry maybe my post isn't clear enough

the symmetric key is protected by the certificate
and the certificate is protected by the DMK

When I OPEN SYMMETRIC KEY, decryption by certificate

Should I use OPEN MASTER KEY too?

is the DMK automatically opened? (I only encrypt it by password, but not dropping the SMK encrypt)