We have a situation where a symmetric key has been created (a long time ago) using
TRIPLE_DES. This is used to encrypt a password column, and is used by around ten stored procedures (that utilizes these with
OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE MyCertificate01;)
I want to replace the certificate and key (to offer a modicum of future-proofing) with
AES_192. However, I'd prefer to avoid editing the stored procedures if possible.
I wrote the following routine that:
- Unencrypts the password column into a plain text field
- Drops the existing key and certificate
- Creates a new certificate and key with the same names as above
- Re-encrypts the plain text column using the new cert/key
My question is, to the trained-eye, is there anything here that sets off alarm bells, particularly because the old and new names are the same, and that these are used within stored procedures?
I've tested the code and it worked, but my lack of experience with encryption and a dose of information-overload today makes me very nervous, hence the plea for expertise...
-- Decrypt data ALTER TABLE [dbo].[tbl_Users] ADD PasswordClear nvarchar(250) NULL; GO OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE MyCertificate01; UPDATE [tbl_Users] SET PasswordClear = CONVERT(nvarchar(250), DECRYPTBYKEY(PasswordEnc)) CLOSE SYMMETRIC KEY SSN_Key_01; GO -- Drop old encryption ALTER TABLE [dbo].[tbl_Users] DROP COLUMN PasswordEnc; GO DROP SYMMETRIC KEY SSN_Key_01; GO DROP CERTIFICATE [MyCertificate01]; GO -- Create new certificate and key CREATE CERTIFICATE MyCertificate01 WITH SUBJECT = 'My Certificate 01'; GO CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = AES_192 ENCRYPTION BY CERTIFICATE MyCertificate01; GO ALTER TABLE [dbo].[tbl_Users] ADD PasswordEnc varbinary(256) NULL; GO OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE MyCertificate01; UPDATE [tbl_Users] SET PasswordEnc = ENCRYPTBYKEY(Key_GUID('SSN_Key_01'), PasswordClear); CLOSE SYMMETRIC KEY SSN_Key_01; GO ALTER TABLE [dbo].[tbl_Users] DROP COLUMN PasswordClear; GO ALTER TABLE [dbo].[tbl_Users] ALTER COLUMN PasswordEnc varbinary(256) NOT NULL; GO