SYMMETRIC KEY Incorrect syntax near 'TRIPLE_DES'

Question

SYMMETRIC KEY Incorrect syntax near 'TRIPLE_DES'

DamianC

SSCrazy Eights

Points: 8015
More actions
September 19, 2019 at 11:10 am

#3681330
Hi All

I'm using SQL2016
I'm having an issue with a piece of code generated in SQL2008
Sample code below
This returns Incorrect syntax near 'TRIPLE_DES'.
So, looking around, it appears as though TRIPLE_DES has been deprecated
Is there an obvious replacement for TRIPLE_DES here?
```
CREATE SYMMETRIC KEY [My_SYMMETRIC_Key]
    AUTHORIZATION [dbo]
    WITH ALGORITHM = TRIPLE_DES
    ENCRYPTION BY PASSWORD = N'password';
```
- Damian
September 25, 2019 at 4:04 am

This was removed by the editor as SPAM
September 30, 2019 at 4:04 am

This was removed by the editor as SPAM
October 1, 2019 at 10:38 am

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

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

Thom A SSC Guru Points: 99126 More actions · Answer 1

As you're on 2016 you have to use AES_128, AES_192, or AES_256; all other algorithms have been deprecated. As per the documentation if you want to use a different (deprecated) algorithm you need to use compatibility mode 120 or lower on the database you are creating the key on.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Steve Jones - SSC Editor SSC Guru Points: 734434 More actions · Answer 2

Use AES. You'll have to decrypt, encrypt your data, but it's more secure.

DamianC SSCrazy Eights Points: 8015 More actions · Answer 3

Thanks, I'll look at AES as I'd like to move this project into 2016

- Damian

DamianC SSCrazy Eights Points: 8015 More actions · Answer 4

Appreciate I could test this but what if i just changed the line WITH ALGORITHM = TRIPLE_DES to WITH ALGORITHM = AES_128

Or is it not that simple?

- Damian

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

You can do that, but the key will not decrypt any data that you've already encrypted. These symmetric keys are deterministic with the same identity and key source, so you can create these and use them to encrypt data, drop them, then create them later to decrypt data.

This management of keys across time is a tricky business, so be careful how you convert this.

DamianC SSCrazy Eights Points: 8015 More actions · Answer 6

Thanks Steve

In this instance, the table is truncated before been populated and encrypted so it sounds like it should be fine (I will test though).

It is decrypted fairly soon after

I get your point regarding existing encrypted data and that makes sense as it would try to use the new algorothm for the decryption of data encrytped using a different algorithm.

- Damian