SQL Server Vulnerability VA2129 - Changes to signed modules should be authorised (Application Database)

Question

Here's the SQL for this vulnerability. My application database has neither 'certificates' or 'asymmetric keys' - but every Stored Procedure in this database is flagged.

The issue is the second IIF statement. As both the certificate & asymmetric key lookups are left JOINS
and I have no data - the query is defaulting to 'Asymmetic Key' - which I don't have.

Could someone correct the vulnerability according. Thanks.

AJ

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]
,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]
FROM
sys.objects AS oj
INNER JOIN sys.schemas AS sc ON oj.schema_id = sc.schema_id
LEFT OUTER JOIN sys.crypt_properties AS cp ON cp.major_id = oj.object_id
LEFT OUTER JOIN sys.sql_modules AS md ON md.object_id = oj.object_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 NOT (
sc.name = 'dbo'
AND oj.name = 'sp_MSrepl_startup'
AND master.dbo.fn_varbintohexstr(HASHBYTES('SHA2_256', DEFINITION)) = '0x78c0596dc09e6f4a26ba44c8572163e4b1f34382349d88b1cc1d1d8361cf0647'
)
AND NOT (
sc.name = 'dbo'
AND oj.name = 'sp_MScleanupmergepublisher'
AND master.dbo.fn_varbintohexstr(HASHBYTES('SHA2_256', DEFINITION)) = '0x173add6847de4a92d3b82abac61f93deadf8d6465d312dba1b403ae1ad6abcfc'
)

Answer 1

Hi Oury,

Not really sure what your requesting... Here's the resultset from one of our application databases. In my opinion the above SQL should return an empty resultset....

AJ

Answer 2

Hi, our security team also flagging this issue.
May I know the solution for this?

Answer 3

There currently is no solution from Microsoft on this issue. I'm hopeful that they will respond here with a solution soon.

AJ

Answer 4

Hi,

It seems that this was fixed in latest versions.
I got the same query and interrogation as you, running SQL Server 2016 SP3.
On SQL Server 2019 though, the query has different source table and no more false results:

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]  
    ,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'  

a+,=)
-=Clement=-