{"stig":{"title":"MS SQL Server 2016 Database Security Technical Implementation Guide","version":"3","release":"5"},"checks":[{"vulnId":"V-213900","ruleId":"SV-213900r1043176_rule","severity":"high","ruleTitle":"SQL Server databases must integrate with an organization-level authentication/access mechanism providing account management and automation for all users, groups, roles, and any other principals.","description":"Enterprise environments make account management for applications and databases challenging and complex. A manual process for account management functions adds the risk of a potential oversight or other error. Managing accounts for the same person in multiple places is inefficient and prone to problems with consistency and synchronization.\n\nA comprehensive application account management process that includes automation helps to ensure that accounts designated as requiring attention are consistently and promptly addressed. \n\nExamples include, but are not limited to, using automation to take action on multiple accounts designated as inactive, suspended, or terminated, or by disabling accounts located in noncentralized account stores, such as multiple servers. Account management functions can also include: assignment of group or role membership; identifying account type; specifying user access authorizations (i.e., privileges); account removal, update, or termination; and administrative alerts. The use of automated mechanisms can include, for example: using email or text messaging to notify account managers when users are terminated or transferred; using the information system to monitor account usage; and using automated telephone notification to report atypical system account usage.\n\nSQL Server must be configured to automatically utilize organization-level account management functions, and these functions must immediately enforce the organization's current account policy. \n\nAutomation may be comprised of differing technologies that when placed together, contain an overall mechanism supporting an organization's automated account management requirements.","checkContent":"Determine if SQL Server is configured to allow the use of contained databases, if it is, take the appropriate precautions to limit their risk.\n\n1) In the Object Explorer in SQL Server Management Studio (SSMS), right-click on the server instance, select \"Properties\", and then select the \"Advanced\" page.\nIf \"Enabled Contained Databases\" is \"False\", this is not a finding.\n\n2) If \"Enabled Contained Databases\" is \"True\", then in a query interface such as the SSMS Transact-SQL editor, run the statement:\nEXEC sp_configure 'contained database authentication'\nIf the returned value in the \"config_value\" and/or \"run_value\" column is \"0\", this is not a finding.\n\n3) Determine whether SQL Server is configured to use only Windows authentication.\nIn a query interface such as the SSMS Transact-SQL editor, run the statement:\nSELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')  \n WHEN 1 THEN 'Windows Authentication'  \n WHEN 0 THEN 'Windows and SQL Server Authentication'  \nEND as [Authentication Mode]\nIf the returned value in the \"Authentication Mode\" column is \"Windows Authentication\", this is not a finding.\n\nIf mixed mode (both SQL Server authentication and Windows authentication) is in use, then it must be documented and approved.\n\nFrom the documentation, obtain the list of accounts authorized to be managed by SQL Server.\nDetermine the accounts (SQL Logins) actually managed by SQL Server.\n\nRun the statement:\n\nSELECT name\nFROM sys.database_principals\nWHERE type_desc = 'SQL_USER'\nAND authentication_type_desc = 'DATABASE';\n\nIf any accounts listed by the query are not listed in the documentation, this is a finding.\nDocumentation must be approved by the information system security officer (ISSO)/ information system security manager (ISSM).","fixText":"If mixed mode is required, document the need and justification; describe the measures taken to ensure the use of SQL Server authentication is kept to a minimum; describe the measures taken to safeguard passwords; list or describe the SQL Logins used; and obtain official approval.\n\nIf mixed mode is not required, disable it as follows: \n\nIn the SSMS Object Explorer, right-click on the server instance, select Properties >> Security page. Click the radio button for \"Windows Authentication Mode\", and then click \"OK\".\n\nRestart the SQL Server instance. \n\nOR\n\nRun the statement: \n\nUSE [master]\nEXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\\Microsoft\\MSSQLServer\\MSSQLServer', N'LoginMode', REG_DWORD, 2\nGO\n\nRestart the SQL Server instance. \n\nFor each account being managed by SQL Server but not requiring it, drop or disable the SQL Database user. Replace it with an appropriately configured account, as needed.\n\nTo drop a User in the SSMS Object Explorer: \n\nNavigate to Databases >> Security Users. Right-click on the User name, and then click \"Delete\".\n\nTo drop a User via a query: \nUSE database_name;\nDROP USER <user_name>;","ccis":["CCI-000015"]},{"vulnId":"V-213901","ruleId":"SV-213901r1137654_rule","severity":"high","ruleTitle":"SQL Server must enforce approved authorizations for logical access to database information and system resources in accordance with applicable access control policies.","description":"Authentication with a DoD-approved PKI certificate does not necessarily imply authorization to access SQL Server. 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 databases, must be properly configured to implement access control policies. \n\nSuccessful 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. \n\nAccess 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. \n\nThis requirement is applicable to access control enforcement applications, a category that includes database management systems.  If SQL Server does not 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.","checkContent":"Review the system documentation to determine the required levels of protection for securables in the database by type of login. \n\nIf the database is tempdb, this is not applicable.\n\nReview the permissions actually in place in the database. \n\nIf the actual permissions do not match the documented requirements, this is a finding.\n\nUse the supplemental file \"Database permission assignments to users and roles.sql\".","fixText":"Use GRANT, REVOKE, DENY, ALTER ROLE … ADD MEMBER … and/or ALTER ROLE …. DROP MEMBER statements to add and remove permissions on database-level securables, bringing them into line with the documented requirements.","ccis":["CCI-000213"]},{"vulnId":"V-213902","ruleId":"SV-213902r960864_rule","severity":"medium","ruleTitle":"SQL Server must protect against a user falsely repudiating by ensuring only clearly unique Active Directory user accounts can connect to the database.","description":"Non-repudiation of actions taken is required in order to maintain data integrity. Examples of particular actions taken by individuals include creating information, sending a message, approving information (e.g., indicating concurrence or signing a contract), and receiving a message. \n\nNon-repudiation protects against later claims by a user of not having created, modified, or deleted a particular data item or collection of data in the database.\n\nIn designing a database, the organization must define the types of data and the user actions that must be protected from repudiation. The implementation must then include building audit features into the application data tables and configuring the DBMS's audit tools to capture the necessary audit trail. Design and implementation also must ensure that applications pass individual user identification to the DBMS, even where the application connects to the DBMS with a standard, shared account.\n\nIf the computer account of a remote computer is granted access to a SQL Server database, any service or scheduled task running as NT AUTHORITY\\SYSTEM or NT AUTHORITY\\NETWORK SERVICE can log into the instance and perform actions. These actions cannot be traced back to a specific user or process.","checkContent":"Execute the following query:\n\nSELECT name\nFROM sys.database_principals\nWHERE type in ('U','G')\nAND name LIKE '%$'\n\nIf no users are returned, this is not a finding.\n\nIf users are returned, determine whether each user is a computer account.\n\nLaunch PowerShell.\n\nExecute the following code:\n\nNote: <name> represents the username portion of the user. For example; if the user is \"CONTOSO\\user1$\", the username is \"user1\".\n\n([ADSISearcher]\"(&(ObjectCategory=Computer)(Name=<name>))\").FindAll()\n\nIf no account information is returned, this is not a finding.\n\nIf account information is returned, this is a finding.","fixText":"Remove all users that were returned in the check SQL Statement:\n\nSELECT name\nFROM sys.database_principals\nWHERE type in ('U','G')\nAND name LIKE '%$'\n\nTo remove users:\n\nRun the following command for each user:\n\nDROP USER [ IF EXISTS ] <user_name>;","ccis":["CCI-000166"]},{"vulnId":"V-213903","ruleId":"SV-213903r960864_rule","severity":"low","ruleTitle":"SQL Server must protect against a user falsely repudiating by use of system-versioned tables (Temporal Tables).","description":"Non-repudiation of actions taken is required in order to maintain data integrity. Examples of particular actions taken by individuals include creating information, sending a message, approving information (e.g., indicating concurrence or signing a contract), and receiving a message.\n\nNon-repudiation protects against later claims by a user of not having created, modified, or deleted a particular data item or collection of data in the database. \n\nIn designing a database, the organization must define the types of data and the user actions that must be protected from repudiation. The implementation must then include building audit features into the application data tables and configuring SQL Server's audit tools to capture the necessary audit trail. Design and implementation also must ensure that applications pass individual user identification to SQL Server, even where the application connects to SQL Server with a standard, shared account. \n\nApplications should use temporal tables to track the changes and history of sensitive data.","checkContent":"Check the server documentation to determine if collecting and keeping historical versions of a table is required.\n\nIf collecting and keeping historical versions of a table is NOT required, this is not a finding.\n\nFind all of the temporal tables in the database using the following query:\n\nSELECT SCHEMA_NAME(T.schema_id) AS schema_name, T.name AS table_name, T.temporal_type_desc, SCHEMA_NAME(H.schema_id) + '.' + H.name AS history_table\nFROM sys.tables T\nJOIN sys.tables H ON T.history_table_id = H.object_id\nWHERE T.temporal_type != 0\nORDER BY schema_name, table_name\n\nUsing the system documentation, determine which tables are required to be temporal tables.\n\nIf any tables listed in the documentation are not in the list created by running the above statement, this is a finding.\n\nEnsure a field exists documenting the login and/or user who last modified the record. \n\nIf this does not exist, this is a finding.","fixText":"Alter sensitive tables to utilize system versioning.\n\nAlter non-temporal table to define periods for system versioning .\n\nALTER TABLE InsurancePolicy \nADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime),   \nSysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL DEFAULT GETUTCDATE(),  \nSysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.99999999');  \n \nALTER TABLE InsurancePolicy SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.InsurancePolicyHistory));\n\nhttps://docs.microsoft.com/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-2016#system_versionin","ccis":["CCI-000166"]},{"vulnId":"V-213904","ruleId":"SV-213904r960864_rule","severity":"medium","ruleTitle":"SQL Server must protect against a user falsely repudiating by ensuring databases are not in a trust relationship.","description":"Non-repudiation of actions taken is required in order to maintain data integrity. Examples of particular actions taken by individuals include creating information, sending a message, approving information (e.g., indicating concurrence or signing a contract), and receiving a message. \n\nNon-repudiation protects against later claims by a user of not having created, modified, or deleted a particular data item or collection of data in the database.\n\nSQL Server provides the ability for high privileged accounts to impersonate users in a database using the TRUSTWORTHY feature. This will allow members of the fixed database role to impersonate any user within the database.","checkContent":"If the database being reviewed is MSDB, trustworthy is required to be enabled, and therefore this is not a finding.\n\nExecute the following query:\n\nSELECT SUSER_SNAME(d.owner_sid) AS DatabaseOwner,\nCASE\nWHEN d.is_trustworthy_on = 0 THEN 'No'\nWHEN d.is_trustworthy_on = 1 THEN 'Yes'\nEND AS IsTrustworthy,\nCASE\nWHEN role.name IN ('sysadmin','securityadmin')\nOR permission.permission_name = 'CONTROL SERVER'\nTHEN 'YES'\nELSE 'No'\nEND AS 'IsOwnerPrivileged'\nFROM sys.databases d\nLEFT JOIN sys.server_principals login ON d.owner_sid = login.sid\nLEFT JOIN sys.server_role_members rm ON login.principal_id = rm.member_principal_id\nLEFT JOIN sys.server_principals role ON rm.role_principal_id = role.principal_id\nLEFT JOIN sys.server_permissions permission ON login.principal_id = permission.grantee_principal_id\nWHERE d.name = DB_NAME()\n\nIf trustworthy is not enabled, this is not a finding.\n\nIf trustworthy is enabled and the database owner is not a privileged account, this is not a finding.\n\nIf trustworthy is enabled and the database owner is a privileged account, review the system documentation to determine if the trustworthy property is required and authorized. If this is not documented, this is a finding.","fixText":"Disable trustworthy on the database.\n\nALTER DATABASE [<database name>] SET TRUSTWORTHY OFF","ccis":["CCI-000166"]},{"vulnId":"V-213905","ruleId":"SV-213905r960882_rule","severity":"medium","ruleTitle":"SQL Server must allow only the ISSM (or individuals or roles appointed by the ISSM) to select which auditable events are to be audited.","description":"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.\n\nSuppression of auditing could permit an adversary to evade detection.\n\nMisconfigured 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.","checkContent":"Obtain the list of approved audit maintainers from the system documentation.\n\nUse the following query to review database roles and their membership, all of which enable the ability to create and maintain audit specifications.\n\nSELECT\n    R.name AS role_name,\n    RM.name AS role_member_name,\n    RM.type_desc\nFROM sys.database_principals R\nJOIN sys.database_role_members DRM ON \n    R.principal_id = DRM.role_principal_id\nJOIN sys.database_principals RM ON \n    DRM.member_principal_id = RM.principal_id\nWHERE R.type = 'R'\n    AND R.name = 'db_owner'\nORDER BY \n    role_member_name\n\nIf any role memberships are not documented and authorized, this is a finding.\t\n\n\nReview the database roles and individual users that have the following permissions, all of which enable the ability to create and maintain audit definitions.\n\nALTER ANY DATABASE AUDIT\nCONTROL\n\nUse the following query to determine the roles and users that have the listed permissions:\n\nSELECT\n\tPERM.permission_name,\n\tDP.name AS principal_name,\n\tDP.type_desc AS principal_type,\n\tDBRM.role_member_name\nFROM sys.database_permissions PERM\nJOIN sys.database_principals DP ON PERM.grantee_principal_id = DP.principal_id\nLEFT OUTER JOIN (\n\tSELECT\n\t\tR.principal_id AS role_principal_id,\n\t\tR.name AS role_name,\n\t\tRM.name AS role_member_name\n\tFROM sys.database_principals R\n\tJOIN sys.database_role_members DRM ON R.principal_id = DRM.role_principal_id\n\tJOIN sys.database_principals RM ON DRM.member_principal_id = RM.principal_id\n\tWHERE R.type = 'R'\n) DBRM ON DP.principal_id = DBRM.role_principal_id\nWHERE PERM.permission_name IN ('CONTROL','ALTER ANY DATABASE AUDIT')\nORDER BY\n\tpermission_name, \n\tprincipal_name, \n\trole_member_name\n\n\nIf any of the roles or users returned have permissions that are not documented, or the documented audit maintainers do not have permissions, this is a finding.","fixText":"Create a database role specifically for audit maintainers, and give it permission to maintain audits, without granting it unnecessary permissions (The role name used here is an example; other names may be used.):\n\nCREATE ROLE DATABASE_AUDIT_MAINTAINERS;\nGO\n\nGRANT ALTER ANY DATABASE AUDIT TO DATABASE_AUDIT_MAINTAINERS;\nGO\n\nUse REVOKE and/or DENY and/or ALTER ROLE ... DROP MEMBER ... statements to remove the ALTER ANY DATABASE AUDIT permission from all users.  Then, for each authorized database user, run the statement:  \n\nALTER ROLE DATABASE_AUDIT_MAINTAINERS ADD MEMBER;\nGO\n\nUse REVOKE and/or DENY and/or ALTER SERVER ROLE ... DROP MEMBER ... statements to remove CONTROL DATABASE permission from logins that do not need it.","ccis":["CCI-000171"]},{"vulnId":"V-213907","ruleId":"SV-213907r1167464_rule","severity":"medium","ruleTitle":"SQL Server must limit privileges to change software modules, to include stored procedures, functions, and triggers, and links to software external to SQL Server.","description":"If the system 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.\n\nAccordingly, only qualified and authorized individuals shall be allowed to obtain access to information system components for purposes of initiating changes, including upgrades and modifications.\n\nUnmanaged changes that occur to the database software libraries or configuration can lead to unauthorized or compromised installations.","checkContent":"Obtain a listing of schema ownership from the server documentation.\n\nExecute the following query to obtain a current listing of schema ownership.\n\nSELECT s.name AS schema_name, p.name AS owning_principal\n  FROM sys.schemas s\n  JOIN sys.database_principals p ON s.principal_id = p.principal_id\n WHERE p.name != 'dbo'\n   AND (s.name != p.name\n        or p.name not in\n         ( 'db_accessadmin'\n         , 'db_backupoperator'\n         , 'db_datareader'\n         , 'db_datawriter'\n         , 'db_ddladmin'\n         , 'db_denydatareader'\n         , 'db_denydatawriter'\n         , 'db_owner'\n         , 'db_securityadmin'\n         , 'guest'\n         , 'INFORMATION_SCHEMA'\n         , 'sys'\n         , 'TargetServersRole'\n         , 'SQLAgentUserRole'\n         , 'SQLAgentReaderRole'\n         , 'SQLAgentOperatorRole'\n         , 'DatabaseMailUserRole'\n         , 'db_ssisadmin'\n         , 'db_ssisltduser'\n         , 'db_ssisoperator'\n         , 'replmonitor'\n         , '##MS_SSISServerCleanupJobLogin##'\n         )\n       )\n ORDER BY schema_name\n\nIf any schema is owned by an unauthorized database principal, this is a finding.","fixText":"Transfer ownership of database schemas to authorized database principals.\n\nALTER AUTHORIZATION ON SCHEMA::[<Schema Name>] TO [<Principal Name>]","ccis":["CCI-001499"]},{"vulnId":"V-213908","ruleId":"SV-213908r960960_rule","severity":"medium","ruleTitle":"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.","description":"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.\n\nConversely, if critical tables or other objects in SQL Server rely on unauthorized owner accounts, these objects may be lost when an account is removed.","checkContent":"Review system documentation to identify SQL Server accounts authorized to own database objects. \n\nIf the SQL Server database ownership list does not exist or needs to be updated, this is a finding. \n\nThe following query can be of use in making this determination: \n\n;with objects_cte as\n(SELECT o.name, o.type_desc,\n   CASE\n    WHEN o.principal_id is null then s.principal_id\n     ELSE o.principal_id\n    END as principal_id\n FROM sys.objects o\n INNER JOIN sys.schemas s\n ON o.schema_id = s.schema_id\n WHERE o.is_ms_shipped = 0\n)\nSELECT cte.name, cte.type_desc, dp.name as ObjectOwner \nFROM objects_cte cte\nINNER JOIN sys.database_principals dp\nON cte.principal_id = dp.principal_id\nORDER BY dp.name, cte.name\n\nIf any of the listed owners is not authorized, this is a finding.","fixText":"Add and/or update system documentation to include any accounts authorized for object ownership and remove any account not authorized. \n\nTo change the schema owning a database object in SQL Server, use this code as an example: \n\nUSE AdventureWorks2012;  \nGO  \nALTER SCHEMA HumanResources TRANSFER Person.Address;  \nGO  \n\nCaution: This can break code. This Fix should be implemented in conjunction with corrections to such code. Test before deploying in production. Deploy during a scheduled maintenance window.","ccis":["CCI-001499"]},{"vulnId":"V-213909","ruleId":"SV-213909r1167467_rule","severity":"medium","ruleTitle":"The role(s)/group(s) used to modify database structure (including but not necessarily limited to tables, indexes, storage, etc.) and logic modules (stored procedures, functions, triggers, links to software external to SQL Server, etc.) must be restricted to authorized users.","description":"If SQL Server were to allow any user to make changes to database structure or logic, those changes might be implemented without undergoing the appropriate testing and approvals that are part of a robust change management process.\n\nAccordingly, only qualified and authorized individuals must be allowed to obtain access to information system components for purposes of initiating changes, including upgrades and modifications.\n\nUnmanaged changes that occur to the database software libraries or configuration can lead to unauthorized or compromised installations.\n\nDBMS functionality and the nature and requirements of databases will vary; so, while users are not permitted to install unapproved software, there may be instances where the organization allows the user to install approved software packages such as from an approved software repository. The requirements for production servers will be more restrictive than those used for development and research.\n\nThe DBMS must enforce software installation by users based on what types of software installations are permitted (e.g., updates and security patches to existing software) and what types of installations are prohibited (e.g., software whose pedigree regarding being potentially malicious is unknown or suspect) by the organization. \n\nIn the case of a database management system, this requirement covers stored procedures, functions, triggers, views, etc.\n\nSatisfies: SRG-APP-000133-DB-000362, SRG-APP-000133-DB-000179, SRG-APP-000378-DB-000365","checkContent":"Obtain a listing of users and roles who are authorized to create, alter, or replace logic modules from the server documentation.\n\nExecute the following query:\nSELECT P.type_desc AS principal_type, P.name AS principal_name, O.type_desc,\n   CASE class\n    WHEN 0 THEN DB_NAME()\n    WHEN 1 THEN OBJECT_SCHEMA_NAME(major_id) + '.' + OBJECT_NAME(major_id)\n    WHEN 3 THEN SCHEMA_NAME(major_id)\n      ELSE class_desc + '(' + CAST(major_id AS nvarchar) + ')'\n   END AS securable_name, DP.state_desc, DP.permission_name\nFROM sys.database_permissions DP\nJOIN sys.database_principals P ON DP.grantee_principal_id = P.principal_id\nLEFT OUTER JOIN sys.all_objects O ON O.object_id = DP.major_id AND O.type IN ('TR','TA','P','X','RF','PC','IF','FN','TF','U')\nWHERE DP.type IN ('AL','ALTG') AND DP.class IN (0, 1, 53)\n\nSELECT R.name AS role_name, M.type_desc AS principal_type, M.name AS principal_name\nFROM sys.database_principals R\nJOIN sys.database_role_members DRM ON R.principal_id = DRM.role_principal_id\nJOIN sys.database_principals M ON DRM.member_principal_id = M.principal_id\nWHERE R.name IN ('db_ddladmin','db_owner')\nAND M.name != 'dbo'\n\nIf any users or role permissions returned are not authorized to modify the specified object or type, this is a finding.\n\nIf any user or role membership is not authorized, this is a finding.","fixText":"Document and obtain approval for any nonadministrative users who require the ability to modify database structure and logic modules.\n\nREVOKE ALTER ON [<Object Name>] TO [<Principal Name>]","ccis":["CCI-001499"]},{"vulnId":"V-213910","ruleId":"SV-213910r961125_rule","severity":"medium","ruleTitle":"In the event of a system failure, hardware loss or disk failure, SQL Server must be able to restore necessary databases with least disruption to mission processes.","description":"Failure to a known state can address safety or security in accordance with the mission/business needs of the organization. Failure to a known secure state helps prevent a loss of confidentiality, integrity, or availability in the event of a failure of the information system or a component of the system. In the event of a system failure, SQL Server must be able to bring the database back to a consistent state.","checkContent":"Review the system security plan (SSP) to determine whether the database is static, the recovery model to be used, the backup schedule, and the plan for testing database restoration.\n\nIf the SSP does not state that the database is static, assume that it is not static. If any of the other information is absent, this is a finding. \n\nIf the database is not static, and the documented recovery model is Bulk Logged, but the justification and authorization for this are not documented, this is a finding. \n\nRun the following to determine Recovery Model:\n\nUSE [master]\nGO\n\nSELECT name, recovery_model_desc\nFROM sys.databases\nORDER BY name\n\nIf the recovery model description does not match the documented recovery model, this is a finding. \n\nReview the jobs set up to implement the backup plan. If they are absent, this is a finding. \n\nCheck the history of the backups by running the following query.  It checks the last 30 days of backups by database.\nUSE [msdb]\nGO\n\nSELECT database_name, \n   CASE type\n    WHEN 'D' THEN 'Full'\n    WHEN 'I' THEN 'Differential'\n    WHEN 'L' THEN 'Log'\n   ELSE type\n   END AS backup_type,\n is_copy_only,\n backup_start_date, backup_finish_date\nFROM dbo.backupset\nWHERE backup_start_date >= dateadd(day, - 30, getdate()) \nORDER BY database_name, backup_start_date DESC\n\nIf the history indicates a pattern of job failures by missing or gaps in backups, this is a finding. \n\nReview evidence that database recovery is tested annually or more often, and that the most recent test was successful. If not, this is a finding.","fixText":"Modify the system security plan, to include whether the database is static, the correct recovery model to be used, the backup schedule, and the plan for testing database restoration. \n\nIn SQL Server Management Studio, Object Explorer, right-click on the name of the database; select Properties. Select the Options page. Set the Recovery Model field, near the top of the page, to the correct value. \n\nIn Object Explorer, expand >> SQL Server Agent >> Jobs. Create, modify, and delete jobs to implement the backup schedule. (Alternatively, this may done using T-SQL code or Third-party Backup software.) \n\nCorrect any issues that have been causing backups to fail. \n\nTest the restoration of the database at least once a year; correct any issues that cause it to fail. Maintain a record of these tests.","ccis":["CCI-001665"]},{"vulnId":"V-213911","ruleId":"SV-213911r961128_rule","severity":"medium","ruleTitle":"The Database Master Key encryption password must meet DOD password complexity requirements.","description":"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.","checkContent":"From the query prompt: \n\nSELECT name \nFROM [master].sys.databases \nWHERE state = 0 \n\nRepeat for each database: \nFrom the query prompt: \nUSE [database name] \nSELECT COUNT(name) \nFROM sys.symmetric_keys s, sys.key_encryptions k \nWHERE s.name = '##MS_DatabaseMasterKey##' \nAND s.symmetric_key_id = k.key_id \nAND k.crypt_type in ('ESKP', 'ESP2', 'ESP3')\n\nIf the value returned is zero, this is not applicable.\n\nIf the value returned is greater than zero, a Database Master Key exists and is encrypted with a password. \n\nReview procedures and evidence of password requirements used to encrypt Database Master Keys. \n\nIf 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.","fixText":"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. \n\nTo change the Database Master Key encryption password: \n\nUSE [database name]; \nALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = '[new password]'; \n\nNote: 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.","ccis":["CCI-001199"]},{"vulnId":"V-213912","ruleId":"SV-213912r961128_rule","severity":"medium","ruleTitle":"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.","description":"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.","checkContent":"If no databases require encryption, this is not a finding. \n\nFrom the query prompt: \n\nSELECT name \nFROM [master].sys.databases \nWHERE is_master_key_encrypted_by_server = 1 \nAND owner_sid <> 1 \nAND state = 0; \n(Note that this query assumes that the [sa] account is not used as the owner of application databases, in keeping with other STIG guidance. If this is not the case, modify the query accordingly.) \n\nIf no databases are returned by the query, this is not a finding. \n\nFor 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. \n\nIf 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. \n\nIf the additional requirements are not in place, this is a finding.","fixText":"Where possible, encrypt the Database Master Key with a password known only to the application administrator.\n\nWhere 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.","ccis":["CCI-001199"]},{"vulnId":"V-213913","ruleId":"SV-213913r961128_rule","severity":"medium","ruleTitle":"The Certificate used for encryption must be backed up and stored in a secure location that is not on the SQL Server.","description":"Backup and recovery of the Certificate used for encryption is critical to the complete recovery of the database. Not having this key can lead to loss of data during recovery.","checkContent":"If the application owner and authorizing official have determined that encryption of data at rest is not required, this is not a finding.\n\nReview procedures for and evidence of backup of the Certificate used for encryption in the System Security Plan. \n\nIf the procedures or evidence does not exist, this is a finding. \n\nIf the procedures do not indicate that a backup of the Certificate used for encryption is stored in a secure location that is not on the SQL Server, this is a finding. \n\nIf procedures do not indicate access restrictions to the Certificate backup, this is a finding.","fixText":"Document and implement procedures to safely back up and store the Certificate used for encryption in a secure location that is not on the SQL Server. Include in the procedures to establish evidence of backup and storage as well as careful, restricted access and restoration of the Certificate.\n\nBACKUP CERTIFICATE 'CertificateName' TO FILE = 'path_to_file' \nWITH PRIVATE KEY (FILE = 'path_to_pvk', ENCRYPTION BY PASSWORD = 'password'); \n\nAs this requires a password, ensure it is not exposed to unauthorized persons or stored as plain text.","ccis":["CCI-001199"]},{"vulnId":"V-213914","ruleId":"SV-213914r961131_rule","severity":"low","ruleTitle":"SQL Server must isolate security functions from non-security functions.","description":"An isolation boundary provides access control and protects the integrity of the hardware, software, and firmware that perform security functions. \n\nSecurity functions are the hardware, software, and/or firmware of the information system responsible for enforcing the system security policy and supporting the isolation of code and data on which the protection is based.\n\nDevelopers and implementers can increase the assurance in security functions by employing well-defined security policy models; structured, disciplined, and rigorous hardware and software development techniques; and sound system/security engineering principles. \n\nDatabase Management Systems typically separate security functionality from non-security functionality via separate databases or schemas. Database objects or code implementing security functionality should not be commingled with objects or code implementing application logic. When security and non-security functionality are commingled, users who have access to non-security functionality may be able to access security functionality.","checkContent":"Determine elements of security functionality (lists of permissions, additional authentication information, stored procedures, application specific auditing, etc.) that are being housed inside SQL server.\n\nFor any elements found, check SQL Server to determine if these objects or code implementing security functionality are located in a separate security domain, such as a separate database, schema, or table created specifically for security functionality.\n\nIf the database is a SQL Server default database (master, msdb, model, tempdb), this is NA.\n\nRun the following query to list all the user-defined databases:\n\nSELECT Name \nFROM sys.databases \nWHERE database_id > 4 \nORDER BY 1;\n\nReview the database structure to determine where security related functionality is stored. If security-related database objects or code are not kept separate, this is a finding.","fixText":"Check the server documentation, locate security-related database objects and code in a separate database, schema, table, or other separate security domain from database objects and code implementing application logic.  \n\nMicrosoft SQL Server 2005 introduced the concept of database object schemas. Schemas are analogous to separate namespaces or containers used to store database objects. Security permissions apply to schemas, making them an important tool for separating and protecting database objects based on access rights. Schemas reduce the work required, and improve the flexibility, for security-related administration of a database.\n\nUser-schema separation allows for more flexibility in managing database object permissions. A schema is a named container for database objects, which allows the user to group objects into separate namespaces.\n\nWhere possible, locate security-related database objects and code in a separate database, schema, or other separate security domain from database objects and code implementing application logic. In all cases, use GRANT, REVOKE, DENY, ALTER ROLE … ADD MEMBER … and/or ALTER ROLE …. DROP MEMBER statements to add and remove permissions on server-level and database-level security-related objects to provide effective isolation.","ccis":["CCI-001084"]},{"vulnId":"V-213915","ruleId":"SV-213915r1137656_rule","severity":"medium","ruleTitle":"Database contents must be protected from unauthorized and unintended information transfer by enforcement of a data-transfer policy.","description":"Applications, including DBMSs, must prevent unauthorized and unintended information transfer via shared system resources. \n\nData used for the development and testing of applications often involves copying data from production. It is important that specific procedures exist for this process, to include the conditions under which such transfer may take place, where the copies may reside, and the rules for ensuring sensitive data are not exposed.\n\nCopies of sensitive data must not be misplaced or left in a temporary location without the proper controls.","checkContent":"Review the procedures for the refreshing of development/test data from production. Review any scripts or code that exists for the movement of production data to development/test systems, or to any other location or for any other purpose. Verify that copies of production data are not left in unprotected locations. If the code that exists for data movement does not comply with the organization-defined data transfer policy and/or fails to remove any copies of production data from unprotected locations, this is a finding.","fixText":"Modify any code used for moving data from production to development/test systems to comply with the organization-defined data transfer policy, and to ensure copies of production data are not left in unsecured locations.","ccis":["CCI-001090"]},{"vulnId":"V-213916","ruleId":"SV-213916r961158_rule","severity":"medium","ruleTitle":"SQL Server must check the validity of all data inputs except those specifically identified by the organization.","description":"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.\n\nWith 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. Potentially, the attacker can gain unauthorized access to data, including security settings, and severely corrupt or destroy the database.\n\nEven when no such hijacking takes place, invalid input that gets recorded in the database, whether accidental or malicious, reduces the reliability and usability of the system. Available protections include data types, referential constraints, uniqueness constraints, range checking, and application-specific logic. Application-specific logic can be implemented within the database in stored procedures and triggers, where appropriate.\n\nThis calls for inspection of application source code, which will require collaboration with the application developers. It is recognized that in many cases, the database administrator (DBA) is organizationally separate from the application developers, and may have limited, if any, access to source code. Nevertheless, protections of this type are so important to the secure operation of databases that they must not be ignored. At a minimum, the DBA must attempt to obtain assurances from the development organization that this issue has been addressed, and must document what has been discovered.","checkContent":"Review DBMS code (stored procedures, functions, triggers), application code, settings, column and field definitions, and constraints to determine whether the database is protected against invalid input. \n\nIf code exists that allows invalid data to be acted upon or input into the database, this is a finding. \n\nIf column/field definitions are not reflective of the data, this is a finding. \n\nIf columns/fields do not contain constraints and validity checking where required, this is a finding. \n\nWhere a column/field is noted in the system documentation as necessarily free-form, even though its name and context suggest that it should be strongly typed and constrained, the absence of these protections is not a finding. \n\nWhere a column/field is clearly identified by name, caption or context as Notes, Comments, Description, Text, etc., the absence of these protections is not a finding.","fixText":"Use parameterized queries, constraints, foreign keys, etc. to validate data input. \n\nModify SQL Server to properly use the correct column data types as required in the database.","ccis":["CCI-001310"]},{"vulnId":"V-213917","ruleId":"SV-213917r961167_rule","severity":"medium","ruleTitle":"SQL Server must provide non-privileged users with error messages that provide information necessary for corrective actions without revealing information that could be exploited by adversaries.","description":"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.\n\nDatabases 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 troubleshooting but very useful to someone targeting the system.\n\nCarefully 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.","checkContent":"Review application behavior and custom database code (stored procedures, triggers), to determine whether error messages contain information beyond what is needed for explaining the issue to general users.\n\nIf database error messages contain PII data, sensitive business data, or information useful for identifying the host system or database structure, this is a finding.","fixText":"Adjust database code to remove any information not required for explaining the error to an end user.\n\nConsider enabling trace flag 3625 to mask certain system-level error information returned to non-administrative users.\n\nLaunch SQL Server Configuration Manager >> Click SQL Services >> Open the instance properties >> Click the Service Parameters tab >> Enter \"-T3625\" >> Click Add >> Click OK >> Restart SQL instance.","ccis":["CCI-001312"]},{"vulnId":"V-213919","ruleId":"SV-213919r1167469_rule","severity":"medium","ruleTitle":"SQL Server must associate organization-defined types of security labels having organization-defined security label values with information in process, transit, or storage.","description":"Without the association of security labels to information, there is no basis for SQL Server to make security-related access-control decisions.\n\nSecurity labels are abstractions representing the basic properties or characteristics of an entity (e.g., subjects and objects) with respect to safeguarding information.\n\nThese 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.\n\nOne example includes marking data as classified or CUI. 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.\n\nThe mechanism used to support security labeling may be a feature of SQL Server, a third-party product, or custom application code.\n\nSatisfies: SRG-APP-000313-DB-000309, SRG-APP-000311-DB-000308, SRG-APP-000314-DB-000310","checkContent":"If security labeling is not required, this is not a finding.\n\nIf security labeling requirements have been specified, but neither a third-party solution nor a SQL Server Row-Level security solution is implemented that reliably maintains labels on information, this is a finding.","fixText":"Deploy SQL Server Row-Level Security (see link below) or a third-party software, or add custom data structures, data elements and application code, to provide reliable security labeling of information in process.\n\nhttps://msdn.microsoft.com/en-us/library/dn765131.aspx","ccis":["CCI-002263"]},{"vulnId":"V-213921","ruleId":"SV-213921r961317_rule","severity":"low","ruleTitle":"SQL Server must enforce discretionary access control policies, as defined by the data owner, over defined subjects and objects.","description":"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.\n\nWhen 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. \n\nA 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. \n\nThe 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.","checkContent":"Review system documentation to determine requirements for object ownership and authorization delegation.\n\nUse the following query to discover database object ownership:\n\nSchemas not owned by the schema or dbo:\n\nSELECT name AS schema_name, USER_NAME(principal_id) AS schema_owner\nFROM sys.schemas\nWHERE schema_id != principal_id\n AND principal_id != 1\n\nObjects owned by an individual principal:\n\nSELECT object_id, name AS securable, \n  USER_NAME(principal_id) AS object_owner,\n  type_desc\nFROM sys.objects\nWHERE is_ms_shipped = 0 AND principal_id IS NOT NULL\nORDER BY type_desc, securable, object_owner\n\nUse the following query to discover database users who have been delegated the right to assign additional permissions:\n\nSELECT U.type_desc, U.name AS grantee,\n DP.class_desc AS securable_type,\n   CASE DP.class\n    WHEN 0 THEN DB_NAME()\n    WHEN 1 THEN OBJECT_NAME(DP.major_id) \n    WHEN 3 THEN SCHEMA_NAME(DP.major_id)\n   ELSE CAST(DP.major_id AS nvarchar)\n   END AS securable,\n       permission_name, state_desc\nFROM sys.database_permissions DP\nJOIN sys.database_principals U ON DP.grantee_principal_id = U.principal_id\nWHERE DP.state = 'W'\nORDER BY grantee, securable_type, securable\n\nIf any of these rights are not documented and authorized, this is a finding.","fixText":"To correct object ownership:\n\nALTER AUTHORIZATION ON <Securable> TO <Principal>\n\nTo revoke any unauthorized permissions:\n\nREVOKE [Permission] ON <Securable> TO <Principal>","ccis":["CCI-002165"]},{"vulnId":"V-213922","ruleId":"SV-213922r1167471_rule","severity":"medium","ruleTitle":"Execution of stored procedures and functions that utilize execute as must be restricted to necessary cases only.","description":"In certain situations, to provide required functionality, a DBMS needs to execute internal logic (stored procedures, functions, triggers, etc.) and/or external code modules with elevated privileges. However, if the privileges required for execution are at a higher level than the privileges assigned to organizational users invoking the functionality applications/programs, those users are indirectly provided with greater privileges than assigned by organizations.\n\nPrivilege elevation must be utilized only where necessary and protected from misuse.","checkContent":"Review the system documentation to obtain a listing of stored procedures and functions that utilize impersonation. Execute the following query:\n\nSELECT S.name AS schema_name, O.name AS module_name,\nUSER_NAME(\nCASE M.execute_as_principal_id\nWHEN -2 THEN COALESCE(O.principal_id, S.principal_id)\nELSE M.execute_as_principal_id\nEND\n) AS execute_as\nFROM sys.sql_modules M\nJOIN sys.objects O ON M.object_id = O.object_id\nJOIN sys.schemas S ON O.schema_id = S.schema_id\nWHERE execute_as_principal_id IS NOT NULL\nand       O.name not in \n(             \n'fn_sysdac_get_username',\n                             'fn_sysutility_ucp_get_instance_is_mi',\n                             'sp_send_dbmail',\n                             'sp_SendMailMessage',\n                             'sp_syscollector_create_collection_set',\n                             'sp_syscollector_delete_collection_set',\n                             'sp_syscollector_disable_collector',\n                             'sp_syscollector_enable_collector',\n                             'sp_syscollector_get_collection_set_execution_status',\n                             'sp_syscollector_run_collection_set',\n                             'sp_syscollector_start_collection_set',\n                             'sp_syscollector_update_collection_set',\n                             'sp_syscollector_upload_collection_set',\n                             'sp_syscollector_verify_collector_state',\n                             'sp_syspolicy_add_policy',\n                             'sp_syspolicy_add_policy_category_subscription',\n                             'sp_syspolicy_delete_policy',\n                             'sp_syspolicy_delete_policy_category_subscription',\n                             'sp_syspolicy_update_policy',\n                             'sp_sysutility_mi_add_ucp_registration',\n                             'sp_sysutility_mi_disable_collection',\n                             'sp_sysutility_mi_enroll',\n                             'sp_sysutility_mi_initialize_collection',\n                             'sp_sysutility_mi_remove',\n                             'sp_sysutility_mi_remove_ucp_registration',\n                             'sp_sysutility_mi_upload',\n                             'sp_sysutility_mi_validate_enrollment_preconditions',\n                             'sp_sysutility_ucp_add_mi',\n                             'sp_sysutility_ucp_add_policy',\n                             'sp_sysutility_ucp_calculate_aggregated_dac_health',\n                             'sp_sysutility_ucp_calculate_aggregated_mi_health',\n                             'sp_sysutility_ucp_calculate_computer_health',\n                             'sp_sysutility_ucp_calculate_dac_file_space_health',\n                             'sp_sysutility_ucp_calculate_dac_health',\n                             'sp_sysutility_ucp_calculate_filegroups_with_policy_violations',\n                             'sp_sysutility_ucp_calculate_health',\n                             'sp_sysutility_ucp_calculate_mi_file_space_health',\n                             'sp_sysutility_ucp_calculate_mi_health',\n                             'sp_sysutility_ucp_configure_policies',\n                             'sp_sysutility_ucp_create',\n                             'sp_sysutility_ucp_delete_policy',\n                             'sp_sysutility_ucp_delete_policy_history',\n                             'sp_sysutility_ucp_get_policy_violations',\n                             'sp_sysutility_ucp_initialize',\n                             'sp_sysutility_ucp_initialize_mdw',\n                             'sp_sysutility_ucp_remove_mi',\n                             'sp_sysutility_ucp_update_policy',\n                             'sp_sysutility_ucp_update_utility_configuration',\n                             'sp_sysutility_ucp_validate_prerequisites',\n                             'sp_validate_user',\n                             'syscollector_collection_set_is_running_update_trigger',\n                             'sysmail_help_status_sp'\n)\n\nORDER BY schema_name, module_name\n\nIf any procedures or functions are returned that are not documented, this is a finding.","fixText":"Alter stored procedures and functions to remove the \"EXECUTE AS\" statement.","ccis":["CCI-002233"]},{"vulnId":"V-213924","ruleId":"SV-213924r961461_rule","severity":"medium","ruleTitle":"SQL Server must enforce access restrictions associated with changes to the configuration of the database(s).","description":"Failure to provide logical access restrictions associated with changes to configuration may have significant effects on the overall security of the system. \n\nWhen dealing with access restrictions pertaining to change control, it should be noted that any changes to the hardware, software, and/or firmware components of the information system can potentially have significant effects on the overall security of the system. \n\nAccordingly, only qualified and authorized individuals should be allowed to obtain access to system components for the purposes of initiating changes, including upgrades and modifications.","checkContent":"Execute the following query to obtain a listing of user databases whose owner is a member of a fixed server role:\n\n SELECT \n              D.name AS database_name, SUSER_SNAME(D.owner_sid) AS owner_name,\n              FRM.is_fixed_role_member\nFROM sys.databases D\nOUTER APPLY (\n              SELECT MAX(fixed_role_member) AS is_fixed_role_member\n              FROM (\n                            SELECT IS_SRVROLEMEMBER(R.name, SUSER_SNAME(D.owner_sid)) AS fixed_role_member\n                            FROM sys.server_principals R\n                            WHERE is_fixed_role = 1\n              ) A\n) FRM\nWHERE D.database_id > 4\n              AND (FRM.is_fixed_role_member = 1 \n                            OR FRM.is_fixed_role_member IS NULL)\nORDER BY database_name \n\nIf no databases are returned, this is not a finding. \n\nFor each database/login returned, review the Server Role memberships \n\n1.\tIn SQL Server Management Studio, Expand “Logins”\n2.\tDouble-click the name of the Login\n3.\tClick the “Server Roles” tab \n\nIf any server roles are selected, but not documented and authorized, this is a finding.","fixText":"Remove unauthorized users from roles:\n\nALTER ROLE DROP MEMBER user;\n\nhttps://msdn.microsoft.com/en-us/library/ms189775.aspx\n\nSet the owner of the database to an authorized login:\n\nALTER AUTHORIZATION ON database::DatabaseName TO login;\n\nhttps://msdn.microsoft.com/en-us/library/ms187359.aspx","ccis":["CCI-001813"]},{"vulnId":"V-213926","ruleId":"SV-213926r1196261_rule","severity":"high","ruleTitle":"SQL Server must implement 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.","description":"Database management systems (DBMSs) handling 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 SQL Server or implemented via additional software or operating system/file system settings, as appropriate to the situation.\n\nSelection 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). \n\nThe 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.\n\nSatisfies: SRG-APP-000428-DB-000386, SRG-APP-000429-DB-000387","checkContent":"Review the system documentation to determine whether the organization has defined the information at rest be protected from modification, which must include at a minimum, PII and classified information. \n\nIf no information is identified as requiring such protection, this is not a finding. \n\nReview the configuration of SQL Server, Windows, and additional software as relevant. \n\nIf full-disk encryption is required, and Windows or the storage system is not configured for this, this is a finding. \n\nIf database transparent data encryption (TDE) is called for, verify it is enabled: \n\nSELECT db.name AS DatabaseName, db.is_encrypted AS IsEncrypted,\nCASE\nWHEN dm.encryption_state = 0 THEN 'No database encryption key present, no encryption'\nWHEN dm.encryption_state = 1 THEN 'Unencrypted'\nWHEN dm.encryption_state = 2 THEN 'Encryption in progress'\nWHEN dm.encryption_state = 3 THEN 'Encrypted'\nWHEN dm.encryption_state = 4 THEN 'Key change in progress'\nWHEN dm.encryption_state = 5 THEN 'Decryption in progress'\nWHEN dm.encryption_state = 6 THEN 'Protection change in progress'\nEND AS EncryptionState,\ndm.encryption_state AS EncryptionState,\ndm.key_algorithm AS KeyAlgorithm,\ndm.key_length AS KeyLength\nFROM sys.databases db\nLEFT OUTER JOIN sys.dm_database_encryption_keys dm ON db.database_id = dm.database_id\nWHERE db.database_id NOT IN (1,2,3,4)\n\nFor each user database for which encryption is called for and that is marked Unencrypted, this is a finding. \n\nIf table/column encryption and/or a separation between those who own the data (and can view it) and those who manage the data (but should have no access) is required for PII or similar types of data, use Always Encrypted. The details for configuring Always Encrypted are located here: https://msdn.microsoft.com/en-us/library/mt163865.aspx.\n\nReview the definitions and contents of the relevant tables/columns for the Always Encrypted settings. If any of the information that requires cryptographic protection is not encrypted, this is a finding.","fixText":"Where full-disk encryption is required, configure Windows and/or the storage system to provide this. \n\nWhere transparent data encryption (TDE) is required, create a master key, obtain a certificate protected by the master key, create a database encryption key and protect it by the certificate, and then set the database to use encryption. For guidance from MSDN on how to do this: https://msdn.microsoft.com/en-us/library/bb934049.aspx. \n\nWhere table/column encryption is required, enable encryption on the tables/columns in question. For guidance from the Microsoft Developer Network on how to do this with Always Encrypted: https://msdn.microsoft.com/en-us/library/mt163865.aspx.","ccis":["CCI-002475"]},{"vulnId":"V-251040","ruleId":"SV-251040r1117186_rule","severity":"high","ruleTitle":"SQL Server must use NSA-approved cryptography to protect classified information in accordance with the data owners requirements.","description":"Use of weak or untested encryption algorithms undermines the purposes of utilizing encryption to protect data. The application must implement cryptographic modules adhering to the higher standards approved by the federal government since this provides assurance they have been tested and validated.\n\nIt is the responsibility of the data owner to assess the cryptography requirements in light of applicable federal laws, Executive Orders, directives, policies, regulations, and standards.\n\nNSA-approved cryptography for classified networks is hardware based. This requirement addresses the compatibility of a DBMS with the encryption devices.","checkContent":"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.\n\nReview system documentation to determine whether cryptography for classified or sensitive information is required by the information owner.\n\nIf the system documentation does not specify the type of information hosted on SQL Server as classified, sensitive, and/or unclassified, this is a finding.\n\nIf neither classified nor sensitive information exists within SQL Server databases or configuration, this is not a finding.\n\nVerify that Windows is configured to require the use of FIPS-compliant algorithms.\n\nClick \"Start\",  enter \"Local Security Policy\", and then press \"Enter\". Expand \"Local Policies\", select \"Security Options\", and then locate \"System Cryptography: Use FIPS compliant algorithms for encryption, hashing, and signing\".\n\nIf the Security Setting for this option is \"Disabled\", this is a finding.\n\nNote: The list of acceptable algorithms is \"AES 256\" and \"Triple DES\".\n\nIf cryptography is being used by SQL Server, verify that the cryptography is NIST FIPS 140-2 or 140-3 certified by running the following SQL query:\n\nSELECT DISTINCT name, algorithm_desc\nFROM sys.symmetric_keys\nWHERE key_algorithm NOT IN ('D3','A3')\nORDER BY name\n\nIf any items listed show an uncertified NIST FIPS 140-2 algorithm type, this is a finding.","fixText":"Configure cryptographic functions to use NSA-approved cryptography compliant algorithms.\n\nUse DoD code-signing certificates to create asymmetric keys stored in the database used to encrypt sensitive data stored in the database.\n\nRun the following SQL script to create a certificate:\nUSE \nCREATE CERTIFICATE \n ENCRYPTION BY PASSWORD = <'password'>\n FROM FILE = <'path/file_name'>\n WITH SUBJECT = 'name of person creating key',\n EXPIRY_DATE = '<'expiration date: yyyymmdd'>'\n\nRun the following SQL script to create a symmetric key and assign an existing certificate:\nUSE \nCREATE SYMMETRIC KEY <'key name'>\n WITH ALGORITHM = AES_256 \n ENCRYPTION BY CERTIFICATE\n\nFor Transparent Data Encryption (TDE):\nUSE master;\nCREATE MASTER KEY ENCRYPTION BY PASSWORD = '';\nCREATE CERTIFICATE  . . .;\nUSE ;\nCREATE DATABASE ENCRYPTION KEY\nWITH ALGORITHM = AES_256\nENCRYPTION BY SERVER CERTIFICATE ;\nALTER DATABASE \nSET ENCRYPTION ON;","ccis":["CCI-002450"]}]}