Apparently there is a new tool from Microsoft where you can discover, track, and remediate potential database vulnerabilities. This tool is available for both on-premises SQL Server and Azure SQL Database. I actually cannot find the download for the on-premises version so I decided to give it a go in Azure SQL Database.

Before I start lets discuss what it actually is.

This is a service built into the Azure SQL Database service. The service employs a knowledge base of rules that flag security vulnerabilities and highlight deviations from best practices, such as misconfigurations, excessive permissions, and unprotected sensitive data. The rules are based on Microsoft’s best practices, and focus on the security issues that present the biggest risks to your database and its valuable data.  (source https://docs.microsoft.com/en-us/azure/sql-database/sql-vulnerability-assessment)

Within the portal you will find the option (currently in Preview) under settings.

Next you will need to select your subscription and the storage account to where it will hold the files.

Literally hit the SCAN button.

This is the output. Let’s look at a failure.

So the tool is complaining about the fact I am not really following the concept of LUA – Least privileged user account, well that is something I can’t help when using Microsoft’s in-build data sync services.

The TSQL the tool runs is:

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,
prin.type_desc AS [Principal Type],
prin.name AS Principal
FROM sys.database_permissions perms
LEFT JOIN
sys.database_principals prin
ON perms.grantee_principal_id = prin.principal_id
LEFT JOIN
sys.assemblies asm
ON perms.major_id = asm.assembly_id
LEFT JOIN
sys.xml_schema_collections xmlsc
ON perms.major_id = xmlsc.xml_collection_id
LEFT JOIN
sys.service_message_types msgt
ON perms.major_id = msgt.message_type_id
LEFT JOIN
sys.service_contracts svcc
ON perms.major_id = svcc.service_contract_id
LEFT JOIN
sys.services svcs
ON perms.major_id = svcs.service_id
LEFT JOIN
sys.remote_service_bindings rsb
ON perms.major_id = rsb.remote_service_binding_id
LEFT JOIN
sys.routes rts
ON perms.major_id = rts.route_id
LEFT JOIN
sys.database_principals printarget
ON perms.major_id = printarget.principal_id
LEFT JOIN
sys.symmetric_keys sym
On perms.major_id = sym.symmetric_key_id
LEFT JOIN
sys.asymmetric_keys asym
ON perms.major_id = asym.asymmetric_key_id
LEFT JOIN
sys.certificates crt
ON perms.major_id = crt.certificate_id
LEFT JOIN
sys.fulltext_catalogs ftc
ON perms.major_id = ftc.fulltext_catalog_id
WHERE
    permission_name IN ('CONTROL', 'TAKE OWNERSHIP', 'REFERENCES')
AND grantee_principal_id NOT IN (DATABASE_PRINCIPAL_ID('guest'), DATABASE_PRINCIPAL_ID('public'))
    AND class in (3,4,5,6,10,15,16,17,18,19,23,24,25,26)

On other failures it might even mention remediation steps. For one such example I was told to run:

REVOKE SELECT ON SCHEMA::[dss] FROM [DataSync_reader]
REVOKE EXECUTE ON SCHEMA::[dss] FROM [DataSync_executor]
REVOKE SELECT ON SCHEMA::[dss] FROM [DataSync_executor]
REVOKE SELECT ON SCHEMA::[TaskHosting] FROM [DataSync_reader]
REVOKE EXECUTE ON SCHEMA::[TaskHosting] FROM [DataSync_executor]
REVOKE SELECT ON SCHEMA::[TaskHosting] FROM [DataSync_executor]
REVOKE CREATE FUNCTION FROM [DataSync_admin] REVOKE CREATE PROCEDURE FROM [DataSync_admin]
REVOKE CREATE TABLE FROM [DataSync_admin]
REVOKE CREATE TYPE FROM [DataSync_admin]
REVOKE CREATE VIEW FROM [DataSync_admin]
REVOKE VIEW DATABASE STATE FROM [DataSync_admin]
REVOKE VIEW DEFINITION ON SYMMETRIC KEYS::[DataSyncEncryptionKey_efc1b60cf57e4da084fa2fe8ff8f30fe] FROM [DataSync_executor]
REVOKE VIEW DEFINITION ON SYMMETRIC KEYS::[DataSyncEncryptionKey_efc1b60cf57e4da084fa2fe8ff8f30fe] FROM [DataSync_admin]

I am not entirely convinced running this would be safe for my data sync group but never the less I still like the concept of the tool though.

Filed under: Azure, Azure SQL DB Tagged: Admin, Azure, Azure SQL DB, Checks, portal, Security