SQL Server security admins, attention: Auditing is missing attempts to change permissions, leading to repudiation and miss elevation attempts

Auditing is one if not the most crucial security control (read more about it here: Security concept: Audit Trail): without it it’s hard to figure out exactly how a breach happened, when it started, sometimes even if it happened at all and last but not least: “whodunnit” – in technical terms: non-repudiation. Unless the breach is at a stage that users notice, only Auditing can tell you that a breach happened.

Advice
All SQL Server installations should contain at least a basic security audit.

When running some tests in November last year I noticed that some events were not captured on a new SQL Server 2022 system. We (Sarpedon Quality Lab LLC, USA and Sarpedon Quality Lab GmbH, Germany) quickly ran more tests and compared different systems with a definitive result:

SQL Auditing fails to capture the SERVER_OBJECT_PERMISSION_CHANGE_GROUP Audit Action group

Affected: SQL Server 2022 (including CU 17) and Azure SQL Managed Instance. No fix till today.

This server-level audit action group is being monitored on our customer’s servers, because events in this category can directly impact the security of the SQL Server Instance.

The first to inform (besides our customers) was the security team at Microsoft Data of course and also wasted many hours with the Microsoft Support system just to get this rolling as a ticket.

Given the timeline and importance of this event, I want to raise awareness of this auditing-gap, hoping it gets fixed soon.

Recommended temporary workaround

There is no practical workaround for Auditing. Auditing every batch_completed is likely going to impact not only the system itself but also overload the audit and processing thereof.

Instead I recommend to keep track of server object permissions by taking scheduled snapshots of the system table sys.server_permissions and compare it over time for changes. Changes of server-level permissions should be the exception and always have a proper explanation. This way one would at least capture permissions that are changes and left in place. What it can miss are changes to permissions and then reverting the change quickly again. Therefore, make sure to audit other events like impersonation or role-membership changes.

Code to test

And here is the code to test this event on your system:

Set up an Audit including the event “SERVER_OBJECT_PERMISSION_CHANGE_GROUP”:

USE [master]

GO

CREATE SERVER AUDIT SARPEDON_SecurityAuditToFile

TO FILE — configure according to your needs

(             FILEPATH                   = N’D:\SQLData\Audits’

,      MAXSIZE                           = 100 MB

,      MAX_ROLLOVER_FILES  = 100

,      RESERVE_DISK_SPACE  = OFF

) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE)

GO

CREATE SERVER AUDIT SPECIFICATION SARPEDON_SecurityAuditSpec

FOR SERVER AUDIT SARPEDON_SecurityAuditToFile

ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),

— … in addition to many others

GO

ALTER SERVER AUDIT SARPEDON_SecurityAuditToFile

WITH (STATE = ON)

GO

ALTER SERVER AUDIT SPECIFICATION SARPEDON_SecurityAuditSpec

WITH (STATE = ON)

Test: if the system is affected by the bug, it will NOT capture any of the following event types:

— replace “MALICIOUS” with a Test Login of your choice

GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO MALICIOUS;

GO

GRANT ALTER ON ENDPOINT::[TSQL Default TCP] TO MALICIOUS

GO

GRANT IMPERSONATE ON LOGIN::sa TO MALICIOUS

GO

GRANT ALTER ON LOGIN::sa TO MALICIOUS

GO

— [role_SecurityAdmins] is a custom server role

GRANT ALTER ON SERVER ROLE::[role_SecurityAdmins] TO MALICIOUS

GO

Call to action

Please upvote the official bug-report:
Security-bug: SQL Auditing SERVER_OBJECT_PERMISSION_CHANGE_GROUP does not capture anything

And while you’re there, why not also vote for these other Auditing related suggestions:

1. Having the same Audit name on server and inside contained AG will lead to error when trying to start it (but not when creating it)
2. Implicit addition of database user is not audited
3. When adding an Entra ID admin for an ARC-enabled SQL Server this needs to be auditable in the instance itself
4. RESTORE VERIFYONLY should not be captured under CREATE DATABASE Audit Action group
5. Document ALL_AUDIT_SPECIFICATIONS_AND_ACTIONS wait type

Thank you

Happy Auditing

Andreas