Encrypting Data in SQL Server 2005

Question

Encrypting Data in SQL Server 2005

webnoel

SSC Rookie

Points: 30
More actions
January 17, 2010 at 4:32 am

#219382

Hi there,
I am developing an Complaint registration application which requires utmost confidentiality. One of the requirements of the application is an encrypted database.
I have read through a lot of sites and it seems like most of the available encryption options involve "keys" that need to be stored somewhere.
I remember reading an article on encryption that allowed an SQL User to access an encrypted database (via stored procedures) to read and write data. The encryption involved the SQL Login and the Stored procedure would be doing all the encryption and decryption.
Does anyone know if this is possible? Has anyone come across this article?
I would appreciate any kind of help in this matter.
Regards
Glen

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

OomBoom Hall of Fame Points: 3481 More actions · Answer 1

Hi

I have seen these manual encryption procedures but why would you want to use it. It is going to cost alot of work to get it working and maintain it. The key that is created is stored in the database. You have to make a backup to be able to decrypt after a restore on another system. The backed up key can be secured in a safe on a CD.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

paulm-510034 SSC Enthusiast Points: 136 More actions · Answer 2

Hi,

Database encryption isn't generally recommended as it will slow your application.

I'd recommend encryption of table columns that contain sensitive data(user details, phone number, email, etc). Anything in the public domain isn't worth encrypting(company address, company fax, etc).

See example below

PaulM (administrator)

14-10-09 15:10

USE AdventureWorks

GO

--CREATE Master KEY

IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id=101)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'dkjuw4r$$#1946kcj$ngJKL95Q'

GO

--CREATE Certificate

CREATE certificate HRCert

WITH subject = 'Job Candidate Comments'

GO

--CREATE Symmetric KEY

CREATE Symmetric KEY Commentkey

WITH algorithm = DES

encryption BY certificate HRCert

GO

--ADD COLUMN TO TABLE

ALTER TABLE HumanResources.JobCandidate

ADD Comments varbinary(8000)

GO

--OPEN/Decrypt AND UPDATE

OPEN symmetric KEY Commentkey

Decryption BY certificate HRCert

UPDATE HumanResources.JobCandidate

SET Comments = EncryptByKey(Key_GUID('Commentkey'), 'NO commnets')

GO

--select data to view encrypted contents

select JobCandidate.ModifiedDate,Comments from HumanResources.JobCandidate

--OPEN/Decrypt AND select decrypted comments

OPEN symmetric KEY Commentkey

Decryption BY certificate HRCert

select JobCandidateID

, ModifiedDate

,Convert(varchar,DecryptByKey(Comments)) as "Decrypted Comments"

from HumanResources.JobCandidate

Your application then accesses the application using either sql or windows authentication(preferably the later) and you grant access for that id to the symmetric key and certificate.

I hope that helps.

Paul