Switching encrypted column from Symmetric to Asymmetric encryption

Question

We have an existing table in an on-prem database (SQL Server 2008 R2) that utilizes cell-level encryption on a single column. The developer who originally encrypted the data created a self-signed certificate and a symmetric key using the following SQL:

USE DBName
GO
CREATE CERTIFICATE SelfSignedCert
WITH SUBJECT = 'Secure Data';
GO

CREATE SYMMETRIC KEY SymKey1
 WITH ALGORITHM = AES_128
 ENCRYPTION BY CERTIFICATE SelfSignedCert;
GO

To access the encrypted data, we have used a stored procedure that references the certificate and symmetric key – something like this:

OPEN SYMMETRIC KEY SymKey1
 DECRYPTION BY CERTIFICATE SelfSignedCert;
SELECT CONVERT(varchar(200), DecryptByKey(settingValueEncrypt)) AS settingValue
FROM dbo.SecureTable
WHERE settingName = @MySetting
CLOSE SYMMETRIC KEY SymKey1;

In addition, the lines above to OPEN and CLOSE the symmetric key are used whenever we’ve needed to INSERT new encrypted values or UPDATE existing ones.

If we were to now switch over to using asymmetric encryption on the same table and column, how should we go about doing so? Would it be as simple as:

a. Create a new password-protected certificate
b. Create a new asymmetric key
c. Decrypt the data in our existing encrypted field (using our existing key/cert) into a new/temporary plain text column
d. Encrypt the plain text column using the new cert and key into the existing encrypted field
e. Delete the temporary plain-text column
f. Update our stored procedure to reference the new certificate and public key

Answer 1

Hi @Mark Sanchez ,

The best practice is to add the new form of encryption before removing the old form of encryption.
After CLOSE SYMMETRIC KEY SymKey1, you also need DROP SYMMETRIC KEY SymKey1
Then follow the steps in this blog: Using Asymmetric Keys to Implement Column Encryption
However, One thing to note is that RSA_1024 and RSA_512 are deprecated. To use RSA_1024 or RSA_512 (not recommended) you must set the database to database compatibility level 120 or lower.
Here is a related official document: CREATE ASYMMETRIC KEY (Transact-SQL)

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.