Data encryption

Question

Data encryption

Trybbe

Hall of Fame

Points: 3967
More actions
December 18, 2008 at 3:23 am

#197328

HI! I need to encrypt the salary field in my staff table. I have tried a few lines but I just don't know how to implement it on the table:
CREATE SYMMETRIC KEY SecureSymmetricKey
WITH ALGORITHM = DESX
ENCRYPTION BY PASSWORD = N'StrongPassword';
---------------------------------------------------
OPEN SYMMETRIC KEY SecureSymmetricKey
DECRYPTION BY PASSWORD = N'StrongPassword';
---------------------------------------------------
I need now help in implementing this on a column in the table. The site where I got this code from uses a declared variable and not a table hence my dilema.
Please help

Viewing 5 posts - 1 through 4 (of 4 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Look up the EncryptByKey and DecryptByKey functions in Books Online.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Trybbe Hall of Fame Points: 3967 More actions · Answer 2

Thanks for the feedback Gail

I got it right, using the update statement:

OPEN Symmetric Key HRSecureSymmetricKey

Decryption By Password = N'HRTestPassword'

UPDATE Encrypt_SAL

SET SalEncrypt = ENcryptByKey(Key_GUID('HRSecureSymmetricKey'),CAST(TotalPack AS VARCHAR(50)));

So now I gonna implement. Thanx

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

thatok (12/18/2008)
Decryption By Password = N'HRTestPassword'

I hope you're going to make that password a little more complex in production.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Trybbe Hall of Fame Points: 3967 More actions · Answer 4

By all means. I wouldn't dream of going into production with that kind of password.