Free DISA STIG and SRG Library | Vaulted

MS SQL Server 2014 Database Security Technical Implementation Guide

Version 1 Release 2
2016-10-28
U_MS_SQL_Server_2014_Database_STIG_V1R2_Manual-xccdf.xml
This Security Technical Implementation Guide is published as a tool to improve the security of Department of Defense (DoD) information systems. The requirements are derived from the National Institute of Standards and Technology (NIST) 800-53 and related documents. Comments or proposed revisions to this document should be sent via e-mail to the following address: disa.stig_spt@mail.mil.

Vulnerabilities (43)

SQL Server must enforce approved authorizations for logical access to information and database-level system resources in accordance with applicable access control policies.

Finding ID
SQL4-00-002000
Rule ID
SV-81847r1_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000033-DB-000084
CCI
CCI-000213
Target Key
(None)
Documentable
No
Discussion

Authentication with a DoD-approved PKI certificate does not necessarily imply authorization to access the database and all its contents. To mitigate the risk of unauthorized access to sensitive information by entities that have been issued certificates by DoD-approved PKIs, all DoD systems, including SQL Server databases, must be properly configured to implement access control policies. Successful authentication must not automatically give an entity access to an asset or security boundary. Authorization procedures and controls must be implemented to ensure each authenticated entity also has a validated and current authorization. Authorization is the process of determining whether an entity, once authenticated, is permitted to access a specific asset. Information systems use access control policies and enforcement mechanisms to implement this requirement. Access control policies include identity-based policies, role-based policies, and attribute-based policies. Access enforcement mechanisms include access control lists, access control matrices, and cryptography. These policies and mechanisms must be employed by the application to control access between users (or processes acting on behalf of users) and objects (e.g., devices, files, records, processes, programs, and domains) in the information system. This requirement is applicable to access control enforcement applications, a category that includes SQL Server. If SQL Server is not configured to follow applicable policy when approving access, it may be in conflict with networks or other applications in the information system. This may result in users either gaining or being denied access inappropriately and in conflict with applicable policy.

Fix Text

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.

Check Content

Review the system documentation to determine the required levels of protection for securables in the database, by type of user. Review the permissions actually in place in the database. The database permission functions and views provided in the supplemental file Permissions.sql can help with this. If the actual permissions do not match the documented requirements, this is a finding.

SQL Server must generate Trace or Audit records for organization-defined auditable events.

Finding ID
SQL4-00-011200
Rule ID
SV-81849r2_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000089-DB-000064
CCI
CCI-000169
Target Key
(None)
Documentable
No
Discussion

Audit records can be generated from various components within the information system (e.g., network interface, hard disk, modem, etc.). From an application perspective, certain specific application functionalities may be audited as well. The list of audited events is the set of events for which audits are to be generated. This set of events is typically a subset of the list of all events for which the system is capable of generating audit records. Examples are auditable events, time stamps, source and destination addresses, user/process identifiers, event descriptions, success/fail indications, file names involved, and access control or flow control rules invoked. Organizations define which application components shall provide auditable events. The DBMS must provide auditing for the list of events defined by the organization or risk negatively impacting forensic investigations into malicious behavior in the information system. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016.

Fix Text

Design and deploy a SQL Server Audit or Trace that captures all auditable events. The script provided in the supplemental file Trace.sql can be used to create a trace; edit it as necessary to capture any additional, locally-defined events. The script provided in the supplemental file Audit.sql can be used to create an audit; edit it as necessary to capture any additional, locally-defined events.

Check Content

If there are no locally-defined security tables or procedures, this is not applicable. If neither SQL Server Audit nor SQL Server Trace is in use for audit purposes, this is a finding. If SQL Server Trace is in use for audit purposes, verify that all required events are being audited. From the query prompt: SELECT * FROM sys.traces; All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding. Determine the trace(s) being used for the auditing requirement. In the following, replace # with a trace ID being used for the auditing requirements. From the query prompt: SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#); The following required event IDs should all be among those listed; if not, this is a finding. Any additional events locally defined should also be in the list; if not, this is a finding. 14 -- Audit Login 15 -- Audit Logout 16 -- Attention 17 -- ExistingConnection 18 -- Audit Server Starts and Stops 20 -- Audit Login Failed 42 -- SP:Starting 43 -- SP:Completed 46 -- Object:Created 47 -- Object:Deleted 90 -- User-defined Event 102 -- Audit Database Scope GDR Event 103 -- Audit Object GDR Event 104 -- Audit AddLogin Event 105 -- Audit Login GDR Event 106 -- Audit Login Change Property Event 107 -- Audit Login Change Password Event 108 -- Audit Add Login to Server Role Event 109 -- Audit Add DB User Event 110 -- Audit Add Member to DB Role Event 111 -- Audit Add Role Event 112 -- Audit App Role Change Password Event 113 -- Audit Statement Permission Event 115 -- Audit Backup/Restore Event 116 -- Audit DBCC Event 117 -- Audit Change Audit Event 118 -- Audit Object Derived Permission Event 128 -- Audit Database Management Event 129 -- Audit Database Object Management Event 130 -- Audit Database Principal Management Event 131 -- Audit Schema Object Management Event 132 -- Audit Server Principal Impersonation Event 133 -- Audit Database Principal Impersonation Event 134 -- Audit Server Object Take Ownership Event 135 -- Audit Database Object Take Ownership Event 152 -- Audit Change Database Owner 153 -- Audit Schema Object Take Ownership Event 162 -- User error message 164 -- Object:Altered 170 -- Audit Server Scope GDR Event 171 -- Audit Server Object GDR Event 172 -- Audit Database Object GDR Event 173 -- Audit Server Operation Event 175 -- Audit Server Alter Trace Event 176 -- Audit Server Object Management Event 177 -- Audit Server Principal Management Event 178 -- Audit Database Operation Event 180 -- Audit Database Object Access Event If SQL Server Audit is in use, proceed as follows. The basic SQL Server Audit configuration provided in the supplemental file Audit.sql uses broad, server-level audit action groups for this purpose. SQL Server Audit's flexibility makes other techniques possible. If an alternative technique is in use and demonstrated effective, this is not a finding. Determine the name(s) of the server audit specification(s) in use. To look at audits and audit specifications, in Management Studio's object explorer, expand <server name> >> Security >> Audits and <server name> >> Security >> Server Audit Specifications. Also, <server name> >> Databases >> <database name> >> Security >> Database Audit Specifications. Alternatively, review the contents of the system views with "audit" in their names. Run the following code to verify that all configuration-related actions are being audited: USE [master]; GO SELECT * FROM sys.server_audit_specification_details WHERE server_specification_id = (SELECT server_specification_id FROM sys.server_audit_specifications WHERE [name] = '<server_audit_specification_name>'); GO Examine the list produced by the query. If the audited_result column is not "SUCCESS AND FAILURE" on every row, this is a finding. If any of the following audit action groups is not included in the list, this is a finding. APPLICATION_ROLE_CHANGE_PASSWORD_GROUP AUDIT_CHANGE_GROUP BACKUP_RESTORE_GROUP DATABASE_CHANGE_GROUP DATABASE_OBJECT_ACCESS_GROUP DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP DATABASE_OBJECT_PERMISSION_CHANGE_GROUP DATABASE_OPERATION_GROUP DATABASE_OWNERSHIP_CHANGE_GROUP DATABASE_PERMISSION_CHANGE_GROUP DATABASE_PRINCIPAL_CHANGE_GROUP DATABASE_PRINCIPAL_IMPERSONATION_GROUP DATABASE_ROLE_MEMBER_CHANGE_GROUP DBCC_GROUP FAILED_LOGIN_GROUP LOGIN_CHANGE_PASSWORD_GROUP LOGOUT_GROUP SCHEMA_OBJECT_ACCESS_GROUP SCHEMA_OBJECT_CHANGE_GROUP SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP SERVER_OBJECT_CHANGE_GROUP SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP SERVER_OBJECT_PERMISSION_CHANGE_GROUP SERVER_OPERATION_GROUP SERVER_PERMISSION_CHANGE_GROUP SERVER_PRINCIPAL_CHANGE_GROUP SERVER_PRINCIPAL_IMPERSONATION_GROUP SERVER_ROLE_MEMBER_CHANGE_GROUP SERVER_STATE_CHANGE_GROUP SUCCESSFUL_LOGIN_GROUP TRACE_CHANGE_GROUP

Where SQL Server Audit is in use at the database level, SQL Server must allow only the ISSM (or individuals or roles appointed by the ISSM) to select which auditable events are to be audited at the database level.

Finding ID
SQL4-00-011320
Rule ID
SV-81851r1_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000090-DB-000065
CCI
CCI-000171
Target Key
(None)
Documentable
No
Discussion

Without the capability to restrict which roles and individuals can select which events are audited, unauthorized personnel may be able to prevent or interfere with the auditing of critical events. Suppression of auditing could permit an adversary to evade detection. Misconfigured audits can degrade the system's performance by overwhelming the audit log. Misconfigured audits may also make it more difficult to establish, correlate, and investigate the events relating to an incident or identify those responsible for one. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016. This version of the requirement deals with SQL Server Audit-based audit trails.

Fix Text

Create a database role specifically for audit maintainers, and give it permission to maintain audits, without granting it unnecessary permissions: USE <database name>; GO CREATE ROLE DATABASE_AUDIT_MAINTAINERS; GO GRANT ALTER ANY DATABASE AUDIT TO DATABASE_AUDIT_MAINTAINERS; GO (The role name used here is an example; other names may be used.) 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 <user name> ; 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.

Check Content

If SQL Server Audit is not in use at the database level, this is not applicable (NA). Obtain the list of approved audit maintainers from the system documentation. Review the database roles and individual users that have the following permissions, both of which enable the ability to maintain audit definitions: ALTER ANY DATABASE AUDIT CONTROL ON DATABASE The functions and views provided in the supplemental file Permissions.sql can assist in this review. In the following, "STIG" stands for the schema where you have deployed these views and functions. To see which logins and server roles have been granted these permissions: SELECT * FROM STIG.database_permissions P WHERE (P.[Permission] = 'ALTER ANY DATABASE AUDIT') OR (P.[Permission] = 'CONTROL' AND P.[Securable Type or Class] = 'DATABASE') ); To see what users and database roles inherit these permissions from the database roles reported by the previous query, repeat the following for each one: SELECT * FROM STIG.members_of_database_role(<database role name>); To see all the permissions in effect for a database principal (server role or login): SELECT * FROM STIG.server_effective_permissions(<principal name>); If designated personnel are not able to configure auditable events, this is a finding. If unapproved personnel are able to configure auditable events, this is a finding.

SQL Server must be monitored to discover unauthorized changes to functions.

Finding ID
SQL4-00-014900
Rule ID
SV-81855r2_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000133-DB-000179
CCI
CCI-001499
Target Key
(None)
Documentable
No
Discussion

When dealing with change control issues, it should be noted, any changes to the hardware, software, and/or firmware components of SQL Server and/or application can potentially have significant effects on the overall security of the system. If SQL Server were to allow any user to make changes to software libraries, then those changes might be implemented without undergoing the appropriate testing and approvals that are part of a robust change management process. This requirement is contingent upon the language in which the application is programmed, as many application architectures in use today incorporate their software libraries into, and make them inseparable from, their compiled distributions, rendering them static and version-dependent. However, this requirement does apply to applications with software libraries accessible and configurable, as in the case of interpreted languages. Accordingly, only qualified and authorized individuals shall be allowed to obtain access to SQL Server components for purposes of initiating changes, including upgrades and modifications. Unmanaged changes that occur to the SQL Server software libraries or configuration, such as Functions, can lead to unauthorized or compromised installations.

Fix Text

Configure a SQL Server timed job that automatically checks all system and user-defined Functions for being modified. (The supplemental file Track.sql, provided with this STIG, can be used to establish a monitoring job. This should be supplemented with a process for informing the appropriate personnel. Other techniques for achieving the same ends, such as the use of DDL triggers, are acceptable.)

Check Content

Check the SQL Server configuration for a timed job that automatically checks all system and user-defined Functions for being modified by running the following SQL Server query: EXEC msdb.dbo.sp_help_job @job_name = '<enter . . . job name>'; (Alternatively, in SQL Server Management Studio, navigate to SQL Server Agent and examine the job from there.) If a timed job or some other method is not implemented to check for Functions being modified, this is a finding.

SQL Server must be monitored to discover unauthorized changes to triggers.

Finding ID
SQL4-00-015100
Rule ID
SV-81857r2_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000133-DB-000179
CCI
CCI-001499
Target Key
(None)
Documentable
No
Discussion

When dealing with change control issues, it should be noted, any changes to the hardware, software, and/or firmware components of SQL Server and/or application can potentially have significant effects on the overall security of the system. If SQL Server were to allow any user to make changes to software libraries, then those changes might be implemented without undergoing the appropriate testing and approvals that are part of a robust change management process. This requirement is contingent upon the language in which the application is programmed, as many application architectures in use today incorporate their software libraries into, and make them inseparable from, their compiled distributions, rendering them static and version-dependent. However, this requirement does apply to applications with software libraries accessible and configurable, as in the case of interpreted languages. Accordingly, only qualified and authorized individuals shall be allowed to obtain access to SQL Server components for purposes of initiating changes, including upgrades and modifications. Unmanaged changes that occur to the SQL Server software libraries or configuration, such as Triggers, can lead to unauthorized or compromised installations.

Fix Text

Configure a SQL Server timed job that automatically checks all system and user-defined Triggers for modification. (The supplemental file Track.sql, provided with this STIG, can be used to establish a monitoring job. This should be supplemented with a process for informing the appropriate personnel. Other techniques for achieving the same ends, such as the use of DDL triggers, are acceptable.)

Check Content

Check the SQL Server configuration for the timed job that automatically checks all system and user-defined Triggers for being modified by running the following SQL Server query: EXEC msdb.dbo.sp_help_job @job_name = '<enter . . . job name>'; (Alternatively, in SQL Server Management Studio, navigate to SQL Server Agent and examine the job from there.) If such a job, or an alternative method of monitoring triggers for modification, does not exist, this is a finding

SQL Server must be monitored to discover unauthorized changes to stored procedures.

Finding ID
SQL4-00-015200
Rule ID
SV-81859r2_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000133-DB-000179
CCI
CCI-001499
Target Key
(None)
Documentable
No
Discussion

When dealing with change control issues, it should be noted, any changes to the hardware, software, and/or firmware components of SQL Server and/or application can potentially have significant effects on the overall security of the system. If SQL Server were to allow any user to make changes to software libraries, then those changes might be implemented without undergoing the appropriate testing and approvals that are part of a robust change management process. This requirement is contingent upon the language in which the application is programmed, as many application architectures in use today incorporate their software libraries into, and make them inseparable from, their compiled distributions, rendering them static and version-dependent. However, this requirement does apply to applications with software libraries accessible and configurable, as in the case of interpreted languages. Accordingly, only qualified and authorized individuals shall be allowed to obtain access to SQL Server components for purposes of initiating changes, including upgrades and modifications. Unmanaged changes that occur to the SQL Server software libraries or configuration, such as Stored Procedures, can lead to unauthorized or compromised installations.

Fix Text

Configure a SQL Server timed job that automatically checks all system and user-defined Stored Procedures for modification. (The supplemental file Track.sql, provided with this STIG, can be used to establish a monitoring job. This should be supplemented with a process for informing the appropriate personnel. Other techniques for achieving the same ends, such as the use of DDL triggers, are acceptable.)

Check Content

Check for the existence of a job to monitor for changes to stored procedures: EXEC msdb.dbo.sp_help_job @job_name = '<enter . . . job name>'; (Alternatively, in SQL Server Management Studio, navigate to SQL Server Agent and examine the job from there.) If such a job, or an alternative method of monitoring stored procedures for modification, does not exist, this is a finding.

Database objects (including but not limited to tables, indexes, storage, stored procedures, functions, triggers, links to software external to SQL Server, etc.) must be owned by database/DBMS principals authorized for ownership.

Finding ID
SQL4-00-015600
Rule ID
SV-81861r1_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000133-DB-000200
CCI
CCI-001499
Target Key
(None)
Documentable
No
Discussion

Within the database, object ownership implies full privileges to the owned object, including the privilege to assign access to the owned objects to other subjects. Database functions and procedures can be coded using definer's rights. This allows anyone who utilizes the object to perform the actions if they were the owner. If not properly managed, this can lead to privileged actions being taken by unauthorized individuals. Conversely, if critical tables or other objects rely on unauthorized owner accounts, these objects may be lost when an account is removed.

Fix Text

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: USE <database name>; GO ALTER SCHEMA <name of new schema> TRANSFER <name of old schema>.<object name>; 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.

Check Content

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 view STIG.database_permissions, included in the supplemental file, Permissions.sql, can be of use in making this determination: USE <database name>; GO SELECT DISTINCT S.[Schema/Owner] AS [Owner], O.[Schema/Owner] AS [Schema], O.[Securable] FROM STIG.database_permissions O INNER JOIN STIG.database_permissions S ON S.[Securable] = O.[Schema/Owner] AND O.[Securable Type or Class] = 'OBJECT_OR_COLUMN' AND S.[Securable Type or Class] = 'SCHEMA' WHERE S.[Schema/Owner] NOT IN ('dbo', 'sys', 'INFORMATION_SCHEMA' ... ) -- Complete the "NOT IN" list with the names of user accounts authorized for ownership. ; If any of the listed owners is not authorized, this is a finding.

In a database owned by a login not having administrative privileges at the instance level, the database property TRUSTWORTHY must be OFF unless required and authorized.

Finding ID
SQL4-00-015620
Rule ID
SV-81863r1_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000133-DB-000200
CCI
CCI-001499
Target Key
(None)
Documentable
No
Discussion

SQL Server's fixed (built-in) server roles, especially [sysadmin], have powerful capabilities that could cause great harm if misused, so their use must be tightly controlled. The SQL Server instance uses each database's TRUSTWORTHY property to guard against tampering that could enable unwarranted privilege escalation. When TRUSTWORTHY is 0/False/Off, SQL Server prevents the database from accessing resources in other databases. When TRUSTWORTHY is 1/True/On, SQL Server permits access to other databases (subject to other protections). SQL Server sets TRUSTWORTHY OFF when it creates a new database. SQL Server forces TRUSTWORTHY OFF, irrespective of its prior value, when an existing database is attached to it, to address the possibility that an adversary may have tampered with the database, introducing malicious code. To set TRUSTWORTHY ON, an account with the [sysadmin] role must issue an ALTER DATABASE command. Although SQL Server itself treats this property conservatively, application installer programs may set TRUSTWORTHY ON and leave it on. This provides an opportunity for misuse. When TRUSTWORTHY is ON, users of the database can take advantage of the database owner's privileges, by impersonating the owner. This can have particularly serious consequences if the database owner is the [sa] login (which may have been renamed in accordance with SQL4-00-010200, and disabled in accordance with SQL4-00-017100, but nonetheless can be invoked in an EXECUTE AS USER = 'dbo' statement, or CREATE PROCEDURE ... WITH EXECUTE AS OWNER ...). The [sa] login cannot be removed from the [sysadmin] role. The user impersonating [sa] - or another [sysadmin] account - is then able to perform administrative actions across all databases under the instance, including making any himself or any other login a member of [sysadmin]. Most of the other fixed server roles could be similarly abused. Therefore, TRUSTWORTHY must not be used on databases owned by logins that are members of the fixed server roles. Further, if TRUSTWORTHY is to be used for any other database, the need must be documented and approved. The system database [msdb] is an exception: it is required to be TRUSTWORTHY.

Fix Text

Run the SQL statements: USE [master]; GO ALTER DATABASE <name> SET TRUSTWORTHY OFF; GO

Check Content

If the database is owned by an account that is directly or indirectly a member of a fixed (built-in) server role, this is not applicable (NA). Run the query: USE <database name>; GO SELECT DB_NAME() AS [Database], SUSER_SNAME(D.owner_sid) AS [Database Owner], CASE WHEN D.is_trustworthy_on = 1 THEN 'ON' ELSE 'off' END AS [Trustworthy] FROM sys.databases D WHERE D.[name] = DB_NAME() AND DB_NAME() <> 'msdb' AND D.is_trustworthy_on = 1; GO If the query returns a row indicating that the TRUSTWORTHY setting is OFF, or returns no rows, this is not a finding. Review the system security plan to determine whether the need for TRUSTWORTHY is documented and approved. If not, this is a finding.

In a database owned by [sa], or by any other login having administrative privileges at the instance level, the database property TRUSTWORTHY must be OFF.

Finding ID
SQL4-00-015610
Rule ID
SV-81865r1_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000133-DB-000200
CCI
CCI-001499
Target Key
(None)
Documentable
No
Discussion

SQL Server's fixed (built-in) server roles, especially [sysadmin], have powerful capabilities that could cause great harm if misused, so their use must be tightly controlled. The SQL Server instance uses each database's TRUSTWORTHY property to guard against tampering that could enable unwarranted privilege escalation. When TRUSTWORTHY is 0/False/Off, SQL Server prevents the database from accessing resources in other databases. When TRUSTWORTHY is 1/True/On, SQL Server permits access to other databases (subject to other protections). SQL Server sets TRUSTWORTHY OFF when it creates a new database. SQL Server forces TRUSTWORTHY OFF, irrespective of its prior value, when an existing database is attached to it, to address the possibility that an adversary may have tampered with the database, introducing malicious code. To set TRUSTWORTHY ON, an account with the [sysadmin] role must issue an ALTER DATABASE command. Although SQL Server itself treats this property conservatively, application installer programs may set TRUSTWORTHY ON and leave it on. This provides an opportunity for misuse. When TRUSTWORTHY is ON, users of the database can take advantage of the database owner's privileges, by impersonating the owner. This can have particularly serious consequences if the database owner is the [sa] login (which may have been renamed in accordance with SQL4-00-010200, and disabled in accordance with SQL4-00-017100, but nonetheless can be invoked in an EXECUTE AS USER = 'dbo' statement, or CREATE PROCEDURE ... WITH EXECUTE AS OWNER ...). The [sa] login cannot be removed from the [sysadmin] role. The user impersonating [sa] - or another [sysadmin] account - is then able to perform administrative actions across all databases under the instance, including making any himself or any other login a member of [sysadmin]. Most of the other fixed server roles could be similarly abused. Therefore, TRUSTWORTHY must not be used on databases owned by logins that are members of the fixed server roles. Further, if TRUSTWORTHY is to be used for any other database, the need must be documented and approved. The system database [msdb] is an exception: it is required to be TRUSTWORTHY.

Fix Text

Set the TRUSTWORTHY property OFF; or remove the database owner from the fixed server role(s); or change the database owner. To set the TRUSTWORTHY property OFF: USE [master]; GO ALTER DATABASE <name> SET TRUSTWORTHY OFF; GO Verify that this produced the intended result by re-running the query specified in the Check. To determine the path or paths by which the database owner is assigned the fixed server role or roles, run this query: USE <database name>; GO WITH C AS ( SELECT P.name AS [Parent Server Role], CAST('Fixed' AS varchar(8)) AS [Server Role Type], M.name AS [Member], M.type_desc AS [Member Type], P.name AS [Root], 1 AS [Level] FROM [sys].[server_role_members] X INNER JOIN [sys].[server_principals] P ON P.principal_id = X.role_principal_id INNER JOIN [sys].[server_principals] M ON M.principal_id = X.member_principal_id WHERE P.is_fixed_role = 1 UNION ALL SELECT P.name AS [Parent Server Role], CASE WHEN M.is_fixed_role = 1 THEN CAST('Fixed' AS varchar(8)) ELSE CAST('Custom' AS varchar(8)) END AS [Server Role Type], M.name AS [Member], M.type_desc AS [Member Type], C.[Root] AS [Root], C.[Level] + 1 AS [Level] FROM [sys].[server_role_members] X INNER JOIN [sys].[server_principals] P ON P.principal_id = X.role_principal_id INNER JOIN [sys].[server_principals] M ON M.principal_id = X.member_principal_id INNER JOIN C ON P.name = C.Member ) , B AS ( SELECT C.[Member] AS [Leaf], C.[Root], C.[Parent Server Role], C.[Server Role Type], C.[Member], C.[Member Type], C.[Level] FROM C WHERE C.[Member Type] NOT LIKE '%ROLE%' UNION ALL SELECT B.[Leaf], C.[Root], C.[Parent Server Role], C.[Server Role Type], C.[Member], C.[Member Type], C.[Level] FROM C INNER JOIN B ON C.[Member] = B.[Parent Server Role] AND C.[Level] = B.[Level] - 1 AND C.[Root] = B.[Root] ) SELECT DB_NAME() AS [Database], B.[Leaf] AS [Owner Login], B.[Root] AS[Top-Level Server Role], B.[Parent Server Role], B.[Server Role Type], B.[Member], B.[Member Type], B.[Level] FROM B WHERE B.[Leaf] = (SELECT SUSER_SNAME(D.owner_sid) FROM sys.databases D WHERE D.Name = DB_NAME()) ORDER BY B.[Root], B.[Level], B.[Parent Server Role], B.[Member] ; GO To remove the database owner from a fixed server role or a custom server role: USE [master]; GO ALTER SERVER ROLE <fixed/custom server role name> DROP MEMBER <database owner name>; GO Verify that this produced the intended result by re-running the Check query. To change the database owner: USE [master]; GO ALTER AUTHORIZATION ON DATABASE::<DB name> TO <new owner name>; GO Verify that this produced the intended result by re-running the Check query.

Check Content

Run the SQL statements: USE <database name>; GO WITH FixedServerRoles(RoleName) AS ( SELECT 'sysadmin' UNION SELECT 'securityadmin' UNION SELECT 'serveradmin' UNION SELECT 'setupadmin' UNION SELECT 'processadmin' UNION SELECT 'diskadmin' UNION SELECT 'dbcreator' UNION SELECT 'bulkadmin' ) SELECT DB_NAME() AS [Database], SUSER_SNAME(D.owner_sid) AS [Database Owner], F.RoleName AS [Fixed Server Role], CASE WHEN D.is_trustworthy_on = 1 THEN 'ON' ELSE 'off' END AS [Trustworthy] FROM FixedServerRoles F INNER JOIN sys.databases D ON D.Name = DB_NAME() WHERE IS_SRVROLEMEMBER(F.RoleName, SUSER_SNAME(D.owner_sid)) = 1 AND DB_NAME() <> 'msdb' AND D.is_trustworthy_on = 1; GO If the query returns any rows, this is a finding.

In the event of a system failure, SQL Server must preserve any information necessary to return to operations with least disruption to mission processes.

Finding ID
SQL4-00-021210
Rule ID
SV-81867r2_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000226-DB-000147
CCI
CCI-001665
Target Key
(None)
Documentable
No
Discussion

Failure to a known state can address safety or security in accordance with the mission/business needs of the organization. The existence and reliability of database backups is an essential aspect of the ability to fail to a known state. It helps prevent a loss of confidentiality, integrity, or availability in the event of a failure of the information system or a component of the system. Backups must be performed according to an appropriate schedule, and must be tested periodically to provide assurance that they can be used for restoring the database.

Fix Text

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 <server name> >> SQL Server Agent >> Jobs. Create, modify and delete jobs to implement the backup schedule. (Alternatively, this may done using T-SQL code.) 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.

Check Content

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, but the documented recovery model is Simple, 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. In SQL Server Management Studio, Object Explorer, right-click on the name of the database; select Properties. Select the Options page. Observe the Recovery Model field, near the top of the page. If this does not match the documented recovery model, this is a finding. In Object Explorer, expand <server name> >> SQL Server Agent >> Jobs. Review the jobs set up to implement the backup plan. If they are absent, this is a finding. Right-click on each backup job; select View History. If the history indicates a pattern of job failures, 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.

The Database Master Key encryption password must meet DoD password complexity requirements.

Finding ID
SQL4-00-024000
Rule ID
SV-81869r1_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000231-DB-000154
CCI
CCI-001199
Target Key
(None)
Documentable
No
Discussion

Weak passwords may be easily guessed. When passwords are used to encrypt keys used for encryption of sensitive data, then the confidentiality of all data encrypted using that key is at risk.

Fix Text

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.

Check Content

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 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.

The Database Master Key must be encrypted by the Service Master Key, where a Database Master Key is required and another encryption method has not been specified.

Finding ID
SQL4-00-024100
Rule ID
SV-81871r1_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000231-DB-000154
CCI
CCI-001199
Target Key
(None)
Documentable
No
Discussion

When not encrypted by the Service Master Key, system administrators or application administrators may access and use the Database Master Key to view sensitive data that they are not authorized to view. Where alternate encryption means are not feasible, encryption by the Service Master Key may be necessary. To help protect sensitive data from unauthorized access by DBAs, mitigations may be in order. Mitigations may include automatic alerts or other audit events when the Database Master Key is accessed outside of the application or by a DBA account.

Fix Text

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.

Check Content

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.

Database Master Key passwords must not be stored in credentials within the database.

Finding ID
SQL4-00-024200
Rule ID
SV-81873r1_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000231-DB-000154
CCI
CCI-001199
Target Key
(None)
Documentable
No
Discussion

Storage of the Database Master Key password in a database credential allows decryption of sensitive data by privileged users who may not have a need-to-know requirement to access the data.

Fix Text

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'

Check Content

From the query prompt: SELECT COUNT(credential_id) FROM [master].sys.master_key_passwords If count is not 0, this is a finding.

Symmetric keys (other than the database master key) must use a DoD certificate to encrypt the key.

Finding ID
SQL4-00-024300
Rule ID
SV-81875r2_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000231-DB-000154
CCI
CCI-001199
Target Key
(None)
Documentable
No
Discussion

Data within the database is protected by use of encryption. The symmetric keys are critical for this process. If the symmetric keys were to be compromised the data could be disclosed to unauthorized personnel. The database master key is exempt, as a password must be supplied when creating it.

Fix Text

Configure or alter symmetric keys to encrypt keys with certificates or authorized asymmetric keys. In a query tool: 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.

Check Content

In a query tool: USE <database name>; GO 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 s.name <> '##MS_DatabaseMasterKey##' AND k.crypt_type IN ('ESKP', 'ESKS') ORDER BY s.name, k.crypt_type_desc; GO 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.

The Service Master Key must be backed up, stored offline and off-site.

Finding ID
SQL4-00-024500
Rule ID
SV-81877r1_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000231-DB-000154
CCI
CCI-001199
Target Key
(None)
Documentable
No
Discussion

Backup and recovery of the Service Master Key may be critical to the complete recovery of the database. Not having this key can lead to loss of data during recovery.

Fix Text

Document and implement procedures to safely back up 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 off-site. BACKUP SERVICE MASTER KEY TO FILE = 'path_to_file' ENCRYPTION BY PASSWORD = 'password'; As this requires a password, take care to ensure it is not exposed to unauthorized persons or stored as plain text.

Check Content

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.

Database contents must be protected from unauthorized and unintended information transfer by enforcement of a data-transfer policy.

Finding ID
SQL4-00-021800
Rule ID
SV-81879r1_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000243-DB-000128
CCI
CCI-001090
Target Key
(None)
Documentable
No
Discussion

The purpose of this control is to prevent information, including encrypted representations of information, produced by the actions of a prior user/role (or the actions of a process acting on behalf of a prior user/role) from being available to any current user/role (or current process) that obtains access to a shared system resource (e.g., registers, main memory, secondary storage) after the resource has been released back to the information system. Control of information in shared resources is also referred to as object reuse. Data used for the development and testing of applications often involves copying data from production. It is important that specific procedures exist for this process, so copies of sensitive data are not misplaced or left in a temporary location without the proper controls.

Fix Text

Create and document a process for moving data from production to development/test systems and follow the process. Modify any code used for moving data from production to development/test systems to ensure copies of production data are not left in unsecured locations.

Check Content

Verify there are proper procedures in place for the transfer of development/test data from production. Review any scripts or code that exists for the movement of production data to development/test and verify copies of production data are not left in unprotected locations. If there is no documented procedure for data movement from production to development/test, this is a finding. If data movement code that copies from production to development/test does exist and leaves any copies of production data in unprotected locations, this is a finding.

SQL Server must check the validity of all data inputs except those specifically identified by the organization.

Finding ID
SQL4-00-022500
Rule ID
SV-81881r2_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000251-DB-000160
CCI
CCI-001310
Target Key
(None)
Documentable
No
Discussion

Invalid user input occurs when a user inserts data or characters into an application’s data entry fields and the application is unprepared to process that data. This results in unanticipated application behavior potentially leading to an application or information system compromise. Invalid user input is one of the primary methods employed when attempting to compromise an application. SQL Server needs to validate the data user’s attempt to input to the application for processing. Rules for checking the valid syntax and semantics of information system inputs (e.g., character set, length, numerical range, acceptable values) are in place to verify inputs match specified definitions for format and content. Inputs passed to interpreters are prescreened to prevent the content from being unintentionally interpreted as commands. A poorly designed database system can have many problems. A common issue with these types of systems is the missed opportunity to use constraints. This calls for inspection of application source code, which will require collaboration with the application developers. It is recognized that in many cases, the database administrator (DBA) is organizationally separate from the application developers and may have limited, if any, access to source code. Nevertheless, protections of this type are so important to the secure operation of databases that they must not be ignored. At a minimum, the DBA must attempt to obtain assurances from the development organization that this issue has been addressed and must document what has been discovered.

Fix Text

Use triggers, constraints, foreign keys, etc. to validate data input. Modify SQL Server to properly use the correct column data types as required in the database.

Check Content

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.

The DBMS and associated applications must reserve the use of dynamic code execution for situations that require it.

Finding ID
SQL4-00-031500
Rule ID
SV-81883r2_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000251-DB-000391
CCI
CCI-001310
Target Key
(None)
Documentable
No
Discussion

With respect to database management systems, one class of threat is known as SQL Injection, or more generally, code injection. It takes advantage of the dynamic execution capabilities of various programming languages, including dialects of SQL. In such cases, the attacker deduces the manner in which SQL statements are being processed, either from inside knowledge or by observing system behavior in response to invalid inputs. When the attacker identifies scenarios where SQL queries are being assembled by application code (which may be within the database or separate from it) and executed dynamically, the attacker is then able to craft input strings that subvert the intent of the query. Potentially, the attacker can gain unauthorized access to data, including security settings, and severely corrupt or destroy the database. The principal protection against code injection is not to use dynamic execution except where it provides necessary functionality that cannot be utilized otherwise. Use strongly typed data items rather than general-purpose strings as input parameters to task-specific, pre-compiled stored procedures and functions (and triggers). This calls for inspection of application source code, which will require collaboration with the application developers. It is recognized that in many cases, the database administrator (DBA) is organizationally separate from the application developers and may have limited, if any, access to source code. Nevertheless, protections of this type are so important to the secure operation of databases that they must not be ignored. At a minimum, the DBA must attempt to obtain assurances from the development organization that this issue has been addressed and must document what has been discovered

Fix Text

Where dynamic code execution is employed in circumstances where the objective could practically be satisfied by static execution with strongly typed parameters, modify the code to do so.

Check Content

Review source code in the database (stored procedures, functions, triggers) and application source code, to identify cases of dynamic code execution. If dynamic code execution is employed in circumstances where the objective could practically be satisfied by static execution with strongly typed parameters, this is a finding.

The DBMS and associated applications, when making use of dynamic code execution, must scan input data for invalid values that may indicate a code injection attack.

Finding ID
SQL4-00-031600
Rule ID
SV-81885r2_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000251-DB-000392
CCI
CCI-001310
Target Key
(None)
Documentable
No
Discussion

With respect to database management systems, one class of threat is known as SQL Injection, or more generally, code injection. It takes advantage of the dynamic execution capabilities of various programming languages, including dialects of SQL. In such cases, the attacker deduces the manner in which SQL statements are being processed, either from inside knowledge or by observing system behavior in response to invalid inputs. When the attacker identifies scenarios where SQL queries are being assembled by application code (which may be within the database or separate from it) and executed dynamically, the attacker is then able to craft input strings that subvert the intent of the query. Potentially, the attacker can gain unauthorized access to data, including security settings, and severely corrupt or destroy the database. The principal protection against code injection is not to use dynamic execution except where it provides necessary functionality that cannot be utilized otherwise. Use strongly typed data items rather than general-purpose strings as input parameters to task-specific, pre-compiled stored procedures and functions (and triggers). When dynamic execution is necessary, ways to mitigate the risk include the following, which should be implemented both in the on-screen application and at the database level, in the stored procedures: -- Allow strings as input only when necessary. -- Rely on data typing to validate numbers, dates, etc. Do not accept invalid values. If substituting other values for them, think carefully about whether this could be subverted. -- Limit the size of input strings to what is truly necessary. -- If single quotes/apostrophes, double quotes, semicolons, equals signs, angle brackets, or square brackets will never be valid as input, reject them. -- If comment markers will never be valid as input, reject them. In SQL, these are -- or /* */ -- If HTML and XML tags, entities, comments, etc., will never be valid, reject them. -- If wildcards are present, reject them unless truly necessary. In SQL these are the underscore and the percentage sign, and the word ESCAPE is also a clue that wildcards are in use. -- If SQL key words, such as SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, ESCAPE, UNION, GRANT, REVOKE, DENY, MODIFY will never be valid, reject them. Use case-insensitive comparisons when searching for these. Bear in mind that some of these words, particularly Grant (as a person's name), could also be valid input. -- If there are range limits on the values that may be entered, enforce those limits. -- Institute procedures for inspection of programs for correct use of dynamic coding, by a party other than the developer. -- Conduct rigorous testing of program modules that use dynamic coding, searching for ways to subvert the intended use. -- Record the inspection and testing in the system documentation. -- Bear in mind that all this applies not only to screen input, but also to the values in an incoming message to a web service or to a stored procedure called by a software component that has not itself been hardened in these ways. Not only can the caller be subject to such vulnerabilities; it may itself be the attacker. This calls for inspection of application source code, which will require collaboration with the application developers. It is recognized that in many cases, the database administrator (DBA) is organizationally separate from the application developers and may have limited, if any, access to source code. Nevertheless, protections of this type are so important to the secure operation of databases that they must not be ignored. At a minimum, the DBA must attempt to obtain assurances from the development organization that this issue has been addressed and must document what has been discovered

Fix Text

Where dynamic code execution is used, modify the code to implement protections against code injection.

Check Content

Review source code in the database (stored procedures, functions, triggers) and application source code to identify cases of dynamic code execution. If dynamic code execution is employed without protective measures against code injection, this is a finding.

The DBMS and associated applications must provide non-privileged users with error messages that provide information necessary for corrective actions without revealing information that could be exploited by adversaries.

Finding ID
SQL4-00-022800
Rule ID
SV-81887r2_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000266-DB-000162
CCI
CCI-001312
Target Key
(None)
Documentable
No
Discussion

Any DBMS or associated application providing too much information in error messages on the screen or printout risks compromising the data and security of the system. The structure and content of error messages need to be carefully considered by the organization and development team. Databases can inadvertently provide a wealth of information to an attacker through improperly handled error messages. In addition to sensitive business or personal information, database errors can provide host names, IP addresses, user names, and other system information not required for end-user troubleshooting but very useful to someone targeting the system. Carefully consider the structure/content of error messages. The extent to which information systems are able to identify and handle error conditions is guided by organizational policy and operational requirements. Information that could be exploited by adversaries includes, for example, logon attempts with passwords entered by mistake as the username, mission/business information that can be derived from (if not stated explicitly by) information recorded, and personal information, such as account numbers, social security numbers, and credit card numbers. It is important that detailed error messages be visible only to those who are authorized to view them; that general users receive only generalized acknowledgment that errors have occurred; and that these generalized messages appear only when relevant to the user's task. For example, a message along the lines of, "An error has occurred. Unable to save your changes. If this problem persists, please contact your help desk" would be relevant. A message such as "Warning: your transaction generated a large number of page splits" would likely not be relevant. "ABGQ is not a valid widget code" would be appropriate; but "The INSERT statement conflicted with the FOREIGN KEY constraint "WidgetTransactionFK". The conflict occurred in database "DB7", table "dbo.WidgetMaster", column 'WidgetCode'" would not, as it reveals too much about the database structure. This calls for inspection of application source code, which will require collaboration with the application developers. It is recognized that in many cases, the database administrator (DBA) is organizationally separate from the application developers and may have limited, if any, access to source code. Nevertheless, protections of this type are so important to the secure operation of databases that they must not be ignored. At a minimum, the DBA must attempt to obtain assurances from the development organization that this issue has been addressed and must document what has been discovered.

Fix Text

Configure DBMS settings, custom database code, and associated application code not to divulge sensitive information or information useful for system identification in error messages that are displayed to general users.

Check Content

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.

SQL Server must reveal detailed error messages only to the ISSO, ISSM (or their designees), SA and DBA.

Finding ID
SQL4-00-022900
Rule ID
SV-81889r2_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000267-DB-000163
CCI
CCI-001314
Target Key
(None)
Documentable
No
Discussion

If the DBMS provides too much information in error logs and administrative messages to the screen, this could lead to compromise. The structure and content of error messages need to be carefully considered by the organization and development team. The extent to which the information system is able to identify and handle error conditions is guided by organizational policy and operational requirements. Some default DBMS error messages can contain information that could aid an attacker in, among others things, identifying the database type, host address, or state of the database. Custom errors may contain sensitive customer information. It is important that detailed error messages be visible only to those who are authorized to view them; that general users receive only generalized acknowledgment that errors have occurred; and that these generalized messages appear only when relevant to the user's task. For example, a message along the lines of, "An error has occurred. Unable to save your changes. If this problem persists, please contact your help desk" would be relevant. A message such as "Warning: your transaction generated a large number of page splits" would likely not be relevant. "ABGQ is not a valid widget code" would be appropriate; but "The INSERT statement conflicted with the FOREIGN KEY constraint "WidgetTransactionFK". The conflict occurred in database "DB7", table "dbo.WidgetMaster", column 'WidgetCode'" would not, as it reveals too much about the database structure. Administrative users authorized to review detailed error messages typically are the ISSO, ISSM, SA and DBA. Other individuals or roles may be specified according to organization-specific needs, with appropriate approval. This calls for inspection of application source code, which will require collaboration with the application developers. It is recognized that in many cases, the database administrator (DBA) is organizationally separate from the application developers and may have limited, if any, access to source code. Nevertheless, protections of this type are so important to the secure operation of databases that they must not be ignored. At a minimum, the DBA must attempt to obtain assurances from the development organization that this issue has been addressed and must document what has been discovered.

Fix Text

Configure audit logging, tracing and/or custom code in the database or application to record detailed error messages generated by SQL Server, for review by authorized personnel.

Check Content

Review application behavior, custom database code (stored procedures; triggers) and DBMS audit and trace settings, to determine whether detailed error messages are logged or stored for review by authorized personnel. If detailed error messages are not available to individuals authorized to view them, this is a finding.

When supporting applications that require security labeling of data, SQL Server must associate organization-defined types of security labels having organization-defined security label values with information in storage.

Finding ID
SQL4-00-031900
Rule ID
SV-81891r1_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000311-DB-000308
CCI
CCI-002262
Target Key
(None)
Documentable
No
Discussion

Without the association of security labels to information, there is no basis for the DBMS to make security-related access-control decisions. Security labels are abstractions representing the basic properties or characteristics of an entity (e.g., subjects and objects) with respect to safeguarding information. These labels are typically associated with internal data structures (e.g., tables, rows) within the database and are used to enable the implementation of access control and flow control policies, reflect special dissemination, handling or distribution instructions, or support other aspects of the information security policy. One example includes marking data as classified or FOUO. These security labels may be assigned manually or during data processing, but, either way, it is imperative these assignments are maintained while the data is in storage. If the security labels are lost when the data is stored, there is the risk of a data compromise. SQL Server does not include security labeling as a standard or licensable feature. However, a community-developed SQL Server Label Security Toolkit can be downloaded from the Microsoft-supported web site codeplex.com. Other implementations may also exist. Custom application code is also a viable way to implement a solution.

Fix Text

Deploy third-party software such as the SQL Server Label Security Toolkit, or add custom data structures, data elements and application code, to provide reliable security labeling of information in storage.

Check Content

If security labeling is not required, this is not a finding. If security labeling requirements have been specified, but the security labeling is not implemented or does not reliably maintain labels on information in storage, this is a finding.

When supporting applications that require security labeling of data, SQL Server must associate organization-defined types of security labels having organization-defined security label values with information in process.

Finding ID
SQL4-00-032000
Rule ID
SV-81893r1_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000313-DB-000309
CCI
CCI-002263
Target Key
(None)
Documentable
No
Discussion

Without the association of security labels to information, there is no basis for the DBMS to make security-related access-control decisions. Security labels are abstractions representing the basic properties or characteristics of an entity (e.g., subjects and objects) with respect to safeguarding information. These labels are typically associated with internal data structures (e.g., tables, rows) within the database and are used to enable the implementation of access control and flow control policies, reflect special dissemination, handling or distribution instructions, or support other aspects of the information security policy. One example includes marking data as classified or FOUO. These security labels may be assigned manually or during data processing, but, either way, it is imperative these assignments are maintained while the data is in storage. If the security labels are lost when the data is stored, there is the risk of a data compromise. SQL Server does not include security labeling as a standard or licensable feature. However, a community-developed SQL Server Label Security Toolkit can be downloaded from the Microsoft-supported web site codeplex.com. Other implementations may also exist. Custom application code is also a viable way to implement a solution.

Fix Text

Deploy third-party software such as the SQL Server Label Security Toolkit, or add custom data structures, data elements and application code, to provide reliable security labeling of information in process.

Check Content

If security labeling is not required, this is not a finding. If security labeling requirements have been specified, but the security labeling is not implemented or does not reliably maintain labels on information in process, this is a finding.

When supporting applications that require security labeling of data, SQL Server must associate organization-defined types of security labels having organization-defined security label values with information in transmission.

Finding ID
SQL4-00-032100
Rule ID
SV-81895r1_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000314-DB-000310
CCI
CCI-002264
Target Key
(None)
Documentable
No
Discussion

Without the association of security labels to information, there is no basis for the DBMS to make security-related access-control decisions. Security labels are abstractions representing the basic properties or characteristics of an entity (e.g., subjects and objects) with respect to safeguarding information. These labels are typically associated with internal data structures (e.g., tables, rows) within the database and are used to enable the implementation of access control and flow control policies, reflect special dissemination, handling or distribution instructions, or support other aspects of the information security policy. One example includes marking data as classified or FOUO. These security labels may be assigned manually or during data processing, but, either way, it is imperative these assignments are maintained while the data is in storage. If the security labels are lost when the data is stored, there is the risk of a data compromise. SQL Server does not include security labeling as a standard or licensable feature. However, a community-developed SQL Server Label Security Toolkit can be downloaded from the Microsoft-supported web site codeplex.com. Other implementations may also exist. Custom application code is also a viable way to implement a solution.

Fix Text

Deploy third-party software such as the SQL Server Label Security Toolkit, or add custom data structures, data elements and application code, to provide reliable security labeling of information in transmission.

Check Content

If security labeling is not required, this is not a finding. If security labeling requirements have been specified, but the security labeling is not implemented or does not reliably maintain labels on information in transmission, this is a finding.

Time stamps in database tables, intended for auditing or activity-tracking purposes, must include both date and time of day, with a minimum granularity of one second.

Finding ID
SQL4-00-033700
Rule ID
SV-81897r1_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000375-DB-000323
CCI
CCI-001889
Target Key
(None)
Documentable
No
Discussion

If time stamps are not consistently applied and there is no common time reference, it is difficult to perform forensic analysis, in audit files, trace files/tables, and application data tables. Time stamps generated by SQL Server must include date and time, to a granularity of one second or finer. Time is commonly expressed in Coordinated Universal Time (UTC), a modern continuation of Greenwich Mean Time (GMT), or local time with an offset from UTC. Granularity of time measurements refers to the precision available in time stamp values. Granularity coarser than one second is not sufficient for audit trail purposes, and granularity finer than one second is recommended. Time stamp values are typically presented with three or more decimal places of seconds; however, the actual granularity may be coarser than the apparent precision. For example, SQL Server's GETDATE()/CURRENT_TMESTAMP values are presented to three decimal places, but the granularity is not one millisecond: it is about 1/300 of a second. The data types that can be used for this purpose in SQL Server are: DATETIME2 - precision variable from a whole second down to a ten-millionth (subject to the actual precision of the hardware and operating system) DATETIMEOFFSET - as datetime2, together with local offset from UTC DATE, together with TIME (same precision considerations as for datetime2) DATETIME - precision 1/300 of a second Character-string data types allowing for at least 20 characters are also permissible, but not recommended. SQL Server built-in functions for retrieving current timestamps are: (high precision) sysdatetime(), sysdatetimeoffset(), sysutcdatetime(); (lower precision) CURRENT_TIMESTAMP or getdate(), getutcdate(). Ensure that values recorded for tracking purposes in data tables are correctly defined and maintained. (Design decisions about which tables require audit-trail or activity-tracking columns are outside the scope of this STIG. This requirement applies only to the data type and maintenance of such columns if they do exist.) The SMALLDATETIME data type is not precise enough for this purpose. Although it gives the impression of including a seconds component, the seconds value is always "00". SQL Server offers a data type called TIMESTAMP that is not a representation of date and time. Rather, it is a database state counter and does not correspond to calendar and clock time. This requirement does not refer to that meaning of TIMESTAMP. To avoid confusion, Microsoft recommends using the newer name for this data type, ROWVERSION, instead.

Fix Text

Modify applications and/or column/field definitions so that the time stamps in audit-trail and activity-tracking columns/fields in application data include date and time of day, to a granularity of one second or finer, and are recorded accurately.

Check Content

Review the column definitions and contents of audit-trail and activity-tracking timestamps in database tables. If these are not defined and maintained to include date and time of day, accurate to a granularity of one second or finer, this is a finding.

SQL Server must implement and/or support cryptographic mechanisms to prevent unauthorized modification of organization-defined information at rest (to include, at a minimum, PII and classified information) on organization-defined information system components.

Finding ID
SQL4-00-034700
Rule ID
SV-81899r1_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000428-DB-000386
CCI
CCI-002475
Target Key
(None)
Documentable
No
Discussion

Databases holding data requiring "data at rest" protections must employ cryptographic mechanisms to prevent unauthorized disclosure and modification of the information at rest. These cryptographic mechanisms may be native to the DBMS or implemented via additional software or operating system/file system settings, as appropriate to the situation. Selection of a cryptographic mechanism is based on the need to protect the integrity of organizational information. The strength of the mechanism is commensurate with the security category and/or classification of the information. Organizations have the flexibility to either encrypt all information on storage devices (i.e., full disk encryption) or encrypt specific data structures (e.g., files, records, or fields). The decision whether and what to encrypt rests with the data owner and is also influenced by the physical measures taken to secure the equipment and media on which the information resides.

Fix Text

Where full-disk encryption is required, configure Windows and/or the storage system to provide this. Where transparent data encryption (TDE) is required, deploy the necessary stack of certificates and keys, and set the Encryption Enabled to True. For guidance from the Microsoft Developer Network on how to do this, perform a web search for "SQL Server 2014 TDE". Where column encryption is required, deploy the necessary stack of certificates and keys, and enable encryption on the columns in question. For guidance from the Microsoft Developer Network on how to do this, perform a web search for "SQL Server 2014 Encrypt a Column of Data".

Check Content

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: In SQL Server Management Studio, Object Explorer, expand the instance and right-click on the database name; select properties. Select the Options page, State section, Encryption Enabled parameter. If the value displayed is False, this is a finding. If column encryption, done via SQL Server features, is required, review the definitions and contents of the relevant tables and columns. If any of the information defined as requiring cryptographic protection is not encrypted in a manner that provides the required level of protection, this is a finding.

When invalid inputs are received, SQL Server must behave in a predictable and documented manner that reflects organizational and system objectives.

Finding ID
SQL4-00-035200
Rule ID
SV-81901r2_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000447-DB-000393
CCI
CCI-002754
Target Key
(None)
Documentable
No
Discussion

A common vulnerability is unplanned behavior when invalid inputs are received. This requirement guards against adverse or unintended system behavior caused by invalid inputs, where information system responses to the invalid input may be disruptive or cause the system to fail into an unsafe state. The behavior will be derived from the organizational and system requirements and includes, but is not limited to, notification of the appropriate personnel, creating an audit record, and rejecting invalid input. This calls for inspection of application source code, which will require collaboration with the application developers. It is recognized that in many cases, the database administrator (DBA) is organizationally separate from the application developers and may have limited, if any, access to source code. Nevertheless, protections of this type are so important to the secure operation of databases that they must not be ignored. At a minimum, the DBA must attempt to obtain assurances from the development organization that this issue has been addressed and must document what has been discovered.

Fix Text

Revise and deploy the source code for database program objects (stored procedures, functions, triggers) and application source code, to implement the documented behavior.

Check Content

Review system documentation to determine how input errors are to be handled in general and if any special handling is defined for specific circumstances. Review the source code for database program objects (stored procedures, functions, triggers) and application source code to identify how the system responds to invalid input. If it does not implement the documented behavior, this is a finding.

Trace or Audit records must be generated when categorized information (e.g., classification levels/security levels) is accessed.

Finding ID
SQL4-00-035800
Rule ID
SV-81903r2_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000494-DB-000344
CCI
CCI-000172
Target Key
(None)
Documentable
No
Discussion

Changes in categorized information must be tracked. Without an audit trail, unauthorized access to protected data could go undetected. For detailed information on categorizing information, refer to FIPS Publication 199, Standards for Security Categorization of Federal Information and Information Systems, and FIPS Publication 200, Minimum Security Requirements for Federal Information and Information Systems. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016. Note also that Trace does not support auditing of SELECT statements, whereas Audit does. Since Trace does not provide for tracking SELECT statements, it is necessary to provide this tracking at the application level, if Trace is used for audit purposes.

Fix Text

Where SQL Server Trace is in use, implement tracking of SELECTs on categorized data at the application level, using the system stored procedure sp_trace_generateevent to write the tracking records to the Trace used for audit purposes. If SQL Server Audit is in use, design and deploy an Audit that captures all auditable events and data items. The script provided in the supplemental file Audit.sql can be used as the basis for this. Supplement the standard audit data as necessary, using Extended Events and/or triggers. Alternatively, to add the necessary data capture to an existing server audit specification, run the script: USE [master]; GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = OFF); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> ADD (SCHEMA_OBJECT_ACCESS_GROUP); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = ON); GO

Check Content

Review the system documentation to determine whether it is required to track categories of information, such as classification or sensitivity level. If it is not, this is not applicable (NA). If neither SQL Server Audit nor SQL Server Trace is in use for audit purposes, this is a finding. If SQL Server Trace is in use for audit purposes, review the application(s) using the database to verify that all SELECT actions on categorized data are being audited, and that the tracking records are written to the SQL Server Trace. If not, this is a finding. If SQL Server Audit is in use, proceed as follows. The basic SQL Server Audit configuration provided in the supplemental file Audit.sql uses the broad, server-level audit action group SCHEMA_OBJECT_ACCESS_GROUP for this purpose. SQL Server Audit's flexibility makes other techniques possible. If an alternative technique is in use and demonstrated effective, this is not a finding. Determine the name(s) of the server audit specification(s) in use. To look at audits and audit specifications, in Management Studio's object explorer, expand <server name> >> Security >> Audits and <server name> >> Security >> Server Audit Specifications. Also, <server name> >> Databases >> <database name> >> Security >> Database Audit Specifications. Alternatively, review the contents of the system views with "audit" in their names. Run the following to verify that all SELECT, INSERT, UPDATE, and DELETE actions on tables and views are being audited: USE [master]; GO SELECT * FROM sys.server_audit_specification_details WHERE server_specification_id = (SELECT server_specification_id FROM sys.server_audit_specifications WHERE [name] = '<server_audit_specification_name>') AND audit_action_name = 'SCHEMA_OBJECT_ACCESS_GROUP'; If no row is returned, this is a finding. If the audited_result column is not "SUCCESS" or "SUCCESS AND FAILURE", this is a finding.

Trace or Audit records must be generated when unsuccessful attempts to access categorized information (e.g., classification levels/security levels) occur.

Finding ID
SQL4-00-035900
Rule ID
SV-81905r2_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000494-DB-000345
CCI
CCI-000172
Target Key
(None)
Documentable
No
Discussion

Changes in categorized information must be tracked. Without an audit trail, unauthorized access to protected data could go undetected. To aid in diagnosis, it is necessary to keep track of failed attempts in addition to the successful ones. For detailed information on categorizing information, refer to FIPS Publication 199, Standards for Security Categorization of Federal Information and Information Systems, and FIPS Publication 200, Minimum Security Requirements for Federal Information and Information Systems. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016. Note also that Trace does not support auditing of SELECT statements, whereas Audit does. Since Trace does not provide for tracking SELECT statements, it is necessary to provide this tracking at the application level, if Trace is used for audit purposes. Use of SQL Server Audit's SCHEMA_OBJECT_ACCESS_GROUP causes capture of all accesses, successful and otherwise, to all schema-scoped objects. The [Succeeded] column in the audit output indicates the success or failure of the attempted action. Be aware, however, that it may report True in some cases where one would intuitively expect False. For example, SELECT 1/0 FROM SYS.ALL_OBJECTS will appear in the audit trail as successful, if the user has permission to perform that action, even though it contains an invalid expression. Some other actions that one would consider failures (such as selecting from a table that does not exist) may not appear at all.

Fix Text

Where SQL Server Trace is in use, implement tracking of SELECTs on categorized data at the application level, using the system stored procedure sp_trace_generateevent to write the tracking records to the Trace used for audit purposes. Include failed attempts in the tracking. If SQL Server Audit is in use, design and deploy an Audit that captures all auditable events and data items. The script provided in the supplemental file Audit.sql can be used as the basis for this. Supplement the standard audit data as necessary, using Extended Events and/or triggers. Alternatively, to add the necessary data capture to an existing server audit specification, run the script: USE [master]; GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = OFF); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> ADD (SCHEMA_OBJECT_ACCESS_GROUP); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = ON); GO

Check Content

Review the system documentation to determine whether it is required to track categories of information, such as classification or sensitivity level. If it is not, this is not applicable (NA). If neither SQL Server Audit nor SQL Server Trace is in use for audit purposes, this is a finding. If SQL Server Trace is in use for audit purposes, review the application(s) using the database to verify that all SELECT actions on categorized data, including unsuccessful attempts, are being audited; and that the tracking records are written to the SQL Server Trace used for audit purposes. If not, this is a finding. If SQL Server Audit is in use, proceed as follows. The basic SQL Server Audit configuration provided in the supplemental file Audit.sql uses the broad, server-level audit action group SCHEMA_OBJECT_ACCESS_GROUP for this purpose. SQL Server Audit's flexibility makes other techniques possible. If an alternative technique is in use and demonstrated effective, this is not a finding. Determine the name(s) of the server audit specification(s) in use. To look at audits and audit specifications, in Management Studio's object explorer, expand <server name> >> Security >> Audits and <server name> >> Security >> Server Audit Specifications. Also, <server name> >> Databases >> <database name> >> Security >> Database Audit Specifications. Alternatively, review the contents of the system views with "audit" in their names. Run the following to verify that all SELECT, INSERT, UPDATE, and DELETE actions on tables and views are being audited: USE [master]; GO SELECT * FROM sys.server_audit_specification_details WHERE server_specification_id = (SELECT server_specification_id FROM sys.server_audit_specifications WHERE [name] = '<server_audit_specification_name>') AND audit_action_name = 'SCHEMA_OBJECT_ACCESS_GROUP'; If no row is returned, this is a finding. If the audited_result column is not "FAILURE" or "SUCCESS AND FAILURE", this is a finding.

SQL Server must generate Trace or Audit records when privileges/permissions are modified via locally-defined security objects.

Finding ID
SQL4-00-036200
Rule ID
SV-81907r2_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000495-DB-000328
CCI
CCI-000172
Target Key
(None)
Documentable
No
Discussion

Changes in the permissions, privileges, and roles granted to users and roles must be tracked. Without an audit trail, unauthorized elevation or restriction of privileges could go undetected. Elevated privileges give users access to information and functionality that they should not have; restricted privileges wrongly deny access to authorized users. In SQL Server, there is no distinction between modification of permissions and granting or dropping them. However, native SQL Server security functionality may be supplemented with application-specific tables and logic, in which case the following actions on these tables and procedures/triggers/functions are also relevant: UPDATE EXECUTE Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016.

Fix Text

Where SQL Server Trace is in use, define and enable a trace that captures all auditable events. The script provided in the supplemental file Trace.sql can be used to do this. Add blocks of code to Trace.sql for each custom event class (integers in the range 82-91; the same event class may be used for all such triggers) used in these triggers. Create triggers to raise a custom event on each locally-defined security table that requires tracking of Insert-Update-Delete operations. The examples provided in the supplemental file CustomTraceEvents.sql can serve as the basis for these. Execute Trace.sql. Where SQL Server Audit is in use, design and deploy a SQL Server Audit that captures all auditable events. The script provided in the supplemental file Audit.sql can be used for this. Alternatively, to add the necessary data capture to an existing server audit specification, run the script: USE [master]; GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = OFF); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> ADD (SCHEMA_OBJECT_ACCESS_GROUP); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = ON); GO

Check Content

Obtain the list of locally-defined security tables, procedures and functions that require tracking. If there are none, this is not a finding. If neither SQL Server Audit nor SQL Server Trace is in use for audit purposes, this is a finding. If SQL Server Trace is in use for audit purposes, review the locally-defined security tables for the existence of triggers to raise a custom event on each Update operation. If such triggers are not present, this is a finding. Verify that all required events are being audited. From the query prompt: SELECT * FROM sys.traces; All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding. Determine the trace(s) being used for the auditing requirement. In the following, replace # with a trace ID being used for the auditing requirements. From the query prompt: SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#); The following required event IDs should be among those listed; if not, this is a finding: 42 -- SP:Starting 43 -- SP:Completed 82-91 -- User-defined Event 162 -- User error message If SQL Server Audit is in use, proceed as follows. Verify that all EXECUTE actions on locally-defined permissions-related procedures are being audited. If not, this is a finding. The basic SQL Server Audit configuration provided in the supplemental file Audit.sql uses the broad, server-level audit action group SCHEMA_OBJECT_ACCESS_GROUP for this purpose. SQL Server Audit's flexibility makes other techniques possible. If an alternative technique is in use and demonstrated effective, this is not a finding. Determine the name(s) of the server audit specification(s) in use. To look at audits and audit specifications, in Management Studio's object explorer, expand <server name> >> Security >> Audits and <server name> >> Security >> Server Audit Specifications. Also, <server name> >> Databases >> <database name> >> Security >> Database Audit Specifications. Alternatively, review the contents of the system views with "audit" in their names. Run the following to verify that all UPDATE and EXECUTE actions on any locally-defined permissions tables, procedures and functions are being audited: USE [master]; GO SELECT * FROM sys.server_audit_specification_details WHERE server_specification_id = (SELECT server_specification_id FROM sys.server_audit_specifications WHERE [name] = '<server_audit_specification_name>') AND audit_action_name = 'SCHEMA_OBJECT_ACCESS_GROUP'; If no row is returned, this is a finding. If the audited_result column is not "SUCCESS" or "SUCCESS AND FAILURE", this is a finding.

SQL Server must generate Trace or Audit records when unsuccessful attempts to modify privileges/permissions via locally-defined security objects occur.

Finding ID
SQL4-00-036300
Rule ID
SV-81909r2_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000495-DB-000329
CCI
CCI-000172
Target Key
(None)
Documentable
No
Discussion

Failed attempts to change the permissions, privileges, and roles granted to users and roles must be tracked. Without an audit trail, unauthorized attempts to elevate or restrict privileges could go undetected. In SQL Server, there is no distinction between modification of permissions and granting or dropping them. However, native SQL Server security functionality may be supplemented with application-specific tables and logic, in which case the following actions on these tables and procedures/triggers/functions are also relevant: UPDATE EXECUTE To aid in diagnosis, it is necessary to keep track of failed attempts in addition to the successful ones. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016. Use of SQL Server Audit's SCHEMA_OBJECT_ACCESS_GROUP causes capture of all accesses, successful and otherwise, to the system views (and all other schema-scoped objects). The [Succeeded] column in the audit output indicates the success or failure of the attempted action. Be aware, however, that it may report True in some cases where one would intuitively expect False. For example, SELECT 1/0 FROM SYS.ALL_OBJECTS will appear in the audit trail as successful, if the user has permission to perform that action, even though it contains an invalid expression. Some other actions that one would consider failures (such as selecting from a table that does not exist) may not appear at all.

Fix Text

Where SQL Server Trace is in use, define and enable a trace that captures all auditable events. The script provided in the supplemental file Trace.sql can be used to do this. Add blocks of code to Trace.sql for each custom event class (integers in the range 82-91; the same event class may be used for all such triggers) used in these triggers. Create triggers to raise a custom event on each locally-defined security table that requires tracking of Insert-Update-Delete operations. The examples provided in the supplemental file CustomTraceEvents.sql can serve as the basis for these. Execute Trace.sql. Where SQL Server Audit is in use, design and deploy a SQL Server Audit that captures all auditable events. The script provided in the supplemental file Audit.sql can be used for this. Alternatively, to add the necessary data capture to an existing server audit specification, run the script: USE [master]; GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = OFF); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> ADD (SCHEMA_OBJECT_ACCESS_GROUP); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = ON); GO

Check Content

Obtain the list of locally-defined security tables, procedures and functions that require tracking. If there are none, this is not a finding. If neither SQL Server Audit nor SQL Server Trace is in use for audit purposes, this is a finding. If SQL Server Trace is in use for audit purposes, review the locally-defined security tables for the existence of triggers to raise a custom event on each Update operation. If such triggers are not present, this is a finding. Verify that all required events are being audited. From the query prompt: SELECT * FROM sys.traces; All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding. Determine the trace(s) being used for the auditing requirement. In the following, replace # with a trace ID being used for the auditing requirements. From the query prompt: SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#); The following required event IDs should be among those listed; if not, this is a finding: 42 -- SP:Starting 43 -- SP:Completed 82-91 -- User-defined Event 162 -- User error message If SQL Server Audit is in use, proceed as follows. Verify that all EXECUTE actions on locally-defined permissions-related procedures are being audited. If not, this is a finding. The basic SQL Server Audit configuration provided in the supplemental file Audit.sql uses the broad, server-level audit action group SCHEMA_OBJECT_ACCESS_GROUP for this purpose. SQL Server Audit's flexibility makes other techniques possible. If an alternative technique is in use and demonstrated effective, this is not a finding. Determine the name(s) of the server audit specification(s) in use. To look at audits and audit specifications, in Management Studio's object explorer, expand <server name> >> Security >> Audits and <server name> >> Security >> Server Audit Specifications. Also, <server name> >> Databases >> <database name> >> Security >> Database Audit Specifications. Alternatively, review the contents of the system views with "audit" in their names. Run the following to verify that all UPDATE and EXECUTE actions on any locally-defined permissions tables, procedures and functions are being audited: USE [master]; GO SELECT * FROM sys.server_audit_specification_details WHERE server_specification_id = (SELECT server_specification_id FROM sys.server_audit_specifications WHERE [name] = '<server_audit_specification_name>') AND audit_action_name = 'SCHEMA_OBJECT_ACCESS_GROUP'; If no row is returned, this is a finding. If the audited_result column is not "FAILURE" or "SUCCESS AND FAILURE", this is a finding.

SQL Server must generate Trace or Audit records when locally-defined security objects are modified.

Finding ID
SQL4-00-036400
Rule ID
SV-81911r2_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000496-DB-000334
CCI
CCI-000172
Target Key
(None)
Documentable
No
Discussion

SQL Server protects its built-in security objects (tables, views, functions, procedures, etc.) from alteration by database users and administrators. However, applications sometimes have additional, security-related objects defined in the database. ALTER operations on these objects must be monitored. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016.

Fix Text

Where SQL Server Trace is in use, define and enable a trace that captures all auditable events. The script provided in the supplemental file Trace.sql can be used to do this. Where SQL Server Audit is in use, design and deploy a SQL Server Audit that captures all auditable events. The script provided in the supplemental file Audit.sql can be used for this. Alternatively, to add the necessary data capture to an existing server audit specification, run the script: USE [master]; GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = OFF); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> ADD (SCHEMA_OBJECT_CHANGE_GROUP); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = ON); GO

Check Content

If there are no locally-defined security tables or procedures, this is not a finding. If neither SQL Server Audit nor SQL Server Trace is in use for audit purposes, this is a finding. If SQL Server Trace is in use for audit purposes, verify that all required events are being audited. From the query prompt: SELECT * FROM sys.traces; All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding. Determine the trace(s) being used for the auditing requirement. In the following, replace # with a trace ID being used for the auditing requirements. From the query prompt: SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#); The following required event IDs should all be among those listed; if not, this is a finding: 46 -- Object:Created 47 -- Object:Deleted 162 -- User error message 164 -- Object:Altered If SQL Server Audit is in use, proceed as follows. The basic SQL Server Audit configuration provided in the supplemental file Audit.sql uses the broad, server-level audit action group SCHEMA_OBJECT_CHANGE_GROUP for this purpose. SQL Server Audit's flexibility makes other techniques possible. If an alternative technique is in use and demonstrated effective, this is not a finding. Determine the name(s) of the server audit specification(s) in use. To look at audits and audit specifications, in Management Studio's object explorer, expand <server name> >> Security >> Audits and <server name> >> Security >> Server Audit Specifications. Also, <server name> >> Databases >> <database name> >> Security >> Database Audit Specifications. Alternatively, review the contents of the system views with "audit" in their names. Run the following to verify that all CREATE, ALTER, and DROP actions on any locally-defined permissions tables, procedures and functions are being audited: USE [master]; GO SELECT * FROM sys.server_audit_specification_details WHERE server_specification_id = (SELECT server_specification_id FROM sys.server_audit_specifications WHERE [name] = '<server_audit_specification_name>') AND audit_action_name = 'SCHEMA_OBJECT_CHANGE_GROUP'; If no row is returned, this is a finding. If the audited_result column is not "SUCCESS" or "SUCCESS AND FAILURE", this is a finding.

SQL Server must generate Trace or Audit records when unsuccessful accesses to designated objects occur.

Finding ID
SQL4-00-038200
Rule ID
SV-81913r2_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000507-DB-000357
CCI
CCI-000172
Target Key
(None)
Documentable
No
Discussion

Without tracking all or selected types of access to all or selected objects (tables, views, procedures, functions, etc.), it would be difficult to establish, correlate, and investigate the events relating to an incident or identify those responsible for one. Types of access include, but are not necessarily limited to: SELECT INSERT UPDATE DELETE EXECUTE To aid in diagnosis, it is necessary to keep track of failed attempts in addition to the successful ones. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016.

Fix Text

Where SQL Server Trace is in use, define and enable a trace that captures all auditable events. The script provided in the supplemental file Trace.sql can be used to do this. If SQL Server Audit is in use, design and deploy an Audit that captures all auditable events and data items. The script provided in the supplemental file Audit.sql can be used as the basis for this. Supplement the standard audit data as necessary, using Extended Events and/or triggers. Alternatively, to add the necessary data capture to an existing server audit specification, run the script: USE [master]; GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = OFF); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> ADD (SCHEMA_OBJECT_ACCESS_GROUP); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = ON); GO

Check Content

If neither SQL Server Audit nor SQL Server Trace is in use for audit purposes, this is a finding. Obtain the list of objects (tables and stored procedures) where tracking of SELECT, INSERT, UPDATE, DELETE, or EXECUTE actions is required. If there are none, this is not a finding. If SQL Server Trace is in use for audit purposes, verify that all required event classes are being audited. From the query prompt: SELECT * FROM sys.traces; All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding. Determine the trace(s) being used for the auditing requirement. In the following, replace # with a trace ID being used for the auditing requirements. From the query prompt: SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#); The following required event ID should be among those listed; if not, this is a finding: 162 -- User error message If SQL Server Audit is in use, proceed as follows. The basic SQL Server Audit configuration provided in the supplemental file Audit.sql uses the server-level audit action group SUCCESSFUL_LOGIN_GROUP for this purpose. SQL Server Audit's flexibility makes other techniques possible. If an alternative technique is in use and demonstrated effective, this is not a finding. Determine the name(s) of the server audit specification(s) in use. To look at audits and audit specifications, in Management Studio's object explorer, expand <server name> >> Security >> Audits and <server name> >> Security >> Server Audit Specifications. Also, <server name> >> Databases >> <database name> >> Security >> Database Audit Specifications. Alternatively, review the contents of the system views with "audit" in their names. Run the following to verify that all logons and connections are being audited: USE [master]; GO SELECT * FROM sys.server_audit_specification_details WHERE server_specification_id = (SELECT server_specification_id FROM sys.server_audit_specifications WHERE [name] = '<server_audit_specification_name>') AND audit_action_name = 'SUCCESSFUL_LOGIN_GROUP'; GO If no row is returned, this is a finding. If the audited_result column is not "FAILURE" or "SUCCESS AND FAILURE", this is a finding.

SQL Server must generate Trace or Audit records when successful accesses to designated objects occur.

Finding ID
SQL4-00-038100
Rule ID
SV-81915r2_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000507-DB-000356
CCI
CCI-000172
Target Key
(None)
Documentable
No
Discussion

Without tracking all or selected types of access to all or selected objects (tables, views, procedures, functions, etc.), it would be difficult to establish, correlate, and investigate the events relating to an incident, or identify those responsible for one. Types of access include, but are not necessarily limited to: SELECT INSERT UPDATE DELETE EXECUTE Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016. Trace does not offer tracking of SELECT operations, so where this is required it must be implemented at the application level.

Fix Text

Where SQL Server Trace is in use, implement tracking of SELECTs on designated tables at the application level, using the system stored procedure sp_trace_generateevent to write the tracking records to the Trace used for audit purposes. Create triggers to raise a custom event on each table that requires tracking of Insert-Update-Delete operations. The examples provided in the supplemental file CustomTraceEvents.sql can serve as the basis for these. Add a block of code to the supplemental file Trace.sql for each custom event class (integers in the range 82-91; the same event class may be used for all such triggers) used in these triggers. Ensure that Trace.sql includes blocks of code for event classes 42, 43, and 162. Execute Trace.sql. If SQL Server Audit is in use, design and deploy an Audit that captures all auditable events and data items. The script provided in the supplemental file Audit.sql can be used as the basis for this. Supplement the standard audit data as necessary, using Extended Events and/or triggers. Alternatively, to add the necessary data capture to an existing server audit specification, run the script: USE [master]; GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = OFF); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> ADD (SCHEMA_OBJECT_ACCESS_GROUP); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = ON); GO

Check Content

If neither SQL Server Audit nor SQL Server Trace is in use for audit purposes, this is a finding. Obtain the list of objects (tables and stored procedures) where tracking of SELECT, INSERT, UPDATE, DELETE, or EXECUTE actions is required. If there are none, this is not a finding. If SQL Server Trace is in use for audit purposes, review the application(s) using the database to verify that all SELECT actions on categorized data are being audited, and that the tracking records are written to the SQL Server Trace used for audit purposes. If not, this is a finding. Review the designated tables for the existence of triggers to raise a custom event on each Insert-Update-Delete operation. If such triggers are not present, this is a finding. Check to see that all required event classes are being audited. From the query prompt: SELECT * FROM sys.traces; All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding. Determine the trace(s) being used for the auditing requirement. In the following, replace # with a trace ID being used for the auditing requirements. From the query prompt: SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#); The following required event IDs should be among those listed; if not, this is a finding: 42 -- SP:Starting 43 -- SP:Completed 82-91 -- User-defined Event (at least one of these; 90 is used in the supplied script) 162 -- User error message If SQL Server Audit is in use, verify that execution of all SELECT, INSERT, UPDATE, DELETE, or EXECUTE actions on the designated objects, is audited,. If any such actions are not audited, this is a finding. If SQL Server Audit is in use, proceed as follows. The basic SQL Server Audit configuration provided in the supplemental file Audit.sql uses the server-level audit action group SUCCESSFUL_LOGIN_GROUP for this purpose. SQL Server Audit's flexibility makes other techniques possible. If an alternative technique is in use and demonstrated effective, this is not a finding. Determine the name(s) of the server audit specification(s) in use. To look at audits and audit specifications, in Management Studio's object explorer, expand <server name> >> Security >> Audits and <server name> >> Security >> Server Audit Specifications. Also, <server name> >> Databases >> <database name> >> Security >> Database Audit Specifications. Alternatively, review the contents of the system views with "audit" in their names. Run the following to verify that all logons and connections are being audited: USE [master]; GO SELECT * FROM sys.server_audit_specification_details WHERE server_specification_id = (SELECT server_specification_id FROM sys.server_audit_specifications WHERE [name] = '<server_audit_specification_name>') AND audit_action_name = 'SUCCESSFUL_LOGIN_GROUP'; GO If no row is returned, this is a finding. If the audited_result column is not "SUCCESS" or "SUCCESS AND FAILURE", this is a finding.

Trace or Audit records must be generated when unsuccessful attempts to delete categorized information (e.g., classification levels/security levels) occur.

Finding ID
SQL4-00-037400
Rule ID
SV-81917r2_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000502-DB-000349
CCI
CCI-000172
Target Key
(None)
Documentable
No
Discussion

Changes in categorized information must be tracked. Without an audit trail, unauthorized access to protected data could go undetected. To aid in diagnosis, it is necessary to keep track of failed attempts in addition to the successful ones. For detailed information on categorizing information, refer to FIPS Publication 199, Standards for Security Categorization of Federal Information and Information Systems, and FIPS Publication 200, Minimum Security Requirements for Federal Information and Information Systems. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016. Since Trace does not provide for tracking SELECT statements, it is necessary to provide that part of the tracking at the application level. Because of this, it may also be appropriate to audit DELETE actions at the application level. However, to capture all DELETEs, whether they come from the application or bypass it, the Trace must be configured to cover them. Use of SQL Server Audit's SCHEMA_OBJECT_ACCESS_GROUP causes capture of all accesses, successful and otherwise, to all schema-scoped objects. The [Succeeded] column in the audit output indicates the success or failure of the attempted action. Be aware, however, that it may report True in some cases where one would intuitively expect False. For example, SELECT 1/0 FROM SYS.ALL_OBJECTS will appear in the audit trail as successful, if the user has permission to perform that action, even though it contains an invalid expression. Some other actions that one would consider failures (such as selecting from a table that does not exist) may not appear at all.

Fix Text

Where SQL Server Trace is in use, create triggers to raise a custom event for DELETEs on each table holding categorized information. The examples provided in the supplemental file CustomTraceEvents.sql can serve as the basis for these. Add a block of code to the supplemental file Trace.sql for each custom event class (integers in the range 82-91; the same event class may be used for all such triggers) used in these triggers. Execute Trace.sql. If SQL Server Audit is in use, design and deploy an Audit that captures all auditable events and data items. The script provided in the supplemental file Audit.sql can be used as the basis for this. Supplement the standard audit data as necessary, using Extended Events and/or triggers. Alternatively, to add the necessary data capture to an existing server audit specification, run the script: USE [master]; GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = OFF); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> ADD (SCHEMA_OBJECT_ACCESS_GROUP); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = ON); GO

Check Content

Review the system documentation to determine whether it is required to track categories of information, such as classification or sensitivity level. If it is not, this is not applicable (NA). If neither SQL Server Audit nor SQL Server Trace is in use for audit purposes, this is a finding. If SQL Server Trace is in use for audit purposes, review the Trace settings, and the triggers on the tables holding categorized information, to determine whether all DELETE actions on these tables are traced, including failed attempts. If not, this is a finding. Check to see that all required event classes are being audited. From the query prompt: SELECT * FROM sys.traces; All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding. Determine the trace(s) being used for the auditing requirement. In the following, replace # with a trace ID being used for the auditing requirements. From the query prompt: SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#); The following required event IDs should be among those listed; if not, this is a finding: 82-91 -- User-defined Event (at least one of these, matching the triggers; 90 is used in the supplied script) 162 -- User error message If SQL Server Audit is in use, proceed as follows. The basic SQL Server Audit configuration provided in the supplemental file Audit.sql uses the broad, server-level audit action group SCHEMA_OBJECT_ACCESS_GROUP for this purpose. SQL Server Audit's flexibility makes other techniques possible. If an alternative technique is in use and demonstrated effective, this is not a finding. Determine the name(s) of the server audit specification(s) in use. To look at audits and audit specifications, in Management Studio's object explorer, expand <server name> >> Security >> Audits and <server name> >> Security >> Server Audit Specifications. Also, <server name> >> Databases >> <database name> >> Security >> Database Audit Specifications. Alternatively, review the contents of the system views with "audit" in their names. Run the following to verify that all SELECT, INSERT, UPDATE, and DELETE actions on tables and views are being audited: USE [master]; GO SELECT * FROM sys.server_audit_specification_details WHERE server_specification_id = (SELECT server_specification_id FROM sys.server_audit_specifications WHERE [name] = '<server_audit_specification_name>') AND audit_action_name = 'SCHEMA_OBJECT_ACCESS_GROUP'; If no row is returned, this is a finding. If the audited_result column is not "FAILURE" or "SUCCESS AND FAILURE", this is a finding.

Trace or Audit records must be generated when categorized information (e.g., classification levels/security levels) is deleted.

Finding ID
SQL4-00-037300
Rule ID
SV-81919r2_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000502-DB-000348
CCI
CCI-000172
Target Key
(None)
Documentable
No
Discussion

Changes in categorized information must be tracked. Without an audit trail, unauthorized access to protected data could go undetected. For detailed information on categorizing information, refer to FIPS Publication 199, Standards for Security Categorization of Federal Information and Information Systems, and FIPS Publication 200, Minimum Security Requirements for Federal Information and Information Systems. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016. Since Trace does not provide for tracking SELECT statements, it is necessary to provide that part of the tracking at the application level. Because of this, it may also be appropriate to audit DELETE actions at the application level. However, to capture all DELETEs, whether they come from the application or bypass it, the Trace must be configured to cover them.

Fix Text

Where SQL Server Trace is in use, create triggers to raise a custom event for DELETEs on each table holding categorized information. The examples provided in the supplemental file CustomTraceEvents.sql can serve as the basis for these. Add a block of code to the supplemental file Trace.sql for each custom event class (integers in the range 82-91; the same event class may be used for all such triggers) used in these triggers. Execute Trace.sql. If SQL Server Audit is in use, design and deploy an Audit that captures all auditable events and data items. The script provided in the supplemental file Audit.sql can be used as the basis for this. Supplement the standard audit data as necessary, using Extended Events and/or triggers. Alternatively, to add the necessary data capture to an existing server audit specification, run the script: USE [master]; GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = OFF); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> ADD (SCHEMA_OBJECT_ACCESS_GROUP); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = ON); GO

Check Content

Review the system documentation to determine whether it is required to track categories of information, such as classification or sensitivity level. If it is not, this is not applicable (NA). If neither SQL Server Audit nor SQL Server Trace is in use for audit purposes, this is a finding. If SQL Server Trace is in use for audit purposes, review the triggers on all tables holding categorized information, to determine whether trace events are generated for all DELETE actions on these tables. If not, this is a finding. Check to see that all required event classes are being audited. From the query prompt: SELECT * FROM sys.traces; All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding. Determine the trace(s) being used for the auditing requirement. In the following, replace # with a trace ID being used for the auditing requirements. From the query prompt: SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#); The following required event IDs should be among those listed; if not, this is a finding: 82-91 -- User-defined Event (at least one of these, matching the triggers; 90 is used in the supplied script) 162 -- User error message If SQL Server Audit is in use, proceed as follows. The basic SQL Server Audit configuration provided in the supplemental file Audit.sql uses the broad, server-level audit action group SCHEMA_OBJECT_ACCESS_GROUP for this purpose. SQL Server Audit's flexibility makes other techniques possible. If an alternative technique is in use and demonstrated effective, this is not a finding. Determine the name(s) of the server audit specification(s) in use. To look at audits and audit specifications, in Management Studio's object explorer, expand <server name> >> Security >> Audits and <server name> >> Security >> Server Audit Specifications. Also, <server name> >> Databases >> <database name> >> Security >> Database Audit Specifications. Alternatively, review the contents of the system views with "audit" in their names. Run the following to verify that all SELECT, INSERT, UPDATE, and DELETE actions on tables and views are being audited: USE [master]; GO SELECT * FROM sys.server_audit_specification_details WHERE server_specification_id = (SELECT server_specification_id FROM sys.server_audit_specifications WHERE [name] = '<server_audit_specification_name>') AND audit_action_name = 'SCHEMA_OBJECT_ACCESS_GROUP'; If no row is returned, this is a finding. If the audited_result column is not "SUCCESS" or "SUCCESS AND FAILURE", this is a finding.

SQL Server must generate Trace or Audit records when unsuccessful attempts to drop locally-defined security objects occur.

Finding ID
SQL4-00-037200
Rule ID
SV-81921r2_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000501-DB-000337
CCI
CCI-000172
Target Key
(None)
Documentable
No
Discussion

SQL Server protects its built-in security objects (tables, views, functions, procedures, etc.) from alteration by database users and administrators. However, applications sometimes have additional, security-related objects defined in the database. DROP operations on these objects must be monitored. To aid in diagnosis, it is necessary to keep track of failed attempts in addition to the successful ones. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016. Use of SQL Server Audit's SCHEMA_OBJECT_CHANGE_GROUP causes capture of all attempts, successful and otherwise, to CREATE, ALTER, or DROP any schema-scoped objects. The [Succeeded] column in the audit output indicates the success or failure of the attempted action. Be aware, however, that it may report True in some cases where one would intuitively expect False.

Fix Text

Where SQL Server Trace is in use, define and enable a trace that captures all auditable events. The script provided in the supplemental file Trace.sql can be used to do this. Add blocks of code to Trace.sql for each custom event class (integers in the range 82-91; the same event class may be used for all such triggers) used in these triggers. Create triggers to raise a custom event on each locally-defined security table that requires tracking of Insert-Update-Delete operations. The examples provided in the supplemental file CustomTraceEvents.sql can serve as the basis for these. Execute Trace.sql. Where SQL Server Audit is in use, design and deploy a SQL Server Audit that captures all auditable events. The script provided in the supplemental file Audit.sql can be used for this. Alternatively, to add the necessary data capture to an existing server audit specification, run the script: USE [master]; GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = OFF); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> ADD (SCHEMA_OBJECT_ACCESS_GROUP); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = ON); GO

Check Content

If neither SQL Server Audit nor SQL Server Trace is in use for audit purposes, this is a finding. If there are no locally-defined security tables or procedures, this is not a finding. If SQL Server Trace is in use for audit purposes, verify that all required events are being audited. From the query prompt: SELECT * FROM sys.traces; All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding. Determine the trace(s) being used for the auditing requirement. In the following, replace # with a trace ID being used for the auditing requirements. From the query prompt: SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#); The following required event IDs should all be among those listed; if not, this is a finding: 46 -- Object:Created 47 -- Object:Deleted 162 -- User error message 164 -- Object:Altered If SQL Server Audit is in use, proceed as follows. The basic SQL Server Audit configuration provided in the supplemental file Audit.sql uses the broad, server-level audit action group SCHEMA_OBJECT_CHANGE_GROUP for this purpose. SQL Server Audit's flexibility makes other techniques possible. If an alternative technique is in use and demonstrated effective, this is not a finding. Determine the name(s) of the server audit specification(s) in use. To look at audits and audit specifications, in Management Studio's object explorer, expand <server name> >> Security >> Audits and <server name> >> Security >> Server Audit Specifications. Also, <server name> >> Databases >> <database name> >> Security >> Database Audit Specifications. Alternatively, review the contents of the system views with "audit" in their names. Run the following to verify that all CREATE, ALTER, and DROP actions on any locally-defined permissions tables, procedures and functions are being audited: USE [master]; GO SELECT * FROM sys.server_audit_specification_details WHERE server_specification_id = (SELECT server_specification_id FROM sys.server_audit_specifications WHERE [name] = '<server_audit_specification_name>') AND audit_action_name = 'SCHEMA_OBJECT_CHANGE_GROUP'; If no row is returned, this is a finding. If the audited_result column is not "FAILURE" or "SUCCESS AND FAILURE", this is a finding.

SQL Server must generate Trace or Audit records when locally-defined security objects are dropped.

Finding ID
SQL4-00-037100
Rule ID
SV-81923r2_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000501-DB-000336
CCI
CCI-000172
Target Key
(None)
Documentable
No
Discussion

SQL Server protects its built-in security objects (tables, views, functions, procedures, etc.) from alteration by database users and administrators. However, applications sometimes have additional, security-related objects defined in the database. DROP operations on these objects must be monitored. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016.

Fix Text

Where SQL Server Trace is in use, define and enable a trace that captures all auditable events. The script provided in the supplemental file Trace.sql can be used to do this. Add blocks of code to Trace.sql for each custom event class (integers in the range 82-91; the same event class may be used for all such triggers) used in these triggers. Create triggers to raise a custom event on each locally-defined security table that requires tracking of Insert-Update-Delete operations. The examples provided in the supplemental file CustomTraceEvents.sql can serve as the basis for these. Execute Trace.sql. Where SQL Server Audit is in use, design and deploy a SQL Server Audit that captures all auditable events. The script provided in the supplemental file Audit.sql can be used for this. Alternatively, to add the necessary data capture to an existing server audit specification, run the script: USE [master]; GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = OFF); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> ADD (SCHEMA_OBJECT_ACCESS_GROUP); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = ON); GO

Check Content

If neither SQL Server Audit nor SQL Server Trace is in use for audit purposes, this is a finding. If there are no locally-defined security tables or procedures, this is not a finding. If SQL Server Trace is in use for audit purposes, verify that all required events are being audited. From the query prompt: SELECT * FROM sys.traces; All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding. Determine the trace(s) being used for the auditing requirement. In the following, replace # with a trace ID being used for the auditing requirements. From the query prompt: SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#); The following required event IDs should all be among those listed; if not, this is a finding: 46 -- Object:Created 47 -- Object:Deleted 162 -- User error message 164 -- Object:Altered If SQL Server Audit is in use, proceed as follows. The basic SQL Server Audit configuration provided in the supplemental file Audit.sql uses the broad, server-level audit action group SCHEMA_OBJECT_CHANGE_GROUP for this purpose. SQL Server Audit's flexibility makes other techniques possible. If an alternative technique is in use and demonstrated effective, this is not a finding. Determine the name(s) of the server audit specification(s) in use. To look at audits and audit specifications, in Management Studio's object explorer, expand <server name> >> Security >> Audits and <server name> >> Security >> Server Audit Specifications. Also, <server name> >> Databases >> <database name> >> Security >> Database Audit Specifications. Alternatively, review the contents of the system views with "audit" in their names. Run the following to verify that all CREATE, ALTER, and DROP actions on any locally-defined permissions tables, procedures and functions are being audited: USE [master]; GO SELECT * FROM sys.server_audit_specification_details WHERE server_specification_id = (SELECT server_specification_id FROM sys.server_audit_specifications WHERE [name] = '<server_audit_specification_name>') AND audit_action_name = 'SCHEMA_OBJECT_CHANGE_GROUP'; If no row is returned, this is a finding. If the audited_result column is not "SUCCESS" or "SUCCESS AND FAILURE", this is a finding.

SQL Server must generate Trace or Audit records when unsuccessful attempts to modify locally-defined security objects occur.

Finding ID
SQL4-00-036500
Rule ID
SV-81925r2_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000496-DB-000335
CCI
CCI-000172
Target Key
(None)
Documentable
No
Discussion

SQL Server protects its built-in security objects (tables, views, functions, procedures, etc.) from alteration by database users and administrators. However, applications sometimes have additional, security-related objects defined in the database. ALTER operations on these objects must be monitored. To aid in diagnosis, it is necessary to keep track of failed attempts in addition to the successful ones. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016. Use of SQL Server Audit's SCHEMA_OBJECT_CHANGE_GROUP causes capture of all attempts, successful and otherwise, to CREATE, ALTER, or DROP any schema-scoped objects. The [Succeeded] column in the audit output indicates the success or failure of the attempted action. Be aware, however, that it may report True in some cases where one would intuitively expect False.

Fix Text

Where SQL Server Trace is in use, define and enable a trace that captures all auditable events. The script provided in the supplemental file Trace.sql can be used to do this. Where SQL Server Audit is in use, design and deploy a SQL Server Audit that captures all auditable events. The script provided in the supplemental file Audit.sql can be used for this. Alternatively, to add the necessary data capture to an existing server audit specification, run the script: USE [master]; GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = OFF); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> ADD (SCHEMA_OBJECT_CHANGE_GROUP); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = ON); GO

Check Content

If there are no locally-defined security tables or procedures, this is not a finding. If neither SQL Server Audit nor SQL Server Trace is in use for audit purposes, this is a finding. If SQL Server Trace is in use for audit purposes, verify that all required events are being audited. From the query prompt: SELECT * FROM sys.traces; All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding. Determine the trace(s) being used for the auditing requirement. In the following, replace # with a trace ID being used for the auditing requirements. From the query prompt: SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#); The following required event IDs should all be among those listed; if not, this is a finding: 46 -- Object:Created 47 -- Object:Deleted 162 -- User error message 164 -- Object:Altered If SQL Server Audit is in use, proceed as follows. The basic SQL Server Audit configuration provided in the supplemental file Audit.sql uses the broad, server-level audit action group SCHEMA_OBJECT_CHANGE_GROUP for this purpose. SQL Server Audit's flexibility makes other techniques possible. If an alternative technique is in use and demonstrated effective, this is not a finding. Determine the name(s) of the server audit specification(s) in use. To look at audits and audit specifications, in Management Studio's object explorer, expand <server name> >> Security >> Audits and <server name> >> Security >> Server Audit Specifications. Also, <server name> >> Databases >> <database name> >> Security >> Database Audit Specifications. Alternatively, review the contents of the system views with "audit" in their names. Run the following to verify that all CREATE, ALTER, and DROP actions on any locally-defined permissions tables, procedures and functions are being audited: USE [master]; GO SELECT * FROM sys.server_audit_specification_details WHERE server_specification_id = (SELECT server_specification_id FROM sys.server_audit_specifications WHERE [name] = '<server_audit_specification_name>') AND audit_action_name = 'SCHEMA_OBJECT_CHANGE_GROUP'; If no row is returned, this is a finding. If the audited_result column is not "FAILURE" or "SUCCESS AND FAILURE", this is a finding.

Trace or Audit records must be generated when categorized information (e.g., classification levels/security levels) is created.

Finding ID
SQL4-00-036600
Rule ID
SV-81927r2_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000498-DB-000346
CCI
CCI-000172
Target Key
(None)
Documentable
No
Discussion

Changes in categorized information must be tracked. Without an audit trail, unauthorized access to protected data could go undetected. For detailed information on categorizing information, refer to FIPS Publication 199, Standards for Security Categorization of Federal Information and Information Systems, and FIPS Publication 200, Minimum Security Requirements for Federal Information and Information Systems. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016. Since Trace does not provide for tracking SELECT statements, it is necessary to provide that part of the tracking at the application level. Because of this, it may also be appropriate to audit INSERT actions at the application level. However, to capture all INSERTs, whether they come from the application or bypass it, the Trace must be configured to cover them.

Fix Text

Where SQL Server Trace is in use, create triggers to raise a custom event for INSERTs on each table holding categorized information. The examples provided in the supplemental file CustomTraceEvents.sql can serve as the basis for these. Add a block of code to the supplemental file Trace.sql for each custom event class (integers in the range 82-91; the same event class may be used for all such triggers) used in these triggers. Execute Trace.sql. If SQL Server Audit is in use, design and deploy an Audit that captures all auditable events and data items. The script provided in the supplemental file Audit.sql can be used as the basis for this. Supplement the standard audit data as necessary, using Extended Events and/or triggers. Alternatively, to add the necessary data capture to an existing server audit specification, run the script: USE [master]; GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = OFF); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> ADD (SCHEMA_OBJECT_ACCESS_GROUP); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = ON); GO

Check Content

Review the system documentation to determine whether it is required to track categories of information, such as classification or sensitivity level. If it is not, this is not applicable (NA). If neither SQL Server Audit nor SQL Server Trace is in use for audit purposes, this is a finding. If SQL Server Trace is in use for audit purposes, review the Trace settings, and the triggers on the tables holding categorized information, to determine whether all INSERT actions on these tables are traced, including failed attempts. If not, this is a finding. Check to see that all required event classes are being audited. From the query prompt: SELECT * FROM sys.traces; All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding. Determine the trace(s) being used for the auditing requirement. In the following, replace # with a trace ID being used for the auditing requirements. From the query prompt: SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#); The following required event IDs should be among those listed; if not, this is a finding: 82-91 -- User-defined Event (at least one of these, matching the triggers; 90 is used in the supplied script) 162 -- User error message If SQL Server Audit is in use, proceed as follows. The basic SQL Server Audit configuration provided in the supplemental file Audit.sql uses the broad, server-level audit action group SCHEMA_OBJECT_ACCESS_GROUP for this purpose. SQL Server Audit's flexibility makes other techniques possible. If an alternative technique is in use and demonstrated effective, this is not a finding. Determine the name(s) of the server audit specification(s) in use. To look at audits and audit specifications, in Management Studio's object explorer, expand <server name> >> Security >> Audits and <server name> >> Security >> Server Audit Specifications. Also, <server name> >> Databases >> <database name> >> Security >> Database Audit Specifications. Alternatively, review the contents of the system views with "audit" in their names. Run the following to verify that all SELECT, INSERT, UPDATE, and DELETE actions on tables and views are being audited: USE [master]; GO SELECT * FROM sys.server_audit_specification_details WHERE server_specification_id = (SELECT server_specification_id FROM sys.server_audit_specifications WHERE [name] = '<server_audit_specification_name>') AND audit_action_name = 'SCHEMA_OBJECT_ACCESS_GROUP'; If no row is returned, this is a finding. If the audited_result column is not "SUCCESS" or "SUCCESS AND FAILURE", this is a finding.

Trace or Audit records must be generated when unsuccessful attempts to create categorized information (e.g., classification levels/security levels) occur.

Finding ID
SQL4-00-036800
Rule ID
SV-81929r2_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000498-DB-000347
CCI
CCI-000172
Target Key
(None)
Documentable
No
Discussion

Changes in categorized information must be tracked. Without an audit trail, unauthorized access to protected data could go undetected. To aid in diagnosis, it is necessary to keep track of failed attempts in addition to the successful ones. For detailed information on categorizing information, refer to FIPS Publication 199, Standards for Security Categorization of Federal Information and Information Systems, and FIPS Publication 200, Minimum Security Requirements for Federal Information and Information Systems. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016. Since Trace does not provide for tracking SELECT statements, it is necessary to provide that part of the tracking at the application level. Because of this, it may also be appropriate to audit INSERT actions at the application level. However, to capture all INSERTs, whether they come from the application or bypass it, the Trace must be configured to cover them. Use of SQL Server Audit's SCHEMA_OBJECT_ACCESS_GROUP causes capture of all accesses, successful and otherwise, to all schema-scoped objects. The [Succeeded] column in the audit output indicates the success or failure of the attempted action. Be aware, however, that it may report True in some cases where one would intuitively expect False; and some other actions that one would consider failures (such as selecting from a table that does not exist) may not appear at all.

Fix Text

Where SQL Server Trace is in use, create triggers to raise a custom event for INSERTs on each table holding categorized information. The examples provided in the supplemental file CustomTraceEvents.sql can serve as the basis for these. Add a block of code to the supplemental file Trace.sql for each custom event class (integers in the range 82-91; the same event class may be used for all such triggers) used in these triggers. Execute Trace.sql. If SQL Server Audit is in use, design and deploy an Audit that captures all auditable events and data items. The script provided in the supplemental file Audit.sql can be used as the basis for this. Supplement the standard audit data as necessary, using Extended Events and/or triggers. Alternatively, to add the necessary data capture to an existing server audit specification, run the script: USE [master]; GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = OFF); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> ADD (SCHEMA_OBJECT_ACCESS_GROUP); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = ON); GO

Check Content

Review the system documentation to determine whether it is required to track categories of information, such as classification or sensitivity level. If it is not, this is not applicable (NA). If neither SQL Server Audit nor SQL Server Trace is in use for audit purposes, this is a finding. If SQL Server Trace is in use for audit purposes, review the Trace settings, and the triggers on the tables holding categorized information, to determine whether all INSERT actions on these tables are traced, including failed attempts. If not, this is a finding. Check to see that all required event classes are being audited. From the query prompt: SELECT * FROM sys.traces; All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding. Determine the trace(s) being used for the auditing requirement. In the following, replace # with a trace ID being used for the auditing requirements. From the query prompt: SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#); The following required event IDs should be among those listed; if not, this is a finding: 82-91 -- User-defined Event (at least one of these, matching the triggers; 90 is used in the supplied script) 162 -- User error message If SQL Server Audit is in use, proceed as follows. The basic SQL Server Audit configuration provided in the supplemental file Audit.sql uses the broad, server-level audit action group SCHEMA_OBJECT_ACCESS_GROUP for this purpose. SQL Server Audit's flexibility makes other techniques possible. If an alternative technique is in use and demonstrated effective, this is not a finding. Determine the name(s) of the server audit specification(s) in use. To look at audits and audit specifications, in Management Studio's object explorer, expand <server name> >> Security >> Audits and <server name> >> Security >> Server Audit Specifications. Also, <server name> >> Databases >> <database name> >> Security >> Database Audit Specifications. Alternatively, review the contents of the system views with "audit" in their names. Run the following to verify that all SELECT, INSERT, UPDATE, and DELETE actions on tables and views are being audited: USE [master]; GO SELECT * FROM sys.server_audit_specification_details WHERE server_specification_id = (SELECT server_specification_id FROM sys.server_audit_specifications WHERE [name] = '<server_audit_specification_name>') AND audit_action_name = 'SCHEMA_OBJECT_ACCESS_GROUP'; If no row is returned, this is a finding. If the audited_result column is not "FAILURE" or "SUCCESS AND FAILURE", this is a finding.

Trace or Audit records must be generated when categorized information (e.g., classification levels/security levels) is modified.

Finding ID
SQL4-00-036650
Rule ID
SV-81931r2_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000498-DB-000346
CCI
CCI-000172
Target Key
(None)
Documentable
No
Discussion

Changes in categorized information must be tracked. Without an audit trail, unauthorized access to protected data could go undetected. For detailed information on categorizing information, refer to FIPS Publication 199, Standards for Security Categorization of Federal Information and Information Systems, and FIPS Publication 200, Minimum Security Requirements for Federal Information and Information Systems. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016. Since Trace does not provide for tracking SELECT statements, it is necessary to provide that part of the tracking at the application level. Because of this, it may also be appropriate to audit UPDATE actions at the application level. However, to capture all UPDATEs, whether they come from the application or bypass it, the Trace must be configured to cover them.

Fix Text

Where SQL Server Trace is in use, create triggers to raise a custom event for UPDATEs on each table holding categorized information. The examples provided in the supplemental file CustomTraceEvents.sql can serve as the basis for these. Add a block of code to the supplemental file Trace.sql for each custom event class (integers in the range 82-91; the same event class may be used for all such triggers) used in these triggers. Execute Trace.sql. If SQL Server Audit is in use, design and deploy an Audit that captures all auditable events and data items. The script provided in the supplemental file Audit.sql can be used as the basis for this. Supplement the standard audit data as necessary, using Extended Events and/or triggers. Alternatively, to add the necessary data capture to an existing server audit specification, run the script: USE [master]; GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = OFF); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> ADD (SCHEMA_OBJECT_ACCESS_GROUP); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = ON); GO

Check Content

Review the system documentation to determine whether it is required to track categories of information, such as classification or sensitivity level. If it is not, this is not applicable (NA). If neither SQL Server Audit nor SQL Server Trace is in use for audit purposes, this is a finding. If SQL Server Trace is in use for audit purposes, review the triggers on all tables holding categorized information, to determine whether trace events are generated for all UPDATE actions on these tables. If not, this is a finding. Check to see that all required event classes are being audited. From the query prompt: SELECT * FROM sys.traces; All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding. Determine the trace(s) being used for the auditing requirement. In the following, replace # with a trace ID being used for the auditing requirements. From the query prompt: SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#); The following required event IDs should be among those listed; if not, this is a finding: 82-91 -- User-defined Event (at least one of these, matching the triggers; 90 is used in the supplied script) 162 -- User error message If SQL Server Audit is in use, proceed as follows. The basic SQL Server Audit configuration provided in the supplemental file Audit.sql uses the broad, server-level audit action group SCHEMA_OBJECT_ACCESS_GROUP for this purpose. SQL Server Audit's flexibility makes other techniques possible. If an alternative technique is in use and demonstrated effective, this is not a finding. Determine the name(s) of the server audit specification(s) in use. To look at audits and audit specifications, in Management Studio's object explorer, expand <server name> >> Security >> Audits and <server name> >> Security >> Server Audit Specifications. Also, <server name> >> Databases >> <database name> >> Security >> Database Audit Specifications. Alternatively, review the contents of the system views with "audit" in their names. Run the following to verify that all SELECT, INSERT, UPDATE, and DELETE actions on tables and views are being audited: USE [master]; GO SELECT * FROM sys.server_audit_specification_details WHERE server_specification_id = (SELECT server_specification_id FROM sys.server_audit_specifications WHERE [name] = '<server_audit_specification_name>') AND audit_action_name = 'SCHEMA_OBJECT_ACCESS_GROUP'; If no row is returned, this is a finding. If the audited_result column is not "SUCCESS" or "SUCCESS AND FAILURE", this is a finding.

Trace or Audit records must be generated when unsuccessful attempts to modify categorized information (e.g., classification levels/security levels) occur.

Finding ID
SQL4-00-036850
Rule ID
SV-81933r2_rule
Severity
Cat II
CCE
(None)
Group Title
SRG-APP-000498-DB-000347
CCI
CCI-000172
Target Key
(None)
Documentable
No
Discussion

Changes in categorized information must be tracked. Without an audit trail, unauthorized access to protected data could go undetected. For detailed information on categorizing information, refer to FIPS Publication 199, Standards for Security Categorization of Federal Information and Information Systems, and FIPS Publication 200, Minimum Security Requirements for Federal Information and Information Systems. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016. Since Trace does not provide for tracking SELECT statements, it is necessary to provide that part of the tracking at the application level. Because of this, it may also be appropriate to audit UPDATE actions at the application level. However, to capture all UPDATEs, whether they come from the application or bypass it, the Trace must be configured to cover them. Use of SQL Server Audit's SCHEMA_OBJECT_ACCESS_GROUP causes capture of all accesses, successful and otherwise, to all schema-scoped objects. The [Succeeded] column in the audit output indicates the success or failure of the attempted action. Be aware, however, that it may report True in some cases where one would intuitively expect False. For example, SELECT 1/0 FROM SYS.ALL_OBJECTS will appear in the audit trail as successful, if the user has permission to perform that action, even though it contains an invalid expression. Some other actions that one would consider failures (such as selecting from a table that does not exist) may not appear at all.

Fix Text

Where SQL Server Trace is in use, create triggers to raise a custom event for UPDATEs on each table holding categorized information. The examples provided in the supplemental file CustomTraceEvents.sql can serve as the basis for these. Add a block of code to the supplemental file Trace.sql for each custom event class (integers in the range 82-91; the same event class may be used for all such triggers) used in these triggers. Execute Trace.sql. If SQL Server Audit is in use, design and deploy an Audit that captures all auditable events and data items. The script provided in the supplemental file Audit.sql can be used as the basis for this. Supplement the standard audit data as necessary, using Extended Events and/or triggers. Alternatively, to add the necessary data capture to an existing server audit specification, run the script: USE [master]; GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = OFF); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> ADD (SCHEMA_OBJECT_ACCESS_GROUP); GO ALTER SERVER AUDIT SPECIFICATION <server_audit_specification_name> WITH (STATE = ON); GO

Check Content

Review the system documentation to determine whether it is required to track categories of information, such as classification or sensitivity level. If it is not, this is not applicable (NA). If neither SQL Server Audit nor SQL Server Trace is in use for audit purposes, this is a finding. If SQL Server Trace is in use for audit purposes, review the Trace settings, and the triggers on the tables holding categorized information, to determine whether all UPDATE actions on these tables are traced, including failed attempts. If not, this is a finding. Check to see that all required event classes are being audited. From the query prompt: SELECT * FROM sys.traces; All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding. Determine the trace(s) being used for the auditing requirement. In the following, replace # with a trace ID being used for the auditing requirements. From the query prompt: SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#); The following required event IDs should be among those listed; if not, this is a finding: 82-91 -- User-defined Event (at least one of these, matching the triggers; 90 is used in the supplied script) 162 -- User error message If SQL Server Audit is in use, proceed as follows. The basic SQL Server Audit configuration provided in the supplemental file Audit.sql uses the broad, server-level audit action group SCHEMA_OBJECT_ACCESS_GROUP for this purpose. SQL Server Audit's flexibility makes other techniques possible. If an alternative technique is in use and demonstrated effective, this is not a finding. Determine the name(s) of the server audit specification(s) in use. To look at audits and audit specifications, in Management Studio's object explorer, expand <server name> >> Security >> Audits and <server name> >> Security >> Server Audit Specifications. Also, <server name> >> Databases >> <database name> >> Security >> Database Audit Specifications. Alternatively, review the contents of the system views with "audit" in their names. Run the following to verify that all SELECT, INSERT, UPDATE, and DELETE actions on tables and views are being audited: USE [master]; GO SELECT * FROM sys.server_audit_specification_details WHERE server_specification_id = (SELECT server_specification_id FROM sys.server_audit_specifications WHERE [name] = '<server_audit_specification_name>') AND audit_action_name = 'SCHEMA_OBJECT_ACCESS_GROUP'; If no row is returned, this is a finding. If the audited_result column is not "SUCCESS" or "SUCCESS AND FAILURE", this is a finding.