Changes to signed modules should be authorized

Mahesh Kumar Babu Kummara 50 Reputation points
2023-04-05T06:13:42.83+00:00

Hi, I was mitigating all security vulnerability risks as suggested by Microsoft, but I got stuck with "VA2129 - Changes to signed modules should be authorized" I don't see any remediation script provided. Could you please tell me how to mitigate the vulnerability?

SQL Server Other
{count} vote

Accepted answer
  1. Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator
    2023-05-03T21:33:49.4566667+00:00

    First, to get better understanding of a failed item on the VA report (or for that matter an item that passed), you can click on it and learn more. For this check, you can read this:User's image

    Now you may say that you still don't really get it, so I will try to explain the idea with my own words.

    If you want a user to be able to read some rows in a table, not others, one way you can deal with this is to put the SELECT statement inside a stored procedure, and in the procedure code control which rows the user can see. You don't grant the user any permission on the table, but only to run the procedure. As long as the procedure and the table has the same owner, there will be no permission check, and therefore the user can get the data. This is known as "ownership chaining" as they talk of above.

    Now, let's say that you instead want to package an "advanced" permission with the procedure. For instance, say that you want to permit a super-user to add users to the database and grant them a specific set of permissions. You can write a stored procedure that accepts a user name as parameter, and then performs the required CREATE USER and GRANT commands. You don't grant the super-user this permission. Instead you sign this procedure with a certificate, and from this certificate you create a user, and it is this certificate user that you grant permissions to create users and grant permissions.

    This is a very powerful technique that I describe in-depth in the article Packaging Permissions in Stored Procedures on my web site.

    This check reports all signed procedures in the database. The idea is that you should investigate to see this is list is OK. That is, are these procedures you know that have been signed and what for. If that is the case for all listed modules, you would click "Approve as Baseline". Next time you run the assessment, you would see signed procedures that have been changed or added since last time.

    Now, in your particular case, I see in the screenshot that this is msdb. In my msdb there 255 signed objects, all signed by ##MS_AgentSigningCertificate##, that is a certificate that ships with SQL Server. But how would you know that the signature are actually valid? Maybe someone has compromised the certificate, changed the procedures and re-signed them? When I look in sys.certificates, I see that the certificate has no private key. And without a private key, you can use a certificate for signing, only for validation of the signature. So you should be fine.

    Note that in the report you don't see the signature, but in the expanded part, there is a query you can run and which gives you more details. Would there be procedures not signed by ##MS_AgentSigningCertificate##, that's kind of interesting!

    0 comments No comments

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.