?
Solved

sql server 2005 certificates for encrypting and decrypting data

Posted on 2009-05-13
14
Medium Priority
?
862 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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
 
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 2000 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

764 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