Asymmetric Key size

Job details

Name:	Asymmetric Key size
Platform:	Sqlserver
Category:	Security
Premium package:	Security and compliance package
Description:	Checks that at least a 2048-bit encryption key size is used for asymmetric keys.
Long description:	Checks that at least a 2048-bit encryption key size is used for asymmetric keys.
Version:	1.2
Default schedule:	1 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’)]

Parameters

Name	Default value	Description
return status	1	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when the asymmetric key size is less than 2048-bit.
history threshold	365	The maximum number of days to keep statistics for in the historic tables.

Job Summary

Purpose: The purpose of this job is to monitor and ensure that all asymmetric encryption keys used in SQL Server databases have a minimum key size of 2048 bits, which is a commonly accepted standard for secure data encryption.
Why: This job is important to ensure the security standards are met and maintained within the system. Using keys of insufficient length may lead to vulnerabilities and potential data breaches.
Manual checking: You can check this manually in SQL Server by using the following commands to query key sizes:

SELECT name, algorithm_desc, key_length FROM sys.asymmetric_keys WHERE key_length < 2048;

Job Configuration

“Job Object”: It revolves primarily around the usage of a stored procedure named dbw_asymmetric_key_size_proc which checks key sizes across databases.
“Scheduling”: Default execution is set to monthly, occurring on the first day of each month (configured as “1 1 1 *”).
“Dependencies”: There are dependencies on certain objects and supporting tables like:
- Main procedure dbw_asymmetric_key_size_proc
- Status table dbw_asymmetric_key_size_info
- History table dbw_asymmetric_key_size_histr

Job Implementation

The SQL procedure iterates through all online and writable databases, excluding system databases like tempdb, to check the size of asymmetric keys.
If any key is found with a size less than 2048 bits, details such as database name, key name, and algorithm are logged.
Historical data about the keys is maintained and old records beyond a configurable threshold (default 365 days) are purged.
The results are used to update compliance reporting, ensuring that system administrators or auditors can easily verify compliance with encryption standards.

Job Reporting

Key Output: A detailed report is generated about databases using encryption keys smaller than 2048 bits.
Report Details: Reports include Database Name, Key Name, Key Size, and the last detection date.
dbw_asymmetric_key_size_info table provides current status while dbw_asymmetric_key_size_histr table keeps history of detected changes over time.

Database	Key Name	Key Size	Last Detected
ExampleDB	Key1	1024	2023-01-01

Additionally, there’s a graphical representation aggregating statistical data about key sizes checked per day.

Upgrades and Compatibility

“Upgrade Path”: This job has been updated to version 1.2 from 1.1 with improvements in the procedure to include better handling of exceptions and historical data cleanup.
“Compatibility”: Designed for Microsoft SQL Server versions post-2005, including Azure SQL Managed Instances.

This robust monitoring setup not only complies with encryption best practices but also helps in maintaining a secure data environment by continuously assessing and reporting the asymmetric key sizes in use.