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

• OomBoom

  Hall of Fame

  Points: 3481

  More actions

  January 18, 2010 at 4:50 am

  #1104870

  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

  January 19, 2010 at 3:20 am

  #1105300

  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