Microsoft SQL Server 2005 Database Security Technical Implementation Guide
Pick two releases to diff their requirements.
Open a previous version of this STIG.
Digest of Updates ✎ 20
Comparison against the immediately-prior release (V8R1.8). Rule matching uses the Group Vuln ID. Content-change detection compares the rule’s description, check, and fix text after stripping inline markup — cosmetic-only edits aren’t flagged.
Content changes 20
- V-15128 Medium check DBMS application user roles should not be assigned unauthorized privileges.
- V-15142 Medium check 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.
- V-15151 Medium check Fixed Database roles should have only authorized users or groups as members.
- V-15159 Medium check The Database Master key encryption password should meet DoD password complexity requirements.
- V-15161 Medium check The Database Master Key should be encrypted by the Service Master Key where required.
- V-15164 Medium check Asymmetric keys should be derived from DoD PKI certificates.
- V-15168 Medium check Symmetric keys should use a master key, certificate, or asymmetric key to encrypt the key.
- V-15172 Medium check Object permissions should not be assigned to PUBLIC or GUEST.
- V-15185 Medium check Asymmetric private key encryption should use an authorized encryption type.
- V-15607 Medium check Application objects should be owned by accounts authorized for ownership.
- V-15629 Medium check Application users privileges should be restricted to assignment using application user roles.
- V-15654 Medium check DBMS symmetric keys should be protected in accordance with NSA or NIST-approved key management technology or processes.
- V-2451 Medium check The guest user account should be disabled.
- V-2457 Medium check Object permission assignments should be authorized.
- V-2458 Medium check Permissions on system tables should be restricted to authorized accounts.
- V-2463 Medium check DDL permissions should be granted only to authorized accounts.
- V-2498 Medium check Permissions using the WITH GRANT OPTION should be granted only to DBA or application administrator accounts.
- V-3727 Low check Database applications should be restricted from using static DDL statements to modify the application schema.
- V-3823 Low check Custom and GOTS application source code stored in the database should be protected with encryption or encoding.
- V-5683 Medium check Application object owner accounts should be disabled when not performing installation or maintenance actions.
- RMF Control
- Severity
- M
- CCI
- Version
- DM1709-SQLServer9
- Vuln IDs
-
- V-2451
- Rule IDs
-
- SV-23779r2_rule
Checks: C-22789r3_chk
From the query prompt: SELECT name FROM [master].sys.databases WHERE name NOT IN ('master', 'tempdb') AND state = 0 Repeat for each database: From the query prompt: USE [database name] SELECT COUNT(grantee_principal_id) FROM sys.database_permissions WHERE grantee_principal_id = 2 AND state = 'G' AND permission_name = 'CONNECT' If any value other than a 0 is returned, this is a Finding.
Fix: F-19706r1_fix
Revoke connect permission from all databases except master and tempdb. From the query prompt: SELECT name FROM [master].sys.databases WHERE name NOT IN ('master', 'tempdb') AND state = 0 Repeat for each database: From the query prompt: USE [database name] REVOKE CONNECT FROM 'guest'
- RMF Control
- Severity
- M
- CCI
- Version
- DM1715-SQLServer9
- Vuln IDs
-
- V-2457
- Rule IDs
-
- SV-23785r2_rule
Checks: C-22833r2_chk
From the query prompt: SELECT name AS [Database Name] FROM [master].sys.databases WHERE name NOT IN ('tempdb', 'ReportServerTempDB') AND state = 0 Repeat for each database: From the query prompt: USE [Database Name] SELECT u.name AS [Principal]', o.name AS [Object], p.permission_name AS [Type] FROM sys.all_objects o, sys.database_principals u, sys.database_permissions p WHERE o.object_id = p.major_id AND p.grantee_principal_id = u.principal_id AND p.state IN ('G', 'W') AND (p.type NOT IN ('DL', 'EX', 'IN', 'SL', 'UP') OR u.name IN ('public', 'guest')) ORDER BY u.name, o.name, p.permission_name Review results listed. If any results listed are not documented in the System Security Plan and authorized by the IAO, this is a Finding. Note: Some permissions assigned to PUBLIC within the master database may require the 'Allow modifications to be made directly to the system catalogs' database setting be temporarily enabled.
Fix: F-20259r1_fix
Revoke unauthorized permissions assigned to application user roles where supported by the DBMS vendor. From the query prompt: USE [database name] REVOKE [permission] ON [object] FROM [group name] Document assigned role permissions in the System Security Plan and authorize with the IAO.
- RMF Control
- Severity
- M
- CCI
- Version
- DM1749-SQLServer9
- Vuln IDs
-
- V-2458
- Rule IDs
-
- SV-23790r2_rule
Checks: C-22796r2_chk
From the query prompt: SELECT name AS [Database Name] FROM [master].sys.databases WHERE state = 0 Repeat for each database: From the query prompt: USE [Database Name] SELECT u.name AS [Principal], t.name AS [Table], p.permission_name AS [Permission], p.state_desc AS [State] FROM sys.database_principals u JOIN sys.database_permissions p ON u.principal_id = p.grantee_principal_id JOIN sys.tables t ON p.major_id = t.object_id WHERE p.state_desc <> 'DENY' AND t.is_ms_shipped = 1 ORDER BY u.name, t.name Review the list of permissions assigned to system tables. If any results listed are not documented in the System Security Plan and authorized by the IAO, this is a Finding. Note: By default, SELECT permission is granted to the PUBLIC role for system tables in all databases. Even though permission is set by default, it is still a Finding.
Fix: F-24604r1_fix
Revoke permissions granted to system tables where supported by the DBMS vendor. From the query prompt: USE [database name] REVOKE [permission] ON [object name] FROM [user name] Document permission grants in the System Security Plan and authorize with the IAO.
- RMF Control
- Severity
- M
- CCI
- Version
- DM1760-SQLServer9
- Vuln IDs
-
- V-2463
- Rule IDs
-
- SV-23804r2_rule
Checks: C-22835r2_chk
From the query prompt: SELECT name AS [Database Name] FROM [master].sys.databases WHERE state = 0 Repeat for each database: From the query prompt: USE [Database Name] SELECT u.name AS [Principal], o.name AS [Object], p.permission_name AS [Permission], p.state_desc AS [State] FROM sys.database_principals u JOIN sys.database_permissions p ON u.principal_id = p.grantee_principal_id LEFT JOIN sys.all_objects o ON p.major_id = o.object_id AND p.class IN (1, 8) LEFT JOIN sys.schemas s ON p.major_id = s.schema_id AND p.class = 3 WHERE p.state_desc <> 'DENY' AND p.state IN ('G', 'W') AND (p.type LIKE 'CR%' OR p.type LIKE 'AL%') ORDER BY u.name, o.name, p.permission_name Compare the results listed to those documented in the System Security Plan. If any accounts listed are: 1. Application users 2. Application user roles 3. Application administrator roles 4. PUBLIC role 5. GUEST user this is a Finding. If any application developer accounts are listed with DDL privileges to production databases, this is a Finding. If results are not documented in the System Security Plan or authorized by the IAO, this is a Finding.
Fix: F-14854r1_fix
Revoke DDL permissions from unauthorized accounts with the REVOKE command: From the query prompt: USE [database name] REVOKE [permission] FROM [user name] Document required DDL permission grants in the System Security Plan and authorize with the IAO.
- RMF Control
- Severity
- M
- CCI
- Version
- DM5144-SQLServer9
- Vuln IDs
-
- V-2498
- Rule IDs
-
- SV-23833r2_rule
Checks: C-22837r2_chk
From the query prompt: SELECT name AS [Database Name] FROM [master].sys.databases WHERE state = 0 Repeat for each database: From the query prompt: USE [Database Name] SELECT u.name AS [Principal], o.name AS [Object], p.permission_name AS [Permission], p.state_desc AS [State] FROM sys.database_principals u JOIN sys.database_permissions p ON u.principal_id = p.grantee_principal_id LEFT JOIN sys.all_objects o ON p.major_id = o.object_id AND p.class IN (1, 8) LEFT JOIN sys.schemas s ON p.major_id = s.schema_id AND p.class = 3 WHERE p.state_desc <> 'DENY' AND p.state IN ('W') ORDER BY u.name, o.name, p.permission_name Review listed object grants. Compare with the list documented in the System Security Plan and authorized by the IAO. If any object grants listed are not documented in the System Security Plan and authorized by the IAO, this is a Finding.
Fix: F-2479r1_fix
Revoke unauthorized permissions granted with the WITH GRANT option. From the query prompt: USE [Database Name] REVOKE GRANT OPTION FOR [object] FROM [Principal] You may re-grant the object permissions to the Principal as shown below. From the query prompt: USE [Database Name] GRANT [permission] ON [object] TO [Principal] Document required object permission grants with Grant Option in the System Security Plan and authorize with the IAO.
- RMF Control
- Severity
- L
- CCI
- Version
- DG0015-SQLServer9
- Vuln IDs
-
- V-3727
- Rule IDs
-
- SV-24072r2_rule
Checks: C-23847r2_chk
From the query prompt: SELECT name AS [Database Name] FROM [master].sys.databases WHERE name NOT IN ('tempdb', 'ReportServerTempDB') AND state = 0 Repeat for each database: From the query prompt: USE [Database Name] SELECT s.name AS [Schema], o.name AS [Object], u.type_desc AS [TYPE], o.create_date AS [Create Date] FROM sys.schemas s JOIN sys.database_principals u ON s.principal_id = u.principal_id JOIN sys.all_objects o ON s.schema_id = o.schema_id WHERE u.name NOT IN ('dbo', 'INFORMATION_SCHEMA', 'sys') AND o.is_ms_shipped = 0 ORDER BY s.name, o.name, o.create_date View the list of objects listed by the queries above. If any object creation dates do not coincide with the software maintenance and upgrade logs or are not objects documented as supporting dynamic object creation functions, investigate the circumstances under which the object was created. If the object is created using static definitions to store temporary data or indicates that the application uses unauthorized DDL statements, this is a Finding. The results of these queries will just give an indication of what objects were created since the database installation or its most recent upgrade. It should not be used as a complete result. For example, using the queries listed above, application objects created with the database installation will not be reported as well as objects owned by default application owner accounts. If the creation dates are more recent than the installation or latest upgrade of the application, the application may be using DDL statements. If the listed objects are not documented in the System Security Plan, this is a Finding.
Fix: F-22643r1_fix
Coordinate with the application designer to modify the application to use static objects with temporary data rather than creating and using temporary objects. Document in the System Security Plan all known object creation that supports dynamic object usage.
- RMF Control
- Severity
- M
- CCI
- Version
- DG0073-SQLServer9
- Vuln IDs
-
- V-3817
- Rule IDs
-
- SV-25285r1_rule
Checks: C-13830r1_chk
If no DBMS accounts authenticate using passwords, this check is Not a Finding. If DBMS uses Host Authentication only, this check is Not a Finding. If the DBMS does not natively support this functionality, this check is Not a Finding. If the DBMS is not configured to lock database accounts after three or an IAO-specified number of consecutive unsuccessful connection attempts within a 60 minute period, this is a Finding. Note: The counter may be reset to 0 if a third failed logon attempt does not occur before reset. Ensure password policy enforcement is enabled for SQL Server accounts per Check DG0079.
Fix: F-20069r1_fix
Set the failed login attempt count to 3 to trigger an account lockout or to the number specified in the System Security Plan where supported by the DBMS. Where this requirement is not compatible with the operation of a front-end application, the unsuccessful logon count and time will be specified and the operational need documented in the System Security Plan.
- RMF Control
- Severity
- L
- CCI
- Version
- DG0091-SQLServer9
- Vuln IDs
-
- V-3823
- Rule IDs
-
- SV-24094r2_rule
Checks: C-23683r2_chk
If this is not a production database, this check is Not a Finding. From the query prompt: SELECT name AS [Database Name] FROM [master].sys.databases WHERE name NOT IN ('tempdb', 'reportserver', 'reportservertempdb') AND state = 0 Repeat for each database: From the query prompt: USE [Database Name] SELECT USER_NAME(o.schema_id) AS [Schema], o.name AS [Object], o.type_desc AS [Type], o.create_date AS [Create Date] FROM sys.all_objects o, sys.sql_modules s WHERE o.object_id = s.object_id AND s.definition IS NOT NULL AND o.is_ms_shipped = 0 ORDER BY USER_NAME(o.schema_id), o.name Review the listed results. If any results listed are not documented in the System Security Plan and authorized by the IAO, this is a Finding.
Fix: F-19555r1_fix
Recreate stored procedures and specify encryption using the ALTER PROCEDURE command. Example: ALTER PROCEDURE [MyProc] WITH ENCRYPTION AS SELECT [mycol1], [mycol2] FROM [mytable]... Replace objects specified between the "[]" characters with custom/GOTS procedure references. Document all exceptions to this requirement in the System Security Plan and authorize with the IAO.
- RMF Control
- Severity
- M
- CCI
- Version
- DG0004-SQLServer9
- Vuln IDs
-
- V-5683
- Rule IDs
-
- SV-24066r2_rule
Checks: C-23668r2_chk
Review list of non-default, non-DBA and non-developer object owners: From the query prompt: SELECT name AS [Database Name] FROM [master].sys.databases WHERE state = 0 Repeat for each database: From the query prompt: USE [Database Name] SELECT DISTINCT s.name AS [Schema], l.name AS [User] FROM sys.all_objects o JOIN sys.schemas s ON o.schema_id = s.schema_id JOIN sys.database_principals u ON s.principal_id = u.principal_id JOIN master.sys.sql_logins l ON u.sid = l.sid WHERE o.is_ms_shipped = 0 AND l.is_disabled = 0 If any login names are returned (not disabled) from the last part of the query, this is a Finding. Note: The 'sa' account is not exempt from this requirement and should be disabled. DBA and developer accounts authorized to own objects in the database may be permitted, but must be documented.
Fix: F-20174r1_fix
Disable logins for all application object owner accounts or members of database roles that own objects: ALTER LOGIN [name] DISABLE Document application object owner accounts that are not disabled in the System Security Plan and authorize with the IAO.
- RMF Control
- Severity
- M
- CCI
- Version
- DG0105-SQLServer9
- Vuln IDs
-
- V-15128
- Rule IDs
-
- SV-24098r2_rule
Checks: C-23688r2_chk
Compare privileges assigned to database application user roles to those defined in the System Security Plan. From the query prompt: SELECT name FROM [master].sys.databases WHERE name NOT IN ('tempdb', 'ReportServerTempDB') AND state = 0 Repeat for each database: USE [database name] SELECT r.name, o.name, p.permission_name FROM sys.database_principals r, sys.database_permissions p, sys.all_objects o WHERE p.grantee_principal_id = r.principal_id AND p.major_id = o.object_id AND r.principal_id NOT IN (0, 2) AND r.type IN ('A', 'R') AND r.is_fixed_role = 0 ORDER BY r.name, o.name, p.permission_name If the assigned privileges do not match the authorized list of privileges, this is a Finding. Note: Default privileges assigned to fixed data roles are considered authorized by default.
Fix: F-23528r1_fix
Use the grant and revoke commands to assign the authorized privileges as listed in the System Security Plan to custom database application or application user roles.
- RMF Control
- Severity
- M
- CCI
- Version
- DG0166-SQLServer9
- Vuln IDs
-
- V-15142
- Rule IDs
-
- SV-19465r2_rule
Checks: 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: 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.
- RMF Control
- Severity
- M
- CCI
- Version
- DM0531-SQLServer9
- Vuln IDs
-
- V-15151
- Rule IDs
-
- SV-23769r2_rule
Checks: C-22831r2_chk
From the query prompt: SELECT name AS [Database Name] FROM [master].sys.databases WHERE state = 0 Repeat for each database: From the query prompt: USE [Database Name] SELECT u.name AS [Principal], g.name AS [Role] FROM sys.database_role_members r, sys.database_principals u, sys.database_principals g WHERE r.role_principal_id = g.principal_id AND r.member_principal_id = u.principal_id AND g.is_fixed_role = 1 ORDER BY u.name, g.name The DBO membership in the db_owner fixed database role does not require explicit authorization and is Not a Finding. Verify fixed database role assignments for each Principal listed in the System Security Plan. If any Principals are not authorized, this is a Finding. Any fixed database role assignments to the Guest Principal is a finding.
Fix: F-19750r1_fix
Grant fixed database roles only to authorized accounts. Revoke unauthorized accounts from fixed database roles. From the SQL Server Management Studio GUI: To deassign roles: 1. Expand [instance name] 2. Expand Databases 3. Expand [database type] 4. Expand [database name] 5. Expand Security 6. Expand Roles 7. Expand Database Roles 8. Double-click the role to be removed from the assigned user 9. Select the user's account under Role Members 10. Click on the Remove button Document fixed database role grants in the System Security Plan and authorize with the IAO.
- RMF Control
- Severity
- M
- CCI
- Version
- DM6175-SQLServer9
- Vuln IDs
-
- V-15159
- Rule IDs
-
- SV-23860r2_rule
Checks: C-13822r2_chk
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 COUNT(name) FROM sys.symmetric_keys s, sys.key_encryptions k WHERE s.name = '##MS_DatabaseMasterKey##' AND s.symmetric_key_id = k.key_id AND k.crypt_type = 'ESKP' If the value returned is greater than 0, a Database Master key exists and is encrypted with a password. Review procedures and evidence of password requirements used to encrypt Database Master Keys. If the passwords are not required to meet DOD password standards, currently 15 characters, 2 uppercase characters, 2 lowercase characters, 2 special characters, and 2 numeric characters and no repeating characters, this is a Finding. Interview the IAO or DBA to determine the method to retrieve the password to use the Database Master Key. If storage of the password occurs unencrypted in application code or other database tables or files, this is a Finding.
Fix: F-14843r1_fix
Assign an encryption password to the Database Master Key that is a minimum of 15 characters, contains at least 2 uppercase characters, 2 lowercase characters, 2 special characters, 2 numeric characters and has no repeating characters. To change the Database Master Key encryption password: USE [database name] ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = '[new password]' Note: The database master key encryption method should not be changed until the effects are thoroughly reviewed. Changing the master key encryption causes all encryption using the database master key to be decrypted and re-encrypted. This action should not be taken during a high-demand time. Please see the MS SQL Server documentation prior to re-encrypting the database master key for detailed information.
- RMF Control
- Severity
- M
- CCI
- Version
- DM6179-SQLServer9
- Vuln IDs
-
- V-15161
- Rule IDs
-
- SV-23861r2_rule
Checks: C-13835r2_chk
From the query prompt: SELECT name FROM [master].sys.databases WHERE is_master_key_encrypted_by_server = 1 AND owner_sid <> 1 AND state = 0 If no databases are returned, this is Not a Finding. For any databases returned, verify in the System Security Plan that encryption of the Database Master Key using the Service Master Key is acceptable and approved by the Information Owner and the encrypted data does not require additional protections to deter or detect DBA access. If not approved, this is a Finding. If approved and additional protections are required, then verify that the additional requirements are in place in accordance with the System Security Plan. These may include additional auditing on access of the Database Master Key with alerts or other automated monitoring. If the additional requirements are not in place, this is a Finding.
Fix: F-14856r1_fix
Where possible, encrypt the Database Master Key with a password known only to the application administrator. Where not possible, configure additional audit events or alerts to detect unauthorized access to the database master key by users not authorized to view sensitive data.
- RMF Control
- Severity
- M
- CCI
- Version
- DM6180-SQLServer9
- Vuln IDs
-
- V-15162
- Rule IDs
-
- SV-25497r1_rule
Checks: C-22863r1_chk
From the query prompt: SELECT COUNT(credential_id) FROM [master].sys.master_key_passwords If count is not 0, this is a Finding.
Fix: F-14844r1_fix
Use the stored procedure sp_control_dbmasterkey_password to remove any credentials that store database master key passwords. From the query prompt: EXEC SP_CONTROL_DBMASTERKEY_PASSWORD @db_name = '[database name]', @action = N'drop'
- RMF Control
- Severity
- M
- CCI
- Version
- DM6184-SQLServer9
- Vuln IDs
-
- V-15164
- Rule IDs
-
- SV-23863r2_rule
Checks: C-13838r2_chk
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 name, SUSER_SNAME(sid) FROM sys.asymmetric_keys ORDER BY name, SUSER_SNAME(sid) If no keys are defined for any database, this check is Not a Finding. If keys are returned, verify the key is associated with a DOD PKI Certificate. Evidence may include review of the certificate of a signed file used to read the key into the database. If the key is not from a DOD PKI certificate or evidence cannot be determined or presented, this is a Finding.
Fix: F-14859r1_fix
Where asymmetric key use is required, the asymmetric should be generated using a code-signing certificate or using the database master key to encrypt the private key. Use of the asymmetric key is expected in DOD installations to be used to support symmetric keys that are in turn used to encrypt sensitive data. In a DOD environment, asymmetric keys generated and stored within the SQL Server database are not expected to be used for storage of DOD PKI certificates associated with DOD personnel and used to authenticate them for any database access. CREATE ASYMMETRIC KEY [key name] OR CREATE ASYMMETRIC KEY [key name] FROM [asymmetric key source] [asymmetric key source] may be FILE = [strong file name] or EXECUTABLE FILE = 'executable file' or ASSEMBLY [assembly name] Each of the asymmetric key sources is expected in a DOD environment to files signed with code-signing certificates issued by the DOD PKMO. Use of the database master key to encrypt is acceptable, especially where the key is generated using the service master key which in turn is generated from the server certificate. In cases where the DBAs are not trusted, use of external key sources is required.
- RMF Control
- Severity
- M
- CCI
- Version
- DM6183-SQLServer9
- Vuln IDs
-
- V-15168
- Rule IDs
-
- SV-23862r2_rule
Checks: C-13837r2_chk
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 s.name, k.crypt_type_desc FROM sys.symmetric_keys s, sys.key_encryptions k WHERE s.symmetric_key_id = k.key_id AND k.crypt_type IN ('KSKP', 'ESKS') AND s.principal_id <> 1 ORDER BY s.name, k.crypt_type_desc Review any symmetric keys that have been defined against the System Security Plan. If any keys are defined that are not documented in the System Security Plan, this is a Finding. Review the System Security Plan to review the encryption mechanism specified for each symmetric key. If the method does not indicate use of certificates, this is a Finding. If the certificate specified is not a DOD PKI certificate, this is a Finding.
Fix: F-14858r1_fix
Configure or alter symmetric keys to encrypt keys with certificates or authorized asymmetric keys: From the query prompt: ALTER SYMMETRIC KEY [key name] ADD ENCRYPTION BY CERTIFICATE [certificate name] ALTER SYMMETRIC KEY [key name] DROP ENCRYPTION BY [password, symmetric key or asymmetric key] The symmetric key must specify a certificate or asymmetric key for encryption. The certificate may be the code-signing certificate used by the application.
- RMF Control
- Severity
- M
- CCI
- Version
- DM6196-SQLServer9
- Vuln IDs
-
- V-15172
- Rule IDs
-
- SV-23871r2_rule
Checks: C-22798r2_chk
From the query prompt: SELECT name AS [Database Name] FROM [master].sys.databases WHERE state = 0 Repeat for each database: From the query prompt: USE [Database Name] SELECT u.name AS [Principal], o.name AS [Object], p.permission_name AS [Permission], p.state_desc AS [State] FROM sys.database_principals u JOIN sys.database_permissions p ON u.principal_id = p.grantee_principal_id JOIN sys.all_objects o ON p.major_id = o.object_id WHERE p.state_desc <> 'DENY' AND u.name IN ('guest', 'public') ORDER BY u.name, o.name, p.permission_name If any results listed are unauthorized, this is a Finding. If any results listed are not documented in the System Security Plan and authorized by the IAO, this is a Finding. Note: Some permissions assigned to PUBLIC within the master database may require the 'Allow modifications to be made directly to the system catalogs' database setting be temporarily be enabled.
Fix: F-19722r1_fix
Revoke any unauthorized object privileges assigned to PUBLIC or GUEST where supported by the DBMS vendor. Document all remaining object privileges assigned to PUBLIC in the System Security Plan and authorize with the IAO. From the query prompt: SELECT name FROM [master].sys.databases WHERE state = 0 Repeat for each database: From the query prompt: USE [database name] REVOKE [privilege] ON [object name] FROM '[public or guest]' Repeat for each object privilege assigned to public or guest: From the query prompt: USE [database name] REVOKE [permission] ON [schema name].[object name] TO PUBLIC To determine correct schema name for the object, use: SELECT SCHEMA_NAME(schema_id) FROM [master].sys.all_objects WHERE name = '[object name]'
- RMF Control
- Severity
- M
- CCI
- Version
- DM6188-SQLServer9
- Vuln IDs
-
- V-15177
- Rule IDs
-
- SV-25498r1_rule
Checks: C-13824r1_chk
Review procedures for and evidence of backup of the SQL Server Service Master Key in the System Security Plan. If the procedures or evidence does not exist, this is a Finding. If the procedures do not indicate offline and off-site storage of the Service Master Key, this is a Finding. If procedures do not indicate access restrictions to the Service Master Key backup, this is a Finding.
Fix: F-14845r1_fix
Document and implement procedures to safely backup and store the service master key. Include in the procedures methods to establish evidence of backup and storage and careful, restricted access and restoration of the service master key. Also, include provisions to store the key offsite. BACKUP SERVICE MASTER KEY TO FILE = 'path_to_file' ENCRYPTION BY PASSWORD = 'password'
- RMF Control
- Severity
- M
- CCI
- Version
- DM6185-SQLServer9
- Vuln IDs
-
- V-15185
- Rule IDs
-
- SV-23864r2_rule
Checks: C-13839r2_chk
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 name, pvt_key_encryption_type_desc FROM sys.asymmetric_keys WHERE pvt_key_encryption_type = 'PW' ORDER BY name, pvt_key_encryption_type_desc If no records are returned, this is Not a Finding. Review any records returned and the encryption type listed. If any do not match the documented approved encryption method as specified in the System Security Plan, this is a Finding.
Fix: F-14860r1_fix
If stored with a private key, the private key is always encrypted either by a specified password, or by the database or service master key. Create or alter the asymmetric key with the approved encryption type specified in the System Security Plan. Document the approved encryption method after considering whether the DBA should be trusted to access the asymmetric key.
- RMF Control
- Severity
- M
- CCI
- Version
- DG0008-SQLServer9
- Vuln IDs
-
- V-15607
- Rule IDs
-
- SV-24068r2_rule
Checks: C-23671r2_chk
From the query prompt: SELECT name AS [Database Name] FROM [master].sys.databases WHERE state = 0 Repeat for each database: From the query prompt: USE [Database Name] SELECT DISTINCT s.name AS [Schema Name], u.name AS [Principal], u.type_desc AS [Type] FROM sys.schemas s JOIN sys.database_principals u ON s.principal_id = u.principal_id JOIN sys.all_objects o ON s.schema_id = o.schema_id WHERE u.name NOT IN ('dbo', 'INFORMATION_SCHEMA', 'sys') ORDER BY s.name, u.name Review the object ownerships listed. Verify any listed accounts are authorized application installation accounts and documented in the System Security Plan. If any accounts are not authorized, this is a Finding. If any authorized accounts listed are not documented in the System Security Plan, this is a Finding.
Fix: F-17828r1_fix
Create database accounts dedicated for application object ownership. To simplify access authorizations, use a single account for each application to avoid cross chaining of ownership, which makes security configuration more complex and degrades system performance. Document all application object ownership in the System Security Plan and authorize with the IAO.
- RMF Control
- Severity
- M
- CCI
- Version
- DG0121-SQLServer9
- Vuln IDs
-
- V-15629
- Rule IDs
-
- SV-24106r2_rule
Checks: C-23691r2_chk
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 u.name, o.name, p.permission_name FROM sys.all_objects o, sys.database_principals u, sys.database_permissions p WHERE o.object_id = p.major_id AND p.grantee_principal_id = u.principal_id AND p.state IN ('G', 'W') AND u.type IN ('S', 'U') ORDER BY u.name, o.name, p.permission_name If any names are listed, this is a Finding.
Fix: F-23530r1_fix
Revoke permissions assigned directly to user accounts and grant them instead to the appropriate group account. From the query prompt: REVOKE [permission] ON [object] FROM [user name] GRANT [permission] ON [object] TO [group name] Document any exceptions to privileges that cannot be assigned via database roles in the System Security Plan.
- RMF Control
- Severity
- M
- CCI
- Version
- DG0122-SQLServer9
- Vuln IDs
-
- V-15630
- Rule IDs
-
- SV-24307r1_rule
Checks: C-13833r1_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. Review privilege assignments to sensitive data stored in the database. Compare assigned privileges to those that are authorized in the System Security Plan. If unauthorized access is granted or sensitive data access requirements are not documented, this is a Finding.
Fix: F-23498r1_fix
Have the Information Owner identify all sensitive data stored in the database specified in the System Security Plan. Define job functions and sensitive data access requirements for the job functions and included them in the System Security Plan. Assign only authorized users for job functions.
- RMF Control
- Severity
- M
- CCI
- Version
- DG0138-SQLServer9
- Vuln IDs
-
- V-15642
- Rule IDs
-
- SV-25369r1_rule
Checks: C-13841r1_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. Review data access requirements for sensitive data as identified and assigned by the Information Owner in the System Security Plan. Review the access controls for sensitive data configured in the database. If the configured access controls do not match those defined in the System Security Plan, this is a Finding.
Fix: F-23502r1_fix
Define, document and implement all sensitive data access controls based on job function in the System Security Plan.
- RMF Control
- Severity
- M
- CCI
- Version
- DG0165-SQLServer9
- Vuln IDs
-
- V-15654
- Rule IDs
-
- SV-21488r2_rule
Checks: C-23695r2_chk
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 USER_NAME(grantee_principal_id) FROM sys.database_permissions WHERE class = 0 AND state IN ('G', 'W') AND type = 'CL' ORDER BY USER_NAME(grantee_principal_id) If no records are returned, this is Not a Finding. If any records are returned, verify they are authorized to have access to manage the Database Master Key. If any do not, this is a Finding.
Fix: F-20181r1_fix
Document all users authorized to access the database master key in the System Security Plan. Restrict authorized users to the application, database owner and SYSADMINs. For each unauthorized user: From the query prompt: REVOKE CONTROL FROM [user name]
- RMF Control
- Severity
- M
- CCI
- Version
- DG0172-SQLServer9
- Vuln IDs
-
- V-15657
- Rule IDs
-
- SV-21490r1_rule
Checks: C-23697r1_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. If the DBMS does not provide the capability to display sensitivity marking of data, this check is Not a Finding. For SQL Server 2005: Review the DBMS configuration for marking and labeling of sensitive data. http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx If security label assignment is not audited for changes, this is a Finding.
Fix: F-20183r1_fix
Define the policy for auditing changes to security labels defined for the data. Document the audit requirements in the System Security Plan and configure database auditing in accordance with the policy.