SQL Server must implement required cryptographic protections using cryptographic modules complying with applicable federal laws, Executive Orders, directives, policies, regulations, standards, and guidance.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-41412	SQL2-00-019500	SV-53940r1_rule		Medium

Description
Cryptography is only as strong as the encryption modules/algorithms employed to encrypt the data. Use of weak or untested encryption algorithms undermines the purposes of utilizing encryption to protect data. Use of cryptography to provide confidentiality and non-repudiation is not effective unless strong methods are employed with its use. Many earlier encryption methods and modules have been broken and/or overtaken by increasing computing power. The NIST FIPS 140-2 cryptographic standards provide proven methods and strengths to employ cryptography effectively. Detailed information on the NIST Cryptographic Module Validation Program (CMVP) is available at the following website: http://csrc.nist.gov/groups/STM/cmvp/index.html.

Description

Cryptography is only as strong as the encryption modules/algorithms employed to encrypt the data. Use of weak or untested encryption algorithms undermines the purposes of utilizing encryption to protect data. Use of cryptography to provide confidentiality and non-repudiation is not effective unless strong methods are employed with its use. Many earlier encryption methods and modules have been broken and/or overtaken by increasing computing power. The NIST FIPS 140-2 cryptographic standards provide proven methods and strengths to employ cryptography effectively. Detailed information on the NIST Cryptographic Module Validation Program (CMVP) is available at the following website: http://csrc.nist.gov/groups/STM/cmvp/index.html.

STIG	Date
Microsoft SQL Server 2012 Database Security Technical Implementation Guide	2014-01-17

Details

Check Text ( C-47949r2_chk )
Run the following SQL query to review SQL Server's cryptographic settings for each database: EXEC sp_MSforeachdb ' SELECT ''?'' AS ''database ?'', * FROM ?.sys.symmetric_keys ORDER BY name, algorithm_desc ' Note: The list of acceptable algorithms: "AES 128", "AES 192", "AES 256" and "Triple DES". If SQL Server cryptographic algorithms listed are found to be unacceptable with applicable federal laws, Executive Orders, directives, policies, regulations, standards and guidance, this is a finding.

Check Text ( C-47949r2_chk )

Run the following SQL query to review SQL Server's cryptographic settings for each database:
EXEC sp_MSforeachdb '
SELECT ''?'' AS ''database ?'', *
FROM ?.sys.symmetric_keys
ORDER BY name, algorithm_desc
'

Note: The list of acceptable algorithms: "AES 128", "AES 192", "AES 256" and "Triple DES".

If SQL Server cryptographic algorithms listed are found to be unacceptable with applicable federal laws, Executive Orders, directives, policies, regulations, standards and guidance, this is a finding.

Fix Text (F-46839r2_fix)
Implement required cryptographic protections using cryptographic modules complying with applicable federal laws, Executive Orders, directives, policies, regulations, standards, and guidance. Run the following SQL script to drop a symmetric key: USE <'database name'> DROP SYMMETRIC KEY <'key name'> Run the following SQL script to drop a certificate: USE <'database name'> DROP CERTIFICATE <'certificate name'> Configure symmetric keys to use approved encryption algorithms. Existing keys are not reconfigurable to use different algorithms. Run the following SQL script to create a certificate: USE <'database name'> CREATE CERTIFICATE <'certificate name'> ENCRYPTION BY PASSWORD = '<'password'>' FROM FILE = <'path/file_name'> WITH SUBJECT = 'name of person creating key', EXPIRY_DATE = '<'expiration date: yyyymmdd'>' Run the following SQL script to create symmetric key and assign a existing certificate: USE <'database name'> CREATE SYMMETRIC KEY <'key name'> WITH ALGORITHM = AES_256 ENCRYPTION BY <'certificate name'>

Fix Text (F-46839r2_fix)

Implement required cryptographic protections using cryptographic modules complying with applicable federal laws, Executive Orders, directives, policies, regulations, standards, and guidance.

Run the following SQL script to drop a symmetric key:
USE <'database name'>
DROP SYMMETRIC KEY <'key name'>

Run the following SQL script to drop a certificate:
USE <'database name'>
DROP CERTIFICATE <'certificate name'>

Configure symmetric keys to use approved encryption algorithms. Existing keys are not reconfigurable to use different algorithms.

Run the following SQL script to create a certificate:
USE <'database name'>
CREATE CERTIFICATE <'certificate name'>
ENCRYPTION BY PASSWORD = '<'password'>'
FROM FILE = <'path/file_name'>
WITH SUBJECT = 'name of person creating key',
EXPIRY_DATE = '<'expiration date: yyyymmdd'>'

Run the following SQL script to create symmetric key and assign a existing certificate:
USE <'database name'>
CREATE SYMMETRIC KEY <'key name'>
WITH ALGORITHM = AES_256
ENCRYPTION BY <'certificate name'>