Checking Out SQL Vulnerability Assessment

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.

SQLVA

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

StorageAcc

Literally hit the SCAN button.

SCANTIME

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

TSQLERROR

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)

LUA

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.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s