Assume that you use SQL Server 2016 and trying to create a Symmetric Key or Asymmetric Key for encrypting data. If you try use an algorithm like TRIPLE_DES, you will get the mentioned error;
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near 'TRIPLE_DES'.
Here is a sample code for seeing this error;
-- Create a database CREATE DATABASE Sales; GO -- Connect with newly cerated database USE Sales; GO -- Create a master key CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd'; GO -- Create a certificate for protecting the our key CREATE CERTIFICATE SalesCertificate WITH SUBJECT = 'Sales Certificate'; GO -- Create the key for encrypting data -- Note that the created certificate protects it. -- However, this will throw an error CREATE SYMMETRIC KEY SalesKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE SalesCertificate; GO
Now, what is the reason for this. The reason for this is, this algorithm is deprecated in SQL Server 2016. Not only that, All Algorithms except AES_128, AES_192, and AES_256 are deprecated in SQL Server 2016.
What if you still need one of these deprecated algorithms? Yes, it is possible, but you need to downgrade the database by changing the Compatibility Level 120 or below.
This code shows the way of doing it.
USE master; GO -- Change the compatibility level to 120 ALTER DATABASE Sales SET COMPATIBILITY_LEVEL = 120; -- And check again USE Sales; GO -- This will work now without any error CREATE SYMMETRIC KEY SalesKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE SalesCertificate; GO
Even though you can use the algorithm after changing the Compatibility Level, remember lower Compatibility Level might not let you to use all functionalities available with SQL Server 2016. Therefore, if possible, use allowed algorithms only.