Solved

sql server 2005 certificates for encrypting and decrypting data

Posted on 2009-05-13
14
825 Views
Last Modified: 2012-05-06
Question I have relates to encryption and decryption of data using Certificates in Sql Server 2005 and how do I recover encrypted data back if I drop this certificate accidentally? Or is this even possible? First I drop already existing certificate and then I tried recreating new certificate where it generates new certificate id and later tried to decrypt encrypted data with new certificate but with no success. Is this even possible? If not then should we rely on certificates that can so easily be dropped. What is the best approach? How can I retrieve encrypted data that was once encrypted using old certificate key.


Example:
 

--Create certificate

CREATE CERTIFICATE WebSitePWDCert

ENCRYPTION BY PASSWORD ='s#29&^*@!q'

WITH SUBJECT = 'This cert encrypts credit card'

GO
 

--Create table if not exist

IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ini_file]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[ini_file](

	[id] [int] IDENTITY(1,1) NOT NULL,

	[ini_name] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

	[ini_binary] [varbinary](8000) NULL,

 CONSTRAINT [PK_ini_file] PRIMARY KEY CLUSTERED 

(

	[id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]
 

GO

END

GO
 

--Insert Encrypted card number to ini_file table

INSERT INTO ini_file (ini_name,ini_binary)

VALUES ('Access',EncryptByCert(cert_id('WebSitePWDCert'),'473429382900'))

GO
 

--Decrypts and shows credit card number. Works Fine.

select	convert(varchar(max), 

	DecryptByCert(cert_id('WebSitePWDCert'),ini_binary,N's#29&^*@!q'))

  from	ini_file

 where	ini_name ='ACCESS'

GO
 

--Here I drop certificate and then rerun select query
 

drop certificate WebSitePWDCert
 

-- Select query now returns null. At this point did I loose all my encrypted data?

 select convert(varchar(max), 

            DecryptByCert(cert_id('WebSitePWDCert'),ini_binary,N's#29&^*@!q'))

   from ini_file

where ini_name ='ACCESS'
 

--Now I Create New Certificate

CREATE CERTIFICATE WebSitePWDCert

ENCRYPTION BY PASSWORD ='s#29&^*@!q'

WITH SUBJECT = 'This cert encrypts credit card'

GO
 

--Execute same select query. But won't work now.

  select convert(varchar(max), 

             DecryptByCert(cert_id('WebSitePWDCert'),ini_binary,N's#29&^*@!q'))

    from  ini_file

 where  ini_name ='ACCESS'

GO
 

-- insert new record using new certificate

INSERT INTO ini_file (ini_name,ini_binary)

VALUES ('Access',EncryptByCert(cert_id('WebSitePWDCert'),'473429382900'))

GO
 

--Execute same select query. Works just for that one record that was just inserted.

  select convert(varchar(max), 

             DecryptByCert(cert_id('WebSitePWDCert'),ini_binary,N's#29&^*@!q'))

    from  ini_file

 where  ini_name ='ACCESS'

GO

Open in new window

0
Comment
Question by:grg-it
  • 8
  • 6
14 Comments
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24382395
No, it is a potential problem...  Think of it as a database, what do you do about securing a database ? simple, back it up. That is what you need to do with your certificates as well...

BACKUP CERTIFICATE certname TO FILE = 'path_to_file'
    [ WITH PRIVATE KEY
      (
        FILE = 'path_to_private_key_file' ,
        ENCRYPTION BY PASSWORD = 'encryption_password'
        [ , DECRYPTION BY PASSWORD = 'decryption_password' ]
      )
    ]


To restore you use the create certificate and point it to the file...
0
 
LVL 1

Author Comment

by:grg-it
ID: 24384498
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?
0
 
LVL 1

Author Comment

by:grg-it
ID: 24384573
Also what should be the file extension I need to back it up to?
0
 
LVL 1

Author Comment

by:grg-it
ID: 24384848
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?
0
 
LVL 1

Author Comment

by:grg-it
ID: 24386044
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
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24386454
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

Open in new window

0
 
LVL 1

Author Comment

by:grg-it
ID: 24387308
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..
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 24387888
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 .
0
 
LVL 1

Author Comment

by:grg-it
ID: 24388066
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.]
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24388208
Do you have a master key ?  

select is_master_key_encrypted_by_server,* from sys.databases
0
 
LVL 1

Author Comment

by:grg-it
ID: 24389850
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.
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 24390108
Well, it depends on use of password... In the example above, it specifies a password as part of the create certificate. If it doesn't have that, then it needs a password somehow and looks for the master key.

From BOL: ENCRYPTION BY PASSWORD = 'password'
Specifies the password that will be used to encrypt the private key. Use this option only if you want to encrypt the certificate with a password. If this clause is omitted, the private key will be encrypted using the database master key.

The above snippet is not overly explicit about "must be this else that" in fact using terms like "optional" tends to paint the wrong picture. Bottom line is there must be a password either at certificate, or master key. By default, the private key is encrypted using the database master key. But, if that does not exist and no password is specified then you get the error.

Further down in BOL we get a better desription :

The ENCRYPTION BY PASSWORD option is not required when the private key will be encrypted with the database master key. Use this option only when the private key will be encrypted with a password. If no password is specified, the private key of the certificate will be encrypted using the database master key. Omitting this clause will cause an error if the master key of the database cannot be opened.

Does that help ? Might take a couple of readings, it almost makes sense the first few reads...
0
 
LVL 1

Author Comment

by:grg-it
ID: 24399071
Yes This clears my doubt... Thank you so much.. 500 points goes in to your pocket.. :)
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24399414
Thanks very much, and happy to help...
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now