Changing Encryption on database from triple des to aes

Question

Changing Encryption on database from triple des to aes

northplatteriver

SSC Enthusiast

Points: 181
More actions
August 7, 2023 at 5:17 pm

#4255107

We currently have a Windows 2016 server running Sql Server 2014 that uses triple des for field encryption. We are in the process of getting that changed to AES 128 or AES 256, but I am not exactly sure how this process works so I thought I would ask for some guidance or directions to some useful links. First Question... Is it better to upgrade to Sql Server 2016 before we change the encryption from Triple Des to AES or is it better to do this after the Sql Server instance has been upgraded....
Second Question... Does the data have to be unencrypted first and then re-encrypted or is it an inplace type of conversion. I am assuming the data must first be unencrypted, then the new keys created and then re-encrypted. There are not too many links on the web about this, but I am guessing it is not uncommon. Any help appreciated.
August 8, 2023 at 2:12 pm

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 735610 More actions · Answer 1

First question: Upgrade.

Doesn't matter, unless you are trying to get an encryption level/algorithm that requires 2016. This is independent of the version outside of that. FWIW, SQL Server 2016 is out of mainstream support already. I wouldn't upgrade below 2019, and would likely aim for 2022. License cost is the same.

Second Question: Process to change algorithm

You must decrypt the data and re-encrypt it with the new algorithm. For column level encryption (I assume this is what you mean by field level), each piece of data is encrypted entirely from your efforts. The server or instance know nothing about it. They can hold the keys for you (symmetric/asymmetric), but they don't know how you've encrypted things.

In fact, clients don't need to know. The DECRYPTBYKEY (https://learn.microsoft.com/en-us/sql/t-sql/functions/decryptbykey-transact-sql?view=sql-server-ver16) function doesn't require you to pick the key. That's known in the header of the data. Assuming clients have access to the old and new keys, you can just do this:

-- open key
open symmetric key myoldkey

update mytable
 set encryptedcol = encryptbykey(Key_GUID('NewEncryptionKey'), decryptbykey(encryptedcol))

Decrypt the old val and encrypt with the new key in one statement. I might do this in batches, as it can take time. However, clients can just get the values with the same code, assuming they have access to the new key and open it.

My process might be:

change code to ensure that all clients open both the new and old keys for operations.
change insert/update code to use the new key
update the encrypted values by decrypting and encrypt with new key

Do those in that order, which can be staged. Do one, make sure it works, then the second, then the third.

northplatteriver SSC Enthusiast Points: 181 More actions · Answer 2

Many thanks for your detailed response, incredibly helpful. The plan was to move to 2016 first because I have read there are some significant performance degradation in versions after 2016 (Brent Ozar), but I do see it is out of mainstream support so 2019 might be the better option.

The following is currently how we create our triple des key.... in your post it appears you are saying I can keep my key for Triple Des open along with a new key that I would create for AES open at the same time. First I need to create a new AES key

This is the script I currently use to create our Triple Des key

***************************************************************

-- The following MUST be done in order

-- DROP Certificates and keys first in object explorer

-- This will be run on both server1 and server2 order does not matter

-- DROP symmetric KEY

drop symmetric key TDesKey;

-- DROP Certificate

drop certificate cert_TDesKey;

drop master key;

create master key encryption by password = '##############';

OPEN MASTER KEY DECRYPTION BY PASSWORD = '################'

create certificate cert_TDesKey with subject = 'Certificate for accessing symmetric keys';

create symmetric key TDesKey

with algorithm = triple_des,

identity_value = 'Data encryption key',

key_source = '##### ###### ######## ############'

encryption by certificate cert_TDesKey;

-- TEST decrypt

open symmetric key TDesKey decryption by certificate cert_TDesKey;

************************************************************************

So can I just add the following to create my AES256 key and have them both exist simultaneously?

*******************************************************************

-AES256 Key Creation

create certificate cert_AES_256Key with subject = 'AES256 Certificate for accessing symetric AES256 keys';

CREATE SYMMETRIC KEY AES_256Key

WITH ALGORITHM = AES_256

identity_value = 'AES256 Data encryption key',

key_source = '##### ###### ######## ############'

ENCRYPTION BY CERTIFICATE CertAES_256Key;

GO

Steve Jones - SSC Editor SSC Guru Points: 735610 More actions · Answer 3

Little background. Apologies if you know this stuff.

A symmetric key is encrypted by one or more other methods. You can have one method (cert) that encrypts multiple symmetric keys.

At the same time, each symmetric key is protected by one or more methods. I could have a symm key protected two different certs or two different asymm keys. This is valid syntax

CREATE SYMMETRIC KEY PIISymKey10
WITH ALGORITHM = AES_192
ENCRYPTION BY PASSWORD = 'MyS3cr#tP@ssword'
             , ASYMMETRIC KEY HRProtection
     , CERTIFICATE MySalaryCert
             , ASYMMETRIC KEY HRProtection2
     , PASSWORD = 'AnotherS$trinSD';

You can also have multiple symm keys open in your session. You use the appropriate one to encrypt or decrypt data as needed.

A more detailed answer to your process question would likely be (for me):

create a new symmetric key using AES_256 (might as well go big) and protect (encrypt) this with your existing cert. No reason to change.
add code for clients that decrypt to open the second key with the first. This ensures the DECRYPTBYKEY will work and be able to pick the algorithm as needed
for clients that encrypt data, add code to open the new key and use that for encryption from now on. This works well, since step #2 means clients can decrypt no matter what.
Add a process that you run, likely in batches, that does this:
1. open both keys
2. update the row(s) using encryptbykey(key_guid('newkey'), decryptbykey(column))

That ought to get you moving.

Does that make sense?

HarshBit Newbie Points: 1 More actions · Answer 4

Hi All ,

I am trying to do something similar and need guidance on the process. I am new to this stuff .

Background Context : One of my client requirement is to see if they can upgrade their datahub/infocentre servers to use AEs encryption instead of RC4 encryption ?

They are currently having windows server 2016 on their datahub infocenter servers and using SQl database and are on cloud .

Please suggest the steps that need to e taken to upgrade from Rc4 to AES .

Also what points needs to considered before encryption

Another Question : will the decrypting the data first ( converting to plain text ) and then decrypting the text needed in this case since the client datahub/infocenters serveres are on cloud . If no please suggest the steps to be done to do this upgrade .

Thanks in advance.

Would be a great help from your end if someone can answer this in detail.

Steve Jones - SSC Editor SSC Guru Points: 735610 More actions · Answer 5

Please start a separate thread for your question, but ultimately to change encryption mechanisms, you need to decrypt and re-encrypt data.