question

artistlover avatar image
artistlover asked

Encrpyt and entire column

i have the code to encrypt one password at a time but i need to encrypt the entire table any idea how to do this? @txtUser VARCHAR(255), @txtPassword VARCHAR(255), @txtType VARCHAR(50) ) AS IF @txtType='enc' BEGIN OPEN SYMMETRIC KEY encPass_Key11 DECRYPTION BY CERTIFICATE encPassword01; UPDATE dbo.tbluser SET password_Encrypted = EncryptByKey(Key_GUID('encPass_Key11'), [password]) where txtuser =@txtuser; END IF @txtType='dec' BEGIN OPEN SYMMETRIC KEY encPass_Key11 DECRYPTION BY CERTIFICATE encPassword01; SELECT u.*,CONVERT(varchar(125), DecryptByKey(password_Encrypted))AS 'pw' FROM dbo.tbluser AS u WHERE txtuser=@txtuser AND CONVERT(varchar(255), DecryptByKey(password_Encrypted))=@txtPassword AND (ISNULL(ysnInactive,'') ='' OR ysnInactive =0 ) ; END
encryption
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

When data has to be refreshed because this is development site. Password encryption is lost and it has to be regenerated. The passwords have to be re encrypted.
0 Likes 0 ·
JohnM avatar image
JohnM answered
So just that single 'Password' column, yes? If so, you can do this by just removing the WHERE clause from your encryption statement. You can see a reference here: http://www.databasejournal.com/features/mssql/article.php/3922881/Column-Level-Encryption-in-SQL-Server.htm That should work. Hope this helps!
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image
JohnM answered
As far as I know, there isn't any type of "table" level encryption. You can of course encrypt all of the columns in the table but that might seem like over kill or potentially a bad idea if your table is large. What are you trying to accomplish? Or are you trying to encrypt the entire column at once?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.