Solved

MS SQL 2008 R2, Table Filed Value --> Encryption & Decryption

Posted on 2011-09-22
1
348 Views
Last Modified: 2012-05-12
SQLQuery17.sql

Hi,

      Our requirement: We need to encrypt the table records (i.e.) field values using 3DES algorithm.

       For that, we got the attached code from MSDN site and working fine. But we have the following 2 questions.

Question 1: If we BACKUP & RESTORE the same database, the decryption is not working for old / existing records. But the new records (i.e. records created after restore) are working fine. But we need to decrypt the old records also. --- HOW?

Question 2: Suppose the master database or master key DAMAGED / LOST, how can we get the same key and decryption for old recods.... HOW?

We need solution for these 2 questions.

If you have any other better logic for encryption / decryption other than the attached code, please let us know, we will follow that logic.

We are developing code in ASP.NET C#3.5 & MS SQL 2008 R2
0
Comment
[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
1 Comment
 
LVL 5

Accepted Solution

by:
Eduardo Goicovich earned 250 total points
ID: 36580883
--1 backup certificate and private key

USE master
GO
BACKUP CERTIFICATE TestSRSPwd
TO FILE = ‘C:\TestSRSPwd.cer’
WITH PRIVATE KEY (FILE = ‘C:\TestSRSPwd.pvk’ ,
ENCRYPTION BY PASSWORD = ‘mY_P@$$w0rd’ )
GO

--2 restore
--2.1 recreate previous key
USE master
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘23987hxJKL969#ghf0%94467GRkjg5k3fd117r$$#1946kcj$n44nhdlj’
GO

--2.2 restore certificate and private key
CREATE CERTIFICATE TestSRSPwd
FROM FILE = ‘C:\TestSRSPwd.cer’
WITH PRIVATE KEY (FILE = ‘C:\TestSRSPwd.pvk’,
DECRYPTION BY PASSWORD = ‘mY_P@$$w0rd’);
GO
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

726 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