SQL Vulnerability Assessment Tool – Rules Reference List

Looking for an online reference page for the rules run by the SQL Vulnerability Assessment tool? Personally, I’ve been looking for such a page for a while now, but never found one. So eventually, I figured “why not do it myself?”. Cool script attached!

This page was last updated on: 2020-11-11, with a total of 41 rules.
Generated using SSMS v18.7.1 (build version 15.0.18358.0)

EDIT: Since I published this reference page initially on January 16th, 2020, Microsoft themselves have created their own version of such a reference list. It’s available here: SQL Vulnerability Assessment rules reference guide (Microsoft Docs). However, their page only contains minimal information, and does not include detailed information such as the “rationale”, the “query”, and the “remediation” sections of the assessment.

A table of contents is available below. Click on the description of each rule for more details, including the relevant T-SQL query and corresponding remediation details. There’s also an anchor link for each rule, for easy reference.

Enjoy!

Disclaimer: The information below is copyrighted to the Microsoft Corporation. I do not hold any rights to it, and it is publicly available to everyone downloading the latest version of SQL Server Management Studio and running a Vulnerability Assessment on any SQL Server database.

Auditing And Logging – 3 rule(s)

Severity: High

VA1258: Database owners are as expected

Applies To: SQL Database, Azure Synapse
⚓ anchor link

Database owners can perform all configuration and maintenance activities on the database, and can also drop databases in SQL Server. Tracking database owners is important to avoid having excessive permission for some principals. Create a baseline which defines the expected database owners for the database. This rule checks whether the database owners are as defined in the baseline.

Rationale:

Keeping track of database owners is important to avoid granting excessive permissions.

Query:

SELECT USER_NAME(member_principal_id) AS [Owner]
FROM sys.database_role_members
WHERE USER_NAME(role_principal_id) = 'db_owner'
    AND USER_NAME(member_principal_id) != 'dbo'

Remediation:

Keep track of database owners. Remove unnecessary database owners to avoid granting excessive permissions or update the baseline to reflect the approved list of owners.

ALTER ROLE db_owner DROP MEMBER [$0]

☝ back to top


Severity: Medium

VA1265: Auditing of both successful and failed login attempts for contained DB authentication should be enabled

Applies To: SQL Server, SQL Managed Instance
⚓ anchor link

SQL Server auditing configuration enables adminstrators to track users logging to SQL Server instances that they’re responsible for. This rules checks that auditing is enabled for both successful and failed login attempts for contained DB authentication.

Rationale:

Logging successful & failed login attempts provides information that can be used to detect brute-force based password attacks against the system as well as forensic information.

Query:

DECLARE @check_results INT = 0;
DECLARE @violation INT = 1;

SELECT @check_results = containment
FROM   sys.databases
WHERE name = db_name();

PRINT @check_results

IF( @check_results != 0 )
  BEGIN
      DECLARE @success_logon_event INT = 0;
      DECLARE @fail_logon_event INT = 0;

      SELECT @success_logon_event = Count(*)
      FROM   sys.server_audits adts,
             sys.server_audit_specifications srvadtspecs,
             sys.server_audit_specification_details srvadtspecdtls
      WHERE  adts.audit_guid = srvadtspecs.audit_guid
             AND adts.is_state_enabled = 1
             AND srvadtspecs.is_state_enabled = 1
             AND srvadtspecdtls.audited_result = 'SUCCESS AND FAILURE'
             AND srvadtspecdtls.audit_action_id = 'DAGS';

      SELECT @fail_logon_event = Count(*)
      FROM   sys.server_audits adts,
             sys.server_audit_specifications srvadtspecs,
             sys.server_audit_specification_details srvadtspecdtls
      WHERE  adts.audit_guid = srvadtspecs.audit_guid
             AND adts.is_state_enabled = 1
             AND srvadtspecs.is_state_enabled = 1
             AND srvadtspecdtls.audited_result = 'SUCCESS AND FAILURE'
             AND srvadtspecdtls.audit_action_id = 'DAGF';

      DECLARE @db_success_logon_event INT = 0;
      DECLARE @db_fail_logon_event INT = 0;

      SELECT @db_success_logon_event = Count(*)
      FROM   sys.server_audits adts,
             sys.database_audit_specifications dbadtspecs,
             sys.database_audit_specification_details dbadtspecdtls
      WHERE  adts.audit_guid = dbadtspecs.audit_guid
             AND adts.is_state_enabled = 1
             AND dbadtspecs.is_state_enabled = 1
             AND dbadtspecdtls.audited_result = 'SUCCESS AND FAILURE'
             AND dbadtspecdtls.audit_action_id = 'DAGS';

      SELECT @db_fail_logon_event = Count(*)
      FROM   sys.server_audits adts,
             sys.database_audit_specifications dbadtspecs,
             sys.database_audit_specification_details dbadtspecdtls
      WHERE  adts.audit_guid = dbadtspecs.audit_guid
             AND adts.is_state_enabled = 1
             AND dbadtspecs.is_state_enabled = 1
             AND dbadtspecdtls.audited_result = 'SUCCESS AND FAILURE'
             AND dbadtspecdtls.audit_action_id = 'DAGF';

      IF( ( @success_logon_event
            + @db_success_logon_event ) > 0
          AND ( @fail_logon_event + @db_fail_logon_event ) > 0 )
        SET @violation = 0;
  END
ELSE
  BEGIN
      SET @violation = 0; -- ignore if DB is not contained
  END

SELECT @violation AS [Violation];

Remediation:

Create and enable a new SERVER AUDIT SPECIFICATION that will audit FAILED_LOGIN_GROUP & SUCCESSFUL_LOGIN_GROUP events. This SERVER AUDIT SPECIFICATION must target a valid SERVER AUDIT object that is enabled.

CREATE DATABASE AUDIT SPECIFICATION [DbAuditSpec_db_logon_information_failed_succeessful]
    FOR SERVER AUDIT []
ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),
ADD (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP)
WITH (STATE = ON)

VA1281: All memberships for user-defined roles should be intended

Applies To: SQL Server, SQL Database, SQL Managed Instance, Azure Synapse
⚓ anchor link

User-defined roles are security principals defined by the user to group principals to easily manage permissions. Monitoring these roles is important to avoid having excessive permissions. Create a baseline which defines expected membership for each user-defined role. This rule checks whether all memberships for user-defined roles are as defined in the baseline

Rationale:

Keeping track of role memberships is important to avoid granting excessive permissions

Query:

SELECT user_name(role_principal_id) as role_name, user_name(member_principal_id) as member_name
FROM sys.database_role_members
WHERE role_principal_id NOT IN (16384,16385,16386,16387,16389,16390,16391,16392,16393)
ORDER BY role_principal_id, member_principal_id

Remediation:

Keep track of role membership and remove unnecessary members from roles to avoid granting excessive permissions or update baseline to comply with new changes

ALTER ROLE [$0] DROP MEMBER [$1]

☝ back to top


Authentication And Authorization – 20 rule(s)

Severity: High

VA1020: Database user GUEST should not be a member of any role

Applies To: SQL Server, SQL Database, SQL Managed Instance, Azure Synapse
⚓ anchor link

The guest user permits access to a database for any logins that are not mapped to a specific database user. This rule checks that no database roles are assigned to the Guest user.

Rationale:

Database Roles are the basic building block at the heart of separation of duties and the principle of least permission. Granting the Guest user membership to specific roles defeats this purpose.

Query:

SELECT name as [Role]
FROM sys.database_role_members AS drms
INNER JOIN sys.database_principals AS dps
    ON drms.role_principal_id = dps.principal_id
WHERE member_principal_id = DATABASE_PRINCIPAL_ID('guest')

Remediation:

Remove the special user GUEST from all roles.

ALTER ROLE [$0] DROP MEMBER GUEST

VA1048: Database principals should not be mapped to the sa account

Applies To: SQL Server, SQL Managed Instance
⚓ anchor link

A database principal that is mapped to the sa account can be exploited by an attacker to elevate permissions to sysadmin.

Rationale:

This enables privileged principals on a database to perform operations on other databases that have ownership chaining enabled – specifically msdb. An attacker can then exploit msdb to become sysadmin.

Query:

SELECT name AS Principal
    , SUSER_SNAME(sid) AS Login
FROM sys.database_principals
WHERE sid = 0x01
    AND principal_id != 1

Remediation:

Change the login associated with the offending user and investigate why this user exists.

DECLARE @newlogin sysname = 'investigate_VA1048_' + convert(nvarchar(50),newid()); DECLARE @cmd nvarchar(max); SET @cmd = 'CREATE LOGIN ' + quotename(@newlogin) + ' WITH PASSWORD = ''' + convert(nvarchar(50),newid()) + ''', CHECK_POLICY = OFF;'; SET @cmd = @cmd + 'ALTER LOGIN ' + quotename(@newlogin) + ' DISABLE;'; SET @cmd = @cmd + 'ALTER USER [$0] WITH LOGIN = ' + quotename(@newlogin) + ';' EXEC( @cmd )

VA2020: Minimal set of principals should be granted ALTER or ALTER ANY USER database-scoped permissions

Applies To: SQL Server, SQL Database, SQL Managed Instance, Azure Synapse
⚓ anchor link

Every SQL Server securable has permissions associated with it that can be granted to principals. Permissions can be scoped at the server level (assigned to logins and server roles) or at the database level (assigned to database users and database roles). These rules check that only a minimal set of principals are granted ALTER or ALTER ANY USER database-scoped permissions.

Rationale:

Developing an application using a least-privileged user account (LUA) approach is an important part of a defensive, in-depth strategy for countering security threats. The LUA approach ensures that users follow the principle of least privilege and always log on with limited user accounts. Administrative tasks are broken out using fixed server roles, and the use of the sysadmin fixed server role is severely restricted. Always follow the principle of least privilege when granting permissions to database users. Grant the minimum permissions necessary to a user or role to accomplish a given task. See https://msdn.microsoft.com/en-us/library/bb669084(v=vs.110).aspx.

Query:

SELECT perms.class_desc AS [Permission Class]
    ,perms.permission_name AS Permission
    ,type_desc AS [Principal Type]
    ,prin.name AS Principal
FROM sys.database_permissions AS perms
INNER JOIN sys.database_principals AS prin ON perms.grantee_principal_id = prin.principal_id
WHERE permission_name IN (
        'ALTER'
        ,'ALTER ANY USER'
        )
    AND user_name(grantee_principal_id) NOT IN (
        'guest'
        ,'public'
        )
    AND perms.class = 0
    AND [state] IN ('G','W')
    AND NOT (
        prin.type = 'S'
        AND prin.name = 'dbo'
        AND prin.authentication_type = 1
        AND prin.owning_principal_id IS NULL
        )

Remediation:

Revoke permissions from principals where not needed. It is recommended to have at most 1 principal granted a specific permission.

REVOKE $1 FROM [$3]

VA2108: Minimal set of principals should be members of fixed high impact database roles

Applies To: SQL Server, SQL Database, SQL Managed Instance, Azure Synapse
⚓ anchor link

SQL Server provides roles to help manage the permissions. Roles are security principals that group other principals. Database-level roles are database-wide in their permission scope. This rule checks that a minimal set of principals are members of the fixed database roles.

Rationale:

Fixed database roles may have administrative permissions on the system. Following the principle of least privilege, it is important to minimize membership in fixed database roles and keep a baseline of these memberships. See https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles for additional information on database roles.

Query:

SELECT user_name(sr.member_principal_id) as [Principal] 
    ,user_name(sr.role_principal_id) as [Role]
    ,type_desc as [Principal Type]
FROM sys.database_role_members AS sr
INNER JOIN sys.database_principals sp ON sp.principal_id = sr.member_principal_id
WHERE sr.role_principal_id IN (user_id('bulkadmin'),
                             user_id('db_accessadmin'),
                             user_id('db_securityadmin'),
                             user_id('db_ddladmin'),
                             user_id('db_backupoperator'),
                             user_id('db_owner'))
ORDER BY sp.name

Remediation:

Remove members who should not have access to the database role

ALTER ROLE [$1] DROP MEMBER [$0]

VA2129: Changes to signed modules should be authorized

Applies To: SQL Server, SQL Database, SQL Managed Instance
⚓ anchor link

You can sign a stored procedure, function or trigger with a certificate or an asymmetric key. This is designed for scenarios when permissions cannot be inherited through ownership chaining or when the ownership chain is broken, such as dynamic SQL. This rule checks for changes made to signed modules which could be an indication of malicious use.

Rationale:

Changes made to the contents of a signed module or to the certificate or asymmetric key that is used to sign it, as well as the introduction of new signed modules could be an an indication of an attack. Setting the known signed modules as a baseline allows you to easily detect changes made, and to evaluate whether the changes are intended.

Query:

SELECT 
    QUOTENAME(sc.name) + '.' + QUOTENAME(oj.name) AS [Module]
    ,IIF(ct.certificate_id IS NOT NULL, ct.name, ak.name) AS [Signing Object]
    ,dp.name AS [Signing Object Owner]
    ,cp.thumbprint AS [Signing Object Thumbprint]
    ,oj.modify_date AS [Last Definition Modify Date]
    ,HASHBYTES('SHA2_256', cp.crypt_property) AS [Hashed Signature Bits]
    ,IIF(ct.certificate_id IS NOT NULL, 'CERTIFICATE', 'ASYMMETRIC KEY') AS [Signing Object Type]
    -- For debbuging, uncomment following lines:
    -- ,IIF(ct.principal_id IS NOT NULL, SUSER_NAME(ct.principal_id), SUSER_NAME(ak.principal_id)) AS [Owner_Name]
    -- ,oj.type_desc
    -- ,crypt_type
    -- ,md.DEFINITION 
    -- ,IIF(ct.subject IS NOT NULL, ct.subject, 'N/A') AS [Certificate Subject]
    -- ,IIF(ct.certificate_id IS NOT NULL, IS_OBJECTSIGNED('OBJECT', oj.object_id, 'certificate', cp.thumbprint), IS_OBJECTSIGNED('OBJECT', oj.object_id, 'asymmetric key', cp.thumbprint)) AS [Is Object Signed]
FROM 
    sys.crypt_properties AS cp
    INNER JOIN sys.objects AS oj ON cp.major_id = oj.object_id
    INNER JOIN sys.schemas AS sc ON oj.schema_id = sc.schema_id
    INNER JOIN sys.sql_modules AS md ON md.object_id = cp.major_id
    LEFT OUTER JOIN sys.certificates AS ct ON cp.thumbprint = ct.thumbprint
    LEFT OUTER JOIN sys.asymmetric_keys AS ak ON cp.thumbprint = ak.thumbprint
    LEFT OUTER JOIN sys.database_principals AS dp ON (ct.sid = dp.sid OR ak.sid = dp.sid)
WHERE 
    oj.type IN ('P','FN','TR')
    AND cp.class_desc = 'OBJECT_OR_COLUMN'

Remediation:

Baseline or remove the signature from the modules

DROP SIGNATURE FROM $0 BY $6 $1

☝ back to top


Severity: Medium

VA1043: Principal GUEST should not have access to any user database

Applies To: SQL Server, SQL Managed Instance
⚓ anchor link

The guest user permits access to a database for any logins that are not mapped to a specific database user. This rule checks that the guest user cannot connect to any database.

Rationale:

The special user GUEST is used to map any login that otherwise has no access to the database. This can result in principals gaining access to a database without having been explicitly granted permission to do so.

Query:

SELECT CASE
    WHEN EXISTS (
        SELECT *
        FROM sys.database_permissions AS perms
        INNER JOIN sys.database_principals AS usrs ON grantee_principal_id = principal_id
            WHERE grantee_principal_id = Database_principal_id('guest')
                AND perms.type = 'CO'
                AND [state] IN ('G', 'W')
    )
    THEN 1
    ELSE 0
    END AS Violation

Remediation:

Remove all permissions granted to GUEST, especially the connect permission

REVOKE CONNECT FROM [GUEST]

VA1095: Excessive permissions should not be granted to PUBLIC role

Applies To: SQL Server, SQL Database, SQL Managed Instance, Azure Synapse
⚓ anchor link

Every SQL Server login belongs to the public server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. This displays a list of all permissions that are granted to the PUBLIC role.

Rationale:

Database Roles are the basic building block at the heart of separation of duties and the principle of least permission. Granting permissions to principals through the default PUBLIC role defeats this purpose.

Query:

SELECT REPLACE(perms.class_desc, '_', ' ') AS [Permission Class]
    ,CASE
        WHEN perms.class = 0
            THEN db_name() -- database
        WHEN perms.class = 3
            THEN schema_name(major_id) -- schema
        WHEN perms.class = 4
            THEN printarget.NAME -- principal
        WHEN perms.class = 5
            THEN asm.NAME -- assembly
        WHEN perms.class = 6
            THEN type_name(major_id) -- type
        WHEN perms.class = 10
            THEN xmlsc.NAME -- xml schema
        WHEN perms.class = 15
            THEN msgt.NAME COLLATE DATABASE_DEFAULT -- message types
        WHEN perms.class = 16
            THEN svcc.NAME COLLATE DATABASE_DEFAULT -- service contracts
        WHEN perms.class = 17
            THEN svcs.NAME COLLATE DATABASE_DEFAULT -- services
        WHEN perms.class = 18
            THEN rsb.NAME COLLATE DATABASE_DEFAULT -- remote service bindings
        WHEN perms.class = 19
            THEN rts.NAME COLLATE DATABASE_DEFAULT -- routes
        WHEN perms.class = 23
            THEN ftc.NAME -- full text catalog
        WHEN perms.class = 24
            THEN sym.NAME -- symmetric key
        WHEN perms.class = 25
            THEN crt.NAME -- certificate
        WHEN perms.class = 26
            THEN asym.NAME -- assymetric key
        END AS [Object]
    ,perms.permission_name AS Permission
FROM sys.database_permissions AS perms
LEFT JOIN sys.database_principals AS prin ON perms.grantee_principal_id = prin.principal_id
LEFT JOIN sys.assemblies AS asm ON perms.major_id = asm.assembly_id
LEFT JOIN sys.xml_schema_collections AS xmlsc ON perms.major_id = xmlsc.xml_collection_id
LEFT JOIN sys.service_message_types AS msgt ON perms.major_id = msgt.message_type_id
LEFT JOIN sys.service_contracts AS svcc ON perms.major_id = svcc.service_contract_id
LEFT JOIN sys.services AS svcs ON perms.major_id = svcs.service_id
LEFT JOIN sys.remote_service_bindings AS rsb ON perms.major_id = rsb.remote_service_binding_id
LEFT JOIN sys.routes AS rts ON perms.major_id = rts.route_id
LEFT JOIN sys.database_principals AS printarget ON perms.major_id = printarget.principal_id
LEFT JOIN sys.symmetric_keys AS sym ON perms.major_id = sym.symmetric_key_id
LEFT JOIN sys.asymmetric_keys AS asym ON perms.major_id = asym.asymmetric_key_id
LEFT JOIN sys.certificates AS crt ON perms.major_id = crt.certificate_id
LEFT JOIN sys.fulltext_catalogs AS ftc ON perms.major_id = ftc.fulltext_catalog_id
WHERE perms.grantee_principal_id = DATABASE_PRINCIPAL_ID('public')
    AND class != 1 -- Object or Columns (class = 1) are handled by VA1054 and have different remediation syntax
    AND [state] IN ('G','W')
    AND NOT (
        perms.class = 0
        AND prin.NAME = 'public'
        AND perms.major_id = 0
        AND perms.minor_id = 0
        AND permission_name IN (
            'VIEW ANY COLUMN ENCRYPTION KEY DEFINITION'
            ,'VIEW ANY COLUMN MASTER KEY DEFINITION'
            )
        )
ORDER BY perms.class
    ,object_name(perms.major_id)
    ,perms.grantor_principal_id
    ,perms.STATE

Remediation:

Revoke any unnecessary permissions granted to PUBLIC, but avoid changing permissions granted out of the box.

REVOKE $2 ON $0::[$1] FROM PUBLIC

VA1248: User-defined database roles should not be members of fixed roles

Applies To: SQL Server, SQL Database, SQL Managed Instance, Azure Synapse
⚓ anchor link

To easily manage the permissions in your databases, SQL Server provides several roles which are security principals that group other principals. They are like groups in the Microsoft Windows operating system. Database accounts and other SQL Server roles can be added into database-level roles. Each member of a fixed-database role can add other users to that same role. This rule checks that no user-defined roles are members of fixed roles

Rationale:

Adding user defined database roles as members of fixed roles could enable unintended privilege escalation, also finding any metadata indicating that the fixed roles have been modified is typically a sign of data corruption or signs of somebody corrupting the metadata in order to hide unusual activity.

Query:

SELECT user_name(roles.role_principal_id) as role, user_name(roles.member_principal_id) as member
FROM sys.database_role_members AS roles, sys.database_principals users
WHERE roles.member_principal_id = users.principal_id
AND ( roles.role_principal_id >= 16384 AND roles.role_principal_id <= 16393)
AND users.type = 'R'
ORDER BY user_name(roles.role_principal_id), user_name(roles.member_principal_id)

Remediation:

Remove user defined roles from fixed-database roles

ALTER ROLE [$0] DROP MEMBER [$1]

VA1267: Contained users should use Windows Authentication

Applies To: SQL Server, SQL Managed Instance
⚓ anchor link

Contained users are users that exist within the database, and do not require a login mapping. This rule checks that contained users use Windows Authentication.

Rationale:

Authentication must be centrally managed in order to enforce the domain password policies.

Query:

SELECT NAME AS [Principal]
FROM   sys.database_principals
WHERE  authentication_type = 2
ORDER  BY NAME,
          type_desc,
          authentication_type

Remediation:

Remove all contained users with password. All affected applications will have to switch to Windows authentication, or create logins with password policy enforcement in order to connect to the DB.

DROP USER [$0];

☝ back to top


Severity: Low

VA1054: Excessive permissions should not be granted to PUBLIC role on objects or columns

Applies To: SQL Server, SQL Database, SQL Managed Instance, Azure Synapse
⚓ anchor link

Every SQL Server login belongs to the public server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. This rule displays a list of all securable objects or columns that are accessible to all users through the PUBLIC role.

Rationale:

Database Roles are the basic building block at the heart of separation of duties and the principle of least permission. Granting permissions to principals through the default PUBLIC role defeats this purpose.

Query:

SELECT permission_name
 ,schema_name
 ,object_name
FROM (
    SELECT objs.TYPE COLLATE database_default AS object_type
        ,schema_name(schema_id) COLLATE database_default AS schema_name
        ,objs.name COLLATE database_default AS object_name
        ,user_name(grantor_principal_id) COLLATE database_default AS grantor_principal_name
        ,permission_name COLLATE database_default AS permission_name
        ,perms.TYPE COLLATE database_default AS TYPE
        ,STATE COLLATE database_default AS STATE
 FROM sys.database_permissions AS perms
 INNER JOIN sys.objects AS objs
 ON objs.object_id = perms.major_id
  WHERE perms.class = 1 -- objects or columns. Other cases are handled by VA1095 which has different remediation syntax
  AND grantee_principal_id = DATABASE_PRINCIPAL_ID('public')
  AND [state] IN (
   'G'
   ,'W'
   )
  AND NOT (
   -- These permissions are granted by default to public
   permission_name = 'EXECUTE'
   AND schema_name(schema_id) = 'dbo'
   AND STATE = 'G'
   AND objs.name IN (
    'fn_sysdac_is_dac_creator'
    ,'fn_sysdac_is_currentuser_sa'
    ,'fn_sysdac_is_login_creator'
    ,'fn_sysdac_get_username'
    ,'sp_sysdac_ensure_dac_creator'
    ,'sp_sysdac_add_instance'
    ,'sp_sysdac_add_history_entry'
    ,'sp_sysdac_delete_instance'
    ,'sp_sysdac_upgrade_instance'
    ,'sp_sysdac_drop_database'
    ,'sp_sysdac_rename_database'
    ,'sp_sysdac_setreadonly_database'
    ,'sp_sysdac_rollback_committed_step'
    ,'sp_sysdac_update_history_entry'
    ,'sp_sysdac_resolve_pending_entry'
    ,'sp_sysdac_rollback_pending_object'
    ,'sp_sysdac_rollback_all_pending_objects'
    ,'fn_sysdac_get_currentusername'
    )
   OR permission_name = 'SELECT'
   AND schema_name(schema_id) = 'sys'
   AND STATE = 'G'
   AND objs.name IN (
    'firewall_rules'
    ,'database_firewall_rules'
    ,'bandwidth_usage'
    ,'database_usage'
    ,'external_library_setup_errors'
    ,'sql_feature_restrictions'
    ,'resource_stats'
    ,'elastic_pool_resource_stats'
    ,'dm_database_copies'
    ,'geo_replication_links'
    ,'database_error_stats'
    ,'event_log'
    ,'database_connection_stats'
    )
   OR permission_name = 'SELECT'
   AND schema_name(schema_id) = 'dbo'
   AND STATE = 'G'
   AND objs.name IN (
    'sysdac_instances_internal'
    ,'sysdac_history_internal'
    ,'sysdac_instances'
    )
   )

 ) t
ORDER BY object_type
 ,schema_name
 ,object_name
 ,TYPE
 ,STATE

Remediation:

Revoke unnecessary permissions granted to PUBLIC

REVOKE $0 ON [$1].[$2] FROM PUBLIC

VA1070: Database users shouldn’t share the same name as a server login

Applies To: SQL Server, SQL Managed Instance
⚓ anchor link

Database users may share the same name as a server login. This rule validates that there are no such users to avoid confusion.

Rationale:

Logins are created at the server level, while users are created at the database level. There are different types of users. Users with login are mapped to server level logins. Users with passwords can be created on a specific database, but are not mapped to server logins. This rule checks that users with passwords do not have the same name as any SQL login, as that can lead to false assessments of access rights.

Query:

SELECT dp.NAME AS [Principal]
FROM   sys.database_principals AS dp
JOIN   sys.server_principals AS sp
       ON dp.NAME = sp.NAME COLLATE database_default
WHERE  dp.sid != sp.sid
       AND dp.authentication_type = 2
ORDER  BY dp.NAME

Remediation:

You should rename the affected users or logins to avoid the confusion. This requires updating all applications which are using those as credentials as well.

VA1094: Database permissions shouldn’t be granted directly to principals

Applies To: SQL Server, SQL Managed Instance
⚓ anchor link

Permissions are rules associated with a securable object to regulate which users can gain access to the object. This rule checks that there are no DB permissions granted directly to users.

Rationale:

Individuals change organizations & job descriptions over time. It is highly recommended to use a centralized access control management through AD group membership.

Query:

SELECT permission_name                  AS [Permission],
       ISNULL(Schema_name(objs.schema_id), Replace(dp.class_desc, '_', ' ')) AS [Permission Class],
        CASE 
         WHEN Schema_name(objs.schema_id) IS NULL  THEN '::' ELSE '.' End AS [Class Separator],
        CASE
         WHEN dp.class = 0 THEN Db_name() -- database
         WHEN dp.class = 1 THEN objs.NAME -- Object or Column (VA1286)
         WHEN dp.class = 3 THEN Schema_name(major_id) -- schema
         WHEN dp.class = 4 THEN printarget.NAME -- principal
         WHEN dp.class = 5 THEN asm.NAME -- assembly
         WHEN dp.class = 6 THEN Type_name(major_id) -- type
         WHEN dp.class = 10 THEN xmlsc.NAME -- xml schema
         WHEN dp.class = 15 THEN msgt.NAME COLLATE database_default -- message types
         WHEN dp.class = 16 THEN svcc.NAME COLLATE database_default -- service contracts
         WHEN dp.class = 17 THEN svcs.NAME COLLATE database_default -- services
         WHEN dp.class = 18 THEN rsb.NAME COLLATE database_default -- remote service bindings
         WHEN dp.class = 19 THEN rts.NAME COLLATE database_default -- routes
         WHEN dp.class = 23 THEN ftc.NAME -- full text catalog
         WHEN dp.class = 24 THEN sym.NAME -- symmetric key
         WHEN dp.class = 25 THEN crt.NAME -- certificate
         WHEN dp.class = 26 THEN asym.NAME -- assymetric key
       END                              AS [Object],
       prin.NAME                        AS [Principal]
FROM sys.database_permissions AS dp
LEFT JOIN sys.all_objects AS objs ON objs.object_id = dp.major_id
LEFT JOIN sys.database_principals AS prin ON dp.grantee_principal_id = prin.principal_id
LEFT JOIN sys.assemblies AS asm ON dp.major_id = asm.assembly_id
LEFT JOIN sys.xml_schema_collections AS xmlsc ON dp.major_id = xmlsc.xml_collection_id
LEFT JOIN sys.service_message_types AS msgt ON dp.major_id = msgt.message_type_id
LEFT JOIN sys.service_contracts AS svcc ON dp.major_id = svcc.service_contract_id
LEFT JOIN sys.services AS svcs ON dp.major_id = svcs.service_id
LEFT JOIN sys.remote_service_bindings AS rsb ON dp.major_id = rsb.remote_service_binding_id
LEFT JOIN sys.routes AS rts ON dp.major_id = rts.route_id
LEFT JOIN sys.database_principals AS printarget ON dp.major_id = printarget.principal_id
LEFT JOIN sys.symmetric_keys AS sym ON dp.major_id = sym.symmetric_key_id
LEFT JOIN sys.asymmetric_keys AS asym ON dp.major_id = asym.asymmetric_key_id
LEFT JOIN sys.certificates AS crt ON dp.major_id = crt.certificate_id
LEFT JOIN sys.fulltext_catalogs AS ftc ON dp.major_id = ftc.fulltext_catalog_id
WHERE (prin.type = 'S' OR prin.type = 'W')
AND dp.type != 'CO'
AND prin.NAME NOT IN ( '##MS_PolicyEventProcessingLogin##',
                       '##MS_PolicyTsqlExecutionLogin##' )
AND [state] IN ('G','W')
ORDER BY Permission, [Permission Class], Object, [Principal]

Remediation:

Revoke permissions granted to users directly. Instead use Windows groups or server roles to grant permissions, and manage role memberships instead.

REVOKE $0 ON $1$2[$3] FROM [$4]

VA1096: Principal GUEST should not be granted permissions in the database

Applies To: SQL Server, SQL Database, SQL Managed Instance, Azure Synapse
⚓ anchor link

Each database includes a user called GUEST. Permissions granted to GUEST are inherited by users who have access to the database, but who do not have a user account in the database. This rule checks that all permissions have been revoked from the GUEST user.

Rationale:

The special user GUEST is used to map any logins that are not mapped to a specific database user. This can result in principals gaining access to a database without having been explicitly granted permission to do so.

Query:

SELECT perms.permission_name AS Permission
FROM sys.database_permissions AS perms
INNER JOIN sys.database_principals AS prin ON perms.grantee_principal_id = prin.principal_id
WHERE prin.[name] = 'guest'
    AND perms.class = 0
    AND [state] IN ('G', 'W')

Remediation:

Revoke any unnecessary permissions granted to the special account GUEST

REVOKE $0 FROM GUEST

VA1097: Principal GUEST should not be granted permissions on objects or columns

Applies To: SQL Server, SQL Database, SQL Managed Instance, Azure Synapse
⚓ anchor link

Each database includes a user called GUEST. Permissions granted to GUEST are inherited by users who have access to the database, but who do not have a user account in the database. This rule checks that all permissions have been revoked from the GUEST user.

Rationale:

The special user GUEST is used to map any logins that are not mapped to a specific database user. This can result in principals gaining access to a database without having been explicitly granted permission to do so.

Query:

SELECT object_schema_name(major_id) AS [Schema Name]
    ,object_name(major_id) AS [Object]
    ,perms.permission_name AS Permission
FROM sys.database_permissions AS perms
INNER JOIN sys.database_principals AS prin ON perms.grantee_principal_id = prin.principal_id
WHERE grantee_principal_id = DATABASE_PRINCIPAL_ID('guest')
    AND perms.class = 1
    AND [state] IN ('G','W')

Remediation:

Revoke any unnecessary permissions granted to the special account GUEST

REVOKE $2 ON [$0].[$1] FROM GUEST

VA1099: GUEST user should not be granted permissions on database securables

Applies To: SQL Server, SQL Database, SQL Managed Instance, Azure Synapse
⚓ anchor link

Each database includes a user called GUEST. Permissions granted to GUEST are inherited by users who have access to the database, but who do not have a user account in the database. This rule checks that all permissions have been revoked from the GUEST user.

Rationale:

The special user GUEST is used to map any logins that are not mapped to a specific database user. This can result in principals gaining access to a database without having been explicitly granted permission to do so.

Query:

SELECT REPLACE(perms.class_desc, '_', ' ') AS [Permission Class],
    CASE
        WHEN perms.class=3 THEN schema_name(major_id) -- schema
        WHEN perms.class=4 THEN printarget.name -- principal
        WHEN perms.class=5 THEN asm.name -- assembly
        WHEN perms.class=6 THEN type_name(major_id) -- type
        WHEN perms.class=10 THEN xmlsc.name -- xml schema
        WHEN perms.class=15 THEN msgt.name COLLATE DATABASE_DEFAULT -- message types
        WHEN perms.class=16 THEN svcc.name COLLATE DATABASE_DEFAULT -- service contracts
        WHEN perms.class=17 THEN svcs.name COLLATE DATABASE_DEFAULT -- services
        WHEN perms.class=18 THEN rsb.name COLLATE DATABASE_DEFAULT -- remote service bindings
        WHEN perms.class=19 THEN rts.name COLLATE DATABASE_DEFAULT -- routes
        WHEN perms.class=23 THEN ftc.name -- full text catalog
        WHEN perms.class=24 then sym.name -- symmetric key
        WHEN perms.class=25 then crt.name -- certificate
        WHEN perms.class=26 then asym.name -- assymetric key
    END AS [Object],
    perms.permission_name AS Permission
FROM sys.database_permissions AS perms
LEFT JOIN
    sys.database_principals AS prin
    ON perms.grantee_principal_id = prin.principal_id
LEFT JOIN
    sys.assemblies AS asm
    ON perms.major_id = asm.assembly_id
LEFT JOIN
    sys.xml_schema_collections AS xmlsc
    ON perms.major_id = xmlsc.xml_collection_id
LEFT JOIN
    sys.service_message_types AS msgt
    ON perms.major_id = msgt.message_type_id
LEFT JOIN
    sys.service_contracts AS svcc
    ON perms.major_id = svcc.service_contract_id
LEFT JOIN
    sys.services AS svcs
    ON perms.major_id = svcs.service_id
LEFT JOIN
    sys.remote_service_bindings AS rsb
    ON perms.major_id = rsb.remote_service_binding_id
LEFT JOIN
    sys.routes AS rts
    ON perms.major_id = rts.route_id
LEFT JOIN
    sys.database_principals AS printarget
    ON perms.major_id = printarget.principal_id
LEFT JOIN
    sys.symmetric_keys AS sym
    On perms.major_id = sym.symmetric_key_id
LEFT JOIN
    sys.asymmetric_keys AS asym
    ON perms.major_id = asym.asymmetric_key_id
    LEFT JOIN
    sys.certificates AS crt
    ON perms.major_id = crt.certificate_id
LEFT JOIN
    sys.fulltext_catalogs AS ftc
    ON perms.major_id = ftc.fulltext_catalog_id
WHERE
    grantee_principal_id = DATABASE_PRINCIPAL_ID('guest')
    AND class in (3,4,5,6,10,15,16,17,18,19,23,24,25,26)
    AND [state] IN ('G','W')

Remediation:

Revoke any unnecessary permissions granted to the special account GUEST

REVOKE $2 ON $0::[$1] FROM GUEST

VA1246: Application roles should not be used

Applies To: SQL Server, SQL Database, SQL Managed Instance
⚓ anchor link

An application role is a database principal that enables an application to run with its own user-like permissions. Application roles enable that only users connecting through a particular application can access specific data. Application roles are password-based (which applications typically hardcode) and not permission based, which exposes the database to approle impersonation by password-guessing. This rule checks that no application roles are defined in the database.

Rationale:

It is important to limit the possibility of acquiring user-like permissions to the database, and since application roles are password based, they can lead to impersonation of the application role via password-guessing.

Query:

SELECT name
FROM sys.database_principals
WHERE type  = 'A'
ORDER BY name

Remediation:

Remove the application roles. Use users without login as a permission-based replacement

DROP APPLICATION ROLE [$0]

VA1282: Orphan database roles should be removed

Applies To: SQL Server, SQL Database, SQL Managed Instance, Azure Synapse
⚓ anchor link

Orphan database roles are user-defined roles that have no members. It is recommended to eliminate orphaned roles as they are not needed on the system. This rule checks whether there are any orphan roles

Rationale:

Reduce the attack surface area by eliminating unnecessary database roles in the system.

Query:

SELECT name FROM sys.database_principals
WHERE type = 'R'
AND principal_id not in (0,16384,16385,16386,16387,16389,16390,16391,16392,16393)
AND principal_id not in ( SELECT distinct role_principal_id
FROM sys.database_role_members )

Remediation:

Drop the unnecessary database roles.

DROP ROLE [$0]

VA2033: Minimal set of principals should be granted EXECUTE permission on objects or columns

Applies To: SQL Server, SQL Database, SQL Managed Instance, Azure Synapse
⚓ anchor link

This rule checks which principals are granted EXECUTE permission on objects or columns to ensure this permission is granted to a minimal set of principals. Every SQL Server securable has permissions associated with it that can be granted to principals. Permissions can be scoped at the server level (assigned to logins and server roles) or at the database level (assigned to database users, database roles or application roles). The EXECUTE permission applies to both stored procedures and scalar functions, which can be used in computed columns.

Rationale:

Developing an application using a least-privileged user account (LUA) approach is an important part of a defensive, in-depth strategy for countering security threats. The LUA approach ensures that users follow the principle of least privilege and always log on with limited user accounts. Administrative tasks are broken out using fixed server roles, and the use of the sysadmin fixed server role is severely restricted. Always follow the principle of least privilege when granting permissions to database users. Grant the minimum permissions necessary to a user or role to accomplish a given task. See https://msdn.microsoft.com/en-us/library/bb669084(v=vs.110).aspx.

Query:

IF object_id('tempdb.dbo.#entries_to_exclude', 'U') IS NOT NULL
    DROP TABLE #entries_to_exclude;

CREATE TABLE #entries_to_exclude (
                object_name VARCHAR(64),
                state_desc VARCHAR(24),
                prin_name VARCHAR(64),
                user_name VARCHAR(20),
                prin_type CHAR(1)
            )
     
INSERT INTO #entries_to_exclude (object_name, state_desc, prin_name, user_name, prin_type)
 VALUES ('sp_add_job', 'DENY', 'TargetServersRole', 'dbo', 'R')
        ,('sp_add_jobschedule', 'DENY', 'TargetServersRole', 'dbo', 'R')
        ,('sp_add_jobserver', 'DENY', 'TargetServersRole', 'dbo', 'R')
        ,('sp_add_jobstep', 'DENY', 'TargetServersRole', 'dbo', 'R')
        ,('sp_addtask', 'DENY', 'TargetServersRole', 'dbo', 'R')
        ,('sp_delete_job', 'DENY', 'TargetServersRole', 'dbo', 'R')
        ,('sp_delete_jobschedule', 'DENY', 'TargetServersRole', 'dbo', 'R')
        ,('sp_delete_jobserver', 'DENY', 'TargetServersRole', 'dbo', 'R')
        ,('sp_delete_jobstep', 'DENY', 'TargetServersRole', 'dbo', 'R')
        ,('sp_droptask', 'DENY', 'TargetServersRole', 'dbo', 'R')
        ,('sp_post_msx_operation', 'DENY', 'TargetServersRole', 'dbo', 'R')
        ,('sp_start_job', 'DENY', 'TargetServersRole', 'dbo', 'R')
        ,('sp_stop_job', 'DENY', 'TargetServersRole', 'dbo', 'R')
        ,('sp_update_job', 'DENY', 'TargetServersRole', 'dbo', 'R')
        ,('sp_update_jobschedule', 'DENY', 'TargetServersRole', 'dbo', 'R')
        ,('sp_update_jobstep', 'DENY', 'TargetServersRole', 'dbo', 'R')
        ,('sp_syspolicy_events_reader', 'GRANT', '##MS_PolicyEventProcessingLogin##', 'dbo', 'S')
        ,('sp_syspolicy_execute_policy', 'GRANT', '##MS_PolicyEventProcessingLogin##', 'dbo', 'S')
        ,('fn_cColvEntries_80', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_cdc_check_parameters', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_cdc_decrement_lsn', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_cdc_get_column_ordinal', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_cdc_get_max_lsn', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_cdc_get_min_lsn', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_cdc_has_column_changed', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_cdc_hexstrtobin', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_cdc_increment_lsn', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_cdc_is_bit_set', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_cdc_map_lsn_to_time', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_cdc_map_time_to_lsn', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_fIsColTracked', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_GetCurrentPrincipal', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_GetRowsetIdFromRowDump', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_hadr_backup_is_preferred_replica', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_hadr_is_primary_replica', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_hadr_is_same_replica', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_IsBitSetInBitmask', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_isrolemember', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_MapSchemaType', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_MSdayasnumber', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_MSgeneration_downloadonly', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_MSget_dynamic_filter_login', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_MSorbitmaps', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_MSrepl_map_resolver_clsid', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_MStestbit', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_MSvector_downloadonly', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_numberOf1InBinaryAfterLoc', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_numberOf1InVarBinary', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_PhysLocFormatter', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_repl_hash_binary', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_repladjustcolumnmap', 'GRANT', 'public', 'dbo', 'R')
        ,('fn_repldecryptver4', 'GRANT', 'public'

Remediation:

Revoke permissions from principals where not needed. It is recommended to have at most 1 principal granted a specific permission.

REVOKE $3 ON [$1].[$2] FROM [$5]

VA2109: Minimal set of principals should be members of fixed low impact database roles

Applies To: SQL Server, SQL Database, SQL Managed Instance, Azure Synapse
⚓ anchor link

SQL Server provides roles to help manage the permissions. Roles are security principals that group other principals. Database-level roles are database-wide in their permission scope. This rule checks that a minimal set of principals are members of the fixed database roles.

Rationale:

Fixed database roles may have administrative permissions on the system. Following the principle of least privilege, it is important to minimize membership in fixed database roles and keep a baseline of these memberships. See https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles for additional information on database roles.

Query:

SELECT user_name(sr.member_principal_id) as [Principal]
    ,user_name(sr.role_principal_id) as [Role]
    ,type_desc as [Principal Type]
FROM sys.database_role_members AS sr
INNER JOIN sys.database_principals AS sp ON sp.principal_id = sr.member_principal_id
WHERE sr.role_principal_id IN (
        user_id('db_datareader')
        ,user_id('db_datawriter')
        ,user_id('db_denydatareader')
        ,user_id('db_denydatawriter')
        )
ORDER BY sp.name

Remediation:

Remove members who should not have access to the database role

ALTER ROLE [$1] DROP MEMBER [$0]

VA2130: Track all users with access to the database

Applies To: SQL Database, Azure Synapse
⚓ anchor link

This check tracks all users with access to a database. Make sure that these users are authorized according to their current role in the organization.

Rationale:

Performing a User Access Review helps identify accounts that were added to the server maliciously and dormant accounts.

Query:

WITH UsersAndRoles (principal_name, sid, type) AS 
(
    SELECT DISTINCT prin.name, prin.sid, prin.type 
    FROM sys.database_principals prin 
        INNER JOIN ( SELECT *
                     FROM sys.database_permissions
                     WHERE type = 'CO' 
                        AND state IN ('G', 'W')
        ) perm 
            ON perm.grantee_principal_id = prin.principal_id 
        WHERE prin.type IN ('S', 'X', 'R', 'E', 'G')
    UNION ALL
    SELECT 
        user_name(rls.member_principal_id), prin.sid, prin.type
    FROM 
        UsersAndRoles cte
        INNER JOIN sys.database_role_members rls
            ON user_name(rls.role_principal_id) = cte.principal_name
        INNER JOIN sys.database_principals prin
            ON rls.member_principal_id = prin.principal_id
        WHERE cte.type = 'R'
),
Users (database_user, sid) AS
(
    SELECT principal_name, sid
    FROM UsersAndRoles
    WHERE type IN ('S', 'X', 'E', 'G')
        AND principal_name != 'dbo'
)
SELECT DISTINCT database_user, sid
    FROM Users
    WHERE sid != 0x01

Remediation:

Revoke unnecessary access granted to users. Add the rest to the baseline.

DROP USER $0

☝ back to top


Data Protection – 7 rule(s)

Severity: High

VA1098: Any Existing Mirroring or SSB endpoint should require AES encryption

Applies To: SQL Server
⚓ anchor link

Mirroring endpoints, which are used for Always On Synchronization, as well as Service Broker endpoints support different encryption algorithms, including no encryption. This rule checks that any existing endpoint requires AES encryption.

Rationale:

Using a weak encryption algorithm or plaintext in communication protocols can lead to data manipulation including data loss, and/or connection hijacking.

Query:

SELECT ep.NAME      AS [Name],
       ep.type_desc AS [Type]
FROM   sys.database_mirroring_endpoints AS dme
JOIN   sys.endpoints AS ep
       ON dme.endpoint_id = ep.endpoint_id
WHERE  dme.encryption_algorithm  2
       AND ep.type BETWEEN 3 AND 4
UNION
SELECT ep.NAME      AS [Name],
       ep.type_desc AS [Type]
FROM   sys.service_broker_endpoints AS sbe
JOIN   sys.endpoints  AS ep 
       ON sbe.endpoint_id = ep.endpoint_id
WHERE  sbe.encryption_algorithm  2
       AND ep.type BETWEEN 3 AND 4

Remediation:

Change the affected endpoints to accept only AES connections

ALTER ENDPOINT [$0] FOR $1 ( ENCRYPTION = REQUIRED ALGORITHM AES )

VA1221: Database Encryption Symmetric Keys should use AES algorithm

Applies To: SQL Server, SQL Database, SQL Managed Instance, Azure Synapse
⚓ anchor link

SQL Server uses encryption keys to help secure data, credentials, and connection information that is stored in a server database. SQL Server has two kinds of keys: symmetric and asymmetric. This rule checks that Database Encryption Symmetric Keys use AES algorithm.

Rationale:

Weak encryption algorithms may lead to weaknesses in the data-at-rest protection.

Query:

SELECT db_name(database_id) as db_name, encryption_state, key_algorithm, key_length, encryptor_type
FROM sys.dm_database_encryption_keys
WHERE key_algorithm != 'AES'
ORDER BY db_name(database_id), encryption_state, key_algorithm, key_length, encryptor_type

Remediation:

Regenerate the DEK using AES

VA1222: Cell-Level Encryption keys should use AES algorithm

Applies To: SQL Server, SQL Managed Instance
⚓ anchor link

Cell-Level Encryption (CLE) allows you to encrypt your data using symmetric and asymmetric keys. This rule checks that Cell-Level Encryption symmetric keys use AES algorithm.

Rationale:

Weak encryption algorithms may lead to weaknesses in the data-at-rest protection

Query:

SELECT NAME AS [Name],
       algorithm_desc AS [Algorithm]
FROM   sys.symmetric_keys
WHERE  key_algorithm NOT IN ( 'A1', 'A2', 'A3' )
ORDER  BY NAME,
          algorithm_desc

Remediation:

Create AES keys, re-encrypt the data using the new key, and drop the affected keys.

VA1223: Certificate keys should use at least 2048 bits

Applies To: SQL Server, SQL Database, Azure Synapse
⚓ anchor link

Certificate keys are used in RSA and other encryption algorithms to protect data. These keys need to be of enough length to secure the user’s data. This rule checks that the key’s length is at least 2048 bits for all certificates.

Rationale:

Key length defines the upper-bound on the encryption algorithm’s security. Using short keys in encryption algorithms may lead to weaknesses in data-at-rest protection.

Query:

SELECT name, issuer_name, cert_serial_number, subject, thumbprint
FROM sys.certificates
WHERE key_length < 2048

Remediation:

Create new certificates, re-encrypt the data/sign-data using the new key, and drop the affected keys.

VA1224: Asymmetric keys’ length should be at least 2048 bits

Applies To: SQL Database
⚓ anchor link

Database asymmetric keys are used in many encryption algorithms, these keys need to be of enough length to secure the encrypted data, this rule checks that all asymmetric keys stored in the database are of length of at least 2048 bits

Rationale:

Key length defines the upper-bound on the encryption algorithm’s security, using short keys in encryption algorithms may lead to weaknesses in the data-at-rest protection

Query:

SELECT name, pvt_key_encryption_type_desc, algorithm_desc
FROM sys.asymmetric_keys
WHERE key_length < 2048
AND NOT (DB_NAME() = 'master' AND name = 'MS_SQLEnableSystemAssemblyLoadingKey')
ORDER BY name, pvt_key_encryption_type_desc, algorithm_desc

Remediation:

Create new asymmetric Keys, re-encrypt the data/sign-data using the new key, and drop the affected keys.

DROP ASYMMETRIC KEY [$0]

☝ back to top


Severity: Medium

VA1219: Transparent data encryption should be enabled

Applies To: SQL Server, SQL Database, SQL Managed Instance
⚓ anchor link

Transparent data encryption (TDE) helps to protect the database files against information disclosure by performing real-time encryption and decryption of the database, associated backups, and transaction log files ‘at rest’, without requiring changes to the application. This rule checks that TDE is enabled on the database.

Rationale:

Transparent Data Encryption (TDE) protects data ‘at rest’, meaning the data and log files are encrypted when stored on disk.

Query:

SELECT CASE WHEN EXISTS
( SELECT *
    FROM sys.databases
    WHERE name = db_name()
    AND is_encrypted = 0)
THEN 1
ELSE 0
END AS [Violation]

Remediation:

Enable TDE on the affected database. Please follow the instructions on https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption

VA1288: Sensitive data columns should be classified

Applies To: SQL Database
⚓ anchor link

This rule checks if the scanned database has potentially sensitive data that has not been classified.

Rationale:

The data residing in your database can have varying levels of business and privacy sensitivity. It is important to be aware of the location of your most sensitive data elements, so that their access can be monitored and tracked. SQL Data Discovery & Classification enables you to assign a distinct classification label to each database column and persist this information as column metadata within the database. This classification metadata can then be used for tracking and monitoring objectives. In addition, access to sensitive data should be more tightly controlled. Built-in SQL security capabilities like Always Encrypted, Dynamic Data Masking, and Row-Level Security can be used to control access and protect data.

Query:

Remediation:

Click the remediation link below to classify columns with sensitive data or to dismiss recommendations for columns that do not contain sensitive data (false positives).

☝ back to top


Installation Updates And Patches – 1 rule(s)

Severity: High

VA2128: Vulnerability Assessment is not supported for SQL Server versions lower than SQL Server 2012

Applies To: SQL Server
⚓ anchor link

To run a Vulnerability Assessment scan on your SQL Server, the server needs to be upgraded to SQL Server 2012 or higher.SQL Server 2008 R2 and below are no longer supported by Microsoft. See here: https://www.microsoft.com/en-us/cloud-platform/windows-sql-server-2008

Rationale:

Older versions of SQL server are no longer supported by Microsoft. Windows Server 2008 R2 end-of-life mainstream support ended on January 13, 2015. On January 14, 2020, Microsoft will end all support for Windows Server 2008 R2.

Query:

SELECT CASE
    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) LIKE '7.%' THEN '1'
    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) LIKE '8.%' THEN '1'
    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) LIKE '9.%' THEN '1'
    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) LIKE '10.%' THEN '1'
    ELSE '0'
END AS Violation

Remediation:

Upgrade your SQL Server version to 2012 or higher.

☝ back to top


Surface Area Reduction – 10 rule(s)

Severity: High

VA1102: The Trustworthy bit should be disabled on all databases except MSDB

Applies To: SQL Server, SQL Managed Instance
⚓ anchor link

The TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts the database and the contents within it. If this option is enabled, database modules (for example, user-defined functions or stored procedures) that use an impersonation context can access resources outside the database. This rule verifies that the TRUSTWORTHY bit is disabled on all databases, except MSDB.

Rationale:

The trustworthy bit (TWbit) is an access control mechanism that enables features that can lead to an elevation of privilege such as CLR and server-scope impersonation. For more information: http://support.microsoft.com/kb/2183687

Query:

SELECT CASE
         WHEN EXISTS (SELECT *
                      FROM   sys.databases
                      WHERE  NAME = Db_name()
                             AND is_trustworthy_on = 1) THEN 1
         ELSE 0
       END       AS Violation,
       Db_name() AS [Database]

Remediation:

Disable the trustworthy bit (TWbit) from all affected databases. If you need to use functionality that is controlled by the TWbit, it is recommended to use digital signatures to enable the functionality instead of enabling the TWbit on the database.

ALTER DATABASE [$1] SET TRUSTWORTHY OFF

VA1245: The database owner information in the database should match the respective database owner information in the master database

Applies To: SQL Server, SQL Managed Instance
⚓ anchor link

Database ownership metadata is stored in two locations – in the master database and in the database itself. This stored metadata can sometimes become out of sync. For instance, when a database has been restored from a different server, or when the server principal stored as dbo no longer exists for some reason, the data stored in the database and the data stored in the master database will be out of sync.

Rationale:

The metadata about the database owner stored inside the database should match that stored in the master database. This helps avoid potential system problems, for instance permission problems when using some features such as CLR.

Query:

SELECT CASE
         WHEN EXISTS (
                 SELECT *
                 FROM   sys.database_principals AS dbprs
                 INNER JOIN sys.databases AS dbs
                 ON  dbprs.sid != dbs.owner_sid
                 WHERE dbs.database_id = Db_id()
                    AND dbprs.principal_id = 1
                 )
             THEN 1
         ELSE 0
         END AS [Violation]

Remediation:

Use ALTER AUTHORIZATION ON DATABASE DDL-command against the database to specify a new server principal that should be the owner of the database

VA1256: User CLR assemblies should not be defined in the database

Applies To: SQL Server, SQL Managed Instance
⚓ anchor link

CLR assemblies can be used to execute arbitrary code on SQL Server process. This rule checks that there are no user-defined CLR assemblies in the database

Rationale:

Using CLR assemblies can bring a security flaw to the SQL Server instance and to all other network resources accessible from it

Query:

SELECT name FROM sys.assemblies WHERE is_user_defined != 0

Remediation:

Drop assemblies from the affected databases

DROP ASSEMBLY [$0]

VA1277: Polybase network encryption should be enabled

Applies To: SQL Server
⚓ anchor link

PolyBase is a technology that accesses and combines both non-relational and relational data, all from within SQL Server. Polybase network encryption option configures SQL Server to encrypt control and data channels when using Polybase. This rule verifies that this option is enabled.

Rationale:

Having any communication protocol without encryption can lead to multiple security problems, including data loss, data tampering & leak of authentication credentials.

Query:

SELECT CASE
         WHEN EXISTS (SELECT *
                      FROM   sys.configurations
                      WHERE  NAME = 'polybase network encryption'
                             AND Cast(value AS INT) = 0) THEN 1
         ELSE 0
       END AS Violation

Remediation:

Enable polybase network encryption (default)

EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE;
EXECUTE sp_configure 'polybase network encryption', 1; RECONFIGURE;
EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE;

VA2062: Database-level firewall rules should not grant excessive access

Applies To: SQL Database
⚓ anchor link

The Azure SQL Database-level firewall helps protect your data by preventing all access to your database until you specify which IP addresses have permission. Database-level firewall rules grant access to the specific database based on the originating IP address of each request. Database-level firewall rules for master and user databases can only be created and managed through Transact-SQL (unlike server-level firewall rules which can also be created and managed using the Azure portal or PowerShell). For more details please see: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-firewall-configure This check verifies that each database-level firewall rule does not grant access to more than 255 IP addresses.

Rationale:

Often, administrators add rules that grant excessive access as part of a troubleshooting process – to eliminate the firewall as the source of a problem, they simply create a rule that allows all traffic to pass to the affected database. Granting excessive access using database firewall rules is a clear security concern, as it violates the principle of least privilege by allowing unnecessary access to your database. In fact, it’s the equivalent of placing the database outside of the firewall.

Query:

SELECT name
    ,start_ip_address
    ,end_ip_address
FROM sys.database_firewall_rules
WHERE ( 
        (CONVERT(bigint, parsename(end_ip_address, 1)) +
         CONVERT(bigint, parsename(end_ip_address, 2)) * 256 + 
         CONVERT(bigint, parsename(end_ip_address, 3)) * 65536 + 
         CONVERT(bigint, parsename(end_ip_address, 4)) * 16777216 ) 
        - 
        (CONVERT(bigint, parsename(start_ip_address, 1)) +
         CONVERT(bigint, parsename(start_ip_address, 2)) * 256 + 
         CONVERT(bigint, parsename(start_ip_address, 3)) * 65536 + 
         CONVERT(bigint, parsename(start_ip_address, 4)) * 16777216 )
      ) > 255;

Remediation:

Remove database firewall rules that grant excessive access

EXECUTE sp_delete_database_firewall_rule N'$0';

VA2064: Database-level firewall rules should be tracked and maintained at a strict minimum

Applies To: SQL Database
⚓ anchor link

The Azure SQL Database-level firewall helps protect your data by preventing all access to your database until you specify which IP addresses have permission. Database-level firewall rules grant access to the specific database based on the originating IP address of each request. Database-level firewall rules for master and user databases can only be created and managed through Transact-SQL (unlike server-level firewall rules which can also be created and managed using the Azure portal or PowerShell). For more details please see: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-firewall-configure. This check enumerates all the database-level firewall rules so that any changes made to them can be identified and addressed.

Rationale:

Firewall rules should be strictly configured to allow access only to client computers that have a valid need to connect to the database. Any superfluous entries in the firewall may pose a threat by allowing an unauthorized source access to your database.

Query:

SELECT name
    ,start_ip_address
    ,end_ip_address
FROM sys.database_firewall_rules

Remediation:

Evaluate each of the database-level firewall rules. Remove any rules that grant unnecessary access and set the rest as a baseline. Deviations from the baseline will be identified and brought to your attention in subsequent scans.

EXECUTE sp_delete_database_firewall_rule N'$0';

☝ back to top


Severity: Medium

VA1044: Remote Admin Connections should be disabled unless specifically required

Applies To: SQL Server, SQL Managed Instance
⚓ anchor link

This rule checks that remote dedicated admin connections are disabled if they are not being used for clustering to reduce attack surface area. SQL Server provides a dedicated administrator connection (DAC). The DAC lets an administrator access a running server to execute diagnostic functions or Transact-SQL statements, or to troubleshoot problems on the server and it becomes an attractive target to attack when it is enabled remotely.

Rationale:

The Dedicated Admin Connection (DAC) is intended to be used by administrators for troubleshooting in scenarios when normal connections are not available due to an abnormal state of the system. For scenarios, other than clusters, the DAC is intended to be used only on the same node, and not remotely, to prevent automated attacks against this entry point.

Query:

SELECT CASE
    WHEN EXISTS (SELECT *
        FROM   sys.configurations
        WHERE  NAME = 'remote admin connections'
            AND Cast(value AS INT) = 1
            AND ISNULL(SERVERPROPERTY('IsClustered'), 0) = 0) THEN 1
    ELSE 0
    END AS Violation

Remediation:

Disable remote dedicated admin connections. A good alternative would be to access box directly and use DAC instead of RDAC.

EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE;
EXECUTE sp_configure 'remote admin connections', 0; RECONFIGURE;
EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE;

VA1051: AUTO_CLOSE should be disabled on all databases

Applies To: SQL Server
⚓ anchor link

The AUTO_CLOSE option specifies whether the database shuts down gracefully and frees resources after the last user disconnects. Regardless of its benefits it can cause denial of service by aggressively opening and closing the database, thus it is important to keep this feature disabled. This rule checks that this option is disabled on the current database.

Rationale:

Databases marked with AUTO_CLOSE allows the DB to be closed if there are no active connections. In the case of partially contained databases, the authentication of users occurs within the database itself, so the database must be opened every time to authenticate a user. Frequent opening/closing of the database consumes additional resources and may contribute to a denial of service attack.

Query:

SELECT CASE
    WHEN EXISTS (SELECT *
    FROM   sys.databases
    WHERE  NAME = Db_name()
        AND is_auto_close_on = 1) THEN 1
    ELSE 0
    END       AS Violation
        , Db_name() AS [Database]

Remediation:

Disable the AUTO_CLOSE option on the affected databases.

ALTER DATABASE [$1] SET AUTO_CLOSE OFF

VA1143: ‘dbo’ user should not be used for normal service operation

Applies To: SQL Server, SQL Database, SQL Managed Instance, Azure Synapse
⚓ anchor link

The ‘dbo’, or database owner, is a user account that has implied permissions to perform all activities in the database. Members of the sysadmin fixed server role are automatically mapped to dbo. This rule checks that dbo is not the only account allowed to access this database. Please note that on a newly created clean database this rule will fail until additional roles are created.

Rationale:

A compromised service that accesses the database with the ‘dbo’ user account will have full control of the database. To avoid this situation, lower privileged users should be defined for normal service operation, while the ‘dbo’ account should only be used for administrative tasks that require this privilege.

Query:

IF((SELECT count(*) from sys.database_principals  WHERE principal_id >= 5 AND principal_id  0) SELECT 0 AS Violation
ELSE SELECT 1 AS Violation

Remediation:

Create users with low privileges to access the DB and any data stored in it with the appropriate set of permissions.

VA1244: Orphaned users should be removed from SQL server databases

Applies To: SQL Server, SQL Managed Instance
⚓ anchor link

A database user that exists on a database, but has no corresponding login in master database or as an external resource (i.e. Windows user) is referred to as an orphaned user and it should either be removed or remapped to a valid login. This rule checks that there are no orphaned users.

Rationale:

Orphaned users are typically signs of a misconfiguration. These users create a risk because potential attackers might get access to them and inherit their permissions on the database.

Query:

SELECT NAME AS Principal
FROM sys.database_principals
WHERE sid NOT IN (
        SELECT sid
        FROM sys.server_principals
        )
    AND authentication_type_desc = 'INSTANCE'
    AND type = 'S'
    AND principal_id != 2
    AND DATALENGTH(sid) <= 28
ORDER BY NAME

Remediation:

Drop the orphaned users or remap them to a different login.

DROP USER [$0]

☝ back to top


Wanna know how I generated this page?
I used this script here.

Cover Image by TheDigitalWay from Pixabay

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.