Microsoft SQL Server 2012 Database Security Technical Implementation Guide

V1R17 2018-03-01       U_SQL_Server_2012_Database_STIG_V1R17_Manual-xccdf.xml
V1R11 2016-06-24       U_SQL_Server_2012_Database_V1R11_Manual-xccdf.xml
The Microsoft SQL Server 2012 Database Security Technical Implementation Guide (STIG) is published as a tool to improve the security of Department of Defense (DoD) information systems. Comments or proposed revisions to this document should be sent via e-mail to the following address: [email protected]
Comparison
All 30
No Change 16
Updated 10
Added 2
Removed 2
V-40911 Added
Findings ID: SQL2-00-021300 Rule ID: SV-53265r5_rule Severity: medium CCI: CCI-001199

Discussion

This control is intended to address the confidentiality and integrity of information at rest in non-mobile devices and covers user information and system information. Information at rest refers to the state of information when it is located on a secondary storage device (e.g., disk drive, tape drive) within an organizational information system. Applications and application users generate information throughout the course of their application use. User-generated data, as well as, application-specific configuration data, needs to be protected. Configurations and/or rule sets for firewalls, gateways, intrusion detection/prevention systems, filtering routers, and authenticator content are examples of system information likely requiring protection. Organizations may choose to employ different mechanisms to achieve confidentiality and integrity protections, as appropriate. If the confidentiality and integrity of SQL Server data is not protected, the data will be open to compromise and unauthorized modification. Protective measures include encryption, physical security of the facility where the storage devices reside, operating system file permissions, and organizational controls. Each of these should be applied as necessary and appropriate.

Checks

If the application owner and Authorizing Official have determined that encryption of data at rest is NOT required, this is not a finding. If the application owner and Authorizing Official have determined that encryption of data at rest is required, ensure the data on secondary devices is encrypted. If full-disk encryption is being used, this is not a finding. If DBMS data encryption is required, ensure the data is encrypted before being put on the secondary device by executing: SELECT d.name AS [Database Name], CASE e.encryption_state WHEN 0 THEN 'No database encryption key present, no encryption' WHEN 1 THEN 'Unencrypted' WHEN 2 THEN 'Encryption in progress' WHEN 3 THEN 'Encrypted' WHEN 4 THEN 'Key change in progress' WHEN 5 THEN 'Decryption in progress' WHEN 6 THEN 'Protection change in progress' END AS [Encryption State] FROM sys.dm_database_encryption_keys e RIGHT JOIN sys.databases d ON DB_NAME(e.database_id) = d.name WHERE d.name NOT IN ('master','model','msdb') ORDER BY 1 ; For each user database where encryption is required, verify that encryption is in effect. If not, this is a finding. Verify that there are physical security measures, operating system access control lists and organizational controls appropriate to the sensitivity level of the data in the database(s). If not, this is a finding.

Fix

Apply appropriate controls to protect the confidentiality and integrity of data on a secondary device. Where encryption is required, this can be done by full-disk encryption or by database encryption. To enable database encryption, create a master key, create a database encryption key, and protect it by using mechanisms tied to the master key, and then set encryption on. Implement physical security measures, operating system access control lists and organizational controls appropriate to the sensitivity level of the data in the database(s).
V-41389 Updated
Findings ID: SQL2-00-000300 Rule ID: SV-53912r34_rule Severity: medium CCI: CCI-0013992262

Discussion

Security attributes are abstractions representing the basic properties or characteristics of an entity (e.g., subjects and objects) with respect to safeguarding information.

These attributes are typically associated with internal data structures (e.g., records, buffers, files) within the information system 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 attributes 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 attributes are lost when the data is stored, there is the risk of a data compromise.

The sensitivity marking or labeling of stored data items promotes the correct handling and protection of data. Without such notification, the user may unwittingly disclose sensitive data to unauthorized users.


(Earlier releases of this STIG suggested using the SQL Server Label Security Toolkit, from codeplex.com. However, codeplex.com has been shut down, and it is unclear whether the Toolkit is still supported. If the organization does have access to the Toolkit, it may still be used, provided the organization accepts responsibility for its support.)

Checks

Review system documentation to determine if the labeling of sensitive data is required under organization-defined guidelines.
If the labeling of sensitive data is not required, this is NA.

Obtain system configuration settings to determine how data labeling is being performed. This can be through triggers or some other SQL-developed means or via a third-party tool. Spot check data and ensure the appropriate labels have been applied to stored data.

If the labeling of sensitive data is required and is not being performed, this is a finding.

Fix

Develop SQL code or acquire a third party tool to perform data labeling. SQL Server Label Security Toolkit can be downloaded from http://www.codeplex.com. This tool can satisfy all data labeling and securityor application code or acquire a third party tool to perform data labeling requirements.
V-41391 Updated
Findings ID: SQL2-00-000400 Rule ID: SV-53914r34_rule Severity: medium CCI: CCI-0014002263

Discussion

Security attributes are abstractions representing the basic properties or characteristics of an entity (e.g., subjects and objects) with respect to safeguarding information.

These attributes are typically associated with internal data structures (e.g., records, buffers, files) within the application 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.

Organizations define the security attributes of their data (e.g., classified, FOUO). Applications generating and/or processing data assigned these organization-defined security attributes must maintain the binding of these attributes to the data when the data is transmitted.

If the application does not maintain the data security attributes when it transmits the data, there is a risk of data compromise.

The sensitivity marking or labeling of data items promotes the correct handling and protection of data. Without such notification, the user may unwittingly disclose sensitive data to unauthorized users. Security labels must be correctly maintained throughout transmission.


(Earlier releases of this STIG suggested using the SQL Server Label Security Toolkit, from codeplex.com. However, codeplex.com has been shut down, and it is unclear whether the Toolkit is still supported. If the organization does have access to the Toolkit, it may still be used, provided the organization accepts responsibility for its support.)

Checks

Review system documentation to determine if the labeling of sensitive data is required under organization-defined guidelines.

If the labeling of sensitive data is not required, this is NA.

Obtain system configuration settings to determine how data labeling is being performed. This can be through triggers or some other SQL-developed means or via a third-party tool.

If the labeling of sensitive information in process is not being performed, this is a finding.

Fix

Develop SQL code or acquire a third party tool to perform data labeling. SQL Server Label Security Toolkit can be downloaded from http://www.codeplex.com. This tool can satisfy all data labeling and securityor application code or acquire a third party tool to perform data labeling requirements.
V-41392 Updated
Findings ID: SQL2-00-000500 Rule ID: SV-53916r34_rule Severity: medium CCI: CCI-0014012264

Discussion

Security attributes are abstractions representing the basic properties or characteristics of an entity (e.g., subjects and objects) with respect to safeguarding information.

These attributes are typically associated with internal data structures (e.g., records, buffers, files) within the application 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.

Organizations define the security attributes of their data (e.g., classified, FOUO). Applications generating and/or processing data assigned these organization-defined security attributes must maintain the binding of these attributes to the data when the data is transmitted.

If the application does not maintain the data security attributes when it transmits the data, there is a risk of data compromise.

The sensitivity marking or labeling of data items promotes the correct handling and protection of data. Without such notification, the user may unwittingly disclose sensitive data to unauthorized users. Security labels must be correctly maintained throughout transmission.


(Earlier releases of this STIG suggested using the SQL Server Label Security Toolkit, from codeplex.com. However, codeplex.com has been shut down, and it is unclear whether the Toolkit is still supported. If the organization does have access to the Toolkit, it may still be used, provided the organization accepts responsibility for its support.)

Checks

Review system documentation to determine if the labeling of sensitive data is required under organization-defined guidelines.
If the labeling of sensitive data is not required, this is NA.

Obtain system configuration settings to determine how data labeling is being performed. This can be through triggers or some other SQL-developed means or via a third-party tool.

If the labeling of sensitive data in transmission is not being performed, this is a finding.

Fix

Develop SQL code or acquire a third party tool to perform data labeling. SQL Server Label Security Toolkit can be downloaded from http://www.codeplex.com. This tool can satisfy all data labeling and securityor application code or acquire a third party tool to perform data labeling requirements.
V-41393 No Change
Findings ID: SQL2-00-000900 Rule ID: SV-53917r3_rule Severity: medium CCI: CCI-001427

Discussion

Security attributes are abstractions representing the basic properties or characteristics of an entity (e.g., subjects and objects) with respect to safeguarding information.

These attributes are typically associated with internal data structures (e.g., records, buffers, files) within the information system 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.

Examples of application security attributes are classified, FOUO, sensitive, etc.

Throughout the course of normal usage, authorized users of applications that handle sensitive data will have the need to associate security attributes with information. Applications that maintain the binding of organization-defined security attributes to data must ensure authorized users can associate security attributes with information. For databases, this is accomplished via labeling.

Checks

Review system documentation to determine if the labeling of sensitive data is required under organization-defined guidelines.
If the labeling of sensitive data is not required, this is NA.

Obtain system configuration setting to determine how data labeling is being performed. This can be through triggers or some other SQL-developed means or via a third-party tool. Determine how authorized users associate security information to data. If authorized users are not able to associate security labels to data, this is a finding.

Fix

Develop SQL code or acquire a third party tool to perform data labeling. SQL Server Label Security Toolkit can be downloaded from http://www.codeplex.com. This tool can satisfy all data labeling and security data labeling requirements.
V-41394 No Change
Findings ID: SQL2-00-011050 Rule ID: SV-53918r3_rule Severity: medium CCI: CCI-001693

Discussion

Discretionary Access Control (DAC) is based on the premise that individual users are "owners" of objects and therefore have discretion over who should be authorized to access the object and in which mode (e.g., read or write). Ownership is usually acquired as a consequence of creating the object or via specified ownership assignment.

DAC allows the owner to determine who will have access to objects they control. An example of DAC includes user-controlled file permissions. DAC models have the potential for the access controls to propagate without limit, resulting in unauthorized access to said objects.

When applications provide a discretionary access control mechanism, the application must be able to limit the propagation of those access rights.

The DBMS must ensure the recipient of object permissions possesses only the access intended. The database must enforce the ability to limit unauthorized rights propagation. If propagation is not prevented, users can continue to grant rights to other users without limit.true

Checks

Check for rights propagation assignment to database permissions by running the following query:

USE <name of database being reviewed>;
SELECT *
FROM sys.database_permissions
WHERE state_desc = 'GRANT_WITH_GRANT_OPTION';

If any of the permissions listed have not been documented and approved as requiring GRANT_WITH_GRANT_OPTION, this is a finding.

Fix

Document and obtain approval for each GRANT_WITH_GRANT_OPTION that is required.

Correct each unapproved GRANT_WITH_GRANT_OPTION with REVOKE and GRANT statements of the form (replacing "UPDATE" with the actual permission at issue):
REVOKE UPDATE ON SampleTable FROM SampleUserOrRole CASCADE;
GRANT UPDATE ON SampleTable TO SampleRole; -- Note, no WITH GRANT OPTION clause here.
V-41395 No Change
Findings ID: SQL2-00-009200 Rule ID: SV-53920r4_rule Severity: medium CCI: CCI-000037

Discussion

Applications employ the concept of least privilege for specific duties and information systems (including specific functions, ports, protocols, and services). The concept of least privilege is also applied to information system processes, ensuring that the processes operate at privilege levels no higher than necessary to accomplish required organizational missions and/or functions. Organizations consider the creation of additional processes, roles, and information system accounts as necessary to achieve least privilege. Organizations also apply least privilege concepts to the design, development, implementation, and operations of information systems.

Developers granted elevated database and/or operating system privileges on production databases can affect the operation and/or security of the database system. Operating system and database privileges assigned to developers on production systems should not be allowed.

Checks

Check the list of SQL Server users against the list of developer accounts by running the following SQL Server query:

SELECT name AS 'Account Name'
, create_date AS 'Account Create Date'
, LOGINPROPERTY(name, 'PasswordLastSetTime') AS 'Password Last Set on'
FROM sys.server_principals
WHERE NOT TYPE IN ('C', 'R', 'U')
AND NOT name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##')
AND sid <> CONVERT(VARBINARY(85), 0x01) -- no 'sa' account
AND is_disabled <> 1
ORDER BY name;

For each developer account found on a production machine, verify if the developer account can change or alter database objects or data in the production database. If any developer account can change or alter database objects or data in a production database, this is a finding.

Fix

Remove unnecessary developer accounts from SQL Server instances hosting only production databases, by running the following SQL script:

USE master
DROP LOGIN <'account name'>
V-41396 No Change
Findings ID: SQL2-00-009300 Rule ID: SV-53921r2_rule Severity: medium CCI: CCI-000037

Discussion

Applications employ the concept of least privilege for specific duties and information systems (including specific functions, ports, protocols, and services). The concept of least privilege is also applied to information system processes, ensuring that the processes operate at privilege levels no higher than necessary to accomplish required organizational missions and/or functions. Organizations consider the creation of additional processes, roles, and information system accounts as necessary to achieve least privilege. Organizations also apply least privilege concepts to the design, development, implementation, and operations of information systems.

Developers granted elevated database and/or operating system privileges on systems that support both development and production databases can affect the operation and/or security of the production database system. Operating system and database privileges assigned to developers on shared development and production systems must be restricted.

Checks

Identify whether SQL Server contains both development and production databases from the system documentation.

If SQL Server is not hosting both production and development databases, this is NA.


If SQL Server is hosting both development and production databases, but this is not clearly documented in the system documentation, this is a finding.
Check the list of SQL Server users against the list of developer accounts by running the following SQL Server query:

SELECT name AS 'Account Name'
, create_date AS 'Account Create Date'
, LOGINPROPERTY(name, 'PasswordLastSetTime') AS 'Password Last Set on'
FROM sys.server_principals
WHERE NOT TYPE IN ('C', 'R', 'U') -- ('C', 'G', 'K', 'R', 'S', 'U')
AND NOT name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##')
AND sid <> CONVERT(VARBINARY(85), 0x01) -- no 'sa' account
AND is_disabled <> 1
ORDER BY name


If no developer user account is listed, this is not a finding.

Check each developer user account privilege listed above.

Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins >> right click <'developer account name'> >> Properties >> User >> Securables.

If any item in the 'Permission' listing, for each highlighted item that exists in the 'Securables' listing, grants production privileges, this is a finding.

Navigate from 'Securables' to 'Server Roles'.

If any 'Server roles' are checked that grant production privileges, this is a finding.

Navigate from 'Server Roles' to 'Users mapped to the login'.

If any checked 'Database role membership' of each highlighted and checked 'Database' are determined to be granting production privileges, this is a finding.

Fix

Within the system documentation, clearly identify if SQL Server is hosting both development and production databases.

Restrict developer privileges to production objects to only objects and data where those privileges are required and authorized by running the following scripts as needed:

Remove the user from direct access to server permission by running the following script:
USE master
REVOKE <'server permission name'> TO <'account name'> CASCADE

Remove the user from user-defined role access by running the following script:
USE master
ALTER SERVER ROLE [<'server role name'>] DROP MEMBER <'user name'>

Remove permissions from developer user accounts that grant permissions beyond the development database.

Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins >> right click <'administrator account name'> >> Properties >> User >> Securables.

Remove 'Securables' permissions from accounts that are beyond what is required.

Navigate from 'Securables' to 'Server Roles'.

Remove 'Server Roles' permissions from accounts that are beyond what is required.

Navigate from 'Server Roles' to 'Users mapped to the login'.

Remove 'Users mapped to the login' permissions from accounts that are beyond what is required.
V-41397 No Change
Findings ID: SQL2-00-009500 Rule ID: SV-53922r5_rule Severity: medium CCI: CCI-000037

Discussion

SQL Server must employ the concept of least privilege for specific duties and information systems (including specific functions, ports, protocols, and services). The concept of least privilege is also applied to information system processes, ensuring that the processes operate at privilege levels no higher than necessary to accomplish required organizational missions and/or functions. Organizations consider the creation of additional processes, roles, and information system accounts as necessary to achieve least privilege. Organizations also apply least privilege concepts to the design, development, implementation, and operations of information systems.

Privileges granted outside the role of the application user job function are more likely to go unmanaged or without oversight for authorization. Maintenance of privileges using roles defined for discrete job functions offers improved oversight of application user privilege assignments and helps to protect against unauthorized privilege assignment.

SQL Server built-in administrative privileges, built-in server roles and built-in database roles must not be assigned directly to administrative user accounts (that is, server logins and database users). If administrative user accounts have direct access to administrative roles, this access must be removed, with the exception of administrative roles that the DBMS assigns to the special database principal [dbo], and will not allow to be altered.

The built-in server role "sysadmin" is a partial exception. This cannot be granted to a user-defined role, only to a login account. Most (not necessarily all) database administrators will need to be members of sysadmin. Without this, most DBCC commands and the system stored procedures/functions listed below are unavailable. The users who require such access must be documented and approved.true

Checks

Check administrative accounts for direct database role membership:

Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins >> right click <'administrator account name'> >> Properties >> User Mapping >> <'highlight database'> >> review 'Database role membership' for each database.

If any administration accounts have a direct privilege to any 'Database role membership' that is part of the SQL Server system, this is a finding.

Check administrative accounts for direct server role membership:

Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins >> right click <'administrator account name'> >> Properties >> Server Roles.

If any administration accounts have direct access to any 'Server roles' privilege that is part of the SQL Server system, this is a finding.

The special database principal [dbo] is an exception. It is mapped to the server login that is the database owner. Some roles cannot be mapped to it or unmapped from it. These role assignments are not a finding.

The built-in server role "sysadmin" is a partial exception. See the Vulnerability Discussion.

Fix

Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins >> right click <'administrator account name'> >> Properties >> User Mapping >> <'highlight the database'> >> review 'Database role membership' each database.

Remove 'Database role membership' by clicking the appropriate check box.

Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins >> right click <'administrator account name'> >> Properties >> Server Roles.

Remove 'Server roles' by clicking the appropriate check box.

The special database principal [dbo] is an exception. It is mapped to the server login that is the database owner. Some roles cannot be mapped to it or unmapped from it.

The built-in server role "sysadmin" is a partial exception. See the Vulnerability Discussion.
V-41399 No Change
Findings ID: SQL2-00-023500 Rule ID: SV-53925r2_rule Severity: medium CCI: CCI-000366

Discussion

When dealing with unauthorized SQL Server job submissions, it should be noted any unauthorized job submissions to SQL Server job/batch queues can potentially have significant effects on the overall security of the system.

If SQL Server were to allow any user to make SQL Server job/batch queue submissions, then those submissions might lead to a compromise of system integrity and/or data. This requirement is contingent upon the SQL Server job/batch queue being review regularly for unauthorized submissions.

Accordingly, only qualified and authorized individuals shall be allowed to obtain access to submit SQL Server jobs. Job/batch queue submissions must adhere to an organization-defined job submission process.

Unmanaged changes that occur to SQL Server job/batch queues can lead to a compromised system.

Checks

Check system documentation for procedures that are regularly implemented in an effort to detect unauthorized SQL Server job submissions.

If procedures that are regularly implemented are not documented in the system documentation, this is a finding.

If the procedures are not implemented regularly or do not detect for unauthorized SQL Server job submissions, this is a finding.

Review Stored Procedures that are able to automatically execute jobs scheduled to start automatically at system startup by running the following query:
SELECT name
FROM master.sys.procedures
WHERE is_auto_executed = 1

If any Stored Procedures listed are not documented as authorized, this is a finding.

Review the SQL Server job history by running the following query:

SELECT * FROM msdb.dbo.sysjobhistory


If any jobs listed are not documented as authorized, this is a finding.

Fix

Document procedures, within the system documentation, that detect for unauthorized SQL Server job submissions.

Develop and implement procedures to detect for unauthorized SQL Server job submissions of Stored Procedures that are automatically executed and Agent jobs that are enabled.
V-41402 Updated
Findings ID: SQL2-00-011200 Rule ID: SV-53928r34_rule Severity: medium CCI: CCI-000169

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.

Checks

Check to see that all required events are being audited.
From the query prompt:
SELECT DISTINCT traceid FROM sys.fn_trace_getinfo(0);
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 listed:
14, 15, 18, 20,
102, 103, 104, 105, 106, 107, 108, 109, 110,
111, 112, 113, 115, 116, 117, 118,
128, 129, 130,
131, 132, 133, 134, 135,
152, 153,
170, 171, 172, 173, 175, 176, 177, 178.
If any of the audit event IDs required above is not listed, this is a finding.

Notes:
1. It is acceptable to have the required event IDs spread across multiple traces, provided all of the traces are always active, and the event IDs are grouped in a logical manner.
2. It is acceptable, from an auditing point of view, to include the same event IDs in multiple traces. However, the effect of this redundancy on performance, storage, and the consolidation of audit logs into a central repository, should be taken into account.
3. It is acceptable to trace additional event IDs. This is the minimum list.
4. Once this check is satisfied, the DBA may find it useful to disable or modify the default trace that is set up by the SQL Server installation process. (Note that the Fix does NOT include code to do this.)
Use the following query to obtain a list of all event IDs, and their meaning:
SELECT * FROM sys.trace_events;
5. Because this check procedure is designed to address multiple requirements/vulnerabilities, it may appear to exceed the needs of some individual requirements. However, it does represent the aggregate of all such requirements.
6. Microsoft has flagged the trace techniques and tools used in this Check and Fix as deprecated. They will be removed at some point after SQL Server 2014. The replacement feature is Extended Events. If Extended Events are in use, and cover all the required audit events listed above, this is not a finding.

Fix


-- Run this script to create and start an audit trace that audits required events.

-- Note: Replace 'D:<path>\<filename>' with the path and file name to your audit file.
-- Adjust the other parameters of SP_TRACE_CREATE to suit your system's circumstances.

-- The database server must be restarted for the trace to take effect.

USE master;
GO

BEGIN TRY DROP PROCEDURE fso_audit END TRY BEGIN CATCH END CATCH;
GO

CREATE PROCEDURE fso_audit AS
-- Create a Queue
DECLARE @rc INT;
DECLARE @TraceID INT;
DECLARE @options INT = 6; -- 6 specifies TRACE_FILE_ROLLOVER (2) and SHUTDOWN_ON_ERROR (4)
DECLARE @tracefile NVARCHAR(128) = 'D:<path>\<filename>';
-- Trace file location and beginning of file name (SQL Server adds a suffix)
DECLARE @maxfilesize BIGINT = 500; -- Trace file size limit in megabytes
DECLARE @stoptime datetime = null; -- do not stop
DECLARE @filecount INT = 10; -- Number of trace files in the rollover set
EXEC @rc = SP_TRACE_CREATE
@TraceID output,
@options,
@tracefile,
@maxfilesize,
@stoptime,
@filecount
;
IF (@rc != 0) GOTO Error;

-- Set the events:
DECLARE @on BIT = 1;

-- Logins are audited based on SQL Server instance
-- setting Audit Level stored in registry
-- HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.[#]\MSSQLServer\AuditLevel
-- Audit Login
-- Occurs when a user successfully logs in to SQL Server.
EXEC SP_TRACE_SETEVENT @TraceID, 14, 1, @on; -- TextData
EXEC SP_TRACE_SETEVENT @TraceID, 14, 6, @on; -- NTUserName
EXEC SP_TRACE_SETEVENT @TraceID, 14, 7, @on; -- NTDomainName
EXEC SP_TRACE_SETEVENT @TraceID, 14, 8, @on; -- HostName
EXEC SP_TRACE_SETEVENT @TraceID, 14, 10, @on; -- ApplicationName
EXEC SP_TRACE_SETEVENT @TraceID, 14, 11, @on; -- LoginName
EXEC SP_TRACE_SETEVENT @TraceID, 14, 12, @on; -- SPID
EXEC SP_TRACE_SETEVENT @TraceID, 14, 14, @on; -- StartTime
EXEC SP_TRACE_SETEVENT @TraceID, 14, 23, @on; -- Success
EXEC SP_TRACE_SETEVENT @TraceID, 14, 26, @on; -- ServerName
EXEC SP_TRACE_SETEVENT @TraceID, 14, 35, @on; -- DatabaseName
EXEC SP_TRACE_SETEVENT @TraceID, 14, 41, @on; -- LoginSid
EXEC SP_TRACE_SETEVENT @TraceID, 14, 60, @on; -- IsSystem
EXEC SP_TRACE_SETEVENT @TraceID, 14, 64, @on; -- SessionLoginName
-- Audit Logout
-- Occurs when a user logs out of SQL Server.
EXEC SP_TRACE_SETEVENT @TraceID, 15, 6, @on; -- NTUserName
EXEC SP_TRACE_SETEVENT @TraceID, 15, 7, @on; -- NTDomainName
EXEC SP_TRACE_SETEVENT @TraceID, 15, 8, @on; -- HostName
EXEC SP_TRACE_SETEVENT @TraceID, 15, 10, @on; -- ApplicationName
EXEC SP_TRACE_SETEVENT @TraceID, 15, 11, @on; -- LoginName
EXEC SP_TRACE_SETEVENT @TraceID, 15, 12, @on; -- SPID
EXEC SP_TRACE_SETEVENT @TraceID, 15, 13, @on; -- Duration
EXEC SP_TRACE_SETEVENT @TraceID, 15, 14, @on; -- StartTime
EXEC SP_TRACE_SETEVENT @TraceID, 15, 15, @on; -- EndTime
EXEC SP_TRACE_SETEVENT @TraceID, 15, 23, @on; -- Success
EXEC SP_TRACE_SETEVENT @TraceID, 15, 26, @on; -- ServerName
EXEC SP_TRACE_SETEVENT @TraceID, 15, 35, @on; -- DatabaseName
EXEC SP_TRACE_SETEVENT @TraceID, 15, 41, @on; -- LoginSid
EXEC SP_TRACE_SETEVENT @TraceID, 15, 60, @on; -- IsSystem
EXEC SP_TRACE_SETEVENT @TraceID, 15, 64, @on; -- SessionLoginName
-- Audit Server Starts and Stops
-- Occurs when the SQL Server service state is modified.
EXEC SP_TRACE_SETEVENT @TraceID, 18, 6, @on; -- NTUserName
EXEC SP_TRACE_SETEVENT @TraceID, 18, 7, @on; -- NTDomainName
EXEC SP_TRACE_SETEVENT @TraceID, 18, 8, @on; -- HostName
EXEC SP_TRACE_SETEVENT @TraceID, 18, 10, @on; -- ApplicationName
EXEC SP_TRACE_SETEVENT @TraceID, 18, 11, @on; -- LoginName
EXEC SP_TRACE_SETEVENT @TraceID, 18, 12, @on; -- SPID
EXEC SP_TRACE_SETEVENT @TraceID, 18, 14, @on; -- StartTime
EXEC SP_TRACE_SETEVENT @TraceID, 18, 23, @on; -- Success
EXEC SP_TRACE_SETEVENT @TraceID, 18, 26, @on; -- ServerName
EXEC SP_TRACE_SETEVENT @TraceID, 18, 41, @on; -- LoginSid
EXEC SP_TRACE_SETEVENT @TraceID, 18, 60, @on; -- IsSystem
EXEC SP_TRACE_SETEVENT @TraceID, 18, 64, @on; -- SessionLoginName
-- Audit Login Failed
-- Indicates that a login attempt to SQL Server from a client failed.
EXEC SP_TRACE_SETEVENT @TraceID, 20, 1, @on; -- TextData
EXEC SP_TRACE_SETEVENT @TraceID, 20, 6, @on; -- NTUserName
EXEC SP_TRACE_SETEVENT @TraceID, 20, 7, @on; -- NTDomainName
EXEC SP_TRACE_SETEVENT @TraceID, 20, 8, @on; -- HostName
EXEC SP_TRACE_SETEVENT @TraceID, 20, 10, @on; -- ApplicationName
EXEC SP_TRACE_SETEVENT @TraceID, 20, 11, @on; -- LoginName
EXEC SP_TRACE_SETEVENT @TraceID, 20, 12, @on; -- SPID
EXEC SP_TRACE_SETEVENT @TraceID, 20, 14, @on; -- StartTime
EXEC SP_TRACE_SETEVENT @TraceID, 20, 23, @on; -- Success
EXEC SP_TRACE_SETEVENT @TraceID, 20, 26, @on; -- ServerName
EXEC SP_TRACE_SETEVENT @TraceID, 20, 31, @on; -- Error
EXEC SP_TRACE_SETEVENT @TraceID, 20, 35, @on; -- DatabaseName
EXEC SP_TRACE_SETEVENT @TraceID, 20, 60, @on; -- IsSystem
EXEC SP_TRACE_SETEVENT @TraceID, 20, 64, @on; -- SessionLoginName
-- Audit Statement GDR Event
-- Occurs every time a GRANT, DENY, REVOKE for a statement
-- permission is issued by any user in SQL Server.
EXEC SP_TRACE_SETEVENT @TraceID, 102, 1, @on; -- TextData
EXEC SP_TRACE_SETEVENT @TraceID, 102, 6, @on; -- NTUserName
EXEC SP_TRACE_SETEVENT @TraceID, 102, 7, @on; -- NTDomainName
EXEC SP_TRACE_SETEVENT @TraceID, 102, 8, @on; -- HostName
EXEC SP_TRACE_SETEVENT @TraceID, 102, 10, @on; -- ApplicationName
EXEC SP_TRACE_SETEVENT @TraceID, 102, 11, @on; -- LoginName
EXEC SP_TRACE_SETEVENT @TraceID, 102, 12, @on; -- SPID
EXEC SP_TRACE_SETEVENT @TraceID, 102, 14, @on; -- StartTime
EXEC SP_TRACE_SETEVENT @TraceID, 102, 19, @on; -- StartTime
EXEC SP_TRACE_SETEVENT @TraceID, 102, 23, @on; -- Success
EXEC SP_TRACE_SETEVENT @TraceID, 102, 26, @on; -- ServerName
EXEC SP_TRACE_SETEVENT @TraceID, 102, 28, @on; -- ObjectType
EXEC SP_TRACE_SETEVENT @TraceID, 102, 34, @on; -- ObjectName
EXEC SP_TRACE_SETEVENT @TraceID, 102, 35, @on; -- DatabaseName
EXEC SP_TRACE_SETEVENT @TraceID, 102, 37, @on; -- OwnerName
EXEC SP_TRACE_SETEVENT @TraceID, 102, 39, @on; -- TargetUserName
EXEC SP_TRACE_SETEVENT @TraceID, 102, 40, @on; -- DBUserName
EXEC SP_TRACE_SETEVENT @TraceID, 102, 41, @on; -- LoginSid
EXEC SP_TRACE_SETEVENT @TraceID, 102, 42, @on; -- TargetLoginName
EXEC SP_TRACE_SETEVENT @TraceID, 102, 43, @on; -- TargetLoginSid
EXEC SP_TRACE_SETEVENT @TraceID, 102, 60, @on; -- IsSystem
EXEC SP_TRACE_SETEVENT @TraceID, 102, 64, @on; -- SessionLoginName
-- Audit Object GDR Event
-- Occurs every time a GRANT, DENY, REVOKE for an object
-- permission is issued by any user in SQL Server.
EXEC SP_TRACE_SETEVENT @TraceID, 103, 1, @on; -- TextData
EXEC SP_TRACE_SETEVENT @TraceID, 103, 6, @on; -- NTUserName
EXEC SP_TRACE_SETEVENT @TraceID, 103, 7, @on; -- NTDomainName
EXEC SP_TRACE_SETEVENT @TraceID, 103, 8, @on; -- HostName
EXEC SP_TRACE_SETEVENT @TraceID, 103, 10, @on; -- ApplicationName
EXEC SP_TRACE_SETEVENT @TraceID, 103, 11, @on; -- LoginName
EXEC SP_TRACE_SETEVENT @TraceID, 103, 12, @on; -- SPID
EXEC SP_TRACE_SETEVENT @TraceID, 103, 14, @on; -- StartTime
EXEC SP_TRACE_SETEVENT @TraceID, 103, 19, @on; -- StartTime
EXEC SP_TRACE_SETEVENT @TraceID, 103, 23, @on; -- Success
EXEC SP_TRACE_SETEVENT @TraceID, 103, 26, @on; -- ServerName
EXEC SP_TRACE_SETEVENT @TraceID, 103, 28, @on; -- ObjectType
EXEC SP_TRACE_SETEVENT @TraceID, 103, 34, @on; -- ObjectName
EXEC SP_TRACE_SETEVENT @TraceID, 103, 35, @on; -- DatabaseName
EXEC SP_TRACE_SETEVENT @TraceID, 103, 37, @on; -- OwnerName
EXEC SP_TRACE_SETEVENT @TraceID, 103, 39, @on; -- TargetUserName
EXEC SP_TRACE_SETEVENT @TraceID, 103, 40, @on; -- DBUserName
EXEC SP_TRACE_SETEVENT @TraceID, 103, 41, @on; -- LoginSid
EXEC SP_TRACE_SETEVENT @TraceID, 103, 42, @on; -- TargetLoginName
EXEC SP_TRACE_SETEVENT @TraceID, 103, 43, @on; -- TargetLoginSid
EXEC SP_TRACE_SETEVENT @TraceID, 103, 44, @on; -- ColumnPermissions
EXEC SP_TRACE_SETEVENT @TraceID, 103, 59, @on; -- ParentName
EXEC SP_TRACE_SETEVENT @TraceID, 103, 60, @on; -- IsSystem
EXEC SP_TRACE_SETEVENT @TraceID, 103, 64, @on; -- SessionLoginName
-- Audit AddLogin Event
-- Occurs when a SQL Server login is added or removed;
-- for sp_addlogin and sp_droplogin.
EXEC SP_TRACE_SETEVENT @TraceID, 104, 6, @on; -- NTUserName
EXEC SP_TRACE_SETEVENT @TraceID, 104, 7, @on; -- NTDomainName
EXEC SP_TRACE_SETEVENT @TraceID, 104, 8, @on; -- HostName
EXEC SP_TRACE_SETEVENT @TraceID, 104, 10, @on; -- ApplicationName
EXEC SP_TRACE_SETEVENT @TraceID, 104, 11, @on; -- LoginName
EXEC SP_TRACE_SETEVENT @TraceID, 104, 12, @on; -- SPID
EXEC SP_TRACE_SETEVENT @TraceID, 104, 14, @on; -- StartTime
EXEC SP_TRACE_SETEVENT @TraceID, 104, 23, @on; -- Success
EXEC SP_TRACE_SETEVENT @TraceID, 104, 26, @on; -- ServerName
EXEC SP_TRACE_SETEVENT @TraceID, 104, 35, @on; -- DatabaseName
EXEC SP_TRACE_SETEVENT @TraceID, 104, 41, @on; -- LoginSid
EXEC SP_TRACE_SETEVENT @TraceID, 104, 42, @on; -- TargetLoginName
EXEC SP_TRACE_SETEVENT @TraceID, 104, 43, @on; -- TargetLoginSid
EXEC SP_TRACE_SETEVENT @TraceID, 104, 60, @on; -- IsSystem
EXEC SP_TRACE_SETEVENT @TraceID, 104, 64, @on; -- SessionLoginName
-- Audit Login GDR Event
-- Occurs when a Windows login right is added or removed;
-- for sp_grantlogin, sp_revokelogin, and sp_denylogin.
EXEC SP_TRACE_SETEVENT @TraceID, 105, 6, @on; -- NTUserName
EXEC SP_TRACE_SETEVENT @TraceID, 105, 7, @on; -- NTDomainName
EXEC SP_TRACE_SETEVENT @TraceID, 105, 8, @on; -- HostName
EXEC SP_TRACE_SETEVENT @TraceID, 105, 10, @on; -- ApplicationName
EXEC SP_TRACE_SETEVENT @TraceID, 105, 11, @on; -- LoginName
EXEC SP_TRACE_SETEVENT @TraceID, 105, 12, @on; -- SPID
EXEC SP_TRACE_SETEVENT @TraceID, 105, 14, @on; -- StartTime
EXEC SP_TRACE_SETEVENT @TraceID, 105, 23, @on; -- Success
EXEC SP_TRACE_SETEVENT @TraceID, 105, 26, @on; -- ServerName
EXEC SP_TRACE_SETEVENT @TraceID, 105, 35, @on; -- DatabaseName
EXEC SP_TRACE_SETEVENT @TraceID, 105, 41, @on; -- LoginSid
EXEC SP_TRACE_SETEVENT @TraceID, 105, 42, @on; -- TargetLoginName
EXEC SP_TRACE_SETEVENT @TraceID, 105, 43, @on; -- TargetLoginSid
EXEC SP_TRACE_SETEVENT @TraceID, 105, 60, @on; -- IsSystem
EXEC SP_TRACE_SETEVENT @TraceID, 105, 64, @on; -- SessionLoginName
-- Audit Login Change Property Event
-- Occurs when a property of a login, except passwords,
-- is modified; for sp_defaultdb and sp_defaultlanguage.
EXEC SP_TRACE_SETEVENT @TraceID, 106, 1, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 106, 6, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 106, 7, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 106, 8, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 106, 10, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 106, 11, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 106, 12, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 106, 14, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 106, 23, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 106, 26, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 106, 28, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 106, 34, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 106, 35, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 106, 37, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 106, 41, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 106, 42, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 106, 43, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 106, 60, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 106, 64, @on;
-- Audit Login Change Password Event
-- Occurs when a SQL Server login password is changed.
-- Passwords are not recorded.
EXEC SP_TRACE_SETEVENT @TraceID, 107, 1, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 107, 6, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 107, 7, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 107, 8, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 107, 10, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 107, 11, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 107, 12, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 107, 14, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 107, 23, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 107, 26, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 107, 28, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 107, 34, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 107, 35, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 107, 37, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 107, 41, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 107, 42, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 107, 43, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 107, 60, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 107, 64, @on;
-- Audit Add Login to Server Role Event
-- Occurs when a login is added or removed from a fixed server role;
-- for sp_addsrvrolemember, and sp_dropsrvrolemember.
EXEC SP_TRACE_SETEVENT @TraceID, 108, 1, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 108, 6, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 108, 7, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 108, 8, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 108, 10, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 108, 11, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 108, 12, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 108, 14, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 108, 23, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 108, 26, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 108, 28, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 108, 34, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 108, 35, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 108, 37, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 108, 38, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 108, 40, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 108, 41, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 108, 42, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 108, 43, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 108, 60, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 108, 64, @on;
-- Audit Add DB User Event
-- Occurs when a login is added or removed as a database user
-- (Windows or SQL Server) to a database; for sp_grantdbaccess,
-- sp_revokedbaccess, sp_adduser, and sp_dropuser.
EXEC SP_TRACE_SETEVENT @TraceID, 109, 6, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 109, 7, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 109, 8, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 109, 10, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 109, 11, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 109, 12, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 109, 14, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 109, 21, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 109, 23, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 109, 26, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 109, 35, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 109, 37, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 109, 38, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 109, 39, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 109, 40, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 109, 41, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 109, 42, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 109, 43, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 109, 44, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 109, 51, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 109, 60, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 109, 64, @on;
-- Audit Add Member to DB Role Event
-- Occurs when a login is added or removed as a database user
-- (fixed or user-defined) to a database; for sp_addrolemember,
-- sp_droprolemember, and sp_changegroup.
EXEC SP_TRACE_SETEVENT @TraceID, 110, 1, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 110, 6, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 110, 7, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 110, 8, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 110, 10, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 110, 11, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 110, 12, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 110, 14, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 110, 23, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 110, 26, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 110, 28, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 110, 34, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 110, 35, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 110, 37, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 110, 38, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 110, 39, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 110, 40, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 110, 41, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 110, 60, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 110, 64, @on;
-- Audit Add Role Event
-- Occurs when a login is added or removed as a database user to a
-- database; for sp_addrole and sp_droprole.
EXEC SP_TRACE_SETEVENT @TraceID, 111, 6, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 111, 7, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 111, 8, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 111, 10, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 111, 11, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 111, 12, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 111, 14, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 111, 23, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 111, 26, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 111, 35, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 111, 38, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 111, 40, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 111, 41, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 111, 60, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 111, 64, @on;
-- Audit App Role Change Password Event
-- Occurs when a password of an application role is changed.
EXEC SP_TRACE_SETEVENT @TraceID, 112, 1, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 112, 6, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 112, 7, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 112, 8, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 112, 10, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 112, 11, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 112, 12, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 112, 14, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 112, 23, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 112, 26, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 112, 28, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 112, 34, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 112, 35, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 112, 37, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 112, 38, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 112, 40, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 112, 41, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 112, 60, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 112, 64, @on;
-- Audit Statement Permission Event
-- Occurs when a statement permission (such as CREATE TABLE) is used.
EXEC SP_TRACE_SETEVENT @TraceID, 113, 1, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 113, 6, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 113, 7, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 113, 8, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 113, 10, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 113, 11, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 113, 12, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 113, 14, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 113, 19, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 113, 23, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 113, 26, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 113, 35, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 113, 40, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 113, 41, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 113, 60, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 113, 64, @on;
-- Audit Backup/Restore Event
-- Occurs when a BACKUP or RESTORE command is issued.
EXEC SP_TRACE_SETEVENT @TraceID, 115, 1, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 115, 6, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 115, 7, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 115, 8, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 115, 10, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 115, 11, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 115, 12, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 115, 14, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 115, 23, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 115, 26, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 115, 35, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 115, 40, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 115, 41, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 115, 60, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 115, 64, @on;
-- Audit DBCC Event
-- Occurs when DBCC commands are issued.
EXEC SP_TRACE_SETEVENT @TraceID, 116, 1, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 116, 6, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 116, 7, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 116, 8, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 116, 10, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 116, 11, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 116, 12, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 116, 14, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 116, 23, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 116, 26, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 116, 35, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 116, 37, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 116, 40, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 116, 41, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 116, 44, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 116, 60, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 116, 64, @on;
-- Audit Change Audit Event
-- Occurs when audit trace modifications are made.
EXEC SP_TRACE_SETEVENT @TraceID, 117, 1, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 117, 6, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 117, 7, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 117, 8, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 117, 10, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 117, 11, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 117, 12, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 117, 14, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 117, 23, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 117, 26, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 117, 35, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 117, 37, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 117, 40, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 117, 41, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 117, 44, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 117, 60, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 117, 64, @on;
-- Audit Object Derived Permission Event
-- Occurs when a CREATE, ALTER, and DROP object commands are issued.
EXEC SP_TRACE_SETEVENT @TraceID, 118, 1, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 118, 6, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 118, 7, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 118, 8, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 118, 10, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 118, 11, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 118, 12, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 118, 14, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 118, 23, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 118, 26, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 118, 28, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 118, 34, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 118, 35, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 118, 37, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 118, 40, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 118, 41, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 118, 60, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 118, 64, @on;
-- Audit Database Management Event
-- Occurs when a CREATE, ALTER, or DROP statement executes on
-- database objects, such as schemas.
EXEC SP_TRACE_SETEVENT @TraceID, 128, 1, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 128, 6, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 128, 7, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 128, 8, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 128, 10, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 128, 11, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 128, 12, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 128, 14, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 128, 23, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 128, 26, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 128, 28, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 128, 34, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 128, 35, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 128, 37, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 128, 40, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 128, 41, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 128, 60, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 128, 64, @on;
-- Audit Database Object Management Event
-- Occurs when a CREATE, ALTER, or DROP statement executes on
-- database objects, such as schemas.
EXEC SP_TRACE_SETEVENT @TraceID, 129, 1, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 129, 6, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 129, 7, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 129, 8, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 129, 10, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 129, 11, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 129, 12, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 129, 14, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 129, 23, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 129, 26, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 129, 28, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 129, 34, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 129, 35, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 129, 37, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 129, 40, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 129, 41, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 129, 60, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 129, 64, @on;
-- Audit Database Principal Management Event
-- Occurs when principals, such as users, are created, altered, or
-- dropped from a database.
EXEC SP_TRACE_SETEVENT @TraceID, 130, 1, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 130, 6, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 130, 7, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 130, 8, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 130, 10, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 130, 11, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 130, 12, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 130, 14, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 130, 23, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 130, 26, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 130, 28, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 130, 34, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 130, 35, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 130, 37, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 130, 40, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 130, 41, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 130, 60, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 130, 64, @on;
-- Audit Schema Object Management Event
-- Occurs when server objects are created, altered, or dropped.
EXEC SP_TRACE_SETEVENT @TraceID, 131, 1, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 131, 6, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 131, 7, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 131, 8, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 131, 10, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 131, 11, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 131, 12, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 131, 14, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 131, 23, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 131, 26, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 131, 28, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 131, 34, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 131, 35, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 131, 37, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 131, 40, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 131, 41, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 131, 59, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 131, 60, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 131, 64, @on;
-- Audit Server Principal Impersonation Event
-- Occurs when there is an impersonation within server scope, such
-- as EXECUTE AS LOGIN.
EXEC SP_TRACE_SETEVENT @TraceID, 132, 1, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 132, 6, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 132, 7, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 132, 8, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 132, 10, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 132, 11, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 132, 12, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 132, 14, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 132, 23, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 132, 26, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 132, 28, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 132, 34, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 132, 35, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 132, 40, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 132, 41, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 132, 60, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 132, 64, @on;
-- Audit Database Principal Impersonation Event
-- Occurs when an impersonation occurs within the database scope,
-- such as EXECUTE AS USER or SETUSER.
EXEC SP_TRACE_SETEVENT @TraceID, 133, 1, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 133, 6, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 133, 7, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 133, 8, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 133, 10, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 133, 11, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 133, 12, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 133, 14, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 133, 23, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 133, 26, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 133, 28, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 133, 34, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 133, 35, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 133, 38, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 133, 40, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 133, 41, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 133, 60, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 133, 64, @on;
-- Audit Server Object Take Ownership Event
-- Occurs when the owner is changed for objects in server scope.
EXEC SP_TRACE_SETEVENT @TraceID, 134, 1, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 134, 6, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 134, 7, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 134, 8, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 134, 10, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 134, 11, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 134, 12, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 134, 14, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 134, 23, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 134, 26, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 134, 28, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 134, 34, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 134, 35, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 134, 37, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 134, 39, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 134, 40, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 134, 41, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 134, 42, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 134, 43, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 134, 60, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 134, 64, @on;
-- Audit Database Object Take Ownership Event
-- Occurs when a change of owner for objects within database scope
-- occurs.
EXEC SP_TRACE_SETEVENT @TraceID, 135, 1, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 135, 6, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 135, 7, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 135, 8, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 135, 10, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 135, 11, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 135, 12, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 135, 14, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 135, 23, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 135, 26, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 135, 28, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 135, 34, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 135, 35, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 135, 37, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 135, 39, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 135, 40, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 135, 41, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 135, 60, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 135, 64, @on;
-- Audit Change Database Owner
-- Occurs when ALTER AUTHORIZATION is used to change the owner of a
-- database and permissions are checked to do that.
EXEC SP_TRACE_SETEVENT @TraceID, 152, 1, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 152, 6, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 152, 7, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 152, 8, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 152, 10, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 152, 11, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 152, 12, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 152, 14, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 152, 23, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 152, 26, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 152, 35, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 152, 39, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 152, 40, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 152, 41, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 152, 42, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 152, 43, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 152, 60, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 152, 64, @on;
-- Audit Schema Object Take Ownership Event
-- Occurs when ALTER AUTHORIZATION is used to assign an owner to an
-- object and permissions are checked to do that.
EXEC SP_TRACE_SETEVENT @TraceID, 153, 1, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 153, 6, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 153, 7, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 153, 8, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 153, 10, @on;
EXEC SP_TRACE_SETEVENT @TraceID, 153, 11, @on;
V-41403 Updated
Findings ID: SQL2-00-014900 Rule ID: SV-53930r34_rule Severity: medium CCI: CCI-001499

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

Checks

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.

Fix

Configure a SQL Server timed job that automatically checks all system and user-defined Functions for being modified. If any Function is modified

(The supplemental file Track.sql, provided with this STIG
, can alert must be sent tobe used to establish a monitoring job. This should be supplemented with a process for informing the approperriate personnel for evaluation and possible action.. Other techniques for achieving the same ends, such as the use of DDL triggers, are acceptable.)
V-41404 Updated
Findings ID: SQL2-00-015100 Rule ID: SV-53931r34_rule Severity: medium CCI: CCI-001499

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

Checks

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.

Fix

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.)
V-41406 Updated
Findings ID: SQL2-00-015200 Rule ID: SV-53933r34_rule Severity: medium CCI: CCI-001499

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

Checks

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.

Fix

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.)
V-41407 No Change
Findings ID: SQL2-00-015600 Rule ID: SV-53935r2_rule Severity: medium CCI: CCI-001499

Discussion

SQL Server database ownership is a higher level privilege that grants full rights to everything in that database, including the right to grant privileges to others. SQL Server requires that the owner of a database object be a user, and only one user can be the assigned owner of a database object. This tends to minimize the risk that multiple users could gain unauthorized access, except the one individual who is the owner.

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. Unmanaged or uncontrolled ownership of databases can lead to unauthorized granting of privileges and database alterations.

Checks

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.

Run the following SQL query to determine SQL Server ownership of all database objects:
SELECT name AS 'Database name'
, SUSER_SNAME(owner_sid) AS 'Database Owner'
, state_desc AS 'Database state'
FROM sys.databases

Fix

Add and/or update system documentation to include any accounts authorized for object ownership and remove any account not authorized.

Reassign database ownership to authorized database owner account:
Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Databases >> right click <'database name'> >> Properties >> Files.
Select new database "Owner":
Navigate to click on […] >> Select new Database Owner >> Browse… >> click on box to indicate account >> <'OK'> >> <'OK'> >> <'OK'>
V-41409 No Change
Findings ID: SQL2-00-016900 Rule ID: SV-53937r2_rule Severity: medium CCI: CCI-000381

Discussion

Information systems are capable of providing a wide variety of functions and services. Some of the functions and services, provided by default, may not be necessary to support essential organizational operations (e.g., key missions, functions).

It is detrimental for applications to provide or install by default, functionality exceeding requirements or mission objectives. Examples include, but are not limited to, installing advertising software demonstrations or browser plug-ins not related to requirements or providing a wide array of functionality not required for every mission, yet cannot be disabled.

Applications must adhere to the principles of least functionality by providing only essential capabilities.

Unused and unnecessary SQL Server components increase the number of available attack vectors to SQL Server by introducing additional targets for attack. By minimizing the services and applications installed on the system, the number of potential vulnerabilities is reduced.

Checks

Review the list of components or optional features installed with SQL Server.
If optional features or components of SQL Server are installed, this is not a finding.

If unused components or features of SQL Server are installed, then review the system documentation to verify unused components or features are documented and authorized.
If any are not documented and authorized, this is a finding.

Fix

If any database components or objects of SQL Server are required for operation of applications that will be accessing SQL Server data or configuration, include them in the system documentation. If any unused components or objects of SQL Server are installed, uninstall or remove unused components or objects.
V-41411 Updated
Findings ID: SQL2-00-019300 Rule ID: SV-53939r45_rule Severity: medium CCI: CCI-0010192262

Discussion

When data is written to digital media, such as hard drives, mobile computers, external/removable hard drives, personal digital assistants, flash/thumb drives, etc., there is risk of data loss and/or compromise.

An organizational assessment of risk guides the selection of media and associated information contained on that media requiring restricted access. Organizations need to document, in policy and procedures, the media requiring restricted access, individuals authorized to access the media, and the specific measures taken to restrict access.

Fewer protection measures are needed for media containing information determined by the organization to be in the public domain, to be publicly releasable, or to have limited or no adverse impact if accessed by other than authorized personnel. In these situations, it is assumed the physical access controls where the media resides provide adequate protection.

As part of a defense-in-depth strategy, the organization considers routinely encrypting information at rest on selected secondary storage devices. The
employment of cryptography is at the discretion of the information owner/stewarddecision whether to employ cryptography is the responsibility of the information owner/steward, who exercises discretion within the framework of applicable rules, policies and law. The selection of the cryptographic mechanisms used is based upon maintaining the confidentiality and integrity of the information.

The strength of mechanisms is commensurate with the classification and sensitivity of the information.

Information at rest, when not encrypted, is open to compromise from attackers who have gained unauthorized access to the data files.

Checks

Review SQL Server's cryptographic settings to determine whether data stored in databases is encrypted according to organizational requirements and the system owner.

If all of the data on SQL Server is unclassified and encryption of information is not required, this requirement is NA.

Ensure the data is encrypted by executing:
USE <databse name>;
IF NOT EXISTS
(
SELECT 1
FROM sys.dm_database_encryption_keys
WHERE DB_NAME(database_id) = DB_NAME()
)
SELECT
DB_NAME() AS [Database Name],
'No database encryption key present, no encryption' AS [Encryption State]
ELSE
SELECT
DB_NAME(database_id) AS [Database Name],
CASE encryption_state
WHEN 0 THEN 'No database encryption key present, no encryption'
WHEN 1 THEN 'Unencrypted'
WHEN 2 THEN 'Encryption in progress'
WHEN 3 THEN 'Encrypted'
WHEN 4 THEN 'Key change in progress'
WHEN 5 THEN 'Decryption in progress'
WHEN 6 THEN 'Protection change in progress'
END AS [Encryption State]
FROM sys.dm_database_encryption_keys
WHERE DB_NAME(database_id) = DB_NAME()
;

If any database that is supposed to have encryption enabled is not listed as such, this is a finding.

If encryption is required by the information owner and an approved, NIST-certified cryptography is not used to encrypt stored sensitive information, this is a finding.

Verify all sensitive information is encrypted: entire database, tables, columns and/or data elements, as required by the organization and the system owner.

Fix

Use third-party tools or configure SQL Server to encrypt data stored in the database. Use only NIST-certified or NSA-approved cryptography to provide encryption.

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

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

Set SQL Server configuration settings to encrypt databases, tables, columns, and/or data elements as required by the organization and the system owner.

Document all instances of acceptance of risk by the information owner where sensitive or classified data is not encrypted. Have the ISSO document assurance that the unencrypted sensitive or classified information is otherwise inaccessible to those who do not have need-to-know access to the data. Developers should consider using a record-specific encryption method to protect individual records. For example, by employing the session username or other individualized element as part of the encryption key, then decryption of a data element is only possible by that user or other data accessible only by that user. Data labeling can be helpful in implementation. Consider applying additional auditing of access to any unencrypted sensitive or classified data when accessed by users (with and/or without a need to know).
V-41412 No Change
Findings ID: SQL2-00-019500 Rule ID: SV-53940r5_rule Severity: medium CCI: CCI-001144

Discussion

Cryptography is only as strong as the encryption modules/algorithms employed to encrypt the data.

Use of weak or untested encryption algorithms undermines the purposes of utilizing encryption to protect data.

Use of cryptography to provide confidentiality and non-repudiation is not effective unless strong methods are employed with its use. Many earlier encryption methods and modules have been broken and/or overtaken by increasing computing power. The NIST FIPS 140-2 cryptographic standards provide proven methods and strengths to employ cryptography effectively.

Detailed information on the NIST Cryptographic Module Validation Program (CMVP) is available at the following website: http://csrc.nist.gov/groups/STM/cmvp/index.html.

Checks

If encryption is not required for this database, this is not a finding.

Run the following SQL queries to review SQL Server's cryptographic settings for the database:

USE <database name> ;
IF NOT EXISTS
(
SELECT 1
FROM sys.dm_database_encryption_keys
WHERE DB_NAME(database_id) = DB_NAME()
)
SELECT
DB_NAME() AS [Database Name],
'No database encryption key present, no encryption' AS [Encryption State],
NULL AS [Algorithm],
NULL AS [Key Length]
ELSE
SELECT
DB_NAME(database_id) AS [Database Name],
CASE encryption_state
WHEN 0 THEN 'No database encryption key present, no encryption'
WHEN 1 THEN 'Unencrypted'
WHEN 2 THEN 'Encryption in progress'
WHEN 3 THEN 'Encrypted'
WHEN 4 THEN 'Key change in progress'
WHEN 5 THEN 'Decryption in progress'
WHEN 6 THEN 'Protection change in progress'
END AS [Encryption State],
key_algorithm AS [Algorithm],
key_length AS [Key Length]
FROM sys.dm_database_encryption_keys
WHERE DB_NAME(database_id) = DB_NAME()

SELECT DB_NAME() AS [Database], name, algorithm_desc
FROM sys.symmetric_keys
ORDER BY name, algorithm_desc;

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

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

If the encryption state indicates that the database is unencrypted, this is a finding.

Fix

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

Ensure the database is backed up.

Run the following SQL to undo encryption and drop the existing database encryption key:
USE master;
GO
ALTER DATABASE <database name> SET ENCRYPTION OFF;
GO
USE <database name> ;
GO
DROP DATABASE ENCRYPTION KEY;
GO

Run the following SQL to drop a server certificate from the SQL Server instance:
USE master;
GO
DROP CERTIFICATE <certificate name>;
GO

If applicable, run the following SQL to drop a symmetric key:
USE <database name>;
GO
DROP SYMMETRIC KEY <key name>;
GO

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

Run SQL along the lines of the following to import an externally-created server certificate (see Microsoft documentation for options and syntax details):
USE master;
GO
CREATE CERTIFICATE <certificate name>
FROM FILE = '<path\file_name>'
...
;
GO

Run the following SQL to create a database encryption key and encrypt the database:
USE <database name>;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE <certificate name>;
GO
USE master;
GO
ALTER DATABASE <database name> SET ENCRYPTION ON;
GO

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

If required, run the following SQL to create a symmetric key and assign an existing certificate:
USE <database name>;
GO
CREATE SYMMETRIC KEY <key name>
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE <certificate name>;
V-41415 No Change
Findings ID: SQL2-00-024100 Rule ID: SV-53944r3_rule Severity: medium CCI: CCI-001199

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

Checks

From the query prompt:
SELECT name
FROM [master].sys.databases
WHERE is_master_key_encrypted_by_server = 1
AND owner_sid <> 1
AND state = 0
If no databases are returned, this is not a finding.
For any databases returned, verify in the System Security Plan that encryption of the Database
Master Key using the Service Master Key is acceptable and approved by the Information Owner, and the encrypted data does not require additional protections to deter or detect DBA access. If not approved, this is a finding.

If approved and additional protections are required, then verify the additional requirements are in place in accordance with the System Security Plan. These may include additional auditing on access of the Database Master Key with alerts or other automated monitoring.

If the additional requirements are not in place, this is a finding.

Fix

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.
V-41416 No Change
Findings ID: SQL2-00-024200 Rule ID: SV-53945r2_rule Severity: medium CCI: CCI-001199

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.

Checks

From the query prompt:
SELECT COUNT(credential_id)
FROM [master].sys.master_key_passwords

If count is not 0, this is a finding.

Fix

Use the stored procedure sp_control_dbmasterkey_password to remove any credentials that
store Database Master Key passwords.
From the query prompt:
EXEC SP_CONTROL_DBMASTERKEY_PASSWORD @db_name = '[database name]', @action
= N'drop'
V-41417 Updated
Findings ID: SQL2-00-024300 Rule ID: SV-53946r45_rule Severity: medium CCI: CCI-001199

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

Checks

Run the query:
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.

Fix

Configure or alter symmetric keys to encrypt keys with certificates or authorized asymmetric keys:.
From the query prompt:
ALTER SYMMETRIC KEY [key name] ADD ENCRYPTION BY CERTIFICATE [certificate name]
ALTER SYMMETRIC KEY [key name] DROP ENCRYPTION BY [password, symmetric key or

asymmetric key]

The symmetric key must specify a certificate or asymmetric key for encryption.
V-41420 Updated
Findings ID: SQL2-00-021400 Rule ID: SV-53949r46_rule Severity: medium CCI: CCI-0012002476

Discussion

This control is intended to address the confidentiality and integrity of information at rest in non-mobile devices and covers user information and system information. If the data is not encrypted, it is subject to compromise and unauthorized disclosure.

Note: the system databases (master, msdb, model, resource and tempdb) cannot be encrypted.

The decision whether to employ cryptography is the responsibility of the information owner/steward, who exercises discretion within the framework of applicable rules, policies and law.

Checks

If this is a system database (master, msdb, resource, tempdb or model), this is not applicable (NA).

If the application owner and authorizing official have determined that the database does not require encryption, this is not a finding.

Ensure the data is encrypted by executing:
USE <database name>;
IF NOT EXISTS
(
SELECT 1
FROM sys.dm_database_encryption_keys
WHERE DB_NAME(database_id) = DB_NAME()
)
SELECT
DB_NAME() AS [Database Name],
'No database encryption key present, no encryption' AS [Encryption State]
ELSE
SELECT
DB_NAME(database_id) AS [Database Name],
CASE encryption_state
WHEN 0 THEN 'No database encryption key present, no encryption'
WHEN 1 THEN 'Unencrypted'
WHEN 2 THEN 'Encryption in progress'
WHEN 3 THEN 'Encrypted'
WHEN 4 THEN 'Key change in progress'
WHEN 5 THEN 'Decryption in progress'
WHEN 6 THEN 'Protection change in progress'
END AS [Encryption State]
FROM sys.dm_database_encryption_keys
WHERE DB_NAME(database_id) = DB_NAME()
;

For each user database, ensure that encryption is in effect. If not, this is a finding.

Fix

Use encryption to protect the data. To enable database encryption, create a master key, create a database encryption key, and protect it by using mechanisms tied to the master key, and then set encryption on.
V-41421 No Change
Findings ID: SQL2-00-021800 Rule ID: SV-53950r2_rule Severity: medium CCI: CCI-001090

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.

Checks

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.

Fix

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.
V-41422 No Change
Findings ID: SQL2-00-022000 Rule ID: SV-53951r2_rule Severity: medium CCI: CCI-001092

Discussion

Application management includes the ability to control the number of users and user sessions utilizing an application. Limiting the number of allowed users, and sessions per user, is helpful in limiting risks related to DoS attacks.

This requirement addresses concurrent session control for a single information system account and does not address concurrent sessions by a single user via multiple system accounts.

This requirement may be met via the application or by utilizing information system session control provided by a web server with specialized session management capabilities. If it has been specified that this requirement will be handled by the application, the capability to limit the maximum number of concurrent single user sessions must be designed and built into the application.

The organization will need to define the maximum number of concurrent sessions for SQL Server accounts by account type, by account, or a combination thereof and SQL Server shall enforce this requirement.

Unlimited concurrent connections to SQL Server could allow a successful DoS attack by exhausting connection resources.

Checks

Check SQL Server settings for the number of concurrent Check SQL Server settings for the number of concurrent sessions by running the following script:

USE MASTER
GO

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'user connections'
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO

If SQL Server settings for concurrent sessions is not lower than or equal to the organization-defined maximum number of sessions, this is a finding.

Fix

Configure SQL Server number of concurrent sessions to the organization-defined maximum number of sessions by running the following script:

USE MASTER
GO

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'user connections', <'maximum number of SQL Server concurrent connections'>
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO
V-41424 No Change
Findings ID: SQL2-00-022500 Rule ID: SV-53953r3_rule Severity: medium CCI: CCI-001310

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.

While this matter is of great importance to the secure operation of database management systems, the DBA in a typical installation will communicate with the application development/support staff to obtain assurance that this requirement is met.

Checks

Review SQL Server field definitions, constraints, and foreign keys to determine whether or not data being input into the database is valid.
If field definitions are not reflective of the data, this is a finding.

If column data types are not assigned correctly where required within the database, this is a finding.

If columns do not contain reasonable constraints based on column use, this is a finding.

Fix

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.
V-60671 No Change
Findings ID: SQL2-00-015620 Rule ID: SV-75113r1_rule Severity: medium CCI: CCI-001499

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 SQL2-00-010200, and disabled in accordance with SQL2-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.

Checks

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.

Fix

USE [master];
GO
ALTER DATABASE <name> SET TRUSTWORTHY OFF;
GO
V-60781 No Change
Findings ID: SQL2-00-015610 Rule ID: SV-75233r1_rule Severity: medium CCI: CCI-001499

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 SQL2-00-010200, and disabled in accordance with SQL2-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.

Checks

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.

Fix

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.
V-70627 Added
Findings ID: SQL2-00-017510 Rule ID: SV-85249r2_rule Severity: low CCI: CCI-000553

Discussion

It is important for the appropriate personnel to be aware if the system is at risk of failing to record transaction log data. The transaction log is the heart of a SQL Server database. If it fails, processing will stop. It must always have enough available storage space to cope with peak load. Administrators must be warned about abnormally high space consumption soon enough to take corrective action before all space is used up.

Checks

Review system documentation and/or organizational procedures to determine the threshold value for the storage used by the transaction log, above which staff must be alerted. The threshold may be expressed as an absolute quantity, or a percentage of total available space. If this threshold has not been defined, this is a finding. If monitoring software is in use, and has been configured to alert system and database administrators when the threshold is exceeded, this is not a finding. If manual procedures exist for frequently checking the space used and alerting system and database administrators, and there is evidence that the procedures are adhered to, this is not a finding. Otherwise, this is a finding.

Fix

Decide on, and document, the threshold value for alerting administrators to a shortage of storage for the transaction log. Establish automated or manual monitoring and alerting.
V-41413 Removed
Findings ID: SQL2-00-024000 Rule ID: SV-53942r3_rule Severity: medium CCI: CCI-001199

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.

Checks

From the query prompt: SELECT name FROM [master].sys.databases WHERE state = 0 Repeat for each database: From the query prompt: USE [database name] SELECT 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.

Fix

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.
V-41419 Removed
Findings ID: SQL2-00-024500 Rule ID: SV-53948r2_rule Severity: medium CCI: CCI-001199

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.

Checks

Review procedures for, and evidence of backup of, the SQL Server Service Master Key in the System Security Plan. If the procedures or evidence does not exist, this is a finding. If the procedures do not indicate offline and off-site storage of the Service Master Key, this is a finding. If procedures do not indicate access restrictions to the Service Master Key backup, this is a finding.

Fix

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'