5

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:

  1. Unencrypts the password column into a plain text field
  2. Drops the existing key and certificate
  3. Creates a new certificate and key with the same names as above
  4. 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
3
  • 1
    I would go the extra mile to remember the key used together with data items and make the procedure aware of the key to use for new encryption independent of the key for old ones. This way you can regularly create new keys and use them while no software changes are required to phase out old ones. (Independent if you only change the key or also the algorithm)
    – eckes
    May 16, 2019 at 20:22
  • Okay thanks. Could you please point me in the direction of any learning material I can go read to learn about achieving this? I assume its supported by SQL2012?
    – EvilDr
    May 17, 2019 at 8:21
  • No idea, but just to reiterate, passwords should not be encrypted (only)
    – eckes
    May 17, 2019 at 8:32

2 Answers 2

1

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.

0

I see the following problems:

  • there is a (short) time span where all passwords are unencrypted in the database (and log file and log backups and maybe some error logs or traces etc.), which could be a real security concern
  • better create a certificate with another name and do the decrypt / reencrypt in a single transaction

  • you should never be able to decrypt a password, otherwise it is never save (every developer may be able to call your decrypt routine and get the password of your CEO or your own password and use it to do some harmfull stuff in his / hers / your name)

  • instead of creating a certificate and working with ENCRYPTBYKEY() you should use HASHBYTES('SHA2_256', password + salt), the new password field should be a BINARY(32) in this case (length depends on the algorhytm)

  • when someone logs on, you usually should hash the password (preferable on the client side, so that the unencrypted password will never be send over the LAN to your server) and compare only the hashes. There are usually libraries for the common hashing functions (as SHA_256) in every programming language
  • you are not salting your passwords, so everyone who has the password 'P@assw0rd' has the same hash, which could be used for attacks (set your own password to whatever and looks, who has the same password)
  • so add a salt column (UNIQUEIDENTIFIER data type) with NEWID() as default and add it to the original password before encrypting it
  • in the logon process you may have to send the salt (which is not secret) for the current user to the client to add it to the typed password and hash it
  • when you are implementing the salting / hashing stuff (or make changes in the future), you may need to reset all passwords (because you can't decrypt them)
  • You can't longer (and should never be able to) send a forgotten password mail with the original password. If you have a web surface, send a special link to a reset password mask instead, otherwise set the password to a random one and sent this (this is always a weaker methode) and force the user to reset it after the first login.
3
  • 1
    From MSDN, "In SQL Server, a random salt value is always applied to the encryption. Because a salt value is implicitly generated and included with the encrypted output, you cannot compare values encrypted with SQL Server internal encryption functionality to see if they are equal."
    – EvilDr
    May 9, 2019 at 14:11
  • As for the rest of your points, there are business reasons for symmetric over non-symmetric encryption. The passwords are already reset as you described. The question is more about the dropping and recreating of certs/keys and the functional effect on procedures/logic/stability.
    – EvilDr
    May 10, 2019 at 8:23
  • An iterated and salted hash for password protection needs to be done in the application before sending it to the DB, it is supposed to be slow so better done in the app layer. (@Evildr The automatic salting of encryption does not help for hashes)
    – eckes
    May 17, 2019 at 8:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.