CODEWITHSUNDEEP
sqlsql-server
user10807821
user10807821

Reputation:

Encrypt existing column data in SQL server

I want to encrypt existing data in SQL database columns eg. 'FirstName', 'LastName'

I tried following query to encrypt a single row:

DECLARE @FirstName VARCHAR(100) = (SELECT FirstName FROM db_table WHERE Id = 518)
DECLARE @LastName VARCHAR(100) = (SELECT LastName FROM db_table WHERE Id = 518)

OPEN SYMMETRIC KEY DemoKey  DECRYPTION BY CERTIFICATE MyCert; 

UPDATE db_table 
SET FirstName = EncryptByKey(Key_GUID('DemoKey'), @FirstName),
    LastName = EncryptByKey(Key_GUID('DemoKey'), @LastName)
WHERE Id = 518

CLOSE SYMMETRIC KEY DemoKey;

How do I encrypt multiple rows in the column?

Upvotes: 2

Views: 2383

Answers (1)

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30545

Just remove or modify where clause. That's all

OPEN SYMMETRIC KEY DemoKey  DECRYPTION BY CERTIFICATE MyCert; 

UPDATE db_table 
SET FirstName = EncryptByKey(Key_GUID('DemoKey'), FirstName),
    LastName = EncryptByKey(Key_GUID('DemoKey'), LastName)

Upvotes: 2

Related Questions