MS SQL Server 2014 Database Security Technical Implementation Guide

U_MS_SQL_Server_2014_Database_STIG_V1R1_Manual-xccdf.xml

This Security Technical Implementation Guide is published as a tool to improve the security of Department of Defense (DoD) information systems. The requirements are derived from the National Institute of Standards and Technology (NIST) 800-53 and related documents. Comments or proposed revisions to this document should be sent via e-mail to the following address: [email protected]
Details

Version / Release: V1R1

Published: 2016-04-20

Updated At: 2018-09-23 04:04:47

Download

Filter

Findings
Severity Open Not Reviewed Not Applicable Not a Finding
Overall 0 0 0 0
Low 0 0 0 0
Medium 0 0 0 0
High 0 0 0 0
Drop CKL or SCAP (XCCDF) results here.
    Vuln Rule Version CCI Severity Title Description Status Finding Details Comments
    SV-81847r1_rule SQL4-00-002000 CCI-000213 MEDIUM SQL Server must enforce approved authorizations for logical access to information and database-level system resources in accordance with applicable access control policies. Authentication with a DoD-approved PKI certificate does not necessarily imply authorization to access the database and all its contents. To mitigate the risk of unauthorized access to sensitive information by entities that have been issued certificates by DoD-approved PKIs, all DoD systems, including SQL Server databases, must be properly configured to implement access control policies. Successful authentication must not automatically give an entity access to an asset or security boundary. Authorization procedures and controls must be implemented to ensure each authenticated entity also has a validated and current authorization. Authorization is the process of determining whether an entity, once authenticated, is permitted to access a specific asset. Information systems use access control policies and enforcement mechanisms to implement this requirement. Access control policies include identity-based policies, role-based policies, and attribute-based policies. Access enforcement mechanisms include access control lists, access control matrices, and cryptography. These policies and mechanisms must be employed by the application to control access between users (or processes acting on behalf of users) and objects (e.g., devices, files, records, processes, programs, and domains) in the information system. This requirement is applicable to access control enforcement applications, a category that includes SQL Server. If SQL Server is not configured to follow applicable policy when approving access, it may be in conflict with networks or other applications in the information system. This may result in users either gaining or being denied access inappropriately and in conflict with applicable policy.
    SV-81849r1_rule SQL4-00-011200 CCI-000169 MEDIUM SQL Server must generate Trace or Audit records for organization-defined auditable events. Audit records can be generated from various components within the information system (e.g., network interface, hard disk, modem, etc.). From an application perspective, certain specific application functionalities may be audited as well. The list of audited events is the set of events for which audits are to be generated. This set of events is typically a subset of the list of all events for which the system is capable of generating audit records. Examples are auditable events, time stamps, source and destination addresses, user/process identifiers, event descriptions, success/fail indications, file names involved, and access control or flow control rules invoked. Organizations define which application components shall provide auditable events. The DBMS must provide auditing for the list of events defined by the organization or risk negatively impacting forensic investigations into malicious behavior in the information system. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016.
    SV-81851r1_rule SQL4-00-011320 CCI-000171 MEDIUM Where SQL Server Audit is in use at the database level, SQL Server must allow only the ISSM (or individuals or roles appointed by the ISSM) to select which auditable events are to be audited at the database level. Without the capability to restrict which roles and individuals can select which events are audited, unauthorized personnel may be able to prevent or interfere with the auditing of critical events. Suppression of auditing could permit an adversary to evade detection. Misconfigured audits can degrade the system's performance by overwhelming the audit log. Misconfigured audits may also make it more difficult to establish, correlate, and investigate the events relating to an incident or identify those responsible for one. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016. This version of the requirement deals with SQL Server Audit-based audit trails.
    SV-81855r1_rule SQL4-00-014900 CCI-001499 MEDIUM SQL Server must be monitored to discover unauthorized changes to functions. When dealing with change control issues, it should be noted, any changes to the hardware, software, and/or firmware components of SQL Server and/or application can potentially have significant effects on the overall security of the system. If SQL Server were to allow any user to make changes to software libraries, then those changes might be implemented without undergoing the appropriate testing and approvals that are part of a robust change management process. This requirement is contingent upon the language in which the application is programmed, as many application architectures in use today incorporate their software libraries into, and make them inseparable from, their compiled distributions, rendering them static and version dependent. However, this requirement does apply to applications with software libraries accessible and configurable, as in the case of interpreted languages. Accordingly, only qualified and authorized individuals shall be allowed to obtain access to SQL Server components for purposes of initiating changes, including upgrades and modifications. Unmanaged changes that occur to the SQL Server software libraries or configuration, such as Functions, can lead to unauthorized or compromised installations.
    SV-81857r1_rule SQL4-00-015100 CCI-001499 MEDIUM SQL Server must be monitored to discover unauthorized changes to triggers. When dealing with change control issues, it should be noted, any changes to the hardware, software, and/or firmware components of SQL Server and/or application can potentially have significant effects on the overall security of the system. If SQL Server were to allow any user to make changes to software libraries, then those changes might be implemented without undergoing the appropriate testing and approvals that are part of a robust change management process. This requirement is contingent upon the language in which the application is programmed, as many application architectures in use today incorporate their software libraries into, and make them inseparable from, their compiled distributions, rendering them static and version dependent. However, this requirement does apply to applications with software libraries accessible and configurable, as in the case of interpreted languages. Accordingly, only qualified and authorized individuals shall be allowed to obtain access to SQL Server components for purposes of initiating changes, including upgrades and modifications. Unmanaged changes that occur to the SQL Server software libraries or configuration, such as Triggers, can lead to unauthorized or compromised installations.
    SV-81859r1_rule SQL4-00-015200 CCI-001499 MEDIUM SQL Server must be monitored to discover unauthorized changes to stored procedures. When dealing with change control issues, it should be noted, any changes to the hardware, software, and/or firmware components of SQL Server and/or application can potentially have significant effects on the overall security of the system. If SQL Server were to allow any user to make changes to software libraries, then those changes might be implemented without undergoing the appropriate testing and approvals that are part of a robust change management process. This requirement is contingent upon the language in which the application is programmed, as many application architectures in use today incorporate their software libraries into, and make them inseparable from, their compiled distributions, rendering them static and version dependent. However, this requirement does apply to applications with software libraries accessible and configurable, as in the case of interpreted languages. Accordingly, only qualified and authorized individuals shall be allowed to obtain access to SQL Server components for purposes of initiating changes, including upgrades and modifications. Unmanaged changes that occur to the SQL Server software libraries or configuration, such as Stored Procedures, can lead to unauthorized or compromised installations.
    SV-81861r1_rule SQL4-00-015600 CCI-001499 MEDIUM Database objects (including but not limited to tables, indexes, storage, stored procedures, functions, triggers, links to software external to SQL Server, etc.) must be owned by database/DBMS principals authorized for ownership. Within the database, object ownership implies full privileges to the owned object, including the privilege to assign access to the owned objects to other subjects. Database functions and procedures can be coded using definer's rights. This allows anyone who utilizes the object to perform the actions if they were the owner. If not properly managed, this can lead to privileged actions being taken by unauthorized individuals. Conversely, if critical tables or other objects rely on unauthorized owner accounts, these objects may be lost when an account is removed.
    SV-81863r1_rule SQL4-00-015620 CCI-001499 MEDIUM In a database owned by a login not having administrative privileges at the instance level, the database property TRUSTWORTHY must be OFF unless required and authorized. SQL Server's fixed (built-in) server roles, especially [sysadmin], have powerful capabilities that could cause great harm if misused, so their use must be tightly controlled. The SQL Server instance uses each database's TRUSTWORTHY property to guard against tampering that could enable unwarranted privilege escalation. When TRUSTWORTHY is 0/False/Off, SQL Server prevents the database from accessing resources in other databases. When TRUSTWORTHY is 1/True/On, SQL Server permits access to other databases (subject to other protections). SQL Server sets TRUSTWORTHY OFF when it creates a new database. SQL Server forces TRUSTWORTHY OFF, irrespective of its prior value, when an existing database is attached to it, to address the possibility that an adversary may have tampered with the database, introducing malicious code. To set TRUSTWORTHY ON, an account with the [sysadmin] role must issue an ALTER DATABASE command. Although SQL Server itself treats this property conservatively, application installer programs may set TRUSTWORTHY ON and leave it on. This provides an opportunity for misuse. When TRUSTWORTHY is ON, users of the database can take advantage of the database owner's privileges, by impersonating the owner. This can have particularly serious consequences if the database owner is the [sa] login (which may have been renamed in accordance with SQL4-00-010200, and disabled in accordance with SQL4-00-017100, but nonetheless can be invoked in an EXECUTE AS USER = 'dbo' statement, or CREATE PROCEDURE ... WITH EXECUTE AS OWNER ...). The [sa] login cannot be removed from the [sysadmin] role. The user impersonating [sa] - or another [sysadmin] account - is then able to perform administrative actions across all databases under the instance, including making any himself or any other login a member of [sysadmin]. Most of the other fixed server roles could be similarly abused. Therefore, TRUSTWORTHY must not be used on databases owned by logins that are members of the fixed server roles. Further, if TRUSTWORTHY is to be used for any other database, the need must be documented and approved. The system database [msdb] is an exception: it is required to be TRUSTWORTHY.
    SV-81865r1_rule SQL4-00-015610 CCI-001499 MEDIUM In a database owned by [sa], or by any other login having administrative privileges at the instance level, the database property TRUSTWORTHY must be OFF. SQL Server's fixed (built-in) server roles, especially [sysadmin], have powerful capabilities that could cause great harm if misused, so their use must be tightly controlled. The SQL Server instance uses each database's TRUSTWORTHY property to guard against tampering that could enable unwarranted privilege escalation. When TRUSTWORTHY is 0/False/Off, SQL Server prevents the database from accessing resources in other databases. When TRUSTWORTHY is 1/True/On, SQL Server permits access to other databases (subject to other protections). SQL Server sets TRUSTWORTHY OFF when it creates a new database. SQL Server forces TRUSTWORTHY OFF, irrespective of its prior value, when an existing database is attached to it, to address the possibility that an adversary may have tampered with the database, introducing malicious code. To set TRUSTWORTHY ON, an account with the [sysadmin] role must issue an ALTER DATABASE command. Although SQL Server itself treats this property conservatively, application installer programs may set TRUSTWORTHY ON and leave it on. This provides an opportunity for misuse. When TRUSTWORTHY is ON, users of the database can take advantage of the database owner's privileges, by impersonating the owner. This can have particularly serious consequences if the database owner is the [sa] login (which may have been renamed in accordance with SQL4-00-010200, and disabled in accordance with SQL4-00-017100, but nonetheless can be invoked in an EXECUTE AS USER = 'dbo' statement, or CREATE PROCEDURE ... WITH EXECUTE AS OWNER ...). The [sa] login cannot be removed from the [sysadmin] role. The user impersonating [sa] - or another [sysadmin] account - is then able to perform administrative actions across all databases under the instance, including making any himself or any other login a member of [sysadmin]. Most of the other fixed server roles could be similarly abused. Therefore, TRUSTWORTHY must not be used on databases owned by logins that are members of the fixed server roles. Further, if TRUSTWORTHY is to be used for any other database, the need must be documented and approved. The system database [msdb] is an exception: it is required to be TRUSTWORTHY.
    SV-81867r1_rule SQL4-00-021210 CCI-001665 MEDIUM In the event of a system failure, SQL Server must preserve any information necessary to return to operations with least disruption to mission processes. Discretionary Access Control (DAC) is based on the notion 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 table permissions. When discretionary access control policies are implemented, subjects are not constrained with regard to what actions they can take with information for which they have already been granted access. Thus, subjects that have been granted access to information are not prevented from passing (i.e., the subjects have the discretion to pass) the information to other subjects or objects. A subject that is constrained in its operation by Mandatory Access Control policies is still able to operate under the less rigorous constraints of this requirement. Thus, while Mandatory Access Control imposes constraints preventing a subject from passing information to another subject operating at a different sensitivity level, this requirement permits the subject to pass the information to any subject at the same sensitivity level. The policy is bounded by the information system boundary. Once the information is passed outside of the control of the information system, additional means may be required to ensure the constraints remain in effect. While the older, more traditional definitions of discretionary access control require identity-based access control, that limitation is not required for this use of discretionary access control.
    SV-81869r1_rule SQL4-00-024000 CCI-001199 MEDIUM The Database Master Key encryption password must meet DoD password complexity requirements. 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.
    SV-81871r1_rule SQL4-00-024100 CCI-001199 MEDIUM The Database Master Key must be encrypted by the Service Master Key, where a Database Master Key is required and another encryption method has not been specified. 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.
    SV-81873r1_rule SQL4-00-024200 CCI-001199 MEDIUM Database Master Key passwords must not be stored in credentials within the database. 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.
    SV-81875r1_rule SQL4-00-024300 CCI-001199 MEDIUM Symmetric keys must use a DoD certificate to encrypt the key. 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.
    SV-81877r1_rule SQL4-00-024500 CCI-001199 MEDIUM The Service Master Key must be backed up, stored offline and off-site. 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.
    SV-81879r1_rule SQL4-00-021800 CCI-001090 MEDIUM Database contents must be protected from unauthorized and unintended information transfer by enforcement of a data-transfer policy. 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.
    SV-81881r1_rule SQL4-00-022500 CCI-001310 MEDIUM SQL Server must check the validity of all data inputs except those specifically identified by the organization. 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.
    SV-81883r1_rule SQL4-00-031500 CCI-001310 MEDIUM The DBMS and associated applications must reserve the use of dynamic code execution for situations that require it. With respect to database management systems, one class of threat is known as SQL Injection, or more generally, code injection. It takes advantage of the dynamic execution capabilities of various programming languages, including dialects of SQL. In such cases, the attacker deduces the manner in which SQL statements are being processed, either from inside knowledge or by observing system behavior in response to invalid inputs. When the attacker identifies scenarios where SQL queries are being assembled by application code (which may be within the database or separate from it) and executed dynamically, the attacker is then able to craft input strings that subvert the intent of the query. Potentially, the attacker can gain unauthorized access to data, including security settings, and severely corrupt or destroy the database. The principal protection against code injection is not to use dynamic execution except where it provides necessary functionality that cannot be utilized otherwise. Use strongly typed data items rather than general-purpose strings as input parameters to task-specific, pre-compiled stored procedures and functions (and triggers).
    SV-81885r1_rule SQL4-00-031600 CCI-001310 MEDIUM The DBMS and associated applications, when making use of dynamic code execution, must scan input data for invalid values that may indicate a code injection attack. With respect to database management systems, one class of threat is known as SQL Injection, or more generally, code injection. It takes advantage of the dynamic execution capabilities of various programming languages, including dialects of SQL. In such cases, the attacker deduces the manner in which SQL statements are being processed, either from inside knowledge or by observing system behavior in response to invalid inputs. When the attacker identifies scenarios where SQL queries are being assembled by application code (which may be within the database or separate from it) and executed dynamically, the attacker is then able to craft input strings that subvert the intent of the query. Potentially, the attacker can gain unauthorized access to data, including security settings, and severely corrupt or destroy the database. The principal protection against code injection is not to use dynamic execution except where it provides necessary functionality that cannot be utilized otherwise. Use strongly typed data items rather than general-purpose strings as input parameters to task-specific, pre-compiled stored procedures and functions (and triggers). When dynamic execution is necessary, ways to mitigate the risk include the following, which should be implemented both in the on-screen application and at the database level, in the stored procedures: -- Allow strings as input only when necessary. -- Rely on data typing to validate numbers, dates, etc. Do not accept invalid values. If substituting other values for them, think carefully about whether this could be subverted. -- Limit the size of input strings to what is truly necessary. -- If single quotes/apostrophes, double quotes, semicolons, equals signs, angle brackets, or square brackets will never be valid as input, reject them. -- If comment markers will never be valid as input, reject them. In SQL, these are -- or /* */ -- If HTML and XML tags, entities, comments, etc., will never be valid, reject them. -- If wildcards are present, reject them unless truly necessary. In SQL these are the underscore and the percentage sign, and the word ESCAPE is also a clue that wildcards are in use. -- If SQL key words, such as SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, ESCAPE, UNION, GRANT, REVOKE, DENY, MODIFY will never be valid, reject them. Use case-insensitive comparisons when searching for these. Bear in mind that some of these words, particularly Grant (as a person's name), could also be valid input. -- If there are range limits on the values that may be entered, enforce those limits. -- Institute procedures for inspection of programs for correct use of dynamic coding, by a party other than the developer. -- Conduct rigorous testing of program modules that use dynamic coding, searching for ways to subvert the intended use. -- Record the inspection and testing in the system documentation. -- Bear in mind that all this applies not only to screen input, but also to the values in an incoming message to a web service or to a stored procedure called by a software component that has not itself been hardened in these ways. Not only can the caller be subject to such vulnerabilities; it may itself be the attacker.
    SV-81887r1_rule SQL4-00-022800 CCI-001312 MEDIUM The DBMS and associated applications must provide non-privileged users with error messages that provide information necessary for corrective actions without revealing information that could be exploited by adversaries. Any DBMS or associated application providing too much information in error messages on the screen or printout risks compromising the data and security of the system. The structure and content of error messages need to be carefully considered by the organization and development team. Databases can inadvertently provide a wealth of information to an attacker through improperly handled error messages. In addition to sensitive business or personal information, database errors can provide host names, IP addresses, user names, and other system information not required for end-user troubleshooting but very useful to someone targeting the system. Carefully consider the structure/content of error messages. The extent to which information systems are able to identify and handle error conditions is guided by organizational policy and operational requirements. Information that could be exploited by adversaries includes, for example, logon attempts with passwords entered by mistake as the username, mission/business information that can be derived from (if not stated explicitly by) information recorded, and personal information, such as account numbers, social security numbers, and credit card numbers. It is important that detailed error messages be visible only to those who are authorized to view them; that general users receive only generalized acknowledgment that errors have occurred; and that these generalized messages appear only when relevant to the user's task. For example, a message along the lines of, "An error has occurred. Unable to save your changes. If this problem persists, please contact your help desk" would be relevant. A message such as "Warning: your transaction generated a large number of page splits" would likely not be relevant. "ABGQ is not a valid widget code" would be appropriate; but "The INSERT statement conflicted with the FOREIGN KEY constraint "WidgetTransactionFK". The conflict occurred in database "DB7", table "dbo.WidgetMaster", column "WidgetCode" would not, as it reveals too much about the database structure.
    SV-81889r1_rule SQL4-00-022900 CCI-001314 MEDIUM SQL Server must reveal detailed error messages only to the ISSO, ISSM (or their designees), SA and DBA. If the DBMS provides too much information in error logs and administrative messages to the screen, this could lead to compromise. The structure and content of error messages need to be carefully considered by the organization and development team. The extent to which the information system is able to identify and handle error conditions is guided by organizational policy and operational requirements. Some default DBMS error messages can contain information that could aid an attacker in, among others things, identifying the database type, host address, or state of the database. Custom errors may contain sensitive customer information. It is important that detailed error messages be visible only to those who are authorized to view them; that general users receive only generalized acknowledgment that errors have occurred; and that these generalized messages appear only when relevant to the user's task. For example, a message along the lines of, "An error has occurred. Unable to save your changes. If this problem persists, please contact your help desk" would be relevant. A message such as "Warning: your transaction generated a large number of page splits" would likely not be relevant. "ABGQ is not a valid widget code" would be appropriate; but "The INSERT statement conflicted with the FOREIGN KEY constraint "WidgetTransactionFK". The conflict occurred in database "DB7", table "dbo.WidgetMaster", column "WidgetCode" would not, as it reveals too much about the database structure. Administrative users authorized to review detailed error messages typically are the ISSO, ISSM, SA and DBA. Other individuals or roles may be specified according to organization-specific needs, with appropriate approval.
    SV-81891r1_rule SQL4-00-031900 CCI-002262 MEDIUM When supporting applications that require security labeling of data, SQL Server must associate organization-defined types of security labels having organization-defined security label values with information in storage. Without the association of security labels to information, there is no basis for the DBMS to make security-related access-control decisions. Security labels are abstractions representing the basic properties or characteristics of an entity (e.g., subjects and objects) with respect to safeguarding information. These labels are typically associated with internal data structures (e.g., tables, rows) within the database and are used to enable the implementation of access control and flow control policies, reflect special dissemination, handling or distribution instructions, or support other aspects of the information security policy. One example includes marking data as classified or FOUO. These security labels may be assigned manually or during data processing, but, either way, it is imperative these assignments are maintained while the data is in storage. If the security labels are lost when the data is stored, there is the risk of a data compromise. SQL Server does not include security labeling as a standard or licensable feature. However, a community-developed SQL Server Label Security Toolkit can be downloaded from the Microsoft-supported web site codeplex.com. Other implementations may also exist. Custom application code is also a viable way to implement a solution.
    SV-81893r1_rule SQL4-00-032000 CCI-002263 MEDIUM When supporting applications that require security labeling of data, SQL Server must associate organization-defined types of security labels having organization-defined security label values with information in process. Without the association of security labels to information, there is no basis for the DBMS to make security-related access-control decisions. Security labels are abstractions representing the basic properties or characteristics of an entity (e.g., subjects and objects) with respect to safeguarding information. These labels are typically associated with internal data structures (e.g., tables, rows) within the database and are used to enable the implementation of access control and flow control policies, reflect special dissemination, handling or distribution instructions, or support other aspects of the information security policy. One example includes marking data as classified or FOUO. These security labels may be assigned manually or during data processing, but, either way, it is imperative these assignments are maintained while the data is in storage. If the security labels are lost when the data is stored, there is the risk of a data compromise. SQL Server does not include security labeling as a standard or licensable feature. However, a community-developed SQL Server Label Security Toolkit can be downloaded from the Microsoft-supported web site codeplex.com. Other implementations may also exist. Custom application code is also a viable way to implement a solution.
    SV-81895r1_rule SQL4-00-032100 CCI-002264 MEDIUM When supporting applications that require security labeling of data, SQL Server must associate organization-defined types of security labels having organization-defined security label values with information in transmission. Without the association of security labels to information, there is no basis for the DBMS to make security-related access-control decisions. Security labels are abstractions representing the basic properties or characteristics of an entity (e.g., subjects and objects) with respect to safeguarding information. These labels are typically associated with internal data structures (e.g., tables, rows) within the database and are used to enable the implementation of access control and flow control policies, reflect special dissemination, handling or distribution instructions, or support other aspects of the information security policy. One example includes marking data as classified or FOUO. These security labels may be assigned manually or during data processing, but, either way, it is imperative these assignments are maintained while the data is in storage. If the security labels are lost when the data is stored, there is the risk of a data compromise. SQL Server does not include security labeling as a standard or licensable feature. However, a community-developed SQL Server Label Security Toolkit can be downloaded from the Microsoft-supported web site codeplex.com. Other implementations may also exist. Custom application code is also a viable way to implement a solution.
    SV-81897r1_rule SQL4-00-033700 CCI-001889 MEDIUM Time stamps in database tables, intended for auditing or activity-tracking purposes, must include both date and time of day, with a minimum granularity of one second. If time stamps are not consistently applied and there is no common time reference, it is difficult to perform forensic analysis, in audit files, trace files/tables, and application data tables. Time stamps generated by SQL Server must include date and time, to a granularity of one second or finer. Time is commonly expressed in Coordinated Universal Time (UTC), a modern continuation of Greenwich Mean Time (GMT), or local time with an offset from UTC. Granularity of time measurements refers to the precision available in time stamp values. Granularity coarser than one second is not sufficient for audit trail purposes, and granularity finer than one second is recommended. Time stamp values are typically presented with three or more decimal places of seconds; however, the actual granularity may be coarser than the apparent precision. For example, SQL Server's GETDATE()/CURRENT_TMESTAMP values are presented to three decimal places, but the granularity is not one millisecond: it is about 1/300 of a second. The data types that can be used for this purpose in SQL Server are: DATETIME2 - precision variable from a whole second down to a ten-millionth (subject to the actual precision of the hardware and operating system) DATETIMEOFFSET - as datetime2, together with local offset from UTC DATE, together with TIME (same precision considerations as for datetime2) DATETIME - precision 1/300 of a second Character-string data types allowing for at least 20 characters are also permissible, but not recommended. SQL Server built-in functions for retrieving current timestamps are: (high precision) sysdatetime(), sysdatetimeoffset(), sysutcdatetime(); (lower precision) CURRENT_TIMESTAMP or getdate(), getutcdate(). Ensure that values recorded for tracking purposes in data tables are correctly defined and maintained. (Design decisions about which tables require audit-trail or activity-tracking columns are outside the scope of this STIG. This requirement applies only to the data type and maintenance of such columns if they do exist.) The SMALLDATETIME data type is not precise enough for this purpose. Although it gives the impression of including a seconds component, the seconds value is always "00". SQL Server offers a data type called TIMESTAMP that is not a representation of date and time. Rather, it is a database state counter and does not correspond to calendar and clock time. This requirement does not refer to that meaning of TIMESTAMP. To avoid confusion, Microsoft recommends using the newer name for this data type, ROWVERSION, instead.
    SV-81899r1_rule SQL4-00-034700 CCI-002475 MEDIUM SQL Server must implement and/or support cryptographic mechanisms to prevent unauthorized modification of organization-defined information at rest (to include, at a minimum, PII and classified information) on organization-defined information system components. Databases holding data requiring "data at rest" protections must employ cryptographic mechanisms to prevent unauthorized disclosure and modification of the information at rest. These cryptographic mechanisms may be native to the DBMS or implemented via additional software or operating system/file system settings, as appropriate to the situation. Selection of a cryptographic mechanism is based on the need to protect the integrity of organizational information. The strength of the mechanism is commensurate with the security category and/or classification of the information. Organizations have the flexibility to either encrypt all information on storage devices (i.e., full disk encryption) or encrypt specific data structures (e.g., files, records, or fields). The decision whether and what to encrypt rests with the data owner and is also influenced by the physical measures taken to secure the equipment and media on which the information resides.
    SV-81901r1_rule SQL4-00-035200 CCI-002754 MEDIUM When invalid inputs are received, SQL Server must behave in a predictable and documented manner that reflects organizational and system objectives. A common vulnerability is unplanned behavior when invalid inputs are received. This requirement guards against adverse or unintended system behavior caused by invalid inputs, where information system responses to the invalid input may be disruptive or cause the system to fail into an unsafe state. The behavior will be derived from the organizational and system requirements and includes, but is not limited to, notification of the appropriate personnel, creating an audit record, and rejecting invalid input.
    SV-81903r1_rule SQL4-00-035800 CCI-000172 MEDIUM Trace or Audit records must be generated when categorized information (e.g., classification levels/security levels) is accessed. Changes in categorized information must be tracked. Without an audit trail, unauthorized access to protected data could go undetected. For detailed information on categorizing information, refer to FIPS Publication 199, Standards for Security Categorization of Federal Information and Information Systems, and FIPS Publication 200, Minimum Security Requirements for Federal Information and Information Systems. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016. Note also that Trace does not support auditing of SELECT statements, whereas Audit does. Since Trace does not provide for tracking SELECT statements, it is necessary to provide this tracking at the application level, if Trace is used for audit purposes.
    SV-81905r1_rule SQL4-00-035900 CCI-000172 MEDIUM Trace or Audit records must be generated when unsuccessful attempts to access categorized information (e.g., classification levels/security levels) occur. Changes in categorized information must be tracked. Without an audit trail, unauthorized access to protected data could go undetected. To aid in diagnosis, it is necessary to keep track of failed attempts in addition to the successful ones. For detailed information on categorizing information, refer to FIPS Publication 199, Standards for Security Categorization of Federal Information and Information Systems, and FIPS Publication 200, Minimum Security Requirements for Federal Information and Information Systems. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016. Note also that Trace does not support auditing of SELECT statements, whereas Audit does. Since Trace does not provide for tracking SELECT statements, it is necessary to provide this tracking at the application level, if Trace is used for audit purposes. Use of SQL Server Audit's SCHEMA_OBJECT_ACCESS_GROUP causes capture of all accesses, successful and otherwise, to all schema-scoped objects. The [Succeeded] column in the audit output indicates the success or failure of the attempted action. Be aware, however, that it may report True in some cases where one would intuitively expect False. For example, SELECT 1/0 FROM SYS.ALL_OBJECTS will appear in the audit trail as successful, if the user has permission to perform that action, even though it contains an invalid expression. Some other actions that one would consider failures (such as selecting from a table that does not exist) may not appear at all.
    SV-81907r1_rule SQL4-00-036200 CCI-000172 MEDIUM SQL Server must generate Trace or Audit records when privileges/permissions are modified via locally-defined security objects. Changes in the permissions, privileges, and roles granted to users and roles must be tracked. Without an audit trail, unauthorized elevation or restriction of privileges could go undetected. Elevated privileges give users access to information and functionality that they should not have; restricted privileges wrongly deny access to authorized users. In SQL Server, there is no distinction between modification of permissions and granting or dropping them. However, native SQL Server security functionality may be supplemented with application-specific tables and logic, in which case the following actions on these tables and procedures/triggers/functions are also relevant: UPDATE EXECUTE Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016.
    SV-81909r1_rule SQL4-00-036300 CCI-000172 MEDIUM SQL Server must generate Trace or Audit records when unsuccessful attempts to modify privileges/permissions via locally-defined security objects occur. Failed attempts to change the permissions, privileges, and roles granted to users and roles must be tracked. Without an audit trail, unauthorized attempts to elevate or restrict privileges could go undetected. In SQL Server, there is no distinction between modification of permissions and granting or dropping them. However, native SQL Server security functionality may be supplemented with application-specific tables and logic, in which case the following actions on these tables and procedures/triggers/functions are also relevant: UPDATE EXECUTE To aid in diagnosis, it is necessary to keep track of failed attempts in addition to the successful ones. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016. Use of SQL Server Audit's SCHEMA_OBJECT_ACCESS_GROUP causes capture of all accesses, successful and otherwise, to the system views (and all other schema-scoped objects). The [Succeeded] column in the audit output indicates the success or failure of the attempted action. Be aware, however, that it may report True in some cases where one would intuitively expect False. For example, SELECT 1/0 FROM SYS.ALL_OBJECTS will appear in the audit trail as successful, if the user has permission to perform that action, even though it contains an invalid expression. Some other actions that one would consider failures (such as selecting from a table that does not exist) may not appear at all.
    SV-81911r1_rule SQL4-00-036400 CCI-000172 MEDIUM SQL Server must generate Trace or Audit records when locally-defined security objects are modified. SQL Server protects its built-in security objects (tables, views, functions, procedures, etc.) from alteration by database users and administrators. However, applications sometimes have additional, security-related objects defined in the database. ALTER operations on these objects must be monitored. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016.
    SV-81913r1_rule SQL4-00-038200 CCI-000172 MEDIUM SQL Server must generate Trace or Audit records when unsuccessful accesses to designated objects occur. Without tracking all or selected types of access to all or selected objects (tables, views, procedures, functions, etc.), it would be difficult to establish, correlate, and investigate the events relating to an incident or identify those responsible for one. Types of access include, but are not necessarily limited to: SELECT INSERT UPDATE DELETE EXECUTE To aid in diagnosis, it is necessary to keep track of failed attempts in addition to the successful ones. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016.
    SV-81915r1_rule SQL4-00-038100 CCI-000172 MEDIUM SQL Server must generate Trace or Audit records when successful accesses to designated objects occur. Without tracking all or selected types of access to all or selected objects (tables, views, procedures, functions, etc.), it would be difficult to establish, correlate, and investigate the events relating to an incident, or identify those responsible for one. Types of access include, but are not necessarily limited to: SELECT INSERT UPDATE DELETE EXECUTE Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016. Trace does not offer tracking of SELECT operations, so where this is required it must be implemented at the application level.
    SV-81917r1_rule SQL4-00-037400 CCI-000172 MEDIUM Trace or Audit records must be generated when unsuccessful attempts to delete categorized information (e.g., classification levels/security levels) occur. Changes in categorized information must be tracked. Without an audit trail, unauthorized access to protected data could go undetected. To aid in diagnosis, it is necessary to keep track of failed attempts in addition to the successful ones. For detailed information on categorizing information, refer to FIPS Publication 199, Standards for Security Categorization of Federal Information and Information Systems, and FIPS Publication 200, Minimum Security Requirements for Federal Information and Information Systems. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016. Since Trace does not provide for tracking SELECT statements, it is necessary to provide that part of the tracking at the application level. Because of this, it may also be appropriate to audit DELETE actions at the application level. However, to capture all DELETEs, whether they come from the application or bypass it, the Trace must be configured to cover them. Use of SQL Server Audit's SCHEMA_OBJECT_ACCESS_GROUP causes capture of all accesses, successful and otherwise, to all schema-scoped objects. The [Succeeded] column in the audit output indicates the success or failure of the attempted action. Be aware, however, that it may report True in some cases where one would intuitively expect False. For example, SELECT 1/0 FROM SYS.ALL_OBJECTS will appear in the audit trail as successful, if the user has permission to perform that action, even though it contains an invalid expression. Some other actions that one would consider failures (such as selecting from a table that does not exist) may not appear at all.
    SV-81919r1_rule SQL4-00-037300 CCI-000172 MEDIUM Trace or Audit records must be generated when categorized information (e.g., classification levels/security levels) is deleted. Changes in categorized information must be tracked. Without an audit trail, unauthorized access to protected data could go undetected. For detailed information on categorizing information, refer to FIPS Publication 199, Standards for Security Categorization of Federal Information and Information Systems, and FIPS Publication 200, Minimum Security Requirements for Federal Information and Information Systems. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016. Since Trace does not provide for tracking SELECT statements, it is necessary to provide that part of the tracking at the application level. Because of this, it may also be appropriate to audit DELETE actions at the application level. However, to capture all DELETEs, whether they come from the application or bypass it, the Trace must be configured to cover them.
    SV-81921r1_rule SQL4-00-037200 CCI-000172 MEDIUM SQL Server must generate Trace or Audit records when unsuccessful attempts to drop locally-defined security objects occur. SQL Server protects its built-in security objects (tables, views, functions, procedures, etc.) from alteration by database users and administrators. However, applications sometimes have additional, security-related objects defined in the database. DROP operations on these objects must be monitored. To aid in diagnosis, it is necessary to keep track of failed attempts in addition to the successful ones. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016. Use of SQL Server Audit's SCHEMA_OBJECT_CHANGE_GROUP causes capture of all attempts, successful and otherwise, to CREATE, ALTER, or DROP any schema-scoped objects. The [Succeeded] column in the audit output indicates the success or failure of the attempted action. Be aware, however, that it may report True in some cases where one would intuitively expect False.
    SV-81923r1_rule SQL4-00-037100 CCI-000172 MEDIUM SQL Server must generate Trace or Audit records when locally-defined security objects are dropped. SQL Server protects its built-in security objects (tables, views, functions, procedures, etc.) from alteration by database users and administrators. However, applications sometimes have additional, security-related objects defined in the database. DROP operations on these objects must be monitored. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016.
    SV-81925r1_rule SQL4-00-036500 CCI-000172 MEDIUM SQL Server must generate Trace or Audit records when unsuccessful attempts to modify locally-defined security objects occur. SQL Server protects its built-in security objects (tables, views, functions, procedures, etc.) from alteration by database users and administrators. However, applications sometimes have additional, security-related objects defined in the database. ALTER operations on these objects must be monitored. To aid in diagnosis, it is necessary to keep track of failed attempts in addition to the successful ones. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016. Use of SQL Server Audit's SCHEMA_OBJECT_CHANGE_GROUP causes capture of all attempts, successful and otherwise, to CREATE, ALTER, or DROP any schema-scoped objects. The [Succeeded] column in the audit output indicates the success or failure of the attempted action. Be aware, however, that it may report True in some cases where one would intuitively expect False.
    SV-81927r1_rule SQL4-00-036600 CCI-000172 MEDIUM Trace or Audit records must be generated when categorized information (e.g., classification levels/security levels) is created. Changes in categorized information must be tracked. Without an audit trail, unauthorized access to protected data could go undetected. For detailed information on categorizing information, refer to FIPS Publication 199, Standards for Security Categorization of Federal Information and Information Systems, and FIPS Publication 200, Minimum Security Requirements for Federal Information and Information Systems. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016. Since Trace does not provide for tracking SELECT statements, it is necessary to provide that part of the tracking at the application level. Because of this, it may also be appropriate to audit INSERT actions at the application level. However, to capture all INSERTs, whether they come from the application or bypass it, the Trace must be configured to cover them.
    SV-81929r1_rule SQL4-00-036800 CCI-000172 MEDIUM Trace or Audit records must be generated when unsuccessful attempts to create categorized information (e.g., classification levels/security levels) occur. Changes in categorized information must be tracked. Without an audit trail, unauthorized access to protected data could go undetected. To aid in diagnosis, it is necessary to keep track of failed attempts in addition to the successful ones. For detailed information on categorizing information, refer to FIPS Publication 199, Standards for Security Categorization of Federal Information and Information Systems, and FIPS Publication 200, Minimum Security Requirements for Federal Information and Information Systems. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016. Since Trace does not provide for tracking SELECT statements, it is necessary to provide that part of the tracking at the application level. Because of this, it may also be appropriate to audit INSERT actions at the application level. However, to capture all INSERTs, whether they come from the application or bypass it, the Trace must be configured to cover them. Use of SQL Server Audit's SCHEMA_OBJECT_ACCESS_GROUP causes capture of all accesses, successful and otherwise, to all schema-scoped objects. The [Succeeded] column in the audit output indicates the success or failure of the attempted action. Be aware, however, that it may report True in some cases where one would intuitively expect False; and some other actions that one would consider failures (such as selecting from a table that does not exist) may not appear at all.
    SV-81931r1_rule SQL4-00-036650 CCI-000172 MEDIUM Trace or Audit records must be generated when categorized information (e.g., classification levels/security levels) is modified. Changes in categorized information must be tracked. Without an audit trail, unauthorized access to protected data could go undetected. For detailed information on categorizing information, refer to FIPS Publication 199, Standards for Security Categorization of Federal Information and Information Systems, and FIPS Publication 200, Minimum Security Requirements for Federal Information and Information Systems. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016. Since Trace does not provide for tracking SELECT statements, it is necessary to provide that part of the tracking at the application level. Because of this, it may also be appropriate to audit UPDATE actions at the application level. However, to capture all UPDATEs, whether they come from the application or bypass it, the Trace must be configured to cover them.
    SV-81933r1_rule SQL4-00-036850 CCI-000172 MEDIUM Trace or Audit records must be generated when unsuccessful attempts to modify categorized information (e.g., classification levels/security levels) occur. Changes in categorized information must be tracked. Without an audit trail, unauthorized access to protected data could go undetected. For detailed information on categorizing information, refer to FIPS Publication 199, Standards for Security Categorization of Federal Information and Information Systems, and FIPS Publication 200, Minimum Security Requirements for Federal Information and Information Systems. Use of SQL Server Audit is recommended. All features of SQL Server Audit are available in the Enterprise and Developer editions of SQL Server 2014. It is not available at the database level in other editions. For this or legacy reasons, the instance may be using SQL Server Trace for auditing, which remains an acceptable solution for the time being. Note, however, that Microsoft intends to remove most aspects of Trace at some point after SQL Server 2016. Since Trace does not provide for tracking SELECT statements, it is necessary to provide that part of the tracking at the application level. Because of this, it may also be appropriate to audit UPDATE actions at the application level. However, to capture all UPDATEs, whether they come from the application or bypass it, the Trace must be configured to cover them. Use of SQL Server Audit's SCHEMA_OBJECT_ACCESS_GROUP causes capture of all accesses, successful and otherwise, to all schema-scoped objects. The [Succeeded] column in the audit output indicates the success or failure of the attempted action. Be aware, however, that it may report True in some cases where one would intuitively expect False. For example, SELECT 1/0 FROM SYS.ALL_OBJECTS will appear in the audit trail as successful, if the user has permission to perform that action, even though it contains an invalid expression. Some other actions that one would consider failures (such as selecting from a table that does not exist) may not appear at all.