None Computing title Microsoft Azure SQL Managed Instance Security Technical Implementation Guide version 1 releaseinfo Release: 1 Vuln_Num V-276225 Rule_ID SV-276225r1150094_rule Severity high Rule_Title Azure SQL Managed Instances must integrate with Microsoft Entra ID for providing account management and automation for all users, groups, roles, and any other principals. Vuln_Discuss 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. A comprehensive application account management process that includes automation helps to ensure that accounts designated as requiring attention are consistently and promptly addressed. Examples 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. Azure SQL Managed Instance must be configured to automatically utilize organization-level account management functions, and these functions must immediately enforce the organization's current account policy. Automation may comprise differing technologies, that when placed together, contain an overall mechanism supporting an organization's automated account management requirements. Check_Content Determine if Azure SQL Managed Instance is configured to use Microsoft Entra ID authentication only. Only Microsoft Entra ID will be used to authenticate to the server. SQL authentication will be disabled, including SQL Server administrators and users. In a PowerShell or Cloud Shell interface, run the statement: az sql mi ad-only-auth get --resource-group myresource --name myinstance OR Get-AzSqlInstanceActiveDirectoryOnlyAuthentication -InstanceName myinstance -ResourceGroupName myresource If the returned value in the "AzureADOnlyAuthentication" column is "True", this is not a finding. If Mixed mode (both SQL Server authentication and Entra ID authentication) is in use and the need for mixed mode has not been documented and approved, this is a finding. From the documentation, obtain the list of accounts authorized to be managed by Azure SQL Managed Instance. Determine the accounts (SQL Logins) actually managed by Azure SQL Managed Instance. Run the statement: SELECT name FROM sys.database_principals WHERE type_desc = 'SQL_USER' AND authentication_type_desc = 'INSTANCE'; If any accounts listed by the query are not listed in the documentation, this is a finding. Risk must be accepted by the information system security officer (ISSO)/information system security manager (ISSM). More information regarding this process is available at: https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-azure-ad-only-authentication. Fix_Text If mixed mode is required, document the need and justification; describe the measures taken to ensure the use of Azure SQL Managed Instance authentication is kept to a minimum; describe the measures taken to safeguard passwords; list or describe the SQL logins used; and obtain official approval. If mixed mode is not required, for each account being managed by SQL MI but not requiring it, drop or disable the SQL Database user. Replace it with an appropriately configured account, as needed. To drop a user in the SSMS Object Explorer, navigate to Databases >> database >> Security >> Users. Right-click on the user name and then click "Delete". To drop a user via a query, change the context to the database_name to be evaluated: DROP USER. To enable Microsoft Entra-only Authentication, in a PowerShell or Cloud Shell interface, run the statement: az sql mi ad-only-auth enable --resource-group myresource --name myinstance OR Enable-AzSqlInstanceActiveDirectoryOnlyAuthentication -InstanceName myinstance -ResourceGroupName myresource More information regarding this process is available at the following link: https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-azure-ad-only-authentication. Not_Reviewed Vuln_Num V-276226 Rule_ID SV-276226r1149587_rule Severity high Rule_Title Azure SQL Managed Instance must enforce approved authorizations for logical access to database information and system resources in accordance with applicable access control policies. Vuln_Discuss Authentication with a DOD-approved PKI certificate does not necessarily imply authorization to access Azure SQL Managed Instance. 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. 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 database management systems. If Azure SQL Managed Instance 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. Check_Content Review the system documentation to determine the required levels of protection for Azure SQL Managed Instance server securables, by type of login. Review the permissions actually in place on the server. Execute the supplemental "DatabasePermissions.sql" script to find permissions in place on the server. If the actual permissions do not match the documented requirements, this is a finding. Fix_Text Use GRANT, REVOKE, DENY, ALTER SERVER ROLE … ADD MEMBER … and/or ALTER SERVER ROLE … DROP MEMBER statements to add and remove permissions on server-level securables, bringing them in line with the documented requirements. References: https://docs.microsoft.com/en-us/sql/t-sql/statements/revoke-transact-sql?view=azuresqldb-current Deny: https://docs.microsoft.com/en-us/sql/t-sql/statements/deny-transact-sql?view=azuresqldb-current DROP MEMBER: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-droprolemember-transact-sql?view=azuresqldb-current Not_Reviewed Vuln_Num V-276227 Rule_ID SV-276227r1149590_rule Severity medium Rule_Title Database objects must be owned by Azure SQL Managed Instance principals authorized for ownership. Vuln_Discuss 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. Check_Content Review system documentation to identify Azure SQL Managed Instance accounts authorized to own database objects. If the Azure SQL Managed Instance ownership list does not exist or needs to be updated, this is a finding. The following query can be used to make this determination: ;with objects_cte as (SELECT o.name, o.type_desc, CASE WHEN o.principal_id is null then s.principal_id ELSE o.principal_id END as principal_id FROM sys.objects o INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE o.is_ms_shipped = 0 ) SELECT cte.name, cte.type_desc, dp.name as ObjectOwner FROM objects_cte cte INNER JOIN sys.database_principals dp ON cte.principal_id = dp.principal_id ORDER BY dp.name, cte.name If any of the listed owners is not authorized, this is a finding. Fix_Text Document and obtain approval for any account(s) authorized for object ownership. If necessary, use the ALTER AUTHORIZATION command to change object ownership to an authorized account. Example provided below. ALTER AUTHORIZATION ON OBJECT::test.table TO AuthorizedUser; Refer to: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-authorization-transact-sql Not_Reviewed Vuln_Num V-276228 Rule_ID SV-276228r1149593_rule Severity medium Rule_Title The role(s)/group(s) used to modify database structure and logic modules inside Azure SQL Server Managed Instance must be restricted to authorized users. Vuln_Discuss If the Azure SQL Managed Instance were to allow any user to make changes to database structure or logic, then those changes might be implemented without undergoing the appropriate testing and approvals that are part of a robust change management process. Accordingly, only qualified and authorized individuals will be allowed to obtain access to information system components for purposes of initiating changes, including upgrades and modifications. Unmanaged changes that occur to the database software libraries or configuration can lead to unauthorized or compromised installations. Check_Content Obtain a listing of users and roles who are authorized to modify database structure and logic modules from the server documentation. Execute the following query to obtain a list of database principals: SELECT P.type_desc AS principal_type, P.name AS principal_name, CASE DP.class WHEN 0 THEN DP.class_desc ELSE O.type_desc END AS type_desc,CASE DP.class WHEN 0 THEN DB_NAME() WHEN 1 THEN OBJECT_SCHEMA_NAME(DP.major_id) + '.' + OBJECT_NAME(DP.major_id) WHEN 3 THEN SCHEMA_NAME(DP.major_id) ELSE DP.class_desc + '(' + CAST(DP.major_id AS nvarchar) + ')' END AS securable_name, DP.state_desc, DP.permission_name FROM sys.database_permissions DP JOIN sys.database_principals P ON DP.grantee_principal_id = P.principal_id LEFT OUTER JOIN sys.all_objects O ON O.object_id = DP.major_id AND O.type IN ('TR','TA','P','X','RF','PC','IF','FN','TF','U') WHERE DP.type IN ('AL','ALTG') AND DP.class IN (0, 1, 53); GO Execute the following query to obtain a list of role memberships: SELECT R.name AS role_name, M.type_desc AS principal_type, M.name AS principal_name FROM sys.database_principals R JOIN sys.database_role_members DRM ON R.principal_id = DRM.role_principal_id JOIN sys.database_principals M ON DRM.member_principal_id = M.principal_id WHERE R.name IN ('db_ddladmin','db_owner') AND M.name <> 'dbo'; GO If unauthorized access to the principal(s)/role(s) has been granted, this is a finding. Fix_Text Document and obtain approval for any nonadministrative user(s) who require the ability to modify database structure and logic modules. If necessary, use the ALTER ROLE and/or REVOKE commands to remove unauthorized users access to modify database structure. Examples provided below: ALTER ROLE ddladmin DROP MEMBER UnauthorizedUser; REVOKE SELECT ON OBJECT::test.table FROM UnauthorizedUser; Refer to: https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-role-transact-sql?view=azuresqldb-mi-current Not_Reviewed Vuln_Num V-276229 Rule_ID SV-276229r1149596_rule Severity medium Rule_Title Azure SQL Managed Instance contents must be protected from unauthorized and unintended information transfer by enforcement of a data-transfer policy. Vuln_Discuss Applications, including DBMSs, must prevent unauthorized and unintended information transfer via shared system resources. 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, 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. Copies of sensitive data must not be misplaced or left in a temporary location without the proper controls. Check_Content 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. Fix_Text 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. Not_Reviewed Vuln_Num V-276230 Rule_ID SV-276230r1150043_rule Severity medium Rule_Title Azure SQL Managed Instance and associated applications, when making use of dynamic code execution, must scan input data for invalid values that may indicate a code injection attack. Vuln_Discuss 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, precompiled stored procedures and functions (and triggers). When dynamic execution is necessary, ways to mitigate the risk include the following, which must 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, equal signs, angle brackets, or square brackets will never be valid as input, reject them. - If comment markers will never be valid as input, reject them. In SQL, these are -- or /* */ . - If HTML and XML tags, entities, comments, etc., will never be valid, reject them. - If wildcards are present, reject them unless truly necessary. In SQL these are the underscore and the percentage sign, and the word "ESCAPE" is also a clue that wildcards are in use. - If SQL key words, such as SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, ESCAPE, UNION, GRANT, REVOKE, DENY, MODIFY will never be valid, reject them. Use case-insensitive comparisons when searching for these. Bear in mind that some of these words, particularly "Grant" (as a person's name), could also be valid input. - If there are range limits on the values that may be entered, enforce those limits. - Institute procedures for inspection of programs for correct use of dynamic coding, by a party other than the developer. - Conduct rigorous testing of program modules that use dynamic coding, searching for ways to subvert the intended use. - Record the inspection and testing in the system documentation. - Bear in mind that all this applies not only to screen input, but also to the values in an incoming message to a web service or to a stored procedure called by a software component that has not itself been hardened in these ways. Not only can the caller be subject to such vulnerabilities; it may itself be the attacker. This calls for inspection of application source code, which will require collaboration with the application developers. It is recognized that in many cases, the database administrator (DBA) is organizationally separate from the application developers, and may have limited, if any, access to source code. Nevertheless, protections of this type are so important to the secure operation of databases that they must not be ignored. At a minimum, the DBA must attempt to obtain assurances from the development organization that this issue has been addressed and must document what has been discovered. Satisfies: SRG-APP-000251-DB-000392, SRG-APP-000251-DB-000391 Check_Content Review the system documentation to obtain a listing of stored procedures and functions that utilize dynamic code execution. Execute the following query: DECLARE @tblDynamicQuery TABLE (ID INT identity(1,1), ProcToExecuteDynSQL VARCHAR(500)) INSERT INTO @tblDynamicQuery(ProcToExecuteDynSQL) values('EXEC[ (]@') INSERT INTO @tblDynamicQuery(ProcToExecuteDynSQL) values('EXECUTE[ (]@') INSERT INTO @tblDynamicQuery(ProcToExecuteDynSQL) values('SP_EXECUTESQL[ (]@') SELECT [DatbaseName] = DB_Name() ,[ObjectName] = SCHEMA_NAME([schema_id]) + '.' + (name) ,[ObjectType] = type_desc FROM sys.objects o WHERE o.is_ms_shipped = 0 and o.object_id IN ( SELECT m.object_id FROM sys.sql_modules m JOIN @tblDynamicQuery dsql ON REPLACE(REPLACE(REPLACE(m.definition,CHAR(32),'()'),')(',''),'()',CHAR(32)) like '%' + dsql.ProcToExecuteDynSQL + '%') If any procedures or functions are returned that are not documented, this is a finding. Fix_Text Where dynamic code execution is used, modify the code to implement protections against code injection. When the objective could practically be satisfied by static execution with strongly typed parameters, modify the code to do so. Not_Reviewed Vuln_Num V-276231 Rule_ID SV-276231r1150006_rule Severity medium Rule_Title Azure SQL Managed Instance must associate organization-defined types of security labels having organization-defined security label values with information. Vuln_Discuss Without the association of security labels to information, there is no basis for Azure SQL Managed Instance 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 CUI. These security labels may be assigned manually or during data processing, but, either way, it is imperative these assignments are maintained. If the security labels are lost, there is the risk of a data compromise. The mechanism used to support security labeling may be a feature of Azure SQL Managed Instance, a third-party product, or custom application code. Satisfies: SRG-APP-000313-DB-000309, SRG-APP-000311-DB-000308, SRG-APP-000314-DB-000310 Check_Content If security labeling is not required, this is not a finding. If security labeling requirements have been specified, but a third-party solution, SQL Information Protection, or an Azure SQL Managed Instance Row-Level security solution is implemented that reliably maintains labels on information, this is a finding. Fix_Text Deploy SQL Information Protection (refer to link below) or Azure SQL Managed Instance Row-Level Security (see link below), a third-party software, or add custom data structures, data elements and application code to provide reliable security labeling of information. Refer to: https://learn.microsoft.com/en-us/azure/defender-for-cloud/sql-information-protection-policy?tabs=sqlip-tenant https://learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver16 Not_Reviewed Vuln_Num V-276232 Rule_ID SV-276232r1149605_rule Severity medium Rule_Title Azure SQL Managed Instance must enforce discretionary access control policies, as defined by the data owner, over defined subjects and objects. Vuln_Discuss 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 bound 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. Check_Content Review system documentation to determine requirements for object ownership and authorization delegation. Use the following query to discover database object ownership: Schemas not owned by the schema or dbo: SELECT name AS schema_name, USER_NAME(principal_id) AS schema_owner FROM sys.schemas WHERE schema_id != principal_id AND principal_id != 1 Objects owned by an individual principal: SELECT object_id, name AS securable, USER_NAME(principal_id) AS object_owner, type_desc FROM sys.objects WHERE is_ms_shipped = 0 AND principal_id IS NOT NULL ORDER BY type_desc, securable, object_owner Use the following query to discover database users who have been delegated the right to assign additional permissions: SELECT U.type_desc, U.name AS grantee, DP.class_desc AS securable_type, CASE DP.class WHEN 0 THEN DB_NAME() WHEN 1 THEN OBJECT_NAME(DP.major_id) WHEN 3 THEN SCHEMA_NAME(DP.major_id) ELSE CAST(DP.major_id AS nvarchar) END AS securable, permission_name, state_desc FROM sys.database_permissions DP JOIN sys.database_principals U ON DP.grantee_principal_id = U.principal_id WHERE DP.state = 'W' ORDER BY grantee, securable_type, securable If any of these rights are not documented and authorized, this is a finding. Fix_Text To correct object ownership: Use the ALTER AUTHORIZATION ON::[Object Name] TO [Database principal] Full ALTER AUTHORIZATION command syntax is described in this document: https://docs.microsoft.com/en-us/sql/t-sql/statements/revoke-transact-sql?view=azuresqldb-current To remove unauthorized permissions: Use the REVOKE [Permission name] ON [Object name] TO [Database principal] to remove unauthorized permissions from a database principal on an object. Full REVOKE command syntax is described in this document: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-authorization-transact-sql?view=azuresqldb-current Not_Reviewed Vuln_Num V-276233 Rule_ID SV-276233r1149608_rule Severity medium Rule_Title Azure SQL Managed Instance must restrict execution of stored procedures and functions that utilize "execute as" to necessary cases only. Vuln_Discuss In certain situations, to provide required functionality, a database management system (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. Privilege elevation by "execute as" must be utilized only where necessary and protected from misuse. Check_Content Review the system documentation to obtain a listing of stored procedures and functions that utilize impersonation. Execute the following query: SELECT S.name AS schema_name, O.name AS module_name, USER_NAME( CASE M.execute_as_principal_id WHEN -2 THEN COALESCE(O.principal_id, S.principal_id) ELSE M.execute_as_principal_id END ) AS execute_as FROM sys.sql_modules M JOIN sys.objects O ON M.object_id = O.object_id JOIN sys.schemas S ON O.schema_id = S.schema_id WHERE execute_as_principal_id IS NOT NULL and o.name not in ( 'fn_sysdac_get_username', 'fn_sysutility_ucp_get_instance_is_mi', 'sp_send_dbmail', 'sp_SendMailMessage', 'sp_syscollector_create_collection_set', 'sp_syscollector_delete_collection_set', 'sp_syscollector_disable_collector', 'sp_syscollector_enable_collector', 'sp_syscollector_get_collection_set_execution_status', 'sp_syscollector_run_collection_set', 'sp_syscollector_start_collection_set', 'sp_syscollector_update_collection_set', 'sp_syscollector_upload_collection_set', 'sp_syscollector_verify_collector_state', 'sp_syspolicy_add_policy', 'sp_syspolicy_add_policy_category_subscription', 'sp_syspolicy_delete_policy', 'sp_syspolicy_delete_policy_category_subscription', 'sp_syspolicy_update_policy', 'sp_sysutility_mi_add_ucp_registration', 'sp_sysutility_mi_disable_collection', 'sp_sysutility_mi_enroll', 'sp_sysutility_mi_initialize_collection', 'sp_sysutility_mi_remove', 'sp_sysutility_mi_remove_ucp_registration', 'sp_sysutility_mi_upload', 'sp_sysutility_mi_validate_enrollment_preconditions', 'sp_sysutility_ucp_add_mi', 'sp_sysutility_ucp_add_policy', 'sp_sysutility_ucp_calculate_aggregated_dac_health', 'sp_sysutility_ucp_calculate_aggregated_mi_health', 'sp_sysutility_ucp_calculate_computer_health', 'sp_sysutility_ucp_calculate_dac_file_space_health', 'sp_sysutility_ucp_calculate_dac_health', 'sp_sysutility_ucp_calculate_filegroups_with_policy_violations', 'sp_sysutility_ucp_calculate_health', 'sp_sysutility_ucp_calculate_mi_file_space_health', 'sp_sysutility_ucp_calculate_mi_health', 'sp_sysutility_ucp_configure_policies', 'sp_sysutility_ucp_create', 'sp_sysutility_ucp_delete_policy', 'sp_sysutility_ucp_delete_policy_history', 'sp_sysutility_ucp_get_policy_violations', 'sp_sysutility_ucp_initialize', 'sp_sysutility_ucp_initialize_mdw', 'sp_sysutility_ucp_remove_mi', 'sp_sysutility_ucp_update_policy', 'sp_sysutility_ucp_update_utility_configuration', 'sp_sysutility_ucp_validate_prerequisites', 'sp_validate_user', 'syscollector_collection_set_is_running_update_trigger', 'sysmail_help_status_sp' ) ORDER BY schema_name, module_name If any procedures or functions are returned that are not documented, this is a finding. Fix_Text Alter stored procedures and functions to remove the "execute as" statement. Not_Reviewed Vuln_Num V-276234 Rule_ID SV-276234r1149611_rule Severity medium Rule_Title Azure SQL Managed Instance must prohibit user installation of logic modules without explicit privileged status. Vuln_Discuss Allowing regular users to install software, without explicit privileges, creates the risk that untested or potentially malicious software will be installed on the system. Explicit privileges (escalated or administrative privileges) provide the regular user with explicit capabilities and control that exceed the rights of a regular user. Azure SQL Managed Instance 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. Azure SQL Managed Instance must enforce software installation by users based upon 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 with regard to being potentially malicious is unknown or suspect) by the organization). In the case of a database management system, this requirement covers stored procedures, functions, triggers, views, etc. Check_Content If the Azure SQL Managed Instance supports only software development, experimentation, and/or developer-level testing (that is, excluding production systems, integration testing, stress testing, and user acceptance testing), this is not a finding. Obtain a listing of users and roles authorized to create, alter, or replace logic modules from the server documentation. Execute the following query to obtain a list of database principals: SELECT P.type_desc AS principal_type, P.name AS principal_name, O.type_desc, CASE class WHEN 0 THEN DB_NAME() WHEN 1 THEN OBJECT_SCHEMA_NAME(major_id) + '.' + OBJECT_NAME(major_id) WHEN 3 THEN SCHEMA_NAME(major_id) ELSE class_desc + '(' + CAST(major_id AS nvarchar) + ')' END AS securable_name, DP.state_desc, DP.permission_name FROM sys.database_permissions DP JOIN sys.database_principals P ON DP.grantee_principal_id = P.principal_id LEFT OUTER JOIN sys.all_objects O ON O.object_id = DP.major_id AND O.type IN ('TR','TA','P','X','RF','PC','IF','FN','TF','U') WHERE DP.type IN ('AL','ALTG') AND DP.class IN (0, 1, 53) Execute the following query to obtain a list of role memberships: SELECT R.name AS role_name, M.type_desc AS principal_type, M.name AS principal_name FROM sys.database_principals OR JOIN sys.database_role_members DRM ON R.principal_id = DRM.role_principal_id JOIN sys.database_principals M ON DRM.member_principal_id = M.principal_id WHERE R.name IN ('db_ddladmin','db_owner') AND M.name != 'dbo' If unauthorized access to the principal(s)/role(s) has been granted, this is a finding. Fix_Text Document and obtain approval for any nonadministrative users who require the ability to create, alter, or replace logic modules. Revoke the ALTER permission from unauthorized users and roles: REVOKE ALTER ON [object] FROM [user] Refer to: https://learn.microsoft.com/en-us/sql/t-sql/statements/revoke-server-permissions-transact-sql?view=azuresqldb-mi-current Not_Reviewed Vuln_Num V-276235 Rule_ID SV-276235r1150008_rule Severity medium Rule_Title Azure SQL Managed Instance must enforce access restrictions associated with changes to the configuration of the database(s). Vuln_Discuss Failure to provide logical access restrictions associated with changes to configuration may have significant effects on the overall security of the system. When 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. Accordingly, only qualified and authorized individuals must be allowed to obtain access to system components for the purposes of initiating changes, including upgrades and modifications. Check_Content Execute the following query to obtain a listing of user databases whose owner is a member of a fixed server role and the corresponding roles: SELECT D.name AS database_name, SUSER_SNAME(D.owner_sid) AS owner_name, FRM.is_fixed_role_member, B.fixed_role_memberships FROM sys.databases D OUTER APPLY ( SELECT MAX(fixed_role_member) AS is_fixed_role_member FROM ( SELECT IS_SRVROLEMEMBER(R.name, SUSER_SNAME(D.owner_sid)) AS fixed_role_member FROM sys.server_principals R WHERE is_fixed_role = 1 ) A ) FRM OUTER APPLY ( SELECT STUFF((SELECT ', ' + R.name FROM sys.server_principals R JOIN sys.server_role_members RM ON R.principal_id = RM.role_principal_id WHERE RM.member_principal_id = SUSER_ID(SUSER_SNAME(D.owner_sid)) AND r.is_fixed_role =1 FOR XML PATH(''), TYPE).value('text()[1]','nvarchar(max)'), 1, LEN(','), '') AS fixed_role_memberships ) B WHERE D.database_id > 4 AND (FRM.is_fixed_role_member = 1 OR FRM.is_fixed_role_member IS NULL) ORDER BY database_name If any server roles are returned, but not documented and authorized, this is a finding. Fix_Text Document and obtain approval for logins with privileged permissions and role memberships. If necessary, use the ALTER ROLE and/or REVOKE commands to remove unauthorized privileged permissions and/or role memberships. Example script provided below: ALTER ROLE ddladmin DROP MEMBER UnauthorizedUser; REVOKE SELECT ON OBJECT::test.table FROM UnauthorizedUser; Refer to: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-role-transact-sql https://docs.microsoft.com/en-us/sql/t-sql/statements/revoke-transact-sql If necessary, in the Azure Portal, navigate to the Access Control pane for the Azure SQL Managed Instance to review and remove unauthorized privileged permissions and/or role memberships. Refer to the documentation linked below: https://docs.microsoft.com/en-us/azure/role-based-access-control/role-definitions-list https://docs.microsoft.com/en-us/azure/role-based-access-control/role-assignments-remove Not_Reviewed Vuln_Num V-276236 Rule_ID SV-276236r1150044_rule Severity high Rule_Title Azure SQL Managed Instance must use NSA-approved cryptography to protect classified information in accordance with the data owners' requirements. Vuln_Discuss 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. It 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. NSA-approved cryptography for classified networks is hardware based. This requirement addresses the compatibility of Azure SQL Managed Instance with the encryption devices. Check_Content Use the TSQL query below to determine database encryption state: SELECT DB_NAME(database_id) AS DatabaseName, encryption_state_desc AS EncryptionState, key_algorithm+CAST(key_length AS nvarchar(128)) AS EncryptionAlgorithm, encryptor_type FROM sys.dm_database_encryption_keys Validate that for each database the [EncryptionState] is "ENCRYPTED" and the [EncryptionAlgorithm] returns one of the following values: [AES128], [AES192], or [AES256]. If any other value is returned for either the "EncryptionState" or "EncryptionAlgorithm", this is a finding. Fix_Text Create a compliant key/certificate and enable encryption on the database. Refer to: https://learn.microsoft.com/en-us/azure/azure-sql/database/transparent-data-encryption-byok-configure?view=azuresql&amp;tabs=azure-powershell Then execute the following command: ALTER DATABASE [Database Name Between Brackets] SET ENCRYPTION ON; Not_Reviewed Vuln_Num V-276237 Rule_ID SV-276237r1149620_rule Severity medium Rule_Title Azure SQL Managed Instance must implement cryptographic mechanisms to prevent unauthorized modification of organization-defined information at rest on organization-defined information system components. Vuln_Discuss Azure SQL Managed Instance databases handling data requiring data-at-rest protections must employ cryptographic mechanisms to prevent unauthorized disclosure and modification of the information at rest. 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. 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. Check_Content Review the system documentation to determine whether the organization has defined the information at rest that is to be protected from modification, which must include, at a minimum, PII and classified information. If no information is identified as requiring such protection, this is not a finding. Review the configuration of the Azure SQL Managed Instance to ensure data-at-rest protections are implemented. If any of the information defined as requiring cryptographic protection from modification is not encrypted in a manner that provides the required level of protection, this is a finding. Retrieve the Transparent Database Encryption status for each database using the following TSQL command: SELECT db.name AS DatabaseName, db.is_encrypted AS IsEncrypted, CASE WHEN dm.encryption_state = 0 THEN 'No database encryption key present, no encryption' WHEN dm.encryption_state = 1 THEN 'Unencrypted' WHEN dm.encryption_state = 2 THEN 'Encryption in progress' WHEN dm.encryption_state = 3 THEN 'Encrypted' WHEN dm.encryption_state = 4 THEN 'Key change in progress' WHEN dm.encryption_state = 5 THEN 'Decryption in progress' WHEN dm.encryption_state = 6 THEN 'Protection change in progress' END AS EncryptionState, dm.encryption_state AS EncryptionState, dm.key_algorithm AS KeyAlgorithm, dm.key_length AS KeyLength FROM sys.databases db LEFT OUTER JOIN sys.dm_database_encryption_keys dm ON db.database_id = dm.database_id WHERE db.database_id NOT IN (1,2,3,4) If the application owner and authorizing official have determined that encryption of data at rest is required and the "EncryptionState" column returns "UNENCRYPTED", "DECRYPTION_IN_PROGRESS", or "NULL", this is a finding. Fix_Text Use the following TSQL command to enable Transparent Database Encryption on each unencrypted database. ALTER DATABASE [Database Name Between Brackets] SET ENCRYPTION ON Not_Reviewed Vuln_Num V-276238 Rule_ID SV-276238r1150056_rule Severity medium Rule_Title Azure SQL Managed Instance must implement cryptographic mechanisms preventing the unauthorized disclosure of organization-defined information at rest on organization-defined information system components. Vuln_Discuss Azure SQL Managed Instance handling data requiring data-at-rest protections must employ cryptographic mechanisms to prevent unauthorized disclosure and modification of the information at rest. 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. Check_Content Review the system documentation to determine whether the organization has defined the information at rest that is to be protected from modification, which must include, at a minimum, PII and classified information. If no information is identified as requiring such protection, this is not a finding. Review the configuration of the Azure SQL Managed Instance to ensure data-at-rest protections are implemented. If any of the information defined as requiring cryptographic protection from modification is not encrypted in a manner that provides the required level of protection, this is a finding. Use the query below to check the encryption status for each database. If any databases have an is_encrypted status of "0", this is a finding. SELECT [name] AS DatabaseName,is_encrypted FROM master.sys.databases WHERE database_id > 4; Fix_Text For any database with an is_encrypted status of "0", use the query below to enable transparent data encryption: ALTER DATABASE [Database Name Here] SET ENCRYPTION ON; Not_Reviewed Vuln_Num V-276239 Rule_ID SV-276239r1149626_rule Severity medium Rule_Title When invalid inputs are received, the Azure SQL Managed Instance must behave in a predictable and documented manner that reflects organizational and system objectives. Vuln_Discuss A common vulnerability is unplanned behavior when invalid inputs are received. This requirement guards against adverse or unintended system behavior caused by invalid inputs, where information system responses to the invalid input may be disruptive or cause the system to fail into an unsafe state. The behavior will be derived from the organizational and system requirements and includes, but is not limited to, notification of the appropriate personnel, creating an audit record, and rejecting invalid input. This calls for inspection of application source code, which will require collaboration with the application developers. It is recognized that in many cases, the database administrator (DBA) is organizationally separate from the application developers, and may have limited, if any, access to source code. Nevertheless, protections of this type are so important to the secure operation of databases that they must not be ignored. At a minimum, the DBA must attempt to obtain assurances from the development organization that this issue has been addressed and must document what has been discovered. Check_Content Review database management system (DBMS) code (stored procedures, functions, triggers), application code, settings, column and field definitions, and constraints to determine whether the database is protected against invalid input. If code exists that allows invalid data to be acted upon or input into the database, this is a finding. If column/field definitions are not reflective of the data, this is a finding. If columns/fields do not contain constraints and validity checking where required, this is a finding. Where a column/field is noted in the system documentation as necessarily free-form, even though its name and context suggest that it must be strongly typed and constrained, the absence of these protections is not a finding. Where a column/field is clearly identified by name, caption or context as Notes, Comments, Description, Text, etc., the absence of these protections is not a finding. Fix_Text Use parameterized queries, stored procedures, constraints and foreign keys to validate data input. Modify Azure SQL Managed Instance to properly use the correct column data types as required in the database. Not_Reviewed Vuln_Num V-276240 Rule_ID SV-276240r1149629_rule Severity medium Rule_Title Azure SQL Managed Instance must protect against a user falsely repudiating by ensuring only clearly unique Active Directory user accounts can connect to the database. Vuln_Discuss Nonrepudiation of actions taken is required 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. Nonrepudiation 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. In 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 Azure SQL Managed Instance's audit tools to capture the necessary audit trail. Design and implementation also must ensure that applications pass individual user identification to Azure SQL Managed Instance, even where the application connects to Azure SQL Managed Instance with a standard, shared account. Satisfies: SRG-APP-000080-DB-000063, SRG-APP-000815-DB-000160 Check_Content Obtain the list of authorized Azure SQL Managed Instance accounts in the system documentation. Determine if any accounts are shared. A shared account is defined as a username and password that are used by multiple individuals to log in to Azure SQL Managed Instance. Microsoft Entra ID groups are not shared accounts as the group itself does not have a password. If accounts are to be shared, determine if users are first individually authenticated. If users are not individually authenticated before using the shared account (e.g., by the operating system or possibly by an application making calls to the database), this is a finding. The key is individual accountability. If this can be traced, this is not a finding. If accounts are shared, determine if they are directly accessible to end users. If so, this is a finding. Review contents of audit logs and data tables to confirm the identity of the individual user performing the action is captured. If shared identifiers are found and not accompanied by individual identifiers, this is a finding. Fix_Text Remove user-accessible shared accounts and use individual user IDs. Build/configure applications to ensure successful individual authentication prior to shared account access. Ensure each user's identity is received and used in audit data in all relevant circumstances. Design, develop, and implement a method to log use of any account to which more than one person has access. Restrict interactive access to shared accounts to the fewest persons possible. Not_Reviewed Vuln_Num V-276241 Rule_ID SV-276241r1149632_rule Severity medium Rule_Title Azure SQL Managed Instance must protect against a user falsely repudiating by use of system-versioned tables (Temporal Tables). Vuln_Discuss Nonrepudiation of actions taken is required 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. Nonrepudiation 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. In 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. Check_Content Check the server documentation to determine if collecting and keeping historical versions of a table is required. If collecting and keeping historical versions of a table is NOT required, this is not a finding. Find all of the temporal tables in the database using the following query: SELECT SCHEMA_NAME(T.schema_id) AS schema_name, T.name AS table_name, T.temporal_type_desc, SCHEMA_NAME(H.schema_id) + '.' + H.name AS history_table FROM sys.tables T JOIN sys.tables H ON T.history_table_id = H.object_id WHERE T.temporal_type != 0 ORDER BY schema_name, table_name Using the system documentation, determine which tables are required to be temporal tables. If any tables listed in the documentation are not in the list created by running the above statement, this is a finding. Verify a field exists documenting the login and/or user who last modified the record. If this does not exist, this is a finding. Review the system documentation to determine the history retention period. Navigate to the table in Object Explorer. Right-click on the table, and then select Script Table As >> CREATE To >> New Query Editor Window. Locate the line that contains "SYSTEM_VERSIONING". Locate the text that states "HISTORY_RETENTION_PERIOD". If this text is missing or is set to a value less than the documented history retention period, this is a finding. Fix_Text Alter sensitive tables to utilize system versioning. Alter nontemporal table to define periods for system versioning: ALTER TABLE <MyTableName> ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime), SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL DEFAULT SYSUTCDATETIME(), SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.99999999') ; Enable system versioning with one year retention for historical data: ALTER TABLE <MyTableName> SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 1 YEAR)) ; Refer to: https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=azuresqldb-mi-current#system_versioning Not_Reviewed Vuln_Num V-276242 Rule_ID SV-276242r1150021_rule Severity medium Rule_Title The Azure SQL Managed Instance must be able to generate audit records when attempts to retrieve privileges/permissions occur. Vuln_Discuss Under some circumstances, it may be useful to monitor who/what is reading privilege/permission/role information; therefore, it must be possible to configure auditing to do this. DBMSs typically make such information available through views or functions. This requirement addresses explicit requests for privilege/permission/role membership information. It does not refer to the implicit retrieval of privileges/permissions/role memberships that Azure SQL Managed Instance continually performs to determine if any and every action on the database is permitted. To aid in diagnosis, it is necessary to keep track of failed attempts in addition to the successful ones. Satisfies: SRG-APP-000091-DB-000066, SRG-APP-000091-DB-000325, SRG-APP-000495-DB-000326 Check_Content Review Azure SQL Managed Instance configuration to verify that audit records are produced when privileges/permissions/role memberships are retrieved. To determine if an audit is configured, follow the instructions below: Run this TSQL command to determine if SQL Auditing AuditActionGroups are configured: SELECT DISTINCT sd.audit_action_name FROM sys.server_audit_specification_details sd JOIN sys.server_audit_specifications s ON s.server_specification_id = sd.server_specification_id WHERE s.is_state_enabled = 1 ORDER BY sd.audit_action_name If no values exist for AuditActionGroup, this is a finding. Verify the following AuditActionGroup(s) are configured: SCHEMA_OBJECT_ACCESS_GROUP If any listed AuditActionGroups do not exist in the configuration, this is a finding. Fix_Text Deploy an Azure SQL Managed Instance audit. Refer to the supplemental file "AzureSQLMIAudit.sql" script. Reference: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure?view=azuresql Not_Reviewed Vuln_Num V-276243 Rule_ID SV-276243r1149638_rule Severity medium Rule_Title Azure SQL Managed Instance must initiate session auditing upon startup. Vuln_Discuss Session auditing is used when a user's activities are under investigation. To ensure capture of all activity during those periods when session auditing is in use, it must be in operation for the whole time Azure SQL Managed Instance is running. Check_Content When Audits are enabled, they start up when the audits are enabled and remain operating until the audit is disabled. Check if an audit is configured and enabled. To determine if session auditing is configured and enabled, follow the instructions below: Run this TSQL command to determine if SQL Auditing is configured and enabled: SELECT name AS 'Audit Name', status_desc AS 'Audit Status', audit_file_path AS 'Current Audit File' FROM sys.dm_server_audit_status WHERE status_desc = 'STARTED' All currently defined audits for the Azure SQL Managed Instance will be listed. If no audits are returned, this is a finding. Fix_Text Configure the SQL Audit(s) to automatically start during system start-up. ALTER SERVER AUDIT [] WITH STATE = ON Execute the following query: SELECT name AS 'Audit Name', status_desc AS 'Audit Status', audit_file_path AS 'Current Audit File' FROM sys.dm_server_audit_status WHERE status_desc = 'STARTED' Ensure the SQL STIG Audit is configured to initiate session auditing upon startup. Not_Reviewed Vuln_Num V-276244 Rule_ID SV-276244r1149641_rule Severity medium Rule_Title Azure SQL Managed Instance default demonstration and sample databases, database objects, and applications must be removed. Vuln_Discuss Information systems are capable of providing a wide variety of functions and services. Some of the functions and services, provided by default, may not be necessary to support essential organizational operations (e.g., key missions, functions). It is detrimental for software products to provide, or install by default, functionality exceeding requirements or mission objectives. Examples include, but are not limited to, installing advertising software, demonstrations, or browser plugins not related to requirements or providing a wide array of functionality, not required for every mission, that cannot be disabled. Azure SQL Managed Instance must adhere to the principles of least functionality by providing only essential capabilities. Demonstration and sample database objects and applications present publicly known attack points for malicious users. These demonstration and sample objects are meant to provide simple examples of coding specific functions and are not developed to prevent vulnerabilities from being introduced to the Azure SQL Managed Instance. Check_Content Review vendor documentation and vendor websites to identify vendor-provided demonstration or sample databases, database applications, objects, and files. Review the Azure SQL Managed Instance to determine if any of the demonstration and sample databases, database applications, or files are installed in the database or are included with the Azure SQL Managed Instance. Run the following query to check for names matching known sample databases. Sample databases may have been renamed, so this is not an exhaustive list. SELECT name FROM sys.databases WHERE name LIKE '%pubs%' OR name LIKE '%northwind%' OR name LIKE '%adventureworks%' OR name LIKE '%wideworldimporters%' OR name LIKE '%contoso%' If any sample databases are found, this is a finding. Fix_Text Remove any demonstration and sample databases, database applications, objects, and files from the Azure SQL Managed Instance. Drop Database Syntax: https://learn.microsoft.com/en-us/sql/t-sql/statements/drop-database-transact-sql?view=azuresqldb-mi-current Not_Reviewed Vuln_Num V-276245 Rule_ID SV-276245r1150051_rule Severity medium Rule_Title The Azure SQL Managed Instance audit storage account must be configured to prohibit public access. Vuln_Discuss To prevent unauthorized connection of devices, unauthorized transfer of information, or unauthorized tunneling (i.e., embedding of data types within data types), organizations must disable or restrict unused or unnecessary physical and logical ports/protocols/services on information systems. Applications are capable of providing a wide variety of functions and services. Some of the functions and services provided by default may not be necessary to support essential organizational operations. Additionally, it is sometimes convenient to provide multiple services from a single component (e.g., email and web services); however, doing so increases risk over limiting the services provided by any one component. To support the requirements and principles of least functionality, the application must support the organizational requirements providing only essential capabilities and limiting the use of ports, protocols, and/or services to only those required, authorized, and approved to conduct official business or to address authorized quality of life issues. Database management systems using ports, protocols, and services deemed unsafe are open to attack through those ports, protocols, and services. This can allow unauthorized access to the audit and through the database to other components of the information system. Check_Content Azure SQL Managed Instance must only use approved firewall settings, including disabling public network access. This value is not allowed by default in Azure SQL Managed Instance and must be disabled if not otherwise documented and approved. Obtain a list of all approved firewall settings from the database documentation. Obtain the audit file location(s) by running the following SQL script: SELECT name AS AuditName, log_file_path AS AuditPath FROM sys.server_file_audits For each audit, the AuditPath column will show the Azure Storage location of the audit files. Review the storage networking permissions for the audit: 1. From the Azure Portal, click the storage account. 2. Click "Networking" under Security. 3. Verify the public network endpoint option is set to disabled. If the value is enabled and not specifically approved in the database documentation, this is a finding. Verify Network Security Groups (NSGs) are configured to restrict access only to the resources that require access to the storage account. If access is not restricted, this is a finding. Fix_Text Assign the approved policy to the audit storage account: 1. From the Azure Portal, click the audit storage account. 2. Click "Networking" under security. 3. Review the public network access option. 4. Check the box to "Disable" public network access. 5. Click "Save". Review the audit storage account's NSG configuration for inbound and outbound rules to restrict access to specific ports and resources. For more information about connection policies, refer to: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/connectivity-architecture-overview?view=azuresql&amp;tabs=current Not_Reviewed Vuln_Num V-276246 Rule_ID SV-276246r1150054_rule Severity medium Rule_Title The Azure SQL Managed Instance must be configured to prohibit or restrict the use of organization-defined functions, ports, protocols, and/or services, as defined in the Ports, Protocols, and Services Management Category Assurance List (PPSM CAL) and vulnerability assessments. Vuln_Discuss To prevent unauthorized connection of devices, unauthorized transfer of information, or unauthorized tunneling (i.e., embedding of data types within data types), organizations must disable or restrict unused or unnecessary physical and logical ports/protocols/services on information systems. Applications are capable of providing a wide variety of functions and services. Some of the functions and services provided by default may not be necessary to support essential organizational operations. Additionally, it is sometimes convenient to provide multiple services from a single component (e.g., email and web services); however, doing so increases risk over limiting the services provided by any one component. To support the requirements and principles of least functionality, the application must support the organizational requirements providing only essential capabilities and limiting the use of ports, protocols, and/or services to only those required, authorized, and approved to conduct official business or to address authorized quality of life issues. Database management systems using ports, protocols, and services deemed unsafe are open to attack through those ports, protocols, and services. This can allow unauthorized access to the database and through the database to other components of the information system. Satisfies: SRG-APP-000142-DB-000094, SRG-APP-000383-DB-000364 Check_Content Azure SQL Managed Instance must only use approved firewall settings, including disabling public network access. This value is not allowed by default in Azure SQL Managed Instance and must be disabled if not otherwise documented and approved. Obtain a list of all approved firewall settings from the database documentation: 1. From the Azure Portal, click the storage account. 2. Click "Networking" under Security. 3. Verify the public network endpoint option is set to disabled. If the value is enabled and not specifically approved in the database documentation, this is a finding. Verify Network Security Groups (NSG) are configured to restrict access only to the resources that require access to the managed instance. If access is not restricted, this is a finding. Fix_Text Assign the approved policy to Azure SQL Managed Instance: 1. From the Azure Portal, click the Azure SQL Managed Instance. 2. Click "Networking" under Security. 3. Review the public endpoint option. 4. Check the box to "Disable" public endpoint. 5. Click "Save". Review the Azure SQL Managed Instance's NSG configuration for inbound and outbound rules to restrict access to specific ports and resources. For more information about connection policies, refer to: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/connectivity-architecture-overview?view=azuresql&amp;tabs=current Not_Reviewed Vuln_Num V-276247 Rule_ID SV-276247r1150045_rule Severity medium Rule_Title Azure SQL Managed Instance must uniquely identify and authenticate organizational users (or processes acting on behalf of organizational users). Vuln_Discuss To ensure accountability and prevent unauthenticated access, organizational users must be identified and authenticated to prevent potential misuse and compromise of the system. Organizational users include organizational employees or individuals the organization deems to have equivalent status of employees (e.g., contractors). Organizational users (and any processes acting on behalf of users) must be uniquely identified and authenticated for all accesses, except the following: (i) Accesses explicitly identified and documented by the organization. Organizations document specific user actions that can be performed on the information system without identification or authentication; and (ii) Accesses that occur through authorized use of group authenticators without individual authentication. Organizations may require unique identification of individuals using shared accounts, for detailed accountability of individual activity. Check_Content Review Azure SQL Managed Instance users to determine whether shared accounts exist. (This does not include the case where Azure SQL Managed Instance has a guest or public account that is providing access to publicly available information.) If accounts are determined to be shared, determine if individuals are first individually authenticated. Where an application connects to Azure SQL Managed Instance using a standard, shared account, ensure it also captures the individual user identification and passes it to Azure SQL Managed Instance. If individuals are not individually authenticated before using the shared account (e.g., by the operating system or possibly by an application making calls to the database), this is a finding. If accounts are determined to be shared, determine if they are directly accessible to end users. If so, this is a finding. Fix_Text Remove user-accessible shared accounts and use individual user IDs. If necessary, use the DROP USER command to remove user-accessible shared accounts. Example provided below. DROP USER [SharedAccount]; Not_Reviewed Vuln_Num V-276248 Rule_ID SV-276248r1150096_rule Severity medium Rule_Title Azure SQL Managed Instance must map the PKI-authenticated identity to an associated user account. Vuln_Discuss The DOD standard for authentication is DOD-approved PKI certificates. Once a PKI certificate has been validated, it must be mapped to an Azure SQL Managed Instance database user account or login for the authenticated identity to be meaningful to Azure SQL Managed Instance and useful for authorization decisions. Check_Content Determine if Azure SQL Managed Instance is configured to use Microsoft Entra ID authentication only. To verify Azure Active Directory is configured as the authentication type, use the following PowerShell commands: Get-AzSqlInstance -Name '<Azure SQL Managed Instance Name>' | Get-AzSqlInstanceActiveDirectoryOnlyAuthentication If AzureADOnlyAuthentication returns "True", this is not a finding. If AzureADOnlyAuthentication returns "False" and the need for mixed mode authentication has not been documented and approved, this is a finding. Fix_Text To set the Entra ID Administrator, use the following PowerShell command: Get-AzSqlInstance -Name '<Azure SQL Managed Instance Name>' | Set-AzSqlInstanceActiveDirectoryAdministrator -DisplayName '<Entra Admin User/Group Name>' To enable Entra ID only authentication, use the following PowerShell command: Get-AzSqlInstance -Name '<Azure SQL Managed Instance Name>' | Enable-AzSqlInstanceActiveDirectoryOnlyAuthentication Reference: https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?view=azuresql-mi&amp;tabs=azure-powershell Not_Reviewed Vuln_Num V-276249 Rule_ID SV-276249r1149656_rule Severity medium Rule_Title Azure SQL Managed Instance must uniquely identify and authenticate nonorganizational users (or processes acting on behalf of nonorganizational users). Vuln_Discuss Nonorganizational users include all information system users other than organizational users, which include organizational employees or individuals the organization deems to have equivalent status of employees (e.g., contractors, guest researchers, individuals from allied nations). Nonorganizational users must be uniquely identified and authenticated for all accesses other than those accesses explicitly identified and documented by the organization when related to the use of anonymous access, such as accessing a web server. Accordingly, a risk assessment is used in determining the authentication needs of the organization. Scalability, practicality, and security are simultaneously considered in balancing the need to ensure ease of use for access to federal information and information systems with the need to protect and adequately mitigate risk to organizational operations, assets, individuals, and other organizations. Check_Content Review documentation, Azure SQL Managed Instance settings, and authentication system settings to determine if nonorganizational users are individually identified and authenticated when logging onto the system. If accounts are determined to be shared, determine if individuals are first individually authenticated. Where an application connects to Azure SQL Managed Instance using a standard, shared account, verify it also captures the individual user identification and passes it to Azure SQL Managed Instance. If the documentation indicates that this is a public-facing, read-only (from the point of view of public users) database that does not require individual authentication, this is not a finding. If nonorganizational users are not uniquely identified and authenticated, this is a finding. Fix_Text Ensure all logins are uniquely identifiable and authenticate all nonorganizational users who log onto the system. This can be done via a combination of Azure Entra with unique accounts and the Azure SQL Managed Instance by ensuring mapping to individual accounts. Verify server documentation to ensure accounts are documented and unique. Not_Reviewed Vuln_Num V-276250 Rule_ID SV-276250r1150065_rule Severity medium Rule_Title Azure SQL Managed Instance must separate user functionality (including user interface services) from database management functionality. Vuln_Discuss Information system management functionality includes functions necessary to administer databases, network components, workstations, or servers and typically requires privileged user access. The separation of user functionality from information system management functionality is either physical or logical and is accomplished by using different computers, different central processing units, different instances of the operating system, different network addresses, combinations of these methods, or other methods, as appropriate. An example of this type of separation is observed in web administrative interfaces that use separate authentication methods for users of any other information system resources. This may include isolating the administrative interface on a different domain and with additional access controls. If administrative functionality or information regarding Azure SQL Managed Instance management is presented on an interface available for users, information on Azure SQL Managed Instance settings may be inadvertently made available to the user. Check_Content To validate Azure role-based access controls (RBAC) for a specific resource, use the PowerShell script below: $ManagedInstanceName = '<ManagedInstanceName>' $SqlMI = Get-AzSqlInstance -Name $ManagedInstanceName Get-AzRoleAssignment -Scope $SqlMI.Id | Select-Object DisplayName,SignInName,RoleDefinitionName,ObjectType If a user not assigned information system management responsibilities has membership in any of the following roles, this is a finding: - SQL Managed Instance. - Contributor. - SQL Security Manager. - SqlMI Migration Role. - User Access Administrator. - Owner. - Reader. - Role Based Access Control. - Administrator. Fix_Text To remove an Azure RBAC role assignment, use the Remove-AzRoleAssignment PowerShell command. Example command: $userPrincipalName = 'Fourth.Coffee@contoso.onmicrosoft.us' $RoleDefinitionName = 'SQL Managed Instance Contributor' $ResourceGroupName ='stigtestingrg' $ResourceName ='fourthcoffeemi' $userObjectId =(Get-AzAdUser -UserPrincipalName $userPrincipalName).Id $ResourceID =(Get-AzSqlInstance -ResourceGroupName $ResourceGroupName -Name $ManagedInstanceName).Id Remove-AzRoleAssignment -Scope $ResourceID -RoleDefinitionName $RoleDefinitionName -ObjectId $userObjectId Not_Reviewed Vuln_Num V-276251 Rule_ID SV-276251r1149662_rule Severity high Rule_Title Azure SQL Managed Instance must protect the confidentiality and integrity of all information at rest. Vuln_Discuss This control is intended to address the confidentiality and integrity of information at rest in nonmobile devices and covers user information and system information. Information at rest refers to the state of information when it is located on a secondary storage device (e.g., disk drive, tape drive) within an organizational information system. Applications and application users generate information throughout the course of their application use. User data generated, as well as application-specific configuration data, must be protected. Organizations may choose to employ different mechanisms to achieve confidentiality and integrity protections, as appropriate. If the confidentiality and integrity of application data is not protected, the data will be open to compromise and unauthorized modification. Check_Content Run the following TSQL to determine database encryption status: SELECT db.name AS DatabaseName, db.is_encrypted AS IsEncrypted, CASE WHEN dm.encryption_state = 0 THEN 'No database encryption key present, no encryption' WHEN dm.encryption_state = 1 THEN 'Unencrypted' WHEN dm.encryption_state = 2 THEN 'Encryption in progress' WHEN dm.encryption_state = 3 THEN 'Encrypted' WHEN dm.encryption_state = 4 THEN 'Key change in progress' WHEN dm.encryption_state = 5 THEN 'Decryption in progress' WHEN dm.encryption_state = 6 THEN 'Protection change in progress' END AS EncryptionState, dm.encryption_state AS EncryptionState, dm.key_algorithm AS KeyAlgorithm, dm.key_length AS KeyLength FROM sys.databases db LEFT OUTER JOIN sys.dm_database_encryption_keys dm ON db.database_id = dm.database_id WHERE db.database_id NOT IN (1,2,3,4) If the application owner and authorizing official have determined that encryption of data at rest is required and the EncryptionState column returns "UNENCRYPTED" or "DECRYPTION_IN_PROGRESS", this is a finding. Fix_Text For each database indicating "UNENCRYPTED" or "DECRYPTION_IN_PROGRESS", execute the TSQL command below to enable encryption: ALTER DATABASE [<database name between brackets>] SET ENCRYPTION ON Not_Reviewed Vuln_Num V-276252 Rule_ID SV-276252r1149665_rule Severity medium Rule_Title Azure SQL Managed Instance must be able to allocate audit record storage capacity in accordance with organization-defined audit record storage requirements. Vuln_Discuss To ensure sufficient storage capacity for the audit logs, the database management system (DBMS) must be able to allocate audit record storage capacity. Although another requirement (SRG-APP-000515-DB-000318) mandates that audit data be off-loaded to a centralized log management system, it remains necessary to provide space on the database server to serve as a buffer against outages and capacity limits of the off-loading mechanism. The task of allocating audit record storage capacity is usually performed during initial installation of the DBMS and is closely associated with the database administrator (DBA) and system administrator (SA) roles. The DBA or SA will usually coordinate the allocation of physical drive space with the application owner/installer and the application will prompt the installer to provide the capacity information, the physical location of the disk, or both. In determining the capacity requirements, consider such factors as: total number of users; expected number of concurrent users during busy periods; number and type of events being monitored; types and amounts of data being captured; the frequency/speed with which audit records are off-loaded to the central log management system; and any limitations that exist on the DBMS's ability to reuse the space formerly occupied by off-loaded records. Check_Content Run the following query: SELECT name AS 'Audit Name', status_desc AS 'Audit Status', audit_file_path AS 'Current Audit File' FROM sys.dm_server_audit_status WHERE name NOT IN ('admin_audit','SqlDbThreatDetection_ServerAudit') If no records are returned or the audit_file_path is NULL, this is a finding. Fix_Text Review the Azure SQL Managed Instance Audit file configuration information: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure?view=azuresql Not_Reviewed Vuln_Num V-276253 Rule_ID SV-276253r1150067_rule Severity medium Rule_Title Azure SQL Managed Instance must provide a warning to appropriate support staff when allocated audit record storage volume reaches 75 percent of maximum audit record storage capacity. Vuln_Discuss Auditing for Azure SQL Managed Instance tracks database events and writes them to an audit log in the Azure storage account, Log Analytics workspace, or Event Hubs. Under normal conditions, the audit space allocated by an Azure Storage account can grow quite large. Since a requirement exists to halt processing upon audit failure, a service outage would result. Check_Content Azure SQL Managed Instance must provide notice upon audit storage reaching capacity. Verify if an Azure Rule exists with the following command example: $storageAcct = Get-AzStorageAccount -ResourceGroupName 'Name of RG for Audit Storage' -Name 'Audit Storage Account Name' $metric = Get-AzMetricAlertRuleV2 | Where-Object TargetResourceId -eq $storageAcct.Id $metric.Criteria If no alert exists, this is a finding. If the criteria does not match 75 percent or less than the maximum capacity of 5TB, this is a finding. Fix_Text Utilize Alerts in Microsoft Azure Monitoring and/or third-party tools to configure the system to notify appropriate support staff immediately upon storage volume utilization reaching 75 percent. Refer to: https://learn.microsoft.com/en-us/azure/azure-monitor/alerts/alerts-overview Not_Reviewed Vuln_Num V-276254 Rule_ID SV-276254r1149671_rule Severity medium Rule_Title Azure SQL Managed Instance must generate audit records when security objects are modified. Vuln_Discuss Changes in the database objects (tables, views, procedures, functions) that record and control permissions, privileges, and roles granted to users and roles must be tracked. Without an audit trail, unauthorized changes to the security subsystem could go undetected. The database could be severely compromised or rendered inoperative. Satisfies: SRG-APP-000496-DB-000334, SRG-APP-000496-DB-000335 Check_Content Review Azure SQL Managed Instance configuration to verify that audit records are produced when security objects are modified. To determine if an audit is configured and started by executing the following query: SELECT name AS 'Audit Name', status_desc AS 'Audit Status', audit_file_path AS 'Current Audit File' FROM sys.dm_server_audit_status WHERE name NOT IN ('admin_audit','SqlDbThreatDetection_ServerAudit') If no records are returned, this is a finding. Execute the following query to verify the 'SCHEMA_OBJECT_CHANGE_GROUP' is included in the server audit specification. SELECT a.name AS 'AuditName', s.name AS 'SpecName', d.audit_action_name AS 'ActionName', d.audited_result AS 'Result' FROM sys.server_audit_specifications s JOIN sys.server_audits a ON s.audit_guid = a.audit_guid JOIN sys.server_audit_specification_details d ON s.server_specification_id = d.server_specification_id WHERE a.is_state_enabled = 1 AND d.audit_action_name = 'SCHEMA_OBJECT_CHANGE_GROUP' If the 'SCHEMA_OBJECT_CHANGE_GROUP' is not returned in an active audit, this is a finding. Fix_Text Deploy an Azure SQL Managed Instance audit refer to the supplemental file "AzureSQLMIAudit.txt" script. Reference: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure?view=azuresql Not_Reviewed Vuln_Num V-276255 Rule_ID SV-276255r1149674_rule Severity medium Rule_Title Azure SQL Managed Instance must generate audit records when attempts to modify categorized information (e.g., classification levels/security levels) occur. Vuln_Discuss Changes in categories of 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. Satisfies: SRG-APP-000498-DB-000347, SRG-APP-000498-DB-000346 Check_Content Review Azure SQL Managed Instance configuration to verify audit records are produced when attempts to modify categorized information occur. To determine if an audit is configured, execute the following script: SELECT name AS 'Audit Name', status_desc AS 'Audit Status', audit_file_path AS 'Current Audit File' FROM sys.dm_server_audit_status WHERE name NOT IN ('admin_audit','SqlDbThreatDetection_ServerAudit') If no records are returned, this is a finding. If the auditing the retrieval of privilege/permission/role membership information is required, execute the following query to verify the 'SCHEMA_OBJECT_ACCESS_GROUP' is included in the server audit specification. SELECT a.name AS 'AuditName', s.name AS 'SpecName', d.audit_action_name AS 'ActionName', d.audited_result AS 'Result' FROM sys.server_audit_specifications s JOIN sys.server_audits a ON s.audit_guid = a.audit_guid JOIN sys.server_audit_specification_details d ON s.server_specification_id = d.server_specification_id WHERE a.is_state_enabled = 1 AND d.audit_action_name = 'SCHEMA_OBJECT_ACCESS_GROUP' If the 'SCHEMA_OBJECT_ACCESS_GROUP' is not returned in an active audit, this is a finding. Fix_Text Deploy an Azure SQL Managed Instance audit. Refer to the supplemental file "AzureSQLMIAudit.sql" script. Reference: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure?view=azuresql-mi Not_Reviewed Vuln_Num V-276256 Rule_ID SV-276256r1149677_rule Severity medium Rule_Title Azure SQL Managed Instance must include additional, more detailed, organization-defined information in the audit records for audit events identified by type, location, or subject. Vuln_Discuss Information system auditing capability is critical for accurate forensic analysis. Reconstruction of harmful events or forensic analysis is not possible if audit records do not contain enough information. To support analysis, some types of events will need information to be logged that exceeds the basic requirements of event type, time stamps, location, source, outcome, and user identity. If additional information is not available, it could negatively impact forensic investigations into user actions or other malicious events. The organization must determine what additional information is required for complete analysis of the audited events. The additional information required is dependent on the type of information (e.g., sensitivity of the data and the environment within which it resides). At a minimum, the organization must employ either full-text recording of privileged commands or the individual identities of users of shared accounts, or both. The organization must maintain audit trails in sufficient detail to reconstruct events to determine the cause and impact of compromise. Examples of detailed information the organization may require in audit records are full-text recording of privileged commands or the individual identities of shared account users. Check_Content If an Azure SQL Managed Instance Audit is not in use for audit purposes, this is a finding, unless a third-party product is being used that can perform detailed auditing for Azure SQL Managed Instance. Review system documentation to determine whether Azure SQL Managed Instance is required to audit any events and fields in addition to those in the standard audit. If there are none specified, this is not a finding. If Azure SQL Managed Instance Audit is in use, compare the audit specification(s) with the documented requirements. If any such requirement is not satisfied by the audit specification(s) (or by supplemental, locally-deployed mechanisms), this is a finding. Fix_Text Design and deploy an audit that captures all auditable events and data items. In the event a third-party tool is used for auditing, it must contain all the required information, including but not limited to, events, type, location, subject, date and time, and who made the change. Implement additional custom audits to capture the additional organizational required information. Not_Reviewed Vuln_Num V-276257 Rule_ID SV-276257r1150023_rule Severity medium Rule_Title Azure SQL Managed Instance must generate audit records when attempts to delete security objects occur. Vuln_Discuss The removal of security objects from the database/database management system (DBMS) would seriously degrade a system's information assurance posture. If such an action is attempted, it must be logged. To aid in diagnosis, it is necessary to keep track of failed attempts in addition to the successful ones. Satisfies: SRG-APP-000501-DB-000337, SRG-APP-000501-DB-000336 Check_Content Review Azure SQL Managed Instance configuration to verify audit records are produced when unsuccessful attempts to delete security objects occur. Run this TSQL command to determine if Azure SQL Managed Instance Auditing AuditActionGroups are configured: SELECT a.name AS 'AuditName', s.name AS 'SpecName', d.audit_action_name AS 'ActionName', d.audited_result AS 'Result' FROM sys.server_audit_specifications s JOIN sys.server_audits a ON s.audit_guid = a.audit_guid JOIN sys.server_audit_specification_details d ON s.server_specification_id = d.server_specification_id WHERE a.is_state_enabled = 1 AND d.audit_action_name = 'SCHEMA_OBJECT_CHANGE_GROUP' If the 'SCHEMA_OBJECT_ACCESS_GROUP' is not returned in an active audit, this is a finding. Fix_Text Deploy an Azure SQL Managed Instance audit. Refer to the supplemental file "AzureSQLMIAudit.txt" script. Reference: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure?view=azuresql-mi Not_Reviewed Vuln_Num V-276258 Rule_ID SV-276258r1150024_rule Severity medium Rule_Title Azure SQL Managed Instance must generate audit records when attempts to delete categories of information (e.g., classification levels/security levels) occur. Vuln_Discuss 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. Satisfies: SRG-APP-000502-DB-000349, SRG-APP-000502-DB-000348 Check_Content Determine if an audit is configured and started by executing the following query: SELECT name AS 'Audit Name', status_desc AS 'Audit Status', audit_file_path AS 'Current Audit File' FROM sys.dm_server_audit_status WHERE name NOT IN ('admin_audit','SqlDbThreatDetection_ServerAudit') If no records are returned, this is a finding. If the auditing the retrieval of privilege/permission/role membership information is required, execute the following query to verify the 'SCHEMA_OBJECT_ACCESS_GROUP' is included in the server audit specification. SELECT a.name AS 'AuditName', s.name AS 'SpecName', d.audit_action_name AS 'ActionName', d.audited_result AS 'Result' FROM sys.server_audit_specifications s JOIN sys.server_audits a ON s.audit_guid = a.audit_guid JOIN sys.server_audit_specification_details d ON s.server_specification_id = d.server_specification_id WHERE a.is_state_enabled = 1 AND d.audit_action_name = 'SCHEMA_OBJECT_ACCESS_GROUP' If the 'SCHEMA_OBJECT_ACCESS_GROUP' is not returned in an active audit, this is a finding. Fix_Text Deploy an Azure SQL Managed Instance audit. Refer to the supplemental file "AzureSQLMIAudit.txt" script. Reference: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure?view=azuresql-mi Not_Reviewed Vuln_Num V-276259 Rule_ID SV-276259r1150012_rule Severity medium Rule_Title Azure SQL Managed Instance must generate audit records when logon or connection attempts occur. Vuln_Discuss For completeness of forensic analysis, it is necessary to track failed attempts to log on to Azure SQL Managed Instance. While positive identification may not be possible in a case of failed authentication, as much information as possible about the incident must be captured. Satisfies: SRG-APP-000503-DB-000351, SRG-APP-000503-DB-000350 Check_Content Review Azure SQL Managed Instance configuration to verify audit records are produced when unsuccessful logons or connection attempts occur. Determine if an audit is configured and started by executing the following query: SELECT name AS 'Audit Name', status_desc AS 'Audit Status', audit_file_path AS 'Current Audit File' FROM sys.dm_server_audit_status WHERE name NOT IN ('admin_audit','SqlDbThreatDetection_ServerAudit') Execute the following query to verify the SUCCESSFUL_LOGIN_GROUP and FAILED_LOGIN_GROUP are included in the server audit specification. SELECT a.name AS 'AuditName', s.name AS 'SpecName', d.audit_action_name AS 'ActionName', d.audited_result AS 'Result' FROM sys.server_audit_specifications s JOIN sys.server_audits a ON s.audit_guid = a.audit_guid JOIN sys.server_audit_specification_details d ON s.server_specification_id = d.server_specification_id WHERE a.is_state_enabled = 1 AND d.audit_action_name IN ('SUCCESSFUL_LOGIN_GROUP', 'FAILED_LOGIN_GROUP') If both 'SUCCESSFUL_LOGIN_GROUP' and 'FAILED_LOGIN_GROUP' are not in the active audit, this is a finding. Fix_Text Deploy an Azure SQL Managed Instance audit. Refer to the supplemental file "AzureSQLMIAudit.txt" script. Reference: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure?view=azuresql Not_Reviewed Vuln_Num V-276260 Rule_ID SV-276260r1149689_rule Severity medium Rule_Title Azure SQL Managed Instance must generate audit records for all privileged activities or other system-level access. Vuln_Discuss Without tracking privileged activity, it would be difficult to establish, correlate, and investigate the events relating to an incident or identify those responsible for one. System documentation should include a definition of the functionality considered privileged. A privileged function in this context is any operation that modifies the structure of the database, its built-in logic, or its security settings. This would include all Data Definition Language (DDL) statements and all security-related statements. In an Azure SQL Managed Instance environment, it encompasses but is not necessarily limited to: CREATE ALTER DROP GRANT REVOKE DENY There may also be Data Manipulation Language (DML) statements that, subject to context, should be regarded as privileged. Possible examples in SQL Managed Instance include: TRUNCATE TABLE; DELETE, or DELETE affecting more than n rows, for some n, or DELETE without a WHERE clause; UPDATE or UPDATE affecting more than n rows, for some n, or UPDATE without a WHERE clause; any SELECT, INSERT, UPDATE, or DELETE to an application-defined security table executed by other than a security principal. Depending on the capabilities of Azure SQL Managed Instance and the design of the database and associated applications, audit logging may be achieved by means of DBMS auditing features, database triggers, other mechanisms, or a combination of these. Note that it is particularly important to audit, and tightly control, any action that weakens the implementation of this requirement itself, since the objective is to have a complete audit trail of all administrative activity. Satisfies: SRG-APP-000504-DB-000354, SRG-APP-000504-DB-000355 Check_Content Review Azure SQL Managed Instance configuration to verify that audit records are produced for all privileged activities or other system-level access. Run this TSQL command to determine if SQL Auditing AuditActionGroups are configured: SELECT name AS 'Audit Name', status_desc AS 'Audit Status', audit_file_path AS 'Current Audit File' FROM sys.dm_server_audit_status WHERE name NOT IN ('admin_audit','SqlDbThreatDetection_ServerAudit') If no values exist for Audit Name, this is a finding. Verify the following AuditActionGroup(s) are configured: APPLICATION_ROLE_CHANGE_PASSWORD_GROUP AUDIT_CHANGE_GROUP BACKUP_RESTORE_GROUP DATABASE_CHANGE_GROUP DATABASE_OBJECT_CHANGE_GROUP DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP DATABASE_OBJECT_PERMISSION_CHANGE_GROUP DATABASE_OPERATION_GROUP DATABASE_OWNERSHIP_CHANGE_GROUP DATABASE_PERMISSION_CHANGE_GROUP DATABASE_PRINCIPAL_CHANGE_GROUP DATABASE_PRINCIPAL_IMPERSONATION_GROUP DATABASE_ROLE_MEMBER_CHANGE_GROUP DBCC_GROUP LOGIN_CHANGE_PASSWORD_GROUP SCHEMA_OBJECT_CHANGE_GROUP SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP SERVER_OBJECT_CHANGE_GROUP SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP SERVER_OBJECT_PERMISSION_CHANGE_GROUP SERVER_OPERATION_GROUP SERVER_PERMISSION_CHANGE_GROUP SERVER_PRINCIPAL_CHANGE_GROUP SERVER_PRINCIPAL_IMPERSONATION_GROUP SERVER_ROLE_MEMBER_CHANGE_GROUP SERVER_STATE_CHANGE_GROUP TRACE_CHANGE_GROUP USER_CHANGE_PASSWORD_GROUP SELECT a.name AS 'AuditName', s.name AS 'SpecName', d.audit_action_name AS 'ActionName', d.audited_result AS 'Result' FROM sys.server_audit_specifications s JOIN sys.server_audits a ON s.audit_guid = a.audit_guid JOIN sys.server_audit_specification_details d ON s.server_specification_id = d.server_specification_id WHERE a.is_state_enabled = 1 AND d.audit_action_name IN ('APPLICATION_ROLE_CHANGE_PASSWORD_GROUP','AUDIT_CHANGE_GROUP','BACKUP_RESTORE_GROUP','DATABASE_CHANGE_GROUP','DATABASE_OBJECT_CHANGE_GROUP','DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP','DATABASE_OBJECT_PERMISSION_CHANGE_GROUP','DATABASE_OPERATION_GROUP','DATABASE_OWNERSHIP_CHANGE_GROUP','DATABASE_PERMISSION_CHANGE_GROUP','DATABASE_PRINCIPAL_CHANGE_GROUP','DATABASE_PRINCIPAL_IMPERSONATION_GROUP','DATABASE_ROLE_MEMBER_CHANGE_GROUP','DBCC_GROUP','LOGIN_CHANGE_PASSWORD_GROUP','SCHEMA_OBJECT_CHANGE_GROUP','SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP','SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP','SERVER_OBJECT_CHANGE_GROUP','SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP','SERVER_OBJECT_PERMISSION_CHANGE_GROUP','SERVER_OPERATION_GROUP','SERVER_PERMISSION_CHANGE_GROUP','SERVER_PRINCIPAL_CHANGE_GROUP','SERVER_PRINCIPAL_IMPERSONATION_GROUP','SERVER_ROLE_MEMBER_CHANGE_GROUP','SERVER_STATE_CHANGE_GROUP','TRACE_CHANGE_GROUP','USER_CHANGE_PASSWORD_GROUP') If any listed AuditActionGroups do not exist in the configuration, this is a finding. Fix_Text To deploy an Azure SQL Managed Instance audit, refer to the supplemental file "AzureSQMIAudit.sql". Reference: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure?view=azuresql-mi Not_Reviewed Vuln_Num V-276261 Rule_ID SV-276261r1150068_rule Severity medium Rule_Title Azure SQL Managed Instance must generate audit records showing starting and ending time for user access to the database(s). Vuln_Discuss For completeness of forensic analysis, it is necessary to know how long a user's (or other principal's) connection to the Azure SQL Managed Instance lasts. This can be achieved by recording disconnections, in addition to logons/connections, in the audit logs. Disconnection may be initiated by the user or forced by the system (as in a timeout) or result from a system or network failure. To the greatest extent possible, all disconnections must be logged. Check_Content Determine if an audit is configured and started by executing the following query: SELECT name AS 'Audit Name', status_desc AS 'Audit Status', audit_file_path AS 'Current Audit File' FROM sys.dm_server_audit_status WHERE name NOT IN ('admin_audit','SqlDbThreatDetection_ServerAudit') If no records are returned, this is a finding. Execute the following query to verify the following events are included in the server audit specification: APPLICATION_ROLE_CHANGE_PASSWORD_GROUP AUDIT_CHANGE_GROUP BACKUP_RESTORE_GROUP DATABASE_CHANGE_GROUP DATABASE_OBJECT_CHANGE_GROUP DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP DATABASE_OBJECT_PERMISSION_CHANGE_GROUP DATABASE_OPERATION_GROUP DATABASE_OWNERSHIP_CHANGE_GROUP DATABASE_PERMISSION_CHANGE_GROUP DATABASE_PRINCIPAL_CHANGE_GROUP DATABASE_PRINCIPAL_IMPERSONATION_GROUP DATABASE_ROLE_MEMBER_CHANGE_GROUP DBCC_GROUP LOGIN_CHANGE_PASSWORD_GROUP LOGOUT_GROUP SCHEMA_OBJECT_CHANGE_GROUP SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP SERVER_OBJECT_CHANGE_GROUP SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP SERVER_OBJECT_PERMISSION_CHANGE_GROUP SERVER_OPERATION_GROUP SERVER_PERMISSION_CHANGE_GROUP SERVER_PRINCIPAL_CHANGE_GROUP SERVER_PRINCIPAL_IMPERSONATION_GROUP SERVER_ROLE_MEMBER_CHANGE_GROUP SERVER_STATE_CHANGE_GROUP TRACE_CHANGE_GROUP USER_CHANGE_PASSWORD_GROUP SELECT a.name AS 'AuditName', s.name AS 'SpecName', d.audit_action_name AS 'ActionName', d.audited_result AS 'Result' FROM sys.server_audit_specifications s JOIN sys.server_audits a ON s.audit_guid = a.audit_guid JOIN sys.server_audit_specification_details d ON s.server_specification_id = d.server_specification_id WHERE a.is_state_enabled = 1 AND d.audit_action_name IN ('APPLICATION_ROLE_CHANGE_PASSWORD_GROUP', 'AUDIT_CHANGE_GROUP', 'BACKUP_RESTORE_GROUP', 'DATABASE_CHANGE_GROUP', 'DATABASE_OBJECT_CHANGE_GROUP', 'DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP', 'DATABASE_OBJECT_PERMISSION_CHANGE_GROUP', 'DATABASE_OPERATION_GROUP', 'DATABASE_OWNERSHIP_CHANGE_GROUP', 'DATABASE_PERMISSION_CHANGE_GROUP', 'DATABASE_PRINCIPAL_CHANGE_GROUP', 'DATABASE_PRINCIPAL_IMPERSONATION_GROUP', 'DATABASE_ROLE_MEMBER_CHANGE_GROUP', 'DBCC_GROUP', 'LOGIN_CHANGE_PASSWORD_GROUP', 'LOGOUT_GROUP', 'SCHEMA_OBJECT_CHANGE_GROUP', 'SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP', 'SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP', 'SERVER_OBJECT_CHANGE_GROUP', 'SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP', 'SERVER_OBJECT_PERMISSION_CHANGE_GROUP', 'SERVER_OPERATION_GROUP', 'SERVER_PERMISSION_CHANGE_GROUP', 'SERVER_PRINCIPAL_CHANGE_GROUP', 'SERVER_PRINCIPAL_IMPERSONATION_GROUP', 'SERVER_ROLE_MEMBER_CHANGE_GROUP', 'SERVER_STATE_CHANGE_GROUP', 'TRACE_CHANGE_GROUP', 'USER_CHANGE_PASSWORD_GROUP' ) Order by d.audit_action_name If the identified groups are not returned, this is a finding. Fix_Text Deploy an Azure SQL Managed Instance audit. Refer to the supplemental file "AzureSQLMIAudit.sql" script. Reference: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure?view=azuresql-mi Not_Reviewed Vuln_Num V-276262 Rule_ID SV-276262r1149695_rule Severity medium Rule_Title Azure SQL Managed Instance must generate audit records when concurrent logons/connections by the same user from different workstations occur. Vuln_Discuss For completeness of forensic analysis, it is necessary to track who logs on to Azure SQL Managed Instance. Concurrent connections by the same user from multiple workstations may be valid use of the system; or such connections may be due to improper circumvention of the requirement to use the CAC for authentication; or they may indicate unauthorized account sharing; or they may be because an account has been compromised. If the fact of multiple, concurrent logons by a given user can be reliably reconstructed from the log entries for other events (logons/connections; voluntary and involuntary disconnections), it is not mandatory to create additional log entries specifically for this. Check_Content Review Azure SQL Managed Instance configuration to verify that audit records are produced when concurrent logons/connections by the same user from different workstations occur. To determine if an audit is configured, execute the following script: SELECT name AS 'Audit Name', status_desc AS 'Audit Status', audit_file_path AS 'Current Audit File' FROM sys.dm_server_audit_status If no records are returned, this is a finding. Run this TSQL command to determine if SQL Auditing AuditActionGroups are configured: SELECT DISTINCT sd.audit_action_name FROM sys.server_audit_specification_details sd JOIN sys.server_audit_specifications s ON s.server_specification_id = sd.server_specification_id WHERE s.is_state_enabled = 1 AND sd.audit_action_name = 'SUCCESSFUL_LOGIN_GROUP' If no values exist for AuditActionGroup, this is a finding. Fix_Text Deploy an Azure SQL Managed Instance audit. Refer to the supplemental file "AzureSQLMIAudit.sql" script. Reference: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure?view=azuresql-mi Not_Reviewed Vuln_Num V-276263 Rule_ID SV-276263r1150070_rule Severity medium Rule_Title Azure SQL Managed Instance must be able to generate audit records when access to objects occur. Vuln_Discuss 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. In an Azure SQL Managed Instance environment, types of access include, but are not necessarily limited to: SELECT INSERT UPDATE DELETE EXECUTE Satisfies: SRG-APP-000507-DB-000356, SRG-APP-000507-DB-000357 Check_Content Review Azure SQL Managed Instance configuration to verify audit records are produced when successful accesses to objects occur. Run this TSQL command to determine if SQL Auditing AuditActionGroups are configured: SELECT a.name AS 'AuditName', s.name AS 'SpecName', d.audit_action_name AS 'ActionName', d.audited_result AS 'Result' FROM sys.server_audit_specifications s JOIN sys.server_audits a ON s.audit_guid = a.audit_guid JOIN sys.server_audit_specification_details d ON s.server_specification_id = d.server_specification_id WHERE a.is_state_enabled = 1 AND d.audit_action_name = 'SCHEMA_OBJECT_ACCESS_GROUP' If no values are listed for AuditActionGroups, this is a finding. Fix_Text Deploy an Azure SQL Managed Instance audit. Refer to the supplemental file "AzureSQLMIAudit.sql" script. Reference: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure?view=azuresql-mi Not_Reviewed Vuln_Num V-276264 Rule_ID SV-276264r1149701_rule Severity medium Rule_Title Azure SQL Managed Instance must generate audit records for all direct access to the database(s). Vuln_Discuss In this context, direct access is any query, command, or call to Azure SQL Managed Instance that comes from any source other than the application(s) it supports. For example, the command line or a database management utility program. The intent is to capture all activity from administrative and nonstandard sources. Check_Content Determine whether any Server Audits are configured to filter records. From SQL Server Management Studio execute the following query: SELECT name AS AuditName, predicate AS AuditFilter FROM sys.server_audits WHERE predicate IS NOT NULL If any audits are returned, review the associated filters. If any direct access to the database(s) is being excluded, this is a finding. Fix_Text Check the system documentation for required Azure SQL Managed Instance Audits. Remove any Audit filters that exclude or reduce required auditing. Update filters to ensure direct access is not excluded. Not_Reviewed Vuln_Num V-276265 Rule_ID SV-276265r1149704_rule Severity medium Rule_Title Azure SQL Managed Instance must store audit records in an immutable blob storage container for an organizationally defined period of time. Vuln_Discuss Information stored in one location is vulnerable to accidental or incidental deletion or alteration. When configured and enabled, Azure SQL Managed Instance only supports writing audit records to a container within an Azure Blob Storage Account. To mitigate the risk associated with maintaining a single copy of this audit data, the blob container used to store the audit data generated by this Azure SQL Managed Instance must be protected by an immutable policy. This policy must be configured to lock blobs for an organizationally defined period of time. Check_Content Execute the following TSQL script to return a listing of SQL Audits defined on this Azure SQL Managed Instance: SELECT name, log_file_path AS storage_container FROM sys.server_file_audits If no audit is defined, this finding is Not Applicable. Note: The "storage_container" value includes both the Azure Storage Account name and the blob container. This value uses the format https://<Azure Storage Account Name>.blob.core.usgovcloudapi.net//. Review the system documentation to determine the period of time required for retaining audit data generated by this Azure SQL Managed Instance. If the period of time is not documented, authorized, and approved, this is a finding. Using the query results from above, review the immutable blob policy for each audit's container. 1. Log in to the Azure Portal. 2. In the search box at the top, type "Storage Accounts" and select the search result. 3. Locate and click the name of the Azure Storage Account utilized by the Azure SQL Managed Instance. 4. In the left column, select "Containers". 5. Select the appropriate container from the list provided. 6. In the left column, select "Access policy" under "Immutable blob storage". 7. Click the ellipsis on the right for the "Time-based retention" policy. 8. Note the "Update retention period to" value. If the container does not have a "Time-based retention policy" defined, this is a finding. If the "Time-based retention policy" for the container is not configured for the documented number of days, this is a finding. Fix_Text Use the following script to query the Azure Storage Account(s) for any SQL Audits configured: SELECT name, log_file_path AS storage_container FROM sys.server_file_audits Note: The "storage_container" value includes both the Azure Storage Account name and the blob container. This value uses the format https://<Azure Storage Account Name>.blob.core.usgovcloudapi.net//. For each of the Azure Storage Accounts discovered above, enable an immutable blob storage policy for the blob container to which audit data is written. 1. Log in to the Azure Portal. 2. In the search box at the top, type "Storage Accounts" and select the search result. 3. Locate and click the name of the Azure Storage Account utilized by the Azure SQL Managed Instance. 4. In the left column, select "Containers". 5. Select the appropriate container from the list provided. 6. In the left column, select "Access policy" under "Immutable blob storage". 7. Click "Add Policy". 8. In the right blade, select the Policy Type "Time-based retention". 9. Enter the documented retention period under "Set retention period for". 10. Click "Save". 11. Click the ellipsis to the right of the newly created policy. 12. Select "Lock Policy". 13. Type "yes" to complete locking the policy. Not_Reviewed Vuln_Num V-276267 Rule_ID SV-276267r1150098_rule Severity medium Rule_Title Azure SQL Managed Instance must implement the capability to centrally review and analyze audit records from multiple components within the system using a service such as Azure Log Analytics. Vuln_Discuss Automated mechanisms for centralized reviews and analyses include Security Information and Event Management products. Check_Content Review the system documentation to determine whether a centralized repository of audit data is required by the data owner or organization. If this is not required, this finding is Not Applicable. Run the following query to return a listing of active Server Audits not used for auditing Microsoft Support activities: SELECT audit_guid, name, type_desc, is_operator_audit, is_state_enabled FROM sys.server_audits A WHERE type_desc = 'EXTERNAL MONITOR' AND is_operator_audit = 0 AND is_state_enabled = 1 If no audits are returned, this is a finding. Determine whether the Azure SQL Managed Instance is configured to forward SQL Security Audit Events to a centralized repository such as Log Analytics. 1. Connect to the Azure portal and navigate to the Azure SQL Managed Instance resource. 2. In the left navigation pane, expand "Monitoring". 3. Click "Diagnostic settings". If no diagnostic settings are defined, this is a finding. Locate the diagnostic setting for SQL Security Audit Events by repeating the following steps for each setting defined: 1. Click "Edit settings" on the right. 2. Under "Logs", verify the "SQL Security Audit Event" category is flagged. 3. Under "Destination details", verify "Send to Log Analytics workspace" is flagged. If no Diagnostic setting meets both of these requirements, this is a finding. Fix_Text Configure Azure SQL Managed Instance to implement the capability to centrally review and analyze audit records from multiple components within the system. One option is to use Log Analytics to query data in the Azure Monitor Logs store. Reference: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure?#set-up-auditing-for-your-server-to-event-hubs-or-azure-monitor-logs https://learn.microsoft.com/en-us/azure/azure-monitor/logs/log-analytics-overview?tabs=simple Not_Reviewed Vuln_Num V-276268 Rule_ID SV-276268r1149713_rule Severity medium Rule_Title Azure SQL Server Managed Instance must alert organization-defined personnel or roles upon detection of unauthorized access, modification, or deletion of audit information. Vuln_Discuss Audit information includes all information needed to successfully audit system activity, such as audit records, audit log settings, audit reports, and personally identifiable information. Audit logging tools are those programs and devices used to conduct system audit and logging activities. Protection of audit information focuses on technical protection and limits the ability to access and execute audit logging tools to authorized individuals. Physical protection of audit information is addressed by both media protection controls and physical and environmental protection controls. The common language runtime (CLR) component of the .NET Framework for Microsoft Windows in SQL allows the user to write stored procedures, triggers, user-defined types, user-defined functions, user-defined aggregates, and streaming table-valued functions, using any .NET Framework language, including Microsoft Visual Basic .NET and Microsoft Visual C#. CLR packing assemblies can access resources protected by .NET Code Access Security when it runs managed code. Specifying UNSAFE enables the code in the assembly complete freedom to perform operations in the SQL process space that can potentially compromise the robustness of SQL. UNSAFE assemblies can also potentially subvert the security system of either SQL or the common language runtime. Check_Content Verify the database management system (DBMS) is configured to alert organization-defined personnel or roles upon detection of unauthorized access, modification, or deletion of audit information. If the DBMS is not configured to alert organization-defined personnel or roles upon detection of unauthorized access, modification, or deletion of audit information, this is a finding. Fix_Text Configure the DBMS to alert organization-defined personnel or roles upon detection of unauthorized access, modification, or deletion of audit information. Not_Reviewed Vuln_Num V-276269 Rule_ID SV-276269r1149716_rule Severity medium Rule_Title Azure SQL Managed Instance must prevent the installation of organization-defined software and firmware components without verification that the component has been digitally signed using a certificate recognized and approved by the organization. Vuln_Discuss The database management system (DBMS) must prevent the installation of organization-defined software and firmware components without verification that the component has been digitally signed using a certificate recognized and approved by the organization. The common language runtime (CLR) component of the .NET Framework for Microsoft Windows in SQL allows the user to write stored procedures, triggers, user-defined types, user-defined functions, user-defined aggregates, and streaming table-valued functions, using any .NET Framework language, including Microsoft Visual Basic .NET and Microsoft Visual C#. CLR packing assemblies can access resources protected by .NET Code Access Security when it runs managed code. Specifying UNSAFE enables the code in the assembly complete freedom to perform operations in the SQL process space that can potentially compromise the robustness of SQL. UNSAFE assemblies can also potentially subvert the security system of either SQL or the common language runtime. Check_Content Verify Azure SQL Managed Instance is configured to prevent the installation of organization-defined software and firmware components without verification that the component has been digitally signed using a certificate recognized and approved by the organization. If Azure SQL Managed Instance is not configured to prevent the installation of organization-defined software and firmware components without verification that the component has been digitally signed using a certificate that is recognized and approved by the organization, this is a finding. To determine if CLR is enabled, execute the following commands: EXEC SP_CONFIGURE 'show advanced options', '1'; RECONFIGURE WITH OVERRIDE; EXEC SP_CONFIGURE 'clr enabled'; If the value of "config_value" is "0", this is not a finding. If the value of "config_value" is "1", review the system documentation to determine whether the use of CLR code is approved. If it is not approved, this is a finding. If CLR code is approved, check the database for UNSAFE assembly permission using the following script: USE [master] SELECT * FROM sys.assemblies WHERE permission_set_desc != 'SAFE' AND is_user_defined = 1; If any records are returned, review the system documentation to determine if the use of UNSAFE assemblies is approved. If it is not approved, this is a finding. Fix_Text Configure Azure SQL Managed Instance to prevent the installation of organization-defined software and firmware components without verification that the component has been digitally signed using a certificate that is recognized and approved by the organization. Disable use of or remove any CLR code that is not authorized. To disable the use of CLR, from the query prompt: sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'clr enabled', 0; GO RECONFIGURE; GO For any approved CLR code with Unsafe or External permissions, use the ALTER ASSEMBLY to change the Permission set for the Assembly and ensure a certificate is configured. Not_Reviewed Vuln_Num V-276276 Rule_ID SV-276276r1149737_rule Severity medium Rule_Title Azure SQL Server Managed Instance must, for password-based authentication, require immediate selection of a new password upon account recovery. Vuln_Discuss Password-based authentication applies to passwords regardless of whether they are used in single-factor or multifactor authentication. Long passwords or passphrases are preferable over shorter passwords. Enforced composition rules provide marginal security benefits while decreasing usability. However, organizations may choose to establish certain rules for password generation (e.g., minimum character length for long passwords) under certain circumstances and can enforce this requirement in IA-5(1)(h). Account recovery can occur, for example, in situations when a password is forgotten. Cryptographically protected passwords include salted one-way cryptographic hashes of passwords. The list of commonly used, compromised, or expected passwords includes passwords obtained from previous breach corpuses, dictionary words, and repetitive or sequential characters. The list includes context-specific words, such as the name of the service, username, and derivatives thereof. Check_Content Determine whether Azure SQL Managed Instance is configured to use Entra-only authentication. Run this PowerShell command to determine whether Microsoft Entra-only authentication is enabled: Get-AzSqlInstanceActiveDirectoryOnlyAuthentication -InstanceName <myinstance> -ResourceGroupName <myresource> If "AzureADOnlyAuthentication" value is "True", this is not a finding. OR In a query interface such as the SSMS Transact-SQL editor, run the statement: SELECT CASE SERVERPROPERTY('IsExternalAuthenticationOnly') WHEN 1 THEN 'Entra-only Authentication' WHEN 0 THEN 'Entra and SQL Server Authentication' END as [Authentication Mode] If "Entra-only Authentication" is returned, this is not a finding. Otherwise, verify documentation exists requiring administrators to select "User must change password at next login" when a SQL login password has been reset. If documentation does not exist requiring the selection of a new password upon administrator password reset, this is a finding. Fix_Text Create documentation to ensure administrators select "User must change password at next login" when a SQL login password is reset. OR Enable Entra-only Authentication. Refer to: https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-azure-ad-only-authentication-tutorial?view=azuresql&tabs=azure-powershell Not_Reviewed Vuln_Num V-276285 Rule_ID SV-276285r1149764_rule Severity medium Rule_Title Azure SQL Managed Instance must limit privileges to change software modules, to include stored procedures, functions, and triggers. Vuln_Discuss 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. Accordingly, only qualified and authorized individuals will be allowed to obtain access to information system components for purposes of initiating changes, including upgrades and modifications. Unmanaged changes that occur to the database software libraries or configuration can lead to unauthorized or compromised installations. Check_Content Obtain a listing of users and roles who are authorized to change stored procedures, functions, and triggers from the server documentation. In each user database, execute the following queries: SELECT P.type_desc AS principal_type, P.name AS principal_name, O.type_desc, CASE class WHEN 0 THEN DB_NAME() WHEN 1 THEN OBJECT_SCHEMA_NAME(major_id) + '.' + OBJECT_NAME(major_id) WHEN 3 THEN SCHEMA_NAME(major_id) ELSE class_desc + '(' + CAST(major_id AS nvarchar) + ')' END AS securable_name, DP.state_desc, DP.permission_name FROM sys.database_permissions DP JOIN sys.database_principals P ON DP.grantee_principal_id = P.principal_id LEFT OUTER JOIN sys.all_objects O ON O.object_id = DP.major_id AND O.type IN ('TR','TA','P','X','RF','PC','IF','FN','TF','U') WHERE DP.type IN ('AL','ALTG') AND DP.class IN (0, 1, 53) SELECT R.name AS role_name, M.type_desc AS principal_type, M.name AS principal_name FROM sys.database_principals R JOIN sys.database_role_members DRM ON R.principal_id = DRM.role_principal_id JOIN sys.database_principals M ON DRM.member_principal_id = M.principal_id WHERE R.name IN ('db_ddladmin','db_owner') AND M.name <> 'dbo' If any users or role permissions returned are not authorized to modify the specified object or type, this is a finding. If any user or role membership is not authorized, this is a finding. Fix_Text Revoke the ALTER permission from unauthorized users and roles. REVOKE ALTER ON [<Object Name>] TO [] Not_Reviewed Vuln_Num V-276286 Rule_ID SV-276286r1149767_rule Severity medium Rule_Title Azure SQL Managed Instance must limit privileges to change software modules, to include schema ownership. Vuln_Discuss 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. Accordingly, only qualified and authorized individuals will be allowed to obtain access to information system components for purposes of initiating changes, including upgrades and modifications. Unmanaged changes that occur to the database software libraries or configuration can lead to unauthorized or compromised installations. Check_Content Obtain a listing of schema ownership from the instance documentation. Execute the following query to obtain a current listing of schema ownership. SELECT S.name AS schema_name, P.name AS owning_principal FROM sys.schemas S JOIN sys.database_principals P ON S.principal_id = P.principal_id ORDER BY schema_name If any schema is owned by an unauthorized database principal, this is a finding. Fix_Text Transfer ownership of database schemas to authorized database principals. ALTER AUTHORIZATION ON SCHEMA::[<Schema Name>] TO [] Not_Reviewed Vuln_Num V-276287 Rule_ID SV-276287r1149770_rule Severity medium Rule_Title The database master key (DMK) encryption password for Azure SQL Server Managed Instance must meet DOD password complexity requirements. Vuln_Discuss 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. Check_Content Use the query below to check each database to determine whether a DMK exists and is encrypted with a password: EXEC sp_MSforeachdb 'USE [?]; SELECT DB_NAME() AS DatabaseName,COUNT(name) AS DatabaseMasterKeyExists FROM sys.symmetric_keys s, sys.key_encryptions k WHERE s.name = ''##MS_DatabaseMasterKey##'' AND s.symmetric_key_id = k.key_id AND k.crypt_type in (''ESKP'', ''ESP2'', ''ESP3'')' If the value returned is zero, this is not applicable. If the value returned is greater than zero, a DMK exists and is encrypted with a password. Review procedures and evidence of password requirements used to encrypt DMK. If the passwords are not required to meet DOD password standards, currently a minimum of 15 characters with at least one uppercase character, one lowercase character, one special character, and one numeric character, and at least eight characters changed from the previous password, this is a finding. Fix_Text Assign an encryption password to the DMK that is a minimum of 15 characters with at least one uppercase character, one lowercase character, one special character, and one numeric character, and at least eight characters changed from the previous password. To change the DMK encryption password: USE [database name]; ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'new password'; Note: The DMK encryption method must not be changed until the effects are thoroughly reviewed. Changing the master key encryption causes all encryption using the DMK to be decrypted and re-encrypted. This action must not be taken during a high-demand time. Refer to the Azure SQL Managed Instance documentation found here: https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/create-a-database-master-key?view=azuresqldb-mi-current prior to re-encrypting the DMK for detailed information. Not_Reviewed Vuln_Num V-276288 Rule_ID SV-276288r1150072_rule Severity medium Rule_Title The database master key (DMK) for Azure SQL Server Managed Instance must be encrypted by the service master key (SMK), where a DMK is required and another encryption method has not been specified. Vuln_Discuss When not encrypted by the SMK, system administrators or application administrators may access and use the DMK to view sensitive data that they are not authorized to view. Where alternate encryption means are not feasible, encryption by the SMK 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 DMK is accessed outside of the application or by a DBA account. Check_Content If no databases require encryption, this is not a finding. From the query prompt: SELECT name FROM [master].sys.databases WHERE is_master_key_encrypted_by_server = 1 AND owner_sid <> 1 AND state = 0; Note: This query assumes that the sa account is not used as the owner of application databases, in keeping with other STIG guidance. If this is not the case, modify the query accordingly. If no databases are returned by the query, this is not a finding. For any databases returned, verify in the system security plan (SSP) that encryption of the DMK using the SMK is acceptable and approved by the information owner, and the encrypted data does not require additional protections to deter or detect DBA access. If not approved, this is a finding. If approved and additional protections are required, then verify the additional requirements are in place in accordance with the SSP. These may include additional auditing on access of the DMK with alerts or other automated monitoring. If the additional requirements are not in place, this is a finding. Fix_Text Where possible, encrypt the DMK with a password known only to the application administrator. Where not possible, configure additional audit events or alerts to detect unauthorized access to the DMK by users not authorized to view sensitive data. Not_Reviewed Vuln_Num V-276289 Rule_ID SV-276289r1149776_rule Severity medium Rule_Title The Certificate used for encryption for Azure SQL Managed Instance must be backed up, stored offline and off-site. Vuln_Discuss 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. Check_Content Use the PowerShell command below to retrieve the Transparent Data Encryption key type: $ResourceGroupName = '<Resource Group Name>' $ManagedInstanceName = '<Managed Instance Name>' Get-AzSqlInstanceTransparentDataEncryptionProtector -ResourceGroupName $ResourceGroupname -InstanceName $ManagedInstanceName | Select-Object Type If the application owner and authorizing official (AO) have determined that encryption of data at rest is not required and the Transparent Data Encryption protector key is type "ServiceManaged", this is not a finding. If the application owner and AO have determined that encryption of data at rest is required and the Transparent Data Encryption protector key type is "AzureKeyVault" (commonly referred to as Bring Your Own Key or BYOK), this is a finding. Fix_Text Document and implement procedures to safely back up and store the certificate used for encryption. Include in the procedures methods to establish evidence of backup and storage, and careful, restricted access and restoration of the Certificate. Also, include provisions to store the backup off-site. BACKUP CERTIFICATE 'CertificateName' TO FILE = 'path_to_file' WITH PRIVATE KEY (FILE = 'path_to_pvk', ENCRYPTION BY PASSWORD = 'password'); As this requires a password, take care to ensure it is not exposed to unauthorized persons or stored as plain text. Not_Reviewed Vuln_Num V-276290 Rule_ID SV-276290r1149779_rule Severity low Rule_Title Azure SQL Managed Instance must isolate security functions from nonsecurity functions. Vuln_Discuss An isolation boundary provides access control and protects the integrity of the hardware, software, and firmware that perform security functions. Security 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. Developers 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. Database management systems typically separate security functionality from nonsecurity functionality via separate databases or schemas. Database objects or code implementing security functionality must not be commingled with objects or code implementing application logic. When security and nonsecurity functionality are commingled, users who have access to nonsecurity functionality may be able to access security functionality. Check_Content Determine elements of security functionality (lists of permissions, additional authentication information, stored procedures, application specific auditing, etc.) being housed inside Azure SQL Managed Instance. For any elements found, check Azure SQL Managed Instance 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. Review the system documentation to determine if the necessary database changes cannot be made and that the blockers are also documented. If the necessary changes are documented as not possible, this is not a finding. Review the database structure to determine where security related functionality is stored. If security-related database objects or code are not kept separate, this is a finding. Fix_Text Check documentation to 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. Schemas are analogous to separate namespaces or containers used to store database objects. Security permissions apply to schemas, making them an important tool for separating and protecting database objects based on access rights. Schemas reduce the work required, and improve the flexibility, for security-related administration of a database. User-schema separation allows for more flexibility in managing database object permissions. A schema is a named container for database objects, which allows the user group objects into separate namespaces. Where possible, locate security-related database objects and code in a separate database, schema, or other separate security domain from database objects and code implementing application logic. In all cases, use GRANT, REVOKE, DENY, ALTER ROLE … ADD MEMBER … and/or ALTER ROLE … DROP MEMBER statements to add and remove permissions on server-level and database-level security-related objects to provide effective isolation. Consider submitting a request to the vendor for changes to a COTS database when database structure does not isolate security functions and cannot be altered directly by the database administrators without loss of official support. Not_Reviewed Vuln_Num V-276291 Rule_ID SV-276291r1150014_rule Severity medium Rule_Title Azure SQL Managed Instance must check the validity of all data inputs except those specifically identified by the organization. Vuln_Discuss 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. 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. Potentially, the attacker can gain unauthorized access to data, including security settings, and severely corrupt or destroy the database. Even 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. This calls for inspection of application source code, which will require collaboration with the application developers. It is recognized that in many cases, the database administrator (DBA) is organizationally separate from the application developers, and may have limited, if any, access to source code. Nevertheless, protections of this type are so important to the secure operation of databases that they must not be ignored. At a minimum, the DBA must attempt to obtain assurances from the development organization that this issue has been addressed and must document what has been discovered. Check_Content Review Azure SQL Managed Instance code (stored procedures, functions, triggers), application code, settings, column and field definitions, and constraints to determine whether the database is protected against invalid input. If code exists that allows invalid data to be acted upon or input into the database, this is a finding. If column/field definitions are not reflective of the data, this is a finding. If columns/fields do not contain constraints and validity checking where required, this is a finding. Where a column/field is noted in the system documentation as necessarily free-form, even though its name and context suggest that it must be strongly typed and constrained, the absence of these protections is not a finding. Where a column/field is clearly identified by name, caption or context as Notes, Comments, Description, Text, etc., the absence of these protections is not a finding. Fix_Text Use parameterized queries, constraints, foreign keys, etc., to validate data input. Modify Azure SQL Managed Instance to properly use the correct column data types as required in the database. Consider submitting a request to the vendor for changes to a COTS database when code is discovered that could create invalid inputs and cannot be altered directly by the DBAs without loss of official support. Not_Reviewed Vuln_Num V-276293 Rule_ID SV-276293r1149788_rule Severity high Rule_Title Azure SQL Managed Instance must enforce approved authorizations for logical access to information and system resources in accordance with applicable access control policies. Vuln_Discuss Authentication with a DOD-approved PKI certificate does not necessarily imply authorization to access Azure SQL Managed Instance. 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. 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 database management systems. If Azure SQL Managed Instance 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. Check_Content Review the system documentation to determine the required levels of protection for DBMS server securables, by type of login. Review the permissions actually in place on the server. If the actual permissions do not match the documented requirements, this is a finding. Use the supplemental file "Instance permissions assignments to logins and roles.sql". Fix_Text Use GRANT, REVOKE, DENY, ALTER SERVER ROLE ... ADD MEMBER ... and/or ALTER SERVER ROLE ... DROP MEMBER statements to add and remove permissions on server-level securables, bringing them into line with the documented requirements. Not_Reviewed Vuln_Num V-276294 Rule_ID SV-276294r1149791_rule Severity medium Rule_Title Azure SQL Managed Instance must protect against a user falsely repudiating by ensuring databases are not in a trust relationship. Vuln_Discuss Nonrepudiation of actions taken is required 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. Nonrepudiation 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. Azure SQL Managed Instance 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. Check_Content If the database being reviewed is MSDB, trustworthy is required to be enabled, and therefore, this is not a finding. Execute the following query: SELECT [DatabaseName] = d.name ,[DatabaseOwner] = login.name ,[IsTrustworthy] = CASE WHEN d.is_trustworthy_on = 0 THEN 'No' WHEN d.is_trustworthy_on = 1 THEN 'Yes' END ,[IsOwnerPrivilege] = CASE WHEN role.name IN ('sysadmin','securityadmin') OR permission.permission_name = 'CONTROL SERVER' THEN 'YES' ELSE 'No' END FROM sys.databases d LEFT JOIN sys.server_principals login ON d.owner_sid = login.sid LEFT JOIN sys.server_role_members rm ON login.principal_id = rm.member_principal_id LEFT JOIN sys.server_principals role ON rm.role_principal_id = role.principal_id LEFT JOIN sys.server_permissions permission ON login.principal_id = permission.grantee_principal_id WHERE d.name <> 'msdb' If trustworthy is not enabled, this is not a finding. If trustworthy is enabled and the database owner is not a privileged account, this is not a finding. If trustworthy is enabled and the database owner is a privileged account, review the system documentation to determine if the trustworthy property is required and authorized. If this is not documented, this is a finding. Fix_Text Disable trustworthy on the database. ALTER DATABASE [<database name>] SET TRUSTWORTHY OFF; Not_Reviewed Vuln_Num V-276295 Rule_ID SV-276295r1149794_rule Severity medium Rule_Title Azure SQL Managed Instance must be configured to generate audit records for DOD-defined auditable events within all DBMS/database components. Vuln_Discuss Without the capability to generate audit records, it would be difficult to establish, correlate, and investigate the events relating to an incident or identify those responsible for one. Audit records can be generated from various components within Azure SQL Managed Instance (e.g., process, module). 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. DOD has defined the list of events for which Azure SQL Managed Instance will provide an audit record generation capability as the following: (i) Successful and unsuccessful attempts to access, modify, or delete privileges, security objects, security levels, or categories of information (e.g., classification levels); (ii) Access actions, such as successful and unsuccessful logon attempts, privileged activities, or other system-level access, starting and ending time for user access to the system, concurrent logons from different workstations, successful and unsuccessful accesses to objects, all program initiations, and all direct access to the information system; and (iii) All account creation, modification, disabling, and termination actions. Organizations may define additional events requiring continuous or ad hoc auditing. Check_Content Review documentation to determine if any additional events are required to be audited. If no additional events are required, this is not a finding. Execute the following query to get all of the audits: SELECT name AS 'Audit Name', status_desc AS 'Audit Status', audit_file_path AS 'Current Audit File' FROM sys.dm_server_audit_status WHERE name NOT IN ('admin_audit','SqlDbThreatDetection_ServerAudit') If no audits are returned, this is a finding. To view the actions being audited by the audits, execute the following query: SELECT a.name AS 'AuditName', s.name AS 'SpecName', d.audit_action_name AS 'ActionName', d.audited_result AS 'Result' FROM sys.server_audit_specifications s JOIN sys.server_audits a ON s.audit_guid = a.audit_guid JOIN sys.server_audit_specification_details d ON s.server_specification_id = d.server_specification_id WHERE a.is_state_enabled = 1 Compare the documentation to the list of generated audit events. If there are any missing events, this is a finding. Fix_Text Add all required audit events to the STIG Compliant audit specification server documentation. Refer to the supplemental file "AzureSQLMIAudit.sql" script. Reference: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure?view=azuresql Not_Reviewed Vuln_Num V-276296 Rule_ID SV-276296r1150100_rule Severity medium Rule_Title Azure SQL Managed Instance must allow only documented and approved individuals or roles to select which auditable events are to be audited. Vuln_Discuss 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. Check_Content Obtain the list of information system security officer (ISSO)/information system security manager (ISSM)-approved audit maintainers from the system documentation. Use the following query to review database roles and their membership, all of which enable the ability to create and maintain audit specifications. SELECT R.name AS role_name, RM.name AS role_member_name, RM.type_desc FROM sys.database_principals R JOIN sys.database_role_members DRM ON R.principal_id = DRM.role_principal_id JOIN sys.database_principals RM ON DRM.member_principal_id = RM.principal_id WHERE R.type = 'R' AND R.name = 'db_owner' ORDER BY role_member_name If any role memberships are not documented and authorized, this is a finding. Review the database roles and individual users that have the following permissions, all of which enable the ability to create and maintain audit definitions. ALTER ANY DATABASE AUDIT CONTROL Use the following query to determine the roles and users that have the listed permissions: SELECT PERM.permission_name, DP.name AS principal_name, DP.type_desc AS principal_type, DBRM.role_member_name FROM sys.database_permissions PERM JOIN sys.database_principals DP ON PERM.grantee_principal_id = DP.principal_id LEFT OUTER JOIN ( SELECT R.principal_id AS role_principal_id, R.name AS role_name, RM.name AS role_member_name FROM sys.database_principals R JOIN sys.database_role_members DRM ON R.principal_id = DRM.role_principal_id JOIN sys.database_principals RM ON DRM.member_principal_id = RM.principal_id WHERE R.type = 'R' ) DBRM ON DP.principal_id = DBRM.role_principal_id WHERE PERM.permission_name IN ('CONTROL','ALTER ANY DATABASE AUDIT') ORDER BY permission_name, principal_name, role_member_name If any of the roles or users returned have permissions that are not documented, or the documented audit maintainers do not have permissions, this is a finding. Fix_Text Create a database role specifically for audit maintainers and give it permission to maintain audits without granting it unnecessary permissions. (The role name used here is an example; other names may be used.) CREATE ROLE DATABASE_AUDIT_MAINTAINERS; GO GRANT ALTER ANY DATABASE AUDIT TO DATABASE_AUDIT_MAINTAINERS; GO Use REVOKE and/or DENY and/or ALTER ROLE ... DROP MEMBER ... statements to remove the ALTER ANY DATABASE AUDIT permission from all users. Then, for each authorized database user, run the statement: ALTER ROLE DATABASE_AUDIT_MAINTAINERS ADD MEMBER; GO Use REVOKE and/or DENY and/or ALTER SERVER ROLE ... DROP MEMBER ... statements to remove CONTROL DATABASE permission from logins that do not need it. Not_Reviewed Vuln_Num V-276297 Rule_ID SV-276297r1150073_rule Severity low Rule_Title Azure SQL Managed Instance must have an audit defined to track Microsoft Support Operations. Vuln_Discuss Azure SQL Managed Instance auditing capability is critical for accurate forensic analysis. Reconstruction of harmful events or forensic analysis is not possible if audit records do not contain enough information. To support analysis, some types of events require information to be logged that exceeds the basic requirements of event type, time stamps, location, source, outcome, and user identity. If additional information is not available, it could negatively impact forensic investigations into user actions or other malicious events. The organization must determine what additional information is required for complete analysis of the audited events. The additional information required is dependent on the type of information (e.g., sensitivity of the data and the environment within which it resides). At a minimum, the organization must employ either full-text recording of privileged commands or the individual identities of users of shared accounts, or both. The organization must maintain audit trails in sufficient detail to reconstruct events to determine the cause and impact of compromise. Examples of detailed information the organization may require in audit records are full-text recording of privileged commands or the individual identities of shared account users. Auditing of Microsoft support operations against an Azure SQL Managed Instance allows for the actions and operations performed by Microsoft support engineers, when accessing a server during a support request, to be audited and stored for forensic purposes. Enabling this auditing capability will overwrite an existing audit's configuration and therefore, when required, must be defined within a dedicated audit configuration. Check_Content Review the system documentation to determine if the auditing of Microsoft support operations is required. If is documented as not required, this is Not Applicable. If the system documentation requires the auditing of Microsoft Support operations, run the following query to determine whether an audit has been defined to capture Microsoft Support operations: SELECT audit_id, name, is_state_enabled FROM sys.server_audits WHERE is_operator_audit = 1 If no audit is returned, this is a finding. Fix_Text Create a new SQL Server Audit with the Microsoft support operations option enabled. Adjust the following T-SQL Query for the environment and execute: CREATE SERVER AUDIT [<Enter Name of Audit>] TO URL ( PATH = N'<URL for Blob Container>' ) WITH ( OPERATOR_AUDIT = ON ) GO ALTER SERVER AUDIT [<Enter Name of Audit>] WITH (STATE = ON) GO Alternatively, when using SQL Server Management Studio to create an audit, ensure the Microsoft support operations checkbox is flagged. Not_Reviewed Vuln_Num V-276298 Rule_ID SV-276298r1150102_rule Severity medium Rule_Title The audit information produced by Azure SQL Managed Instance must be protected from unauthorized access. Vuln_Discuss If audit data were to become compromised, then competent forensic analysis and discovery of the true source of potentially malicious system activity is difficult, if not impossible, to achieve. In addition, access to audit records provides information an attacker could potentially use to their advantage. To ensure the veracity of audit data, the information system and/or the application must protect audit information from any and all unauthorized access. This includes read, write, copy, etc. This requirement can be achieved through multiple methods, which will depend upon system architecture and design. Some commonly employed methods include ensuring log files enjoy the proper file system permissions utilizing file system protections and limiting log data location. Additionally, applications with user interfaces to audit records must not allow the unfettered manipulation of or access to those records via the application. If the application provides access to the audit data, the application becomes accountable for ensuring that audit information is protected from unauthorized access. Azure SQL Managed Instance is able to view and manipulate audit file data. Audit information includes all information (e.g., audit records, audit settings, and audit reports) needed to successfully audit information system activity. Satisfies: SRG-APP-000118-DB-000059, SRG-APP-000119-DB-000060, SRG-APP-000120-DB-000061 Check_Content To obtain the Azure SQL Managed Instance audit file location(s), connect to the Azure SQL Managed Instance in SSMS and run the following query: SELECT [AuditStorageAccount] = SUBSTRING(audit_file_path, CHARINDEX('://', audit_file_path ) + 3, CHARINDEX('.', audit_file_path ) - CHARINDEX('://', audit_file_path ) - 3) ,[AuditContainer] = SUBSTRING(audit_file_path, CHARINDEX('/', audit_file_path, CHARINDEX('//', audit_file_path) + 2) + 1, CHARINDEX('/', audit_file_path, CHARINDEX('/', audit_file_path, CHARINDEX('//', audit_file_path) + 2) + 1) - CHARINDEX('/', audit_file_path, CHARINDEX('//',audit_file_path) + 2) - 1) FROM sys.dm_server_audit_status WHERE audit_file_path IS NOT NULL The result will display the name of the storage account where the audit data is stored and the audit container. Review the storage settings for the audit. Verify that the audit storage has the correct permissions by doing the following: 1. Navigate to the audit storage account then select "Containers" under the Data Storage heading. 2. Select the Audit Container name from the query result. 3. Scroll to the Settings heading on the left-side menu and select "Configuration". 4. Verify the following settings: - "Secure transfer required" must be Enabled. - "Allow Blob anonymous access" must be Disabled. - "Allow storage account key access" must be Disabled. - "Allow recommended upper limit for shared access signature (SAS) expiry interval" must have an organizationally defined limit. - "Minimum TLS version" must be set to the latest available version that is supported by the application. Any settings that do not match the above requirements must be approved and documented, if not, this is a finding. Fix_Text Modify storage permissions to meet the requirement to protect against unauthorized access. To review the storage configuration, navigate to the Azure Portal and review the audit storage configuration. 1. Navigate to the audit storage account then select "Containers" under the Data Storage heading. 2. Select the Audit Container name from the query result. 3. Scroll to the Settings heading on the left-side menu and select "Configuration". - Set "Secure transfer required" to Enabled. - Set "Allow Blob anonymous access" to Disabled. - Set "Allow storage account key access" to Disabled. - Set "Allow recommended upper limit for shared access signature (SAS) expiry interval" to an organizationally defined limit. - Set "Minimum TLS version" to the latest available version that is supported by the application. Not_Reviewed Vuln_Num V-276299 Rule_ID SV-276299r1149806_rule Severity medium Rule_Title Azure SQL Managed Instance must protect its audit configuration from unauthorized access, modification, and deletion. Vuln_Discuss Protecting audit data also includes identifying and protecting the tools used to view and manipulate log data; therefore, protecting audit tools is necessary to prevent unauthorized operation on audit data. Applications providing tools to interface with audit data will leverage user permissions and roles identifying the user accessing the tools and the corresponding rights the user enjoys to make access decisions regarding the deletion of audit tools. Audit tools include, but are not limited to, vendor-provided and open source audit tools needed to successfully view and manipulate audit information system activity and records. Audit tools include custom queries and report generators. This focuses on external tools for log maintenance and review. Other STIG requirements govern SQL privileges to maintain trace or audit definitions. Satisfies: SRG-APP-000121-DB-000202, SRG-APP-000122-DB-000203, SRG-APP-000123-DB-000204 Check_Content Check the documentation for a list of approved users with access to Azure SQL Managed Instance Audit files. To create, alter, or drop a server audit, principals require the ALTER ANY SERVER AUDIT or the CONTROL SERVER permission. Review the SQL Server permissions granted to principals. Look for permissions ALTER ANY SERVER AUDIT, ALTER ANY DATABASE AUDIT, CONTROL SERVER: SELECT login.name, perm.permission_name, perm.state_desc FROM sys.server_permissions perm JOIN sys.server_principals login ON perm.grantee_principal_id = login.principal_id WHERE permission_name in ('ALTER ANY DATABASE AUDIT', 'ALTER ANY SERVER AUDIT', 'CONTROL SERVER') AND login.name not like '##MS_%'; Modify audit permissions to meet the requirement to protect against unauthorized access to Audit files. To review the roles and users, navigate to the Azure Portal, and review the Azure Storage container that is hosting the Audit files. Remove any undocumented permissions or excessive permissions to audit storage for user and roles. If unauthorized accounts have these privileges, this is a finding. Fix_Text Apply or modify permissions on tools used to view or modify audit log data (to include traces used for audit purposes), to make them accessible by authorized personnel only. Remove audit-related permissions from individuals and roles not authorized to have them: USE master; DENY [ALTER ANY SERVER AUDIT] TO [User]; GO Not_Reviewed Vuln_Num V-276300 Rule_ID SV-276300r1149809_rule Severity medium Rule_Title Access to xp_cmdshell must be disabled for Azure SQL Server Managed Instance unless specifically required and approved. Vuln_Discuss Information systems are capable of providing a wide variety of functions and services. Some of the functions and services, provided by default, may not be necessary to support essential organizational operations (e.g., key missions, functions). It is detrimental for applications to provide, or install by default, functionality exceeding requirements or mission objectives. Applications must adhere to the principles of least functionality by providing only essential capabilities. Azure SQL Managed Instance may spawn additional external processes to execute procedures that are defined in the Azure SQL Managed Instance but stored in external host files (external procedures). The spawned process used to execute the external procedure may operate within a different OS security context than Azure SQL Managed Instance and provide unauthorized access to the host system. The xp_cmdshell extended stored procedure allows execution of host executables outside the controls of database access permissions. This access may be exploited by malicious users who have compromised the integrity of the Azure SQL Managed Instance database process to control the host operating system to perpetrate additional malicious activity. Check_Content The xp_cmdshell extended stored procedure allows execution of host executables outside the controls of database access permissions. This access may be exploited by malicious users who have compromised the integrity of the Azure SQL Managed Instance database process to control the host operating system to perpetrate additional malicious activity. To determine if xp_cmdshell is enabled, execute the following command: SELECT name, value, value_in_use FROM sys.configurations WHERE name = 'xp_cmdshell' If "value_in_use" is a "1", review the system documentation to determine whether the use of xp_cmdshell is approved. If it is not approved, this is a finding. Fix_Text Disable use of or remove any external application executable object definitions that are not approved. To disable the use of xp_cmdshell, from the query prompt: EXEC SP_CONFIGURE 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; EXEC SP_CONFIGURE 'xp_cmdshell', 0; RECONFIGURE WITH OVERRIDE; EXEC SP_CONFIGURE 'show advanced options', 0; RECONFIGURE WITH OVERRIDE; Not_Reviewed Vuln_Num V-276301 Rule_ID SV-276301r1149812_rule Severity medium Rule_Title Access to CLR code must be disabled for Azure SQL Server Managed Instance, unless specifically required and approved. Vuln_Discuss Information systems are capable of providing a wide variety of functions and services. Some of the functions and services, provided by default, may not be necessary to support essential organizational operations (e.g., key missions, functions). It is detrimental for applications to provide, or install by default, functionality exceeding requirements or mission objectives. Applications must adhere to the principles of least functionality by providing only essential capabilities. Azure SQL Managed Instance may spawn additional external processes to execute procedures that are defined in the Azure SQL Managed Instance but stored in external host files (external procedures). The spawned process used to execute the external procedure may operate within a different OS security context than Azure SQL Managed Instance and provide unauthorized access to the host system. The common language runtime (CLR) component of the .NET Framework for Microsoft Windows in Azure SQL Managed Instance allows a user to write stored procedures, triggers, user-defined types, user-defined functions, user-defined aggregates, and streaming table-valued functions, using any .NET Framework language, including Microsoft Visual Basic .NET and Microsoft Visual C#. CLR packing assemblies can access resources protected by .NET Code Access Security when it runs managed code. Specifying UNSAFE enables the code in the assembly complete freedom to perform operations in the Azure SQL Managed Instance process space that can potentially compromise the robustness of Azure SQL Managed Instance. UNSAFE assemblies can also potentially subvert the security system of either Azure SQL Managed Instance or the common language runtime. Check_Content To determine if CLR is enabled, execute the following command: SELECT name, value, value_in_use FROM sys.configurations WHERE name = 'clr enabled' If "value_in_use" is a "1", review the system documentation to determine whether the use of CLR is approved. If it is not approved, this is a finding. Fix_Text Disable use of or remove any CLR code that is not authorized. To disable the use of CLR, from the query prompt: EXEC SP_CONFIGURE 'clr enabled', 0; RECONFIGURE WITH OVERRIDE; Not_Reviewed Vuln_Num V-276302 Rule_ID SV-276302r1149815_rule Severity medium Rule_Title Access to linked servers must be disabled or restricted for Azure SQL Server Managed Instance, unless specifically required and approved. Vuln_Discuss Information systems are capable of providing a wide variety of functions and services. Some of the functions and services, provided by default, may not be necessary to support essential organizational operations (e.g., key missions, functions). It is detrimental for applications to provide, or install by default, functionality exceeding requirements or mission objectives. Applications must adhere to the principles of least functionality by providing only essential capabilities. Azure SQL Managed Instance may spawn additional external processes to execute procedures that are defined in the Azure SQL Managed Instance but stored in external host files (external procedures). The spawned process used to execute the external procedure may operate within a different OS security context than Azure SQL Managed Instance and provide unauthorized access to the host system. A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. After a linked server is created, distributed queries can be run against this server, and queries can join tables from more than one data source. If the linked server is defined as an instance of SQL Server, Azure SQL Database, or Azure SQL Managed Instance, remote stored procedures can be executed. This access may be exploited by malicious users who have compromised the integrity of the Azure SQL Managed Instance. Check_Content A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. After a linked server is created, distributed queries can be run against this server, and queries can join tables from more than one data source. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed. To obtain a list of linked servers, execute the following command: SELECT name FROM sys.servers s WHERE s.is_linked = 1 Review the system documentation to determine whether the linked servers listed are required and approved. If it is not approved, this is a finding. Run the following to get a linked server login mapping: SELECT s.name, p.principal_id, l.remote_name FROM sys.servers s JOIN sys.linked_logins l ON s.server_id = l.server_id LEFT JOIN sys.server_principals p ON l.local_principal_id = p.principal_id WHERE s.is_linked = 1 Review the linked login mapping and check the remote name as it can impersonate sysadmin. If a login in the list is impersonating sysadmin and system documentation does not require this, it is a finding. Fix_Text Disable use of or remove any linked servers that are not authorized. To remove a linked server and all associated logins, execute the following: EXEC sp_dropserver 'LinkedServerName', 'droplogins'. To remove a login from a linked server, execute the following: EXEC sp_droplinkedsrvlogin 'LoginName', NULL; Not_Reviewed Vuln_Num V-276303 Rule_ID SV-276303r1150104_rule Severity high Rule_Title If DBMS authentication using passwords is employed, Azure SQL Managed Instance must enforce the DOD standards for password complexity and lifetime. Vuln_Discuss OS/enterprise authentication and identification must be used (SRG-APP-000023-DB-000001). Native DBMS authentication may be used only when circumstances make it unavoidable; and must be documented and AO-approved. The DOD standard for authentication is DOD-approved PKI certificates. Authentication based on User ID and Password may be used only when it is not possible to employ a PKI certificate and requires AO approval. In such cases, the DOD standards for password complexity and lifetime must be implemented. DBMS products that can inherit the rules for these from the operating system or access control program (e.g., Microsoft Active Directory or Microsoft Entra) must be configured to do so. For other DBMSs, the rules must be enforced using available configuration parameters or custom code. Satisfies: SRG-APP-000164-DB-000401, SRG-APP-000700-DB-000100 Check_Content Check for use of SQL Authentication: SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly') WHEN 1 THEN 'Windows Authentication' WHEN 0 THEN 'SQL Authentication' END as [Authentication Mode] If the returned value in the Authentication Mode column is "Windows Authentication", this is not a finding. Azure SQL Managed Instance must be configured to inherit password complexity and password lifetime rules from the operating system. Review Azure SQL Managed Instance to ensure logons are created with respect to the complexity settings and password lifetime rules by running the statement: SELECT [name], is_expiration_checked, is_policy_checked FROM sys.sql_logins WHERE is_disabled = 0 Review any accounts returned by the query other than the disabled SA account, ##MS_PolicyTsqlExecutionLogin##, and ##MS_PolicyEventProcessingLogin##. If any account does not have both "is_expiration_checked" and "is_policy_checked" equal to "1", this is a finding. Fix_Text Ensure check of policy and expiration are enforced when SQL logins are created. Use the command below to set CHECK_EXPIRATION and CHECK_POLICY to on for any login found to be noncompliant: ALTER LOGIN [LoginnameHere] WITH CHECK_EXPIRATION=ON; ALTER LOGIN [LoginNameHere] WITH CHECK_POLICY=ON; New SQL authenticated logins must be created with CHECK_EXPIRATION and CHECK_POLICY set to ON. CREATE LOGIN [LoginNameHere] WITH PASSWORD = 'ComplexPasswordHere', CHECK_EXPIRATION = ON, CHECK_POLICY = ON; Not_Reviewed Vuln_Num V-276304 Rule_ID SV-276304r1149821_rule Severity medium Rule_Title Azure SQL Server Managed Instance contained databases must use Microsoft Entra or native Windows principals. Vuln_Discuss OS/enterprise authentication and identification must be used (SRG-APP-000023-DB-000001). Native DBMS authentication may be used only when circumstances make it unavoidable; and must be documented and AO-approved. The DOD standard for authentication is DOD-approved PKI certificates. Authentication based on User ID and Password may be used only when it is not possible to employ a PKI certificate and requires AO approval. In such cases, the DOD standards for password complexity and lifetime must be implemented. DBMS products that can inherit the rules for these from the operating system or access control program (e.g., Microsoft Active Directory or Microsoft Entra) must be configured to do so. For other DBMSs, the rules must be enforced using available configuration parameters or custom code. Check_Content Execute the following query to determine if Contained Databases are used: SELECT * FROM sys.databases WHERE containment = 1 If any records are returned. Check the server documentation for a list of authorized contained database users. Execute the following query to ensure contained database users are not using SQL Authentication: EXEC sp_MSforeachdb 'USE [?]; SELECT DB_NAME() AS DatabaseName, * FROM sys.database_principals dp inner join sys.databases d on d.name = dp.name WHERE dp.authentication_type = 2 and d.containment = 1' If any records are returned, this is a finding. Fix_Text Configure Azure SQL Managed Instance contained databases to have users originating from Microsoft Entra (Azure Active Directory) principals. Remove any users not created from Microsoft Entra principals. Reference Microsoft Entra Authentication: https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-overview?view=azuresql-mi Reference Contained Databases: https://learn.microsoft.com/en-us/sql/relational-databases/databases/contained-databases?view=azuresqldb-mi-current Not_Reviewed Vuln_Num V-276305 Rule_ID SV-276305r1150105_rule Severity high Rule_Title If passwords are used for authentication, Azure SQL Server Managed Instance must transmit only encrypted representations of passwords. Vuln_Discuss The DOD standard for authentication is DOD-approved PKI certificates. Authentication based on User ID and Password may be used only when it is not possible to employ a PKI certificate and requires AO approval. In such cases, passwords must be protected at all times, and encryption is the standard method for protecting passwords during transmission. Azure SQL Managed Instance passwords sent in clear text format across the network are vulnerable to discovery by unauthorized users. Disclosure of passwords may easily lead to unauthorized access to the database. Transport Layer Security (TLS) encryption is a required security setting as a number of known vulnerabilities have been reported against Secure Sockets Layer (SSL) and earlier versions of TLS. Encryption of private information is essential to ensuring data confidentiality. If private information is not encrypted, it can be intercepted and easily read by an unauthorized party. Azure SQL Managed Instance must use a FIPS-approved minimum TLS version, and all non-FIPS-approved SSL and TLS versions must be disabled. NIST SP 800-52 Rev. 2 specifies the preferred configurations for government systems. Check_Content Verify the MinimalTLSversion using the following PowerShell script: $Subscription = 'SubscriptionValueHere' $Environment = 'EnvironmentTypeHere' Connect-AzAccount -Subscription $Subscription -Environment $Environment $ResourceGroup = 'ResourceGroupHere' $ManagedInstance = 'ManagedInstanceHere' $TLSmax = (Get-Command -Name set-azsqlinstance -ParameterName 'minimaltlsversion').parameters['minimaltlsversion'].attributes.where({$_ -is [ValidateSet] }).ValidValues Select-Object -Last 1 $TLScur = (Get-AzSqlInstance -ResourceGroupName $ResourceGroup -Name $ManagedInstance).MinimalTlsVersion Write-Host "Latest TLS Version : [$TlSMax]" Write-Host "Current TLS Version : [$TLScur]" Verify that the minimum TLS version property is set to the latest available TLS version. If a less secure TLS version is set and not documented as required by the supported application, this is a finding. Fix_Text Run the following PowerShell script to set the Azure SQL Managed Instance MinimalTLSversion to the latest available TLS version: ##This is an example script## $ResourceGroup = '<resource group name here>' $ManagedInstance = '<Azure SQL Managed Instance name here>' $TLSmax = (Get-Command -Name set-azsqlinstance -ParameterName 'minimaltlsversion').parameters['minimaltlsversion'].attributes.where({$_ -is [ValidateSet] }).ValidValues | Select-Object -Last 1 Set-AzSqlInstance -ResourceGroupName $ResourceGroup -Name $ManagedInstance -MinimalTlsVersion $TLSmax References: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/minimal-tls-version-configure?view=azuresql-mi Not_Reviewed Vuln_Num V-276306 Rule_ID SV-276306r1150025_rule Severity medium Rule_Title Azure SQL Managed Instance must reveal detailed error messages only to the information system security officer (ISSO), information system security manager (ISSM), system administrator (SA), and database administrator (DBA). Vuln_Discuss If Azure SQL Managed Instance 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 must 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 database management system (DBMS) error messages can contain information that could aid an attacker in, among other 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. Check_Content Error messages within applications, custom database code (stored procedures, triggers) must be enforced by guidelines and code reviews practices. Azure SQL Managed Instance generates certain system events and user-defined events to the Azure SQL Managed Instance error log. The Azure SQL Managed Instance error log can be viewed using SQL Server Management Studio GUI. All users granted the security admin or sysadmin level of permission are able to view the logs. Review the users returned in the following script: USE master GO SELECT Name FROM syslogins WHERE (sysadmin = 1 or securityadmin = 1) and hasaccess = 1; If any nonauthorized users have access to the Azure SQL Managed Instance Error Log in SQL Server Management Studio, this is a finding. Fix_Text Configure audit logging, tracing and/or custom code in the database or application to record detailed error messages generated by Azure SQL Managed Instance, for review by authorized personnel. If any nonauthorized users have access to the Azure SQL Managed Instance Error Log in SQL Server Management Studio. Use the REVOKE or DENY commands to remove them from the security admin or sysadmin roles. Not_Reviewed Vuln_Num V-276307 Rule_ID SV-276307r1150107_rule Severity medium Rule_Title Azure SQL Managed Instance must prevent nonprivileged users from executing privileged functions, to include disabling, circumventing, or altering implemented security safeguards/countermeasures. Vuln_Discuss Preventing nonprivileged users from executing privileged functions mitigates the risk that unauthorized individuals or processes may gain unnecessary access to information or privileges. System documentation should include a definition of the functionality considered privileged. Depending on circumstances, privileged functions can include, for example, establishing accounts, performing system integrity checks, or administering cryptographic key management activities. Nonprivileged users are individuals that do not possess appropriate authorizations. Circumventing intrusion detection and prevention mechanisms or malicious code protection mechanisms are examples of privileged functions that require protection from nonprivileged users. A privileged function in Azure SQL Managed Instance/database context is any operation that modifies the structure of the database, its built-in logic, or its security settings. This would include all Data Definition Language (DDL) statements and all security-related statements. In an Azure SQL Managed Instance environment, it encompasses but is not necessarily limited to: CREATE ALTER DROP GRANT REVOKE DENY There may also be Data Manipulation Language (DML) statements that, subject to context, should be regarded as privileged. Possible examples include: TRUNCATE TABLE; DELETE, or DELETE affecting more than n rows, for some n, or DELETE without a WHERE clause; UPDATE or UPDATE affecting more than n rows, for some n, or UPDATE without a WHERE clause; Any SELECT, INSERT, UPDATE, or DELETE to an application-defined security table executed by other than a security principal. Depending on the capabilities of Azure SQL Managed Instance and the design of the database and associated applications, the prevention of unauthorized use of privileged functions may be achieved by means of database management system (DBMS) security features, database triggers, other mechanisms, or a combination of these. Check_Content Review server-level securables and built-in role membership to ensure only authorized users have privileged access and the ability to create server-level objects and grant permissions to themselves or others. Review the system documentation to determine the required levels of protection for DBMS server securables, by type of login. Review the permissions in place on the server. If the actual permissions do not match the documented requirements, this is a finding. Get all permission assignments to logins and roles: SELECT DISTINCT CASE WHEN SP.class_desc IS NOT NULL THEN CASE WHEN SP.class_desc = 'SERVER' AND S.is_linked = 0 THEN 'SERVER' WHEN SP.class_desc = 'SERVER' AND S.is_linked = 1 THEN 'SERVER (linked)' ELSE SP.class_desc END WHEN E.name IS NOT NULL THEN 'ENDPOINT' WHEN S.name IS NOT NULL AND S.is_linked = 0 THEN 'SERVER' WHEN S.name IS NOT NULL AND S.is_linked = 1 THEN 'SERVER (linked)' WHEN P.name IS NOT NULL THEN 'SERVER_PRINCIPAL' ELSE '???' END AS [Securable Class], CASE WHEN E.name IS NOT NULL THEN E.name WHEN S.name IS NOT NULL THEN S.name WHEN P.name IS NOT NULL THEN P.name ELSE '???' END AS [Securable], P1.name AS [Grantee], P1.type_desc AS [Grantee Type], sp.permission_name AS [Permission], sp.state_desc AS [State], P2.name AS [Grantor], P2.type_desc AS [Grantor Type] FROM sys.server_permissions SP INNER JOIN sys.server_principals P1 ON P1.principal_id = SP.grantee_principal_id INNER JOIN sys.server_principals P2 ON P2.principal_id = SP.grantor_principal_id FULL OUTER JOIN sys.servers S ON SP.class_desc = 'SERVER' AND S.server_id = SP.major_id FULL OUTER JOIN sys.endpoints E ON SP.class_desc = 'ENDPOINT' AND E.endpoint_id = SP.major_id FULL OUTER JOIN sys.server_principals P ON SP.class_desc = 'SERVER_PRINCIPAL' AND P.principal_id = SP.major_id Get all server role memberships: SELECT R.name AS [Role], M.name AS [Member] FROM sys.server_role_members X INNER JOIN sys.server_principals R ON R.principal_id = X.role_principal_id INNER JOIN sys.server_principals M ON M.principal_id = X.member_principal_id The CONTROL SERVER permission is similar but not identical to the sysadmin fixed server role. Permissions do not imply role memberships, and role memberships do not grant permissions (e.g., CONTROL SERVER does not imply membership in the sysadmin fixed server role). Ensure only the documented and approved logins have privileged functions in Azure SQL Managed Instance. If the current configuration does not match the documented baseline, this is a finding. Fix_Text Restrict the granting of permissions to server-level securables to only those authorized. Most notably, members of sysadmin and securityadmin built-in instance-level roles, CONTROL SERVER permission, and use of the GRANT with GRANT permission. Not_Reviewed Vuln_Num V-276308 Rule_ID SV-276308r1149833_rule Severity medium Rule_Title Azure SQL Managed Instance must enforce access restrictions associated with changes to the configuration of the instance. Vuln_Discuss Failure to provide logical access restrictions associated with changes to configuration may have significant effects on the overall security of the system. When dealing with access restrictions pertaining to change control, it must 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. Accordingly, only qualified and authorized individuals must be allowed to obtain access to system components for the purposes of initiating changes, including upgrades and modifications. Check_Content Obtain a list of logins who have privileged permissions and role memberships in SQL. Execute the following query to obtain a list of logins and roles and their respective permissions assignment: SELECT p.name AS Principal, p.type_desc AS Type, sp.permission_name AS Permission, sp.state_desc AS State FROM sys.server_principals p INNER JOIN sys.server_permissions sp ON p.principal_id = sp.grantee_principal_id WHERE sp.permission_name = 'CONTROL SERVER' OR sp.state = 'W' Execute the following query to obtain a list of logins and their role memberships. SELECT m.name AS Member, m.type_desc AS Type, r.name AS Role FROM sys.server_principals m INNER JOIN sys.server_role_members rm ON m.principal_id = rm.member_principal_id INNER JOIN sys.server_principals r ON rm.role_principal_id = r.principal_id WHERE r.name IN ('sysadmin','securityadmin','serveradmin') Check the server documentation to verify the logins and roles returned are authorized. If the logins and/or roles are not documented and authorized, this is a finding. Fix_Text Revoke unauthorized permissions from principals: https://learn.microsoft.com/en-us/sql/t-sql/statements/revoke-server-permissions-transact-sql?view=azuresqldb-mi-current Remove unauthorized logins from roles: ALTER SERVER ROLE DROP MEMBER login; Refer to: https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-server-role-transact-sql?view=azuresqldb-mi-current Not_Reviewed Vuln_Num V-276309 Rule_ID SV-276309r1149836_rule Severity medium Rule_Title Azure Resource Manager must enforce access restrictions associated with changes to the configuration of Azure SQL Managed Instance. Vuln_Discuss Failure to provide logical access restrictions associated with changes to configuration may have significant effects on the overall security of the system. When dealing with access restrictions pertaining to change control, it must 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. Accordingly, only qualified and authorized individuals must be allowed to obtain access to system components for the purposes of initiating changes, including upgrades and modifications. Check_Content Obtain a list of accounts who have privileged access to the server via the Administrators role. For Control Plane Role Memberships, run this script in PowerShell: $ManagedInstanceName = '<ManagedInstanceName>' $SqlMI = Get-AzSqlInstance -Name $ManagedInstanceName Get-AzRoleAssignment -Scope $SqlMI.Id | Select-Object DisplayName,SignInName,RoleDefinitionName,ObjectType Check the documentation to verify the accounts and roles returned are authorized. If the accounts and roles are not documented and authorized, this is a finding. Fix_Text Remove accounts from the Administrators role that are not authorized. Reference: https://learn.microsoft.com/en-us/azure/role-based-access-control/role-assignments-portal Not_Reviewed Vuln_Num V-276310 Rule_ID SV-276310r1150016_rule Severity medium Rule_Title Azure SQL Managed Instance must produce audit records of its enforcement of access restrictions associated with changes to the configuration of Azure SQL Managed Instance or database(s). Vuln_Discuss Without auditing the enforcement of access restrictions against changes to configuration, it would be difficult to identify attempted attacks and an audit trail would not be available for forensic investigation for after-the-fact actions. Enforcement actions are the methods or mechanisms used to prevent unauthorized changes to configuration settings. Enforcement action methods may be as simple as denying access to a file based on the application of file permissions (access restriction). Audit items may consist of lists of actions blocked by access restrictions or changes identified after the fact. Check_Content Determine if an audit is configured to capture denied actions and started by executing the following query: SELECT name AS 'Audit Name', status_desc AS 'Audit Status', audit_file_path AS 'Current Audit File' FROM sys.dm_server_audit_status WHERE name NOT IN ('admin_audit','SqlDbThreatDetection_ServerAudit') If no records are returned, this is a finding. Execute the following query to verify the following events are included in the server audit specification: APPLICATION_ROLE_CHANGE_PASSWORD_GROUP, AUDIT_CHANGE_GROUP, BACKUP_RESTORE_GROUP, DATABASE_CHANGE_GROUP, DATABASE_OBJECT_ACCESS_GROUP, DATABASE_OBJECT_CHANGE_GROUP, DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP, DATABASE_OBJECT_PERMISSION_CHANGE_GROUP, DATABASE_OWNERSHIP_CHANGE_GROUP, DATABASE_OPERATION_GROUP, DATABASE_PERMISSION_CHANGE_GROUP, DATABASE_PRINCIPAL_CHANGE_GROUP, DATABASE_PRINCIPAL_IMPERSONATION_GROUP, DATABASE_ROLE_MEMBER_CHANGE_GROUP, DBCC_GROUP, LOGIN_CHANGE_PASSWORD_GROUP, SCHEMA_OBJECT_CHANGE_GROUP, SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP, SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP, SERVER_OBJECT_CHANGE_GROUP, SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP, SERVER_OBJECT_PERMISSION_CHANGE_GROUP, SERVER_OPERATION_GROUP, SERVER_PERMISSION_CHANGE_GROUP, SERVER_PRINCIPAL_IMPERSONATION_GROUP, SERVER_ROLE_MEMBER_CHANGE_GROUP, SERVER_STATE_CHANGE_GROUP, TRACE_CHANGE_GROUP SELECT a.name AS 'AuditName', s.name AS 'SpecName', d.audit_action_name AS 'ActionName', d.audited_result AS 'Result' FROM sys.server_audit_specifications s JOIN sys.server_audits a ON s.audit_guid = a.audit_guid JOIN sys.server_audit_specification_details d ON s.server_specification_id = d.server_specification_id WHERE a.is_state_enabled = 1 AND d.audit_action_name IN ( 'APPLICATION_ROLE_CHANGE_PASSWORD_GROUP', 'AUDIT_CHANGE_GROUP', 'BACKUP_RESTORE_GROUP', 'DATABASE_CHANGE_GROUP', 'DATABASE_OBJECT_ACCESS_GROUP', 'DATABASE_OBJECT_CHANGE_GROUP', 'DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP', 'DATABASE_OBJECT_PERMISSION_CHANGE_GROUP', 'DATABASE_OWNERSHIP_CHANGE_GROUP', 'DATABASE_OPERATION_GROUP', 'DATABASE_PERMISSION_CHANGE_GROUP', 'DATABASE_PRINCIPAL_CHANGE_GROUP', 'DATABASE_PRINCIPAL_IMPERSONATION_GROUP', 'DATABASE_ROLE_MEMBER_CHANGE_GROUP', 'DBCC_GROUP', 'LOGIN_CHANGE_PASSWORD_GROUP', 'SCHEMA_OBJECT_CHANGE_GROUP', 'SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP', 'SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP', 'SERVER_OBJECT_CHANGE_GROUP', 'SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP', 'SERVER_OBJECT_PERMISSION_CHANGE_GROUP', 'SERVER_OPERATION_GROUP', 'SERVER_PERMISSION_CHANGE_GROUP', 'SERVER_PRINCIPAL_IMPERSONATION_GROUP', 'SERVER_ROLE_MEMBER_CHANGE_GROUP', 'SERVER_STATE_CHANGE_GROUP', 'TRACE_CHANGE_GROUP' ) Order by d.audit_action_name If the identified groups are not returned, this is a finding. Fix_Text Add the required events to the server audit specification to audit denied actions. Refer to the supplemental file "AzureSQLMIAudit.sql" script. Reference: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure?view=azuresql Not_Reviewed Vuln_Num V-276311 Rule_ID SV-276311r1149842_rule Severity medium Rule_Title Azure SQL Managed Instance must maintain a separate execution domain for each executing process. Vuln_Discuss Database management systems can maintain separate execution domains for each executing process by assigning each process a separate address space. Each process has a distinct address space so that communication between processes is controlled through the security functions, and one process cannot modify the executing code of another process. Maintaining separate execution domains for executing processes can be achieved, for example, by implementing separate address spaces. Check_Content Review the server documentation to determine whether use of CLR assemblies is required. To determine if CLR is enabled, execute the following command: SELECT name, value, value_in_use FROM sys.configurations WHERE name = 'clr enabled' If "value_in_use" is a "1", review the system documentation to determine whether the use of CLR code is approved. If it is not approved, this is a finding. Fix_Text Disable use of or remove any CLR code that is not authorized. To disable the use of CLR, from the query prompt: EXEC SP_CONFIGURE 'clr enabled', 0; RECONFIGURE WITH OVERRIDE; Not_Reviewed Vuln_Num V-276312 Rule_ID SV-276312r1150026_rule Severity medium Rule_Title Azure SQL Managed Instance must be able to generate audit records when attempts to access security objects occur. Vuln_Discuss Changes to the security configuration must be tracked. This requirement applies to situations where security data is retrieved or modified via data manipulation operations, as opposed to via specialized security functionality. In an SQL environment, 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. Satisfies: SRG-APP-000492-DB-000332, SRG-APP-000492-DB-000333 Check_Content Determine if an audit is configured and started by executing the following query: SELECT name AS 'Audit Name', status_desc AS 'Audit Status', audit_file_path AS 'Current Audit File' FROM sys.dm_server_audit_status WHERE name NOT IN ('admin_audit','SqlDbThreatDetection_ServerAudit') If no records are returned, this is a finding. Execute the following query to verify the SCHEMA_OBJECT_ACCESS_GROUP is included in the server audit specification: SELECT a.name AS 'AuditName', s.name AS 'SpecName', d.audit_action_name AS 'ActionName', d.audited_result AS 'Result' FROM sys.server_audit_specifications s JOIN sys.server_audits a ON s.audit_guid = a.audit_guid JOIN sys.server_audit_specification_details d ON s.server_specification_id = d.server_specification_id WHERE a.is_state_enabled = 1 AND d.audit_action_name = 'SCHEMA_OBJECT_ACCESS_GROUP' If the 'SCHEMA_OBJECT_ACCESS_GROUP' is not returned in an active audit, this is a finding. Fix_Text Deploy an audit to audit the retrieval of privilege/permission/role membership information when attempts to access security objects occur. Refer to the supplemental file "AzureSQLMIAudit.sql" script. Reference: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure?view=azuresql Not_Reviewed Vuln_Num V-276313 Rule_ID SV-276313r1149848_rule Severity medium Rule_Title Azure SQL Managed Instance must generate audit records when attempts to access categorized information (e.g., classification levels/security levels) occur. Vuln_Discuss 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. To aid in diagnosis, it is necessary to keep track of failed attempts in addition to the successful attempts. Satisfies: SRG-APP-000494-DB-000345, SRG-APP-000494-DB-000344 Check_Content Determine if an audit is configured and started by executing the following query. SELECT name AS 'Audit Name', status_desc AS 'Audit Status', audit_file_path AS 'Current Audit File' FROM sys.dm_server_audit_status WHERE name NOT IN ('admin_audit','SqlDbThreatDetection_ServerAudit') If no records are returned, this is a finding. If the auditing the retrieval of privilege/permission/role membership information is required, execute the following query to verify the 'SCHEMA_OBJECT_ACCESS_GROUP' is included in the server audit specification. SELECT a.name AS 'AuditName', s.name AS 'SpecName', d.audit_action_name AS 'ActionName', d.audited_result AS 'Result' FROM sys.server_audit_specifications s JOIN sys.server_audits a ON s.audit_guid = a.audit_guid JOIN sys.server_audit_specification_details d ON s.server_specification_id = d.server_specification_id WHERE a.is_state_enabled = 1 AND d.audit_action_name = 'SCHEMA_OBJECT_ACCESS_GROUP' If the 'SCHEMA_OBJECT_ACCESS_GROUP' is not returned in an active audit, this is a finding. Fix_Text Deploy an audit to review when data classifications are both successfully and unsuccessfully retrieved. Refer to the supplemental file "AzureSQLMIAudit.sql" script. Reference: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure?view=azuresql Not_Reviewed Vuln_Num V-276314 Rule_ID SV-276314r1149851_rule Severity medium Rule_Title Azure SQL Managed Instance must generate audit records when attempts to add privileges/permissions occur. Vuln_Discuss 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 an Azure SQL Managed Instance environment, adding permissions is typically done via the GRANT command, or, in the negative, the DENY command. To aid in diagnosis, it is necessary to keep track of failed attempts in addition to the successful ones. Check_Content Check that the Azure SQL Managed Instance Audit has a STIG compliant audit. Determine if an audit is configured and started by executing the following query: SELECT name AS 'Audit Name', status_desc AS 'Audit Status', audit_file_path AS 'Current Audit File' FROM sys.dm_server_audit_status WHERE name NOT IN ('admin_audit','SqlDbThreatDetection_ServerAudit') Execute the following query to verify the required audit actions are included in the server audit specification: SELECT a.name AS 'AuditName', s.name AS 'SpecName', d.audit_action_name AS 'ActionName', d.audited_result AS 'Result' FROM sys.server_audit_specifications s JOIN sys.server_audits a ON s.audit_guid = a.audit_guid JOIN sys.server_audit_specification_details d ON s.server_specification_id = d.server_specification_id WHERE a.is_state_enabled = 1 AND d.audit_action_name IN ('DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP' ,'DATABASE_OBJECT_PERMISSION_CHANGE_GROUP' ,'DATABASE_OWNERSHIP_CHANGE_GROUP' ,'DATABASE_PERMISSION_CHANGE_GROUP' ,'DATABASE_ROLE_MEMBER_CHANGE_GROUP' ,'SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP' ,'SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP' ,'SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP' ,'SERVER_OBJECT_PERMISSION_CHANGE_GROUP' ,'SERVER_PERMISSION_CHANGE_GROUP' ,'SERVER_ROLE_MEMBER_CHANGE_GROUP') If any of the following audit actions are not returned in an active audit, this is a finding. DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP DATABASE_OBJECT_PERMISSION_CHANGE_GROUP DATABASE_OWNERSHIP_CHANGE_GROUP DATABASE_PERMISSION_CHANGE_GROUP DATABASE_ROLE_MEMBER_CHANGE_GROUP SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP SERVER_OBJECT_PERMISSION_CHANGE_GROUP SERVER_PERMISSION_CHANGE_GROUP SERVER_ROLE_MEMBER_CHANGE_GROUP If no records are returned, this is a finding. Fix_Text Deploy an audit to review attempts to add privileges/permissions. Refer to the supplemental file "AzureSQLMIAudit.sql" script. Reference: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure?view=azuresql Not_Reviewed Vuln_Num V-276315 Rule_ID SV-276315r1149854_rule Severity medium Rule_Title Azure SQL Managed Instance must generate audit records when attempts to modify privileges/permissions occur. Vuln_Discuss 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 an SQL environment, modifying permissions is typically done via the GRANT, REVOKE, and DENY commands. To aid in diagnosis, it is necessary to keep track of failed attempts in addition to the successful ones. Satisfies: SRG-APP-000495-DB-000329, SRG-APP-000495-DB-000328 Check_Content Check that Azure SQL Managed Instance Audit is being used for the STIG compliant audit. Determine if an audit is configured and started by executing the following query: SELECT name AS 'Audit Name', status_desc AS 'Audit Status', audit_file_path AS 'Current Audit File' FROM sys.dm_server_audit_status WHERE name NOT IN ('admin_audit','SqlDbThreatDetection_ServerAudit') Execute the following query to verify the required audit actions are included in the server audit specification: SELECT a.name AS 'AuditName', s.name AS 'SpecName', d.audit_action_name AS 'ActionName', d.audited_result AS 'Result' FROM sys.server_audit_specifications s JOIN sys.server_audits a ON s.audit_guid = a.audit_guid JOIN sys.server_audit_specification_details d ON s.server_specification_id = d.server_specification_id WHERE a.is_state_enabled = 1 AND d.audit_action_name IN ('DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP' ,'DATABASE_OBJECT_PERMISSION_CHANGE_GROUP' ,'DATABASE_OWNERSHIP_CHANGE_GROUP' ,'DATABASE_PERMISSION_CHANGE_GROUP' ,'DATABASE_ROLE_MEMBER_CHANGE_GROUP' ,'SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP' ,'SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP' ,'SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP' ,'SERVER_OBJECT_PERMISSION_CHANGE_GROUP' ,'SERVER_PERMISSION_CHANGE_GROUP' ,'SERVER_ROLE_MEMBER_CHANGE_GROUP') If any of the following audit actions are not returned in an active audit, this is a finding: DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP DATABASE_OBJECT_PERMISSION_CHANGE_GROUP DATABASE_OWNERSHIP_CHANGE_GROUP DATABASE_PERMISSION_CHANGE_GROUP DATABASE_ROLE_MEMBER_CHANGE_GROUP SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP SERVER_OBJECT_PERMISSION_CHANGE_GROUP SERVER_PERMISSION_CHANGE_GROUP SERVER_ROLE_MEMBER_CHANGE_GROUP If no records are returned, this is a finding. Fix_Text Add the following events to the Azure SQL Managed Instance Audit that is being used for the STIG compliant audit: DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP DATABASE_OBJECT_PERMISSION_CHANGE_GROUP DATABASE_OWNERSHIP_CHANGE_GROUP DATABASE_PERMISSION_CHANGE_GROUP DATABASE_ROLE_MEMBER_CHANGE_GROUP SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP SERVER_OBJECT_PERMISSION_CHANGE_GROUP SERVER_PERMISSION_CHANGE_GROUP SERVER_ROLE_MEMBER_CHANGE_GROUP Deploy an Azure SQL Managed Instance audit. Refer to the supplemental file "AzureSQLMIAudit.sql" script. Reference: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure?view=azuresql Not_Reviewed Vuln_Num V-276316 Rule_ID SV-276316r1150060_rule Severity medium Rule_Title Azure SQL Managed Instance must generate audit records when attempts to delete privileges/permissions occur. Vuln_Discuss 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 an Azure SQL Managed Instance environment, deleting permissions is typically done via the REVOKE or DENY command. To aid in diagnosis, it is necessary to keep track of failed attempts in addition to the successful ones. Satisfies: SRG-APP-000499-DB-000330, SRG-APP-000499-DB-000331 Check_Content Check that Azure SQL Managed Instance Audit is being used for the STIG compliant audit. Determine if an audit is configured and started by executing the following query. If no records are returned, this is a finding. SELECT name AS 'Audit Name', status_desc AS 'Audit Status', audit_file_path AS 'Current Audit File' FROM sys.dm_server_audit_status WHERE name NOT IN ('admin_audit','SqlDbThreatDetection_ServerAudit') Execute the following query to verify the required audit actions are included in the server audit specification: SELECT a.name AS 'AuditName', s.name AS 'SpecName', d.audit_action_name AS 'ActionName', d.audited_result AS 'Result' FROM sys.server_audit_specifications s JOIN sys.server_audits a ON s.audit_guid = a.audit_guid JOIN sys.server_audit_specification_details d ON s.server_specification_id = d.server_specification_id WHERE a.is_state_enabled = 1 AND d.audit_action_name IN ('DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP' ,'DATABASE_OBJECT_PERMISSION_CHANGE_GROUP' ,'DATABASE_OWNERSHIP_CHANGE_GROUP' ,'DATABASE_PERMISSION_CHANGE_GROUP' ,'DATABASE_ROLE_MEMBER_CHANGE_GROUP' ,'SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP' ,'SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP' ,'SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP' ,'SERVER_OBJECT_PERMISSION_CHANGE_GROUP' ,'SERVER_PERMISSION_CHANGE_GROUP' ,'SERVER_ROLE_MEMBER_CHANGE_GROUP') If any of the following audit actions are not returned in an active audit, this is a finding. DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP DATABASE_OBJECT_PERMISSION_CHANGE_GROUP DATABASE_OWNERSHIP_CHANGE_GROUP DATABASE_PERMISSION_CHANGE_GROUP DATABASE_ROLE_MEMBER_CHANGE_GROUP SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP SERVER_OBJECT_PERMISSION_CHANGE_GROUP SERVER_PERMISSION_CHANGE_GROUP SERVER_ROLE_MEMBER_CHANGE_GROUP Fix_Text Add the following events to the Azure SQL Managed Instance Audit being used for the STIG compliant audit: DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP DATABASE_OBJECT_PERMISSION_CHANGE_GROUP DATABASE_OWNERSHIP_CHANGE_GROUP DATABASE_PERMISSION_CHANGE_GROUP DATABASE_ROLE_MEMBER_CHANGE_GROUP SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP SERVER_OBJECT_PERMISSION_CHANGE_GROUP SERVER_PERMISSION_CHANGE_GROUP SERVER_ROLE_MEMBER_CHANGE_GROUP Refer to the supplemental file "AzureSQLMIAudit.sql". Reference: https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-ver16 Not_Reviewed Vuln_Num V-276317 Rule_ID SV-276317r1150033_rule Severity medium Rule_Title The Azure SQL Managed Instance default [sa] account must be disabled. Vuln_Discuss Azure SQL Managed Instance [sa] account has special privileges required to administer the database. The [sa] account is a well-known account and is likely to be targeted by attackers and thus more prone to providing unauthorized access to the database. This [sa] default account is administrative and could lead to catastrophic consequences, including the complete loss of control over Azure SQL Managed Instance. If the [sa] default account is not disabled, an attacker could gain access through the account. Azure SQL Managed Instance by default disables the [sa] account at creation. Some applications that run on Azure SQL Managed Instance require the [sa] account to be enabled for the application to function properly. These applications that require the [sa] account to be enabled are usually legacy systems. Check_Content Check Azure SQL Managed Instance settings to determine if the [sa] account has been disabled by executing the following query: USE master; GO SELECT name, is_disabled FROM sys.sql_logins WHERE principal_id = 1; GO The "name" column contains the current name of the [sa] database server account. If the "is_disabled" column is not set to "1", this is a finding. Fix_Text Modify the enabled flag of Azure SQL Managed Instance's [sa] account by running the following script: USE master; GO ALTER LOGIN [sa] DISABLE; GO Not_Reviewed Vuln_Num V-276318 Rule_ID SV-276318r1150020_rule Severity medium Rule_Title Azure SQL Managed Instance default [sa] account must have its name changed. Vuln_Discuss Azure SQL Managed Instance's [sa] account has special privileges required to administer the database. The [sa] account is a well-known account name that is likely to be targeted by attackers and is thus, more prone to providing unauthorized access to the database. Since [sa] is administrative in nature, the compromise of a default account can have catastrophic consequences, including the complete loss of control over Azure SQL Managed Instance. Since SQL Server needs this account to exist and it must not be removed, one way to mitigate this risk is to change the [sa] account name. Check_Content Verify the Azure SQL Managed Instance default [sa] account name has been changed by executing the following query: USE master; GO SELECT name FROM sys.sql_logins WHERE UPPER(name) = 'SA' OR principal_id = 1; If the name returned has the consecutive letters "sa" in the query output, this is a finding. Fix_Text Modify the Azure SQL Managed Instance's [sa] account name by running the following example script: USE master; GO ALTER LOGIN [sa] WITH NAME = NewAccountName Not_Reviewed Vuln_Num V-276319 Rule_ID SV-276319r1150018_rule Severity medium Rule_Title The Allow Filesystem Enumeration feature must be disabled for Azure SQL Server Managed Instance, unless specifically required and approved. Vuln_Discuss Information systems are capable of providing a wide variety of functions and services. Some of the functions and services, provided by default, may not be necessary to support essential organizational operations (e.g., key missions, functions). It is detrimental for applications to provide, or install by default, functionality exceeding requirements or mission objectives. Applications must adhere to the principles of least functionality by providing only essential capabilities. Azure SQL Managed Instance may spawn additional external processes to execute procedures that are defined in Azure SQL Managed Instance but stored in external host files (external procedures). The spawned process used to execute the external procedure may operate within a different OS security context than Azure SQL Managed Instance and provide unauthorized access to the host system. SQL Server is capable of providing a wide range of features and services. Some of the features and services, provided by default, may not be necessary, and enabling them could adversely affect the security of the system. The Allow Filesystem Enumeration feature allows access to the file system, potentially providing unauthorized access and must be disabled on secure systems. Check_Content To determine if allow filesystem enumeration is enabled, execute the following command: SELECT name, value, value_in_use FROM sys.configurations WHERE name = 'allow filesystem enumeration' If "value_in_use" is a "1", review the system documentation to determine whether the use of allow filesystem enumeration is approved. If it is not approved, this is a finding. Fix_Text Disable use of or remove any external application executable object definitions that are not approved. To disable the use of allow filesystem enumeration, from the query prompt: EXEC SP_CONFIGURE 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; EXEC SP_CONFIGURE 'allow filesystem enumeration', 0; RECONFIGURE WITH OVERRIDE; EXEC SP_CONFIGURE 'show advanced options', 0; RECONFIGURE WITH OVERRIDE; Not_Reviewed Vuln_Num V-276320 Rule_ID SV-276320r1149869_rule Severity medium Rule_Title The CLR Strict Security feature must be enabled for Azure SQL Server Managed Instance, unless specifically required and approved. Vuln_Discuss Information systems are capable of providing a wide variety of functions and services. Some of the functions and services, provided by default, may not be necessary to support essential organizational operations (e.g., key missions, functions). It is detrimental for applications to provide, or install by default, functionality exceeding requirements or mission objectives. Applications must adhere to the principles of least functionality by providing only essential capabilities. Azure SQL Managed Instance may spawn additional external processes to execute procedures that are defined in Azure SQL Managed Instance but stored in external host files (external procedures). The spawned process used to execute the external procedure may operate within a different OS security context than Azure SQL Managed Instance and provide unauthorized access to the host system. Azure SQL Managed Instance is capable of providing a wide range of features and services. CLR Strict Security interprets assemblies as unsafe, ignoring permissions on individual assemblies which otherwise may be able to access external system resources, call unmanaged code, and acquire sysadmin privileges. Check_Content The CLR Strict Security option can be disabled for backward compatibility, but this is not recommended. To determine if CLR Strict Security is enabled, execute the following command: SELECT name, value, value_in_use FROM sys.configurations WHERE name = 'CLR Strict Security' If "value_in_use" is a "0", review the system documentation to determine whether the use of CLR Strict Security is not required and approved. If it is not approved to be disabled, this is a finding. Fix_Text To enable the use of CLR Strict Security, from the query prompt: EXEC SP_CONFIGURE 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; EXEC SP_CONFIGURE 'CLR Strict Security', 1; RECONFIGURE WITH OVERRIDE; EXEC SP_CONFIGURE 'show advanced options', 0; RECONFIGURE WITH OVERRIDE; Reference: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/clr-strict-security? Not_Reviewed Vuln_Num V-276321 Rule_ID SV-276321r1149872_rule Severity medium Rule_Title The Hadoop Connectivity feature must be disabled for Azure SQL Server Managed Instance, unless specifically required and approved. Vuln_Discuss Information systems are capable of providing a wide variety of functions and services. Some of the functions and services, provided by default, may not be necessary to support essential organizational operations (e.g., key missions, functions). It is detrimental for applications to provide, or install by default, functionality exceeding requirements or mission objectives. Applications must adhere to the principles of least functionality by providing only essential capabilities. Azure SQL Managed Instance may spawn additional external processes to execute procedures that are defined in the Azure SQL Managed Instance but stored in external host files (external procedures). The spawned process used to execute the external procedure may operate within a different OS security context than Azure SQL Managed Instance and provide unauthorized access to the host system. Azure SQL Managed Instance is capable of providing a wide range of features and services. Some of the features and services, provided by default, may not be necessary, and enabling them could adversely affect the security of the system. The Hadoop Connectivity feature allows multiple types of external data sources to be created and used across all sessions on the server. An exploit to the Azure SQL Managed Instance could result in a compromise of the host system and external Azure SQL Managed Instance resources. Check_Content To determine if Hadoop Connectivity is enabled, execute the following command: SELECT name, value, value_in_use FROM sys.configurations WHERE name = 'Hadoop Connectivity' If "value_in_use" is a "1", review the system documentation to determine whether the use of Hadoop Connectivity is approved. If it is not approved, this is a finding. Fix_Text Disable the use of or remove any external application executable object definitions that are not approved. To disable the use of the Hadoop Connectivity option, from the query prompt: EXEC SP_CONFIGURE 'hadoop connectivity', 0; RECONFIGURE WITH OVERRIDE; Not_Reviewed Vuln_Num V-276322 Rule_ID SV-276322r1150027_rule Severity medium Rule_Title Azure SQL Server Managed Instance Replication Xps feature must be disabled, unless specifically required and approved. Vuln_Discuss Azure SQL Managed Instance is capable of providing a wide range of features and services. Some of the features and services, provided by default, may not be necessary, and enabling them could adversely affect the security of the system. Enabling Replication XPs opens a significant attack surface area that can be used by an attacker to gather information about the system and potentially abuse the privileges of Azure SQL Managed Instance. Check_Content To determine if Replication Xps is enabled, execute the following command: SELECT name, value, value_in_use FROM sys.configurations WHERE name = 'Replication Xps' If "value_in_use" is a "1", review the system documentation to determine whether the use of Replication Xps is approved. If it is not approved, this is a finding. Fix_Text Disable use of or remove any external application executable object definitions that are not approved. To disable the use of the Replication Xps option, from the query prompt: EXEC SP_CONFIGURE 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; EXEC SP_CONFIGURE 'replication xps', 0; RECONFIGURE WITH OVERRIDE; EXEC SP_CONFIGURE 'show advanced options', 0; RECONFIGURE WITH OVERRIDE; Not_Reviewed Vuln_Num V-276323 Rule_ID SV-276323r1149878_rule Severity high Rule_Title When using command-line tools with Azure SQL Server Managed Instance, such as SQLCMD, in a mixed-mode authentication environment, users must use a logon method that does not expose the password. Vuln_Discuss To prevent the compromise of authentication information, such as passwords and PINs, during the authentication process, the feedback from the information system must not provide any information that would allow an unauthorized user to compromise the authentication mechanism. Obfuscation of user-provided information typed into the system is a method used in addressing this risk. For example, displaying asterisks when a user types in a password or PIN, is an example of obscuring feedback of authentication information. This requirement is applicable when mixed-mode authentication is enabled. When this is the case, password-authenticated accounts can be created in, and authenticated by, Azure SQL Managed Instance. Other STIG requirements prohibit the use of mixed-mode authentication except when justified and approved. This deals with the exceptions. SQLCMD and other command-line tools are part of Azure SQL Managed Instance. These tools can accept a plain-text password but do offer alternative techniques. Since the typical user of these tools is a database administrator, the consequences of password compromise are particularly serious. Therefore, the use of plain-text passwords must be prohibited, as a matter of practice and procedure. Check_Content Run this PowerShell command to determine whether Microsoft Entra-only authentication is enabled: Get-AzSqlInstanceActiveDirectoryOnlyAuthentication -InstanceName <myinstance> -ResourceGroupName <myresource> If "AzureADOnlyAuthentication" value is "True", this is not a finding. For SQLCMD, which cannot be configured not to accept a plain-text password, and any other essential tool with the same limitation, verify the system documentation explains the need for the tool, who uses it, and any relevant mitigations; and that AO approval has been obtained; if not, this is a finding. Request evidence that all users of the tool are trained in the importance of not using the plain-text password option and in how to keep the password hidden; and that they adhere to this practice; if not, this is a finding. Fix_Text Where possible, enable Microsoft Entra-only authentication. Refer to: https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-azure-ad-only-authentication-tutorial?view=azuresql&amp;tabs=azure-powershell If mixed-mode authentication is necessary, then for SQLCMD, which cannot be configured not to accept a plain-text password when mixed-mode authentication is enabled, and any other essential tool with the same limitation, complete the following: 1. Document the need for it, who uses it, and any relevant mitigations, and obtain AO approval. 2. Train all users of the tool in the importance of not using the plain-text password option and in how to keep the password hidden. Not_Reviewed Vuln_Num V-276324 Rule_ID SV-276324r1150034_rule Severity medium Rule_Title Applications connecting to Azure SQL Server Managed Instance must obscure feedback of authentication information during the authentication process to protect the information from possible exploitation/use by unauthorized individuals. Vuln_Discuss To prevent the compromise of authentication information, such as passwords and PINs, during the authentication process, the feedback from the information system must not provide any information that would allow an unauthorized user to compromise the authentication mechanism. Obfuscation of user-provided information when typed into the system is a method used in addressing this risk. For example, displaying asterisks when a user types in a password or PIN, is an example of obscuring feedback of authentication information. Database applications may allow for entry of the account name and password as a visible parameter of the application execution command. This practice must be prohibited and disabled to prevent shoulder surfing. Check_Content Determine whether any applications that access the database allow for entry of the account name and password, or PIN. If any do, determine whether these applications obfuscate authentication data; if they do not, this is a finding. Fix_Text Configure or modify applications to prohibit display of passwords in clear text. Not_Reviewed