data encryption for SQL 2005

Question

data encryption for SQL 2005

marchnw

SSC Rookie

Points: 31
More actions
April 29, 2008 at 9:06 am

#187077

I have a table where I store a user id and their password. Right now the password column is in plain text. I need it to be encrypted. Is there any way to change the data that is already there to be encrypted?

Viewing 7 posts - 1 through 7 (of 7 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Books Online has data on how to do this. Search for "encryption", and it will give you a How-To article on the subject. It tells how to set up an encrypted column, how to get the data into it, etc. From there, it should simply be a matter of replacing the data in the original column with the encrypted column.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

GSquared has it right although you need to change the data type of the column as well, which you can't do while it has data in it. You could add a new column (dropping the old one) and then rename it using SSMS after you are done or you can do a select into a new table then drop the old table and rename the new one. I would put the new table in a new schema, then drop the existing table, and then use alter schema old_schema transfer new_table. Like this:

[font="Courier New"]IF NOT EXISTS

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

CREATE MASTER KEY ENCRYPTION BY

PASSWORD = 'test_encryption_key_1'

GO

CREATE CERTIFICATE NationalIdNo

WITH SUBJECT = 'Citizenship National Id No';

GO

CREATE SYMMETRIC KEY NationalIdKey01

WITH ALGORITHM = TRIPLE_DES

ENCRYPTION BY CERTIFICATE NationalIdNo;

GO

-- Open the symmetric key with which to encrypt the data.

OPEN SYMMETRIC KEY NationalIdKey01

DECRYPTION BY CERTIFICATE NationalIdNo;

CREATE SCHEMA new_schema;

SELECT

EmployeeId,

EncryptByKey(Key_GUID('NationalIdKey01'), NationalIdNumber, 1, HashBytes('SHA1', CONVERT( VARBINARY, LoginId))) AS NationalIdNumber,

ContactID,

LoginID,

ManagerID,

Title,

BirthDate,

MaritalStatus,

Gender,

HireDate,

SalariedFlag,

VacationHours,

SickLeaveHours,

CurrentFlag,

rowguid,

ModifiedDate

INTO

new_schema.Employee

FROM

HumanResources.Employee

GO

DROP TABLE HumanResources.Employee

ALTER SCHEMA HumanResources Transfer new_schema.employee

[/font]

You would need to drop any foreign keys on the source table and add them to the new table so I would script it out first.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

marchnw SSC Rookie Points: 31 More actions · Answer 3

Thanks for the replies

I tried the example from Books Online. It would create my new column but wouldn't put anything in there.

Jack...I can't seem to get rid of an error with the code you gave. Here it is:

'CREATE SCHEMA' must be the first statement in a query batch.

Any ideas?

Thanks again

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Oops, my bad, I actually had corrected the code, but forget to add the correction to my post. You just need a GO on the line before the create schema statement.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

marchnw SSC Rookie Points: 31 More actions · Answer 5

I had already tried that. That's when I got an error saying, "Incorrect syntax near the keyword 'SELECT'." So I wasn't sure if it was right. What about the syntax could be incorrect? Sorry for the bother but this is the first time I've done anything like this.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

A GO after the create schema should work as well.

You could also do Alter Table Add password_encrypted varbinary(128) then

update table

Drop the old column and then in SSMS rename the encrypted column to password.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question