Changing a certifcate+key algorithm by dropping & recreating with the same names 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
Just to feed back on this, the process worked fine and the data was re-encrypted successfully.  

Therefore despite some concerns raised over symmetrical data encryption, (of which there are business rules to justify this), all worked well.  

All stored procedures that reference the key continued to work fine without any modification being required.