Select any two versions of this STIG to compare the individual requirements
Select any old version/release of this STIG to view the previous requirements
Determine if SQL Server is configured to allow the use of contained databases. In the Object Explorer in SQL Server Management Studio (SSMS), right-click on the server instance, select "Properties", and then select the "Advanced" page. If "Enabled Contained Databases" is "True", this is a finding. AND In a query interface such as the SSMS Transact-SQL editor, run the statement: EXEC sp_configure 'contained database authentication' If the returned value in the "config_value" and/or "run_value" column is "1", this is a finding. Determine whether SQL Server is configured to use only Windows authentication. In the Object Explorer in SQL Server Management Studio (SSMS), right-click on the server instance, select "Properties", and then select the "Security" page. If Windows Authentication Mode is not selected, this is a finding. AND In a query interface such as the SSMS Transact-SQL editor, run the statement: SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly') WHEN 1 THEN 'Windows Authentication' WHEN 0 THEN 'Windows and SQL Server Authentication' END as [Authentication Mode] If the returned value in the "Authentication Mode" column is not "Windows Authentication", this is a finding. Mixed mode (both SQL Server authentication and Windows authentication) is in use. If the need for mixed mode has not been documented and approved, this is a finding. From the documentation, obtain the list of accounts authorized to be managed by SQL Server. Determine the accounts (SQL Logins) actually managed by SQL Server. Run the statement: SELECT name FROM sys.database_principals WHERE type_desc = 'SQL_USER' AND authentication_type_desc = 'DATABASE'; If any accounts listed by the query are not listed in the documentation, this is a finding. Documentation must be approved by the ISSO/ISSM.
If mixed mode is required, document the need and justification; describe the measures taken to ensure the use of SQL Server authentication is kept to a minimum; describe the measures taken to safeguard passwords; list or describe the SQL Logins used; and obtain official approval. If mixed mode is not required, disable it as follows: In the SSMS Object Explorer, right-click on the server instance, select Properties >> Security page. Click the radio button for "Windows Authentication Mode", and then click "OK". Restart the SQL Server instance. OR Run the statement: USE [master] EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2 GO Restart the SQL Server instance. For each account being managed by SQL Server but not requiring it, drop or disable the SQL Database user. Replace it with an appropriately configured account, as needed. To drop a User in the SSMS Object Explorer: Navigate to Databases >> Security Users. Right-click on the User name, and then click "Delete". To drop a User via a query: USE database_name; DROP USER <user_name>;
Review the system documentation to determine the required levels of protection for securables in the database, by type of user. If the database is tempdb, this is NA. Review the permissions actually in place in the database. If the actual permissions do not match the documented requirements, this is a finding. Use the supplemental file "Database permission assignments to users and roles.sql".
Use GRANT, REVOKE, DENY, ALTER ROLE … ADD MEMBER … and/or ALTER ROLE …. DROP MEMBER statements to add and remove permissions on database-level securables, bringing them into line with the documented requirements.
Execute the following query: SELECT name FROM sys.database_principals WHERE type in ('U','G') AND name LIKE '%$' If no users are returned, this is not a finding. If users are returned, determine whether each user is a computer account. Launch PowerShell. Execute the following code: Note: <name> represents the username portion of the user. For example; if the user is "CONTOSO\user1$", the username is "user1". ([ADSISearcher]"(&(ObjectCategory=Computer)(Name=<name>))").FindAll() If no account information is returned, this is not a finding. If account information is returned, this is a finding.
Remove all users that were returned in the check SQL Statement: SELECT name FROM sys.database_principals WHERE type in ('U','G') AND name LIKE '%$' To remove users: Run the following command for each user: DROP USER [ IF EXISTS ] <user_name>;
Check the server documentation to determine if collecting and keeping historical versions of a table is required. If collecting and keeping historical versions of a table is NOT required, this is not a finding. Find all of the temporal tables in the database using the following query: SELECT SCHEMA_NAME(T.schema_id) AS schema_name, T.name AS table_name, T.temporal_type_desc, SCHEMA_NAME(H.schema_id) + '.' + H.name AS history_table FROM sys.tables T JOIN sys.tables H ON T.history_table_id = H.object_id WHERE T.temporal_type != 0 ORDER BY schema_name, table_name Using the system documentation, determine which tables are required to be temporal tables. If any tables listed in the documentation are not in the list created by running the above statement, this is a finding. Ensure a field exists documenting the login and/or user who last modified the record. If this does not exist, this is a finding.
Alter sensitive tables to utilize system versioning. Alter non-temporal table to define periods for system versioning . ALTER TABLE InsurancePolicy ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime), SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL DEFAULT GETUTCDATE(), SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.99999999'); ALTER TABLE InsurancePolicy SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.InsurancePolicyHistory)); https://docs.microsoft.com/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-2016#system_versionin
If the database being reviewed is MSDB, trustworthy is required to be enabled, and therefore this is not a finding. Execute the following query: SELECT SUSER_SNAME(d.owner_sid) AS DatabaseOwner, CASE WHEN d.is_trustworthy_on = 0 THEN 'No' WHEN d.is_trustworthy_on = 1 THEN 'Yes' END AS IsTrustworthy, CASE WHEN role.name IN ('sysadmin','securityadmin') OR permission.permission_name = 'CONTROL SERVER' THEN 'YES' ELSE 'No' END AS 'IsOwnerPrivileged' FROM sys.databases d LEFT JOIN sys.server_principals login ON d.owner_sid = login.sid LEFT JOIN sys.server_role_members rm ON login.principal_id = rm.member_principal_id LEFT JOIN sys.server_principals role ON rm.role_principal_id = role.principal_id LEFT JOIN sys.server_permissions permission ON login.principal_id = permission.grantee_principal_id WHERE d.name = DB_NAME() If trustworthy is not enabled, this is not a finding. If trustworthy is enabled and the database owner is not a privileged account, this is not a finding. If trustworthy is enabled and the database owner is a privileged account, review the system documentation to determine if the trustworthy property is required and authorized. If this is not documented, this is a finding.
Disable trustworthy on the database. ALTER DATABASE [<database name>] SET TRUSTWORTHY OFF
Obtain the list of approved audit maintainers from the system documentation. Use the following query to review database roles and their membership, all of which enable the ability to create and maintain audit specifications. SELECT R.name AS role_name, RM.name AS role_member_name, RM.type_desc FROM sys.database_principals R JOIN sys.database_role_members DRM ON R.principal_id = DRM.role_principal_id JOIN sys.database_principals RM ON DRM.member_principal_id = RM.principal_id WHERE R.type = 'R' AND R.name = 'db_owner' ORDER BY role_member_name If any role memberships are not documented and authorized, this is a finding. Review the database roles and individual users that have the following permissions, all of which enable the ability to create and maintain audit definitions. ALTER ANY DATABASE AUDIT CONTROL Use the following query to determine the roles and users that have the listed permissions: SELECT PERM.permission_name, DP.name AS principal_name, DP.type_desc AS principal_type, DBRM.role_member_name FROM sys.database_permissions PERM JOIN sys.database_principals DP ON PERM.grantee_principal_id = DP.principal_id LEFT OUTER JOIN ( SELECT R.principal_id AS role_principal_id, R.name AS role_name, RM.name AS role_member_name FROM sys.database_principals R JOIN sys.database_role_members DRM ON R.principal_id = DRM.role_principal_id JOIN sys.database_principals RM ON DRM.member_principal_id = RM.principal_id WHERE R.type = 'R' ) DBRM ON DP.principal_id = DBRM.role_principal_id WHERE PERM.permission_name IN ('CONTROL','ALTER ANY DATABASE AUDIT') ORDER BY permission_name, principal_name, role_member_name If any of the roles or users returned have permissions that are not documented, or the documented audit maintainers do not have permissions, this is a finding.
Create a database role specifically for audit maintainers, and give it permission to maintain audits, without granting it unnecessary permissions (The role name used here is an example; other names may be used.): CREATE ROLE DATABASE_AUDIT_MAINTAINERS; GO GRANT ALTER ANY DATABASE AUDIT TO DATABASE_AUDIT_MAINTAINERS; GO Use REVOKE and/or DENY and/or ALTER ROLE ... DROP MEMBER ... statements to remove the ALTER ANY DATABASE AUDIT permission from all users. Then, for each authorized database user, run the statement: ALTER ROLE DATABASE_AUDIT_MAINTAINERS ADD MEMBER; GO Use REVOKE and/or DENY and/or ALTER SERVER ROLE ... DROP MEMBER ... statements to remove CONTROL DATABASE permission from logins that do not need it.
Obtain a listing of users and roles who are authorized to change stored procedures, functions, and triggers from the server documentation. In each user database, execute the following query: SELECT P.type_desc AS principal_type, P.name AS principal_name, O.type_desc, CASE class WHEN 0 THEN DB_NAME() WHEN 1 THEN OBJECT_SCHEMA_NAME(major_id) + '.' + OBJECT_NAME(major_id) WHEN 3 THEN SCHEMA_NAME(major_id) ELSE class_desc + '(' + CAST(major_id AS nvarchar) + ')' END AS securable_name, DP.state_desc, DP.permission_name FROM sys.database_permissions DP JOIN sys.database_principals P ON DP.grantee_principal_id = P.principal_id LEFT OUTER JOIN sys.all_objects O ON O.object_id = DP.major_id AND O.type IN ('TR','TA','P','X','RF','PC','IF','FN','TF','U') WHERE DP.type IN ('AL','ALTG') AND DP.class IN (0, 1, 53) SELECT R.name AS role_name, M.type_desc AS principal_type, M.name AS principal_name FROM sys.database_principals R JOIN sys.database_role_members DRM ON R.principal_id = DRM.role_principal_id JOIN sys.database_principals M ON DRM.member_principal_id = M.principal_id WHERE R.name IN ('db_ddladmin','db_owner') AND M.name != 'dbo' If any users or role permissions returned are not authorized to modify the specified object or type, this is a finding. If any user or role membership is not authorized, this is a finding.
Revoke the ALTER permission from unauthorized users and roles. REVOKE ALTER ON [<Object Name>] TO [<Principal Name>]
Obtain a listing of schema ownership from the server documentation. Execute the following query to obtain a current listing of schema ownership. SELECT S.name AS schema_name, P.name AS owning_principal FROM sys.schemas S JOIN sys.database_principals P ON S.principal_id = P.principal_id ORDER BY schema_name If any schema is owned by an unauthorized database principal, this is a finding.
Transfer ownership of database schemas to authorized database principals. ALTER AUTHORIZATION ON SCHEMA::[<Schema Name>] TO [<Principal Name>]
Review system documentation to identify SQL Server accounts authorized to own database objects. If the SQL Server database ownership list does not exist or needs to be updated, this is a finding. The following query can be of use in making this determination: ;with objects_cte as (SELECT o.name, o.type_desc, CASE WHEN o.principal_id is null then s.principal_id ELSE o.principal_id END as principal_id FROM sys.objects o INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE o.is_ms_shipped = 0 ) SELECT cte.name, cte.type_desc, dp.name as ObjectOwner FROM objects_cte cte INNER JOIN sys.database_principals dp ON cte.principal_id = dp.principal_id ORDER BY dp.name, cte.name If any of the listed owners is not authorized, this is a finding.
Add and/or update system documentation to include any accounts authorized for object ownership and remove any account not authorized. To change the schema owning a database object in SQL Server, use this code as an example: USE AdventureWorks2012; GO ALTER SCHEMA HumanResources TRANSFER Person.Address; GO Caution: This can break code. This Fix should be implemented in conjunction with corrections to such code. Test before deploying in production. Deploy during a scheduled maintenance window.
Obtain a listing of users and roles who are authorized to modify database structure and logic modules from the server documentation. Execute the following query: SELECT P.type_desc AS principal_type, P.name AS principal_name, O.type_desc, CASE class WHEN 0 THEN DB_NAME() WHEN 1 THEN OBJECT_SCHEMA_NAME(major_id) + '.' + OBJECT_NAME(major_id) WHEN 3 THEN SCHEMA_NAME(major_id) ELSE class_desc + '(' + CAST(major_id AS nvarchar) + ')' END AS securable_name, DP.state_desc, DP.permission_name FROM sys.database_permissions DP JOIN sys.database_principals P ON DP.grantee_principal_id = P.principal_id LEFT OUTER JOIN sys.all_objects O ON O.object_id = DP.major_id AND O.type IN ('TR','TA','P','X','RF','PC','IF','FN','TF','U') WHERE DP.type IN ('AL','ALTG') AND DP.class IN (0, 1, 53) SELECT R.name AS role_name, M.type_desc AS principal_type, M.name AS principal_name FROM sys.database_principals R JOIN sys.database_role_members DRM ON R.principal_id = DRM.role_principal_id JOIN sys.database_principals M ON DRM.member_principal_id = M.principal_id WHERE R.name IN ('db_ddladmin','db_owner') AND M.name != 'dbo' If any users or role permissions returned are not authorized to modify the specified object or type, this is a finding. If any user or role membership is not authorized, this is a finding.
Document and obtain approval for any non-administrative users who require the ability to modify database structure and logic modules. REVOKE ALTER ON [<Object Name>] TO [<Principal Name>]
Review the system security plan (SSP) to determine whether the database is static, the recovery model to be used, the backup schedule, and the plan for testing database restoration. If the SSP does not state that the database is static, assume that it is not static. If any of the other information is absent, this is a finding. If the database is not static, and the documented recovery model is Bulk Logged, but the justification and authorization for this are not documented, this is a finding. Run the following to determine Recovery Model: USE [master] GO SELECT name, recovery_model_desc FROM sys.databases ORDER BY name If the recovery model description does not match the documented recovery model, this is a finding. Review the jobs set up to implement the backup plan. If they are absent, this is a finding. Check the history of the backups by running the following query. It checks the last 30 days of backups by database. USE [msdb] GO SELECT database_name, CASE type WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Log' ELSE type END AS backup_type, is_copy_only, backup_start_date, backup_finish_date FROM dbo.backupset WHERE backup_start_date >= dateadd(day, - 30, getdate()) ORDER BY database_name, backup_start_date DESC If the history indicates a pattern of job failures by missing or gaps in backups, this is a finding. Review evidence that database recovery is tested annually or more often, and that the most recent test was successful. If not, this is a finding.
Modify the system security plan, to include whether the database is static, the correct recovery model to be used, the backup schedule, and the plan for testing database restoration. In SQL Server Management Studio, Object Explorer, right-click on the name of the database; select Properties. Select the Options page. Set the Recovery Model field, near the top of the page, to the correct value. In Object Explorer, expand >> SQL Server Agent >> Jobs. Create, modify, and delete jobs to implement the backup schedule. (Alternatively, this may done using T-SQL code or Third-party Backup software.) Correct any issues that have been causing backups to fail. Test the restoration of the database at least once a year; correct any issues that cause it to fail. Maintain a record of these tests.
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 in ('ESKP', 'ESP2', 'ESP3') If the value returned is zero, this is not applicable. If the value returned is greater than zero, 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 a minimum of 15 characters with at least 1 upper-case character, 1 lower-case character, 1 special character, and 1 numeric character, and at least 8 characters changed from the previous password, this is a finding.
Assign an encryption password to the Database Master Key that is a minimum of 15 characters with at least 1 upper-case character, 1 lower-case character, 1 special character, and 1 numeric character, and at least 8 characters changed from the previous password. 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.
If no databases require encryption, this is not a finding. 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; (Note that this query assumes that the [sa] account is not used as the owner of application databases, in keeping with other STIG guidance. If this is not the case, modify the query accordingly.) If no databases are returned by the query, 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 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.
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.
If the application owner and authorizing official have determined that encryption of data at rest is not required, this is not a finding. Review procedures for and evidence of backup of the Certificate used for encryption in the System Security Plan. If the procedures or evidence does not exist, this is a finding. If the procedures do not indicate that a backup of the Certificate used for encryption is stored in a secure location that is not on the SQL Server, this is a finding. If procedures do not indicate access restrictions to the Certificate backup, this is a finding.
Document and implement procedures to safely back up and store the Certificate used for encryption in a secure location that is not on the SQL Server. Include in the procedures to establish evidence of backup and storage as well as careful, restricted access and restoration of the Certificate. BACKUP CERTIFICATE 'CertificateName' TO FILE = 'path_to_file' WITH PRIVATE KEY (FILE = 'path_to_pvk', ENCRYPTION BY PASSWORD = 'password'); As this requires a password, ensure it is not exposed to unauthorized persons or stored as plain text.
Determine elements of security functionality (lists of permissions, additional authentication information, stored procedures, application specific auditing, etc.) that are being housed inside SQL server. For any elements found, check SQL Server to determine if these objects or code implementing security functionality are located in a separate security domain, such as a separate database, schema, or table created specifically for security functionality. If the database is a SQL Server default database (master, msdb, model, tempdb), this is NA. Run the following query to list all the user-defined databases: SELECT Name FROM sys.databases WHERE database_id > 4 ORDER BY 1; Review the database structure to determine where security related functionality is stored. If security-related database objects or code are not kept separate, this is a finding.
Check the server documentation, locate security-related database objects and code in a separate database, schema, table, or other separate security domain from database objects and code implementing application logic. Microsoft SQL Server 2005 introduced the concept of database object schemas. Schemas are analogous to separate namespaces or containers used to store database objects. Security permissions apply to schemas, making them an important tool for separating and protecting database objects based on access rights. Schemas reduce the work required, and improve the flexibility, for security-related administration of a database. User-schema separation allows for more flexibility in managing database object permissions. A schema is a named container for database objects, which allows the user to group objects into separate namespaces. Where possible, locate security-related database objects and code in a separate database, schema, or other separate security domain from database objects and code implementing application logic. In all cases, use GRANT, REVOKE, DENY, ALTER ROLE … ADD MEMBER … and/or ALTER ROLE …. DROP MEMBER statements to add and remove permissions on server-level and database-level security-related objects to provide effective isolation.
Review the procedures for the refreshing of development/test data from production. Review any scripts or code that exists for the movement of production data to development/test systems, or to any other location or for any other purpose. Verify that copies of production data are not left in unprotected locations. If the code that exists for data movement does not comply with the organization-defined data transfer policy and/or fails to remove any copies of production data from unprotected locations, this is a finding.
Modify any code used for moving data from production to development/test systems to comply with the organization-defined data transfer policy, and to ensure copies of production data are not left in unsecured locations.
Review DBMS code (stored procedures, functions, triggers), application code, settings, column and field definitions, and constraints to determine whether the database is protected against invalid input. If code exists that allows invalid data to be acted upon or input into the database, this is a finding. If column/field definitions are not reflective of the data, this is a finding. If columns/fields do not contain constraints and validity checking where required, this is a finding. Where a column/field is noted in the system documentation as necessarily free-form, even though its name and context suggest that it should be strongly typed and constrained, the absence of these protections is not a finding. Where a column/field is clearly identified by name, caption or context as Notes, Comments, Description, Text, etc., the absence of these protections is not a finding.
Use parameterized queries, constraints, foreign keys, etc. to validate data input. Modify SQL Server to properly use the correct column data types as required in the database.
Review application behavior and custom database code (stored procedures, triggers), to determine whether error messages contain information beyond what is needed for explaining the issue to general users. If database error messages contain PII data, sensitive business data, or information useful for identifying the host system or database structure, this is a finding.
Adjust database code to remove any information not required for explaining the error to an end user. Consider enabling trace flag 3625 to mask certain system-level error information returned to non-administrative users. Launch SQL Server Configuration Manager >> Click SQL Services >> Open the instance properties >> Click the Service Parameters tab >> Enter "-T3625" >> Click Add >> Click OK >> Restart SQL instance.
If security labeling is not required, this is not a finding. If security labeling requirements have been specified, but neither a third-party solution nor a SQL Server Row-Level security solution is implemented that reliably maintains labels on information in storage, this is a finding.
Deploy SQL Server Row-Level Security (see link below) or a third-party software, or add custom data structures, data elements and application code, to provide reliable security labeling of information in storage. https://msdn.microsoft.com/en-us/library/dn765131.aspx
If security labeling is not required, this is not a finding. If security labeling requirements have been specified, but neither a third-party solution nor a SQL Server Row-Level security solution is implemented that reliably maintains labels on information in process, this is a finding.
Deploy SQL Server Row-Level Security (see link below) or a third-party software, or add custom data structures, data elements and application code, to provide reliable security labeling of information in process. https://msdn.microsoft.com/en-us/library/dn765131.aspx
If security labeling is not required, this is not a finding. If security labeling requirements have been specified, but neither a third-party solution nor a SQL Server Row-Level security solution is implemented that reliably maintains labels on information in transmission, this is a finding.
Deploy SQL Server Row-Level Security (see link below) or a third-party software, or add custom data structures, data elements and application code, to provide reliable security labeling of information in transmission. https://msdn.microsoft.com/en-us/library/dn765131.aspx
Review system documentation to determine requirements for object ownership and authorization delegation. Use the following query to discover database object ownership: Schemas not owned by the schema or dbo: SELECT name AS schema_name, USER_NAME(principal_id) AS schema_owner FROM sys.schemas WHERE schema_id != principal_id AND principal_id != 1 Objects owned by an individual principal: SELECT object_id, name AS securable, USER_NAME(principal_id) AS object_owner, type_desc FROM sys.objects WHERE is_ms_shipped = 0 AND principal_id IS NOT NULL ORDER BY type_desc, securable, object_owner Use the following query to discover database users who have been delegated the right to assign additional permissions: SELECT U.type_desc, U.name AS grantee, DP.class_desc AS securable_type, CASE DP.class WHEN 0 THEN DB_NAME() WHEN 1 THEN OBJECT_NAME(DP.major_id) WHEN 3 THEN SCHEMA_NAME(DP.major_id) ELSE CAST(DP.major_id AS nvarchar) END AS securable, permission_name, state_desc FROM sys.database_permissions DP JOIN sys.database_principals U ON DP.grantee_principal_id = U.principal_id WHERE DP.state = 'W' ORDER BY grantee, securable_type, securable If any of these rights are not documented and authorized, this is a finding.
To correct object ownership: ALTER AUTHORIZATION ON <Securable> TO <Principal> To revoke any unauthorized permissions: REVOKE [Permission] ON <Securable> TO <Principal>
Review the system documentation to obtain a listing of stored procedures and functions that utilize impersonation. Execute the following query: SELECT S.name AS schema_name, O.name AS module_name, USER_NAME( CASE M.execute_as_principal_id WHEN -2 THEN COALESCE(O.principal_id, S.principal_id) ELSE M.execute_as_principal_id END ) AS execute_as FROM sys.sql_modules M JOIN sys.objects O ON M.object_id = O.object_id JOIN sys.schemas S ON O.schema_id = S.schema_id WHERE execute_as_principal_id IS NOT NULL and o.name not in ( 'fn_sysdac_get_username', 'fn_sysutility_ucp_get_instance_is_mi', 'sp_send_dbmail', 'sp_SendMailMessage', 'sp_syscollector_create_collection_set', 'sp_syscollector_delete_collection_set', 'sp_syscollector_disable_collector', 'sp_syscollector_enable_collector', 'sp_syscollector_get_collection_set_execution_status', 'sp_syscollector_run_collection_set', 'sp_syscollector_start_collection_set', 'sp_syscollector_update_collection_set', 'sp_syscollector_upload_collection_set', 'sp_syscollector_verify_collector_state', 'sp_syspolicy_add_policy', 'sp_syspolicy_add_policy_category_subscription', 'sp_syspolicy_delete_policy', 'sp_syspolicy_delete_policy_category_subscription', 'sp_syspolicy_update_policy', 'sp_sysutility_mi_add_ucp_registration', 'sp_sysutility_mi_disable_collection', 'sp_sysutility_mi_enroll', 'sp_sysutility_mi_initialize_collection', 'sp_sysutility_mi_remove', 'sp_sysutility_mi_remove_ucp_registration', 'sp_sysutility_mi_upload', 'sp_sysutility_mi_validate_enrollment_preconditions', 'sp_sysutility_ucp_add_mi', 'sp_sysutility_ucp_add_policy', 'sp_sysutility_ucp_calculate_aggregated_dac_health', 'sp_sysutility_ucp_calculate_aggregated_mi_health', 'sp_sysutility_ucp_calculate_computer_health', 'sp_sysutility_ucp_calculate_dac_file_space_health', 'sp_sysutility_ucp_calculate_dac_health', 'sp_sysutility_ucp_calculate_filegroups_with_policy_violations', 'sp_sysutility_ucp_calculate_health', 'sp_sysutility_ucp_calculate_mi_file_space_health', 'sp_sysutility_ucp_calculate_mi_health', 'sp_sysutility_ucp_configure_policies', 'sp_sysutility_ucp_create', 'sp_sysutility_ucp_delete_policy', 'sp_sysutility_ucp_delete_policy_history', 'sp_sysutility_ucp_get_policy_violations', 'sp_sysutility_ucp_initialize', 'sp_sysutility_ucp_initialize_mdw', 'sp_sysutility_ucp_remove_mi', 'sp_sysutility_ucp_update_policy', 'sp_sysutility_ucp_update_utility_configuration', 'sp_sysutility_ucp_validate_prerequisites', 'sp_validate_user', 'syscollector_collection_set_is_running_update_trigger', 'sysmail_help_status_sp' ) ORDER BY schema_name, module_name If any procedures or functions are returned that are not documented, this is a finding.
Alter stored procedures and functions to remove the "EXECUTE AS" statement.
If the SQL Server instance supports only software development, experimentation, and/or developer-level testing (that is, excluding production systems, integration testing, stress testing, and user acceptance testing), this is not a finding. Obtain a listing of users and roles who are authorized to create, alter, or replace logic modules from the server documentation. In each user database, execute the following query: SELECT P.type_desc AS principal_type, P.name AS principal_name, O.type_desc, CASE class WHEN 0 THEN DB_NAME() WHEN 1 THEN OBJECT_SCHEMA_NAME(major_id) + '.' + OBJECT_NAME(major_id) WHEN 3 THEN SCHEMA_NAME(major_id) ELSE class_desc + '(' + CAST(major_id AS nvarchar) + ')' END AS securable_name, DP.state_desc, DP.permission_name FROM sys.database_permissions DP JOIN sys.database_principals P ON DP.grantee_principal_id = P.principal_id LEFT OUTER JOIN sys.all_objects O ON O.object_id = DP.major_id AND O.type IN ('TR','TA','P','X','RF','PC','IF','FN','TF','U') WHERE DP.type IN ('AL','ALTG') AND DP.class IN (0, 1, 53) SELECT R.name AS role_name, M.type_desc AS principal_type, M.name AS principal_name FROM sys.database_principals R JOIN sys.database_role_members DRM ON R.principal_id = DRM.role_principal_id JOIN sys.database_principals M ON DRM.member_principal_id = M.principal_id WHERE R.name IN ('db_ddladmin','db_owner') AND M.name != 'dbo' If any users or role permissions returned are not authorized to modify the specified object or type, this is a finding. If any user or role membership is not authorized, this is a finding.
Document and obtain approval for any non-administrative users who require the ability to create, alter, or replace logic modules. Revoke the ALTER permission from unauthorized users and roles: REVOKE ALTER ON [<Object Name>] TO [<Principal Name>]
Execute the following query to obtain a listing of user databases whose owner is a member of a fixed server role: SELECT D.name AS database_name, SUSER_SNAME(D.owner_sid) AS owner_name, FRM.is_fixed_role_member FROM sys.databases D OUTER APPLY ( SELECT MAX(fixed_role_member) AS is_fixed_role_member FROM ( SELECT IS_SRVROLEMEMBER(R.name, SUSER_SNAME(D.owner_sid)) AS fixed_role_member FROM sys.server_principals R WHERE is_fixed_role = 1 ) A ) FRM WHERE D.database_id > 4 AND (FRM.is_fixed_role_member = 1 OR FRM.is_fixed_role_member IS NULL) ORDER BY database_name If no databases are returned, this is not a finding. For each database/login returned, review the Server Role memberships 1. In SQL Server Management Studio, Expand “Logins” 2. Double-click the name of the Login 3. Click the “Server Roles” tab If any server roles are selected, but not documented and authorized, this is a finding.
Remove unauthorized users from roles: ALTER ROLE DROP MEMBER user; https://msdn.microsoft.com/en-us/library/ms189775.aspx Set the owner of the database to an authorized login: ALTER AUTHORIZATION ON database::DatabaseName TO login; https://msdn.microsoft.com/en-us/library/ms187359.aspx
Review the system documentation to determine whether the organization has defined the information at rest that is to be protected from modification, which must include, at a minimum, PII and classified information. If no information is identified as requiring such protection, this is not a finding. Review the configuration of SQL Server, Windows, and additional software as relevant. If full-disk encryption is required, and Windows or the storage system is not configured for this, this is a finding. If database transparent data encryption (TDE) is called for, check whether it is enabled: SELECT DB_NAME(database_id) AS [Database Name], CASE encryption_state WHEN 0 THEN 'No database encryption key present, no encryption' WHEN 1 THEN 'Unencrypted' WHEN 2 THEN 'Encryption in progress' WHEN 3 THEN 'Encrypted' WHEN 4 THEN 'Key change in progress' WHEN 5 THEN 'Decryption in progress' WHEN 6 THEN 'Protection change in progress' END AS [Encryption State] FROM sys.dm_database_encryption_keys For each user database for which encryption is called for and it is marked Unencrypted, this is a finding. If table/column encryption and/or a separation between those who own the data (and can view it) and those who manage the data (but should have no access) is required for PII or similar types of data, use Always Encrypted. The details for configuring Always Encrypted are located here: https://msdn.microsoft.com/en-us/library/mt163865.aspx. Review the definitions and contents of the relevant tables/columns for the Always Encryption settings, if any of the information defined as requiring cryptographic protection is not encrypted this is a finding.
Where full-disk encryption is required, configure Windows and/or the storage system to provide this. Where transparent data encryption (TDE) is required, create a master key, obtain a certificate protected by the master key, create a database encryption key and protect it by the certificate, and then set the database to use encryption. For guidance from MSDN on how to do this: https://msdn.microsoft.com/en-us/library/bb934049.aspx. Where table/column encryption is required, enable encryption on the tables/columns in question. For guidance from the Microsoft Developer Network on how to do this with Always Encrypted: https://msdn.microsoft.com/en-us/library/mt163865.aspx.
Review the system documentation to determine whether the organization has defined the information at rest that is to be protected from disclosure, which must include, at a minimum, PII and classified information. If no information is identified as requiring such protection, this is not a finding. If full-disk encryption is required, and Windows or the storage system is not configured for this, this is a finding. If database transparent data encryption (TDE) is called for, check whether it is enabled: SELECT DB_NAME(database_id) AS [Database Name], CASE encryption_state WHEN 0 THEN 'No database encryption key present, no encryption' WHEN 1 THEN 'Unencrypted' WHEN 2 THEN 'Encryption in progress' WHEN 3 THEN 'Encrypted' WHEN 4 THEN 'Key change in progress' WHEN 5 THEN 'Decryption in progress' WHEN 6 THEN 'Protection change in progress' END AS [Encryption State] FROM sys.dm_database_encryption_keys For each user database for which encryption is called for and it is marked Unencrypted, this is a finding. If table/column encryption and/or a separation between those who own the data (and can view it) and those who manage the data (but should have no access) is required for PII or similar types of data, use Always Encrypted. The details for configuring Always Encrypted are located here: https://msdn.microsoft.com/en-us/library/mt163865.aspx. Review the definitions and contents of the relevant tables/columns for the Always Encryption settings, if any of the information defined as requiring cryptographic protection is not encrypted this is a finding.
Where full-disk encryption is required, configure Windows and/or the storage system to provide this. Where transparent data encryption (TDE) is required, create a master key, obtain a certificate protected by the master key, create a database encryption key and protect it by the certificate, and then set the database to use encryption. For guidance from MSDN on how to do this: https://msdn.microsoft.com/en-us/library/bb934049.aspx. Where table/column encryption is required, enable encryption on the table/columns in question. For guidance from the Microsoft Developer Network on how to do this with Always Encrypted: https://msdn.microsoft.com/en-us/library/mt163865.aspx.
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. Review system documentation to determine whether cryptography for classified or sensitive information is required by the information owner. If the system documentation does not specify the type of information hosted on SQL Server as classified, sensitive, and/or unclassified, this is a finding. If neither classified nor sensitive information exists within SQL Server databases or configuration, this is not a finding. Verify that Windows is configured to require the use of FIPS-compliant algorithms. Click "Start", enter "Local Security Policy", and then press "Enter". Expand "Local Policies", select "Security Options", and then locate "System Cryptography: Use FIPS compliant algorithms for encryption, hashing, and signing". If the Security Setting for this option is "Disabled", this is a finding. Note: The list of acceptable algorithms is "AES 256" and "Triple DES". If cryptography is being used by SQL Server, verify that the cryptography is NIST FIPS 140-2 or 140-3 certified by running the following SQL query: SELECT DISTINCT name, algorithm_desc FROM sys.symmetric_keys WHERE key_algorithm NOT IN ('D3','A3') ORDER BY name If any items listed show an uncertified NIST FIPS 140-2 algorithm type, this is a finding.
Configure cryptographic functions to use NSA-approved cryptography compliant algorithms. Use DoD code-signing certificates to create asymmetric keys stored in the database used to encrypt sensitive data stored in the database. Run the following SQL script to create a certificate: USE CREATE CERTIFICATE 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 a symmetric key and assign an existing certificate: USE CREATE SYMMETRIC KEY <'key name'> WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE For Transparent Data Encryption (TDE): USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = ''; CREATE CERTIFICATE . . .; USE ; CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE ; ALTER DATABASE SET ENCRYPTION ON;