Asymmetric keys used by the DBMS for encryption of sensitive data should use DoD PKI Certificates. Private keys used by the DBMS should be protected in accordance with NIST (unclassified data) or NSA (classified data) approved key management and processes.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-15142	DG0166-SQLServer9	SV-19465r2_rule	IAKM-1 IAKM-2 IAKM-3	Medium

Description
Encryption is only effective if the encryption method is robust and the keys used to provide the encryption are not easily discovered. Without effective encryption, sensitive data is vulnerable to unauthorized access.

STIG	Date
Microsoft SQL Server 2005 Database Security Technical Implementation Guide	2015-06-16

Details

Check Text ( C-20481r2_chk )
If no data is identified as being sensitive or classified by the Information Owner, in the System Security Plan or in the AIS Functional Architecture documentation, this check is Not a Finding. If no identified sensitive or classified data requires encryption by the Information Owner in the System Security Plan and/or AIS Functional Architecture documentation, this check is Not a Finding. Note: Protection of DBMS system data is reviewed in other checks. From the query prompt: SELECT name FROM [master].sys.databases WHERE state = 0 Repeat for each database: From the query prompt: USE [database name] SELECT k.name, SUSER_SNAME(u.sid), k.pvt_key_encryption_type FROM sys.asymmetric_keys k, sys.database_principals u WHERE k.principal_id = u.principal_id ORDER BY k.name, SUSER_SNAME(u.sid), k.pvt_key_encryption_type If the total number of records returned for all databases is 0, this is Not a Finding. Note: Compliance will be measured as part of the security review of the application. For each asymmetric key identified as being used to encrypt sensitive date, verify the key owner is not a SYSADMIN: From the query prompt: USE [database name] SELECT o.name, USER_NAME(p.grantee_principal_id), p.permission_name FROM sys.database_permissions p, sys.objects o WHERE p.major_id = o.object_id AND p.class_desc = 'ASYMMETRIC KEY' ORDER BY o.name, USER_NAME(p.grantee_principal_id), p.permission_name If the key owner listed from the previous query is listed as a sysadmin member, this is a Finding. If any key owner of a key listed above is not the application object owner account or an account specific to the application as documented in the System Security Plan, this is a Finding. Review any asymmetric keys whose private key is not encrypted: From the query prompt: SELECT name FROM [master].sys.asymmetric_keys WHERE pvt_key_encryption_type = 'NA' ORDER BY name If any records are returned, this is a Finding. Examine evidence that an audit record is created whenever the asymmetric key is accessed by other than authorized users. In particular, view evidence that access by a SYSADMIN or other system privileged account results in the generation of an audit record. This is required because system privileges allow access to encryption keys and can use them to access sensitive data where they do not have a need to know. If an audit record is not generated for unauthorized access to the asymmetric key, this is a Finding. Note: SQL Server does not provide use of encryption keys stored outside of the instance except to create keys stored within the instance. Therefore, protection of externally stored keys is not addressed for SQL Server in this check.

Check Text ( C-20481r2_chk )

If no data is identified as being sensitive or classified by the Information Owner, in the System Security Plan or in the AIS Functional Architecture documentation, this check is Not a Finding.

If no identified sensitive or classified data requires encryption by the Information Owner in the System Security Plan and/or AIS Functional Architecture documentation, this check is Not a Finding.

Note: Protection of DBMS system data is reviewed in other checks.

From the query prompt:

SELECT name
FROM [master].sys.databases
WHERE state = 0

Repeat for each database:

From the query prompt:

USE [database name]
SELECT k.name, SUSER_SNAME(u.sid), k.pvt_key_encryption_type
FROM sys.asymmetric_keys k, sys.database_principals u
WHERE k.principal_id = u.principal_id
ORDER BY k.name, SUSER_SNAME(u.sid), k.pvt_key_encryption_type

If the total number of records returned for all databases is 0, this is Not a Finding.

Note: Compliance will be measured as part of the security review of the application.

For each asymmetric key identified as being used to encrypt sensitive date, verify the key owner is not a SYSADMIN:

From the query prompt:

USE [database name]
SELECT o.name, USER_NAME(p.grantee_principal_id), p.permission_name
FROM sys.database_permissions p, sys.objects o
WHERE p.major_id = o.object_id
AND p.class_desc = 'ASYMMETRIC KEY'
ORDER BY o.name, USER_NAME(p.grantee_principal_id), p.permission_name

If the key owner listed from the previous query is listed as a sysadmin member, this is a Finding.

If any key owner of a key listed above is not the application object owner account or an account specific to the application as documented in the System Security Plan, this is a Finding.

Review any asymmetric keys whose private key is not encrypted:

From the query prompt:

SELECT name
FROM [master].sys.asymmetric_keys
WHERE pvt_key_encryption_type = 'NA'
ORDER BY name

If any records are returned, this is a Finding.

Examine evidence that an audit record is created whenever the asymmetric key is accessed by other than authorized users. In particular, view evidence that access by a SYSADMIN or other system privileged account results in the generation of an audit record. This is required because system privileges allow access to encryption keys and can use them to access sensitive data where they do not have a need to know.

If an audit record is not generated for unauthorized access to the asymmetric key, this is a Finding.

Note: SQL Server does not provide use of encryption keys stored outside of the instance except to create keys stored within the instance. Therefore, protection of externally stored keys is not addressed for SQL Server in this check.

Fix Text (F-18431r1_fix)
Use DOD code-signing certificates to create asymmetric keys stored in the database and used to encrypt sensitive data stored in the database. Assign the application object owner account as the owner of the asymmetric key. Create audit events for access to the key by other than the application owner account or approved application objects. Revoke any privileges assigned to the asymmetric key to other than the application object owner account and authorized users. Protect the private key by encrypting it with the database or service master key.

Fix Text (F-18431r1_fix)

Use DOD code-signing certificates to create asymmetric keys stored in the database and used to encrypt sensitive data stored in the database.

Assign the application object owner account as the owner of the asymmetric key.

Create audit events for access to the key by other than the application owner account or approved application objects.

Revoke any privileges assigned to the asymmetric key to other than the application object owner account and authorized users.

Protect the private key by encrypting it with the database or service master key.