Symmetric Key encryption

Job details

Name:	Symmetric Key encryption
Platform:	Sqlserver
Category:	Security
Premium package:	Security and compliance package
Description:	Checks that only AES_128, AES_192, and AES_256 symmetric key encryption algorithms are in use.
Long description:	Checks that only AES_128, AES_192, and AES_256 symmetric key encryption algorithms are in use. Algorithms DES, DESX, RC2, RC4 and RC4_128 are considered weak and should no longer be used.
Version:	1.2
Default schedule:	30 1 1 *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & hasengine=‘YES’ & eng_inst_priv = 0 & (engine_edition = ‘Microsoft SQL Server’	engine_edition = ‘Azure SQL Managed Instance’)]

Name	Default value	Description
return status	1	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when weak symmetric key encryption is in use.
history threshold	365	The maximum number of days to keep statistics for in the historic tables.

Purpose: The purpose of this job is to ensure that databases are using only strong symmetric key encryption algorithms (AES_128, AES_192, AES_256) and to flag any usage of weak encryption algorithms (DES, DESX, RC2, RC4, RC4_128).
Why: This job is important to maintain the security integrity of data within the databases. Usage of weak encryption algorithms can lead to vulnerabilities and potential data breaches. Monitoring and updating encryption practices help in complying with security standards and regulations.
Manual checking: To manually check the encryption algorithms used in symmetric keys, a SQL command can be issued to list the encryption type for each key:

SELECT name, algorithm_desc FROM sys.symmetric_keys WHERE algorithm_desc NOT IN ('AES_128', 'AES_192', 'AES_256')

Implementation: The procedure involves creating tables to track current encryption status and historical changes. It also involves cursor operations across all databases to identify non-compliant encryption usage. The results are inserted into tracking tables and a compliance count is maintained.
Dependencies:
- Main Procedure: ‘dbw_symmetric_key_encryption_proc’ – Executes the encryption checks and data logging.
- Current Status Table: Tracks current algorithm used per database and key.
- History Table: Stores aggregated historical data on encryption usage.

Report Title: “Encryption Algorithms”
Contents:
- The report shows current encryption algorithms used across different databases.
- Provides aggregated statistics over time showing how often weak encryption algorithms were detected.
- Uses graphical representations (bar charts) for visual analysis over time.

Compliance and Security: This job is aligned with security best practices that recommend the use of strong encryption algorithms to protect data.
Automated Monitoring Schedule: By default, scheduled to run yearly unless overridden by system requirements or compliance standards.

Historical Data Management: The task includes mechanisms to handle historical data, ensuring that statistics older than a specified threshold (e.g., 365 days) are removed, optimizing performance and storage.
Error Handling and Logging: Errors are captured with detailed messages including the position in the script where it failed, facilitating troubleshooting and corrective actions.
Upgrade Considerations: When upgrading from a previous version (e.g., 1.1 to 1.2), modifications to procedures reflect the improved handling of encryption checks and data management, ensuring compatibility with newer database versions.