Incorrect syntax near 'TRIPLE_DES'. - SQL Server throws an error when try to use algorithms

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;

view plainprint?

1. -- Create a database  
2. CREATE DATABASE Sales;  
3. GO  
4.   
5. -- Connect with newly cerated database  
6. USE Sales;  
7. GO  
8.   
9. -- Create a master key  
10. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';  
11. GO   
12.   
13. -- Create a certificate for protecting the our key   
14. CREATE CERTIFICATE SalesCertificate WITH SUBJECT = 'Sales Certificate';  
15. GO  
16.   
17. -- Create the key for encrypting data  
18. -- Note that the created certificate protects it.  
19. -- However, this will throw an error  
20. CREATE SYMMETRIC KEY SalesKey  
21. WITH ALGORITHM = TRIPLE_DES    
22. ENCRYPTION BY CERTIFICATE SalesCertificate;  
23. GO  

-- 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.

view plainprint?

1. <span style="text-align: left;">USE master;  
2. GO  
3.   
4. -- Change the compatibility level to 120  
5. ALTER DATABASE Sales  
6. SET COMPATIBILITY_LEVEL = 120;  
7.   
8. -- And check again  
9. USE Sales;  
10. GO  
11.   
12. -- This will work now without any error  
13. CREATE SYMMETRIC KEY SalesKey  
14. WITH ALGORITHM = TRIPLE_DES    
15. ENCRYPTION BY CERTIFICATE SalesCertificate;  
16. GO  
17. </span>  

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.