sql server 2005 certificates for encrypting and decrypting data

Mark

Thanx for your response. So if I accidentally delete certificate and then restore certificate by pointing it to this file will above code be still able to decrypt encrypted data?

Also what should be the file extension I need to back it up to?

It's erroring out both on server box and also on my machine:

BACKUP CERTIFICATE WebSitePWDCert TO FILE = 'C\WebSiteCert'
     WITH PRIVATE KEY
      ( FILE = 'C\WebSiteKey',
            DECRYPTION BY PASSWORD = 's#29&^*@!q' ,
            ENCRYPTION BY PASSWORD = 's#29&^*@!q'
      )

Cannot write into file 'C\WebSiteKey'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.

What am I doing wrong?

Also One more question and I will add to this since it's same topic:

Is database master key optional or mandatory for encrypting data? This question is from MCTS Exam 70-431.

Which of the following sentences are true for the database master key?(Choose all that apply.)
A. The database master key is optional
B. The database master key is mandatory if you want to encrypt data.
C. The database master key is created automatically when you create the first certificate.
D. The database master key is created manually.

Now answer to this question is A & D according to MCTS. But I myself tried creating certificate without creating master key and it gave me this message:

[Please create a master key in the database or open the master key in the session before performing this operation.]

So I believe it should be A, B & D. Please comment.

Thanx

Hi, sorry about the delays...

I would have incorrectly answered 'D', always think that certificates use database master key : http://msdn.microsoft.com/en-us/library/ms189586(SQL.90).aspx

But you don't. It does depend on the certificate and if created with "Self Signed" Key, then doesn't need database key.

The backup doesn't work if there is already a file there, and rotten thing doesn't take a variable, so have to exec the backup if using datetime. but should only be backed up once as a recognised name..., so that example below probably just needs to be : BACKUP CERTIFICATE mw_cert_ee_1 TO FILE = 'c:\mysafespot\mw_cert_ee_1'

Does that make sense ?

CREATE CERTIFICATE mw_cert_ee_1 
   ENCRYPTION BY PASSWORD = 'zingalalalu_007'
   WITH SUBJECT = 'EE test MW test and Asker test', 
   START_DATE = '1/1/2009',
   EXPIRY_DATE = '10/10/2009';
go
 
declare @file varchar(1000)
set @file = '''c:\ee\ms_sql_certificate_mw_cert_ee_1_'+replace((convert(varchar,getdate(),121)),':','-')+''''
exec ('BACKUP CERTIFICATE mw_cert_ee_1 TO FILE = '+@file)
GO
 
CREATE SYMMETRIC KEY mw_symmkey_ee_1 WITH ALGORITHM = TRIPLE_DES
    ENCRYPTION BY CERTIFICATE mw_cert_ee_1;
go
 
OPEN SYMMETRIC KEY mw_symmkey_ee_1
DECRYPTION BY CERTIFICATE mw_cert_ee_1 WITH PASSWORD = 'zingalalalu_007'
go
 
declare @f varchar(100)
declare @a varchar(100)
declare @b varchar(100)
set @f='too many secrets'
 
set @a=ENCRYPTBYKEY(KEY_GUID('mw_symmkey_ee_1'), @f) 
set @b=(DECRYPTBYKEY(@a))
 
print 'Value of @f' 
print @f
print 'encrypted value of @f' 
print @a
print 'decrypted value of @f' 
print @b
 
CLOSE SYMMETRIC KEY mw_symmkey_ee_1
go
 
DROP SYMMETRIC KEY mw_symmkey_ee_1
go
 
DROP CERTIFICATE mw_cert_ee_1 
go

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:

According to MCTS correct answer is A & D. But I feel B should also be correct. You said you would have incorrectly answered D. Where as I am talking about B not D.. Please comment..

MS trick question. For B to be true then the truer answer must be either C or D being manual or auto. We know that C cannot be the truist answer, otherwise all A B and D are unneccesary, and we can create database key. So, the only real question is do we need them at all ? And if you look at that link, it does imply that certificates do hang off a database master key, in which case why wouldn't C be the most correct answer ? Therefore, if we can create them manually ie D then it is highly likely that the only other answer to consider is A .

Ok then my next questioni is

When I try creating certificates without creating master key, why does it give me this error:

[Please create a master key in the database or open the master key in the session before performing this operation.]

Do you have a master key ?  

select is_master_key_encrypted_by_server,* from sys.databases

No I don't.. But that's what I am try to say.. When I try creating Certificates it's forcing me to create master key. Why?

And so that makes me believe Choice B is correct.. i.e

B. The database master key is mandatory if you want to encrypt data.

Yes This clears my doubt... Thank you so much.. 500 points goes in to your pocket.. :)