I am in a project to harden our Windows 2019 Servers, one of which runs IIS and MS SQL Express 2022. I am attempting to create a new Audit in MS SQL Server Express 2022 (via SSMS). For security compliance, I need to set the audit destination to the Security Log rather than write to a file.
When I enable the new audit, it fails.
In the Event Viewer, the following two "error" entries that SQL does not have sufficient permission to access the Security Log:
"SQL Server Audit failed to access the security log. Make sure that the SQL service account has the required permissions to access the security log." Event ID: 33208
"SQL Server Audit could not write to the security log." Event ID: 33204
According to this Microsoft Learn page and other searches, this is what I have done:
- Set the EventSourceFlags to "1" in this registry key:
HKLM\SYSTEM\CurrentControlSet\Services\EventLog\Security\MSSQL$$Audit\EventSourceFlags
Set the Audit object access policy in Local Security Policy to Success/Failure.
Ran the following command to configure the Object Access subcategory:
auditpol /set /subcategory:"application generated" /success:enable /failure:enable
None of these steps resolved the problem.
I think the crux of the problem is this:
Practically everything I have read in my searches (including the Microsoft page linked above), says that the service account SQL Server is being run with must be added to Local Security Policy -> Security Settings -> Local Policies -> User Rights Assignment -> Generate Security Audits. The problem is, the service account running the instance of SQL Express is the virtual account NT SERVICE\MSSQL$SQLEXPRESS. This virtual account cannot be selected for the Generate Security Audits policy since it is a virtual account.
I did find that I can create a GPO, and select NT SERVICE\ALL SERVICES for the Generate Security Audits, which should give the service account rights to access the Security Log. I created this GPO and applied it, and it did work. I was able to enable the new audit, so it must have been able to access the Security Log.
However, it had an unintended consequence. This server is also running multiple websites using IIS. When IIS was installed, it added two local virtual accounts IIS APPOOL.NET v4.5 and IIS APPOOL.NET v4.5 Classic to the Generate Security Audits policy. When the GPO was applied to add NT SERVICE\ALL SERVICES it over-wrote the existing local policy and removed the privilege assignments for those virtual accounts.
I think this is a problem since IIS now can't send events to the Security Log. Just as with the virtual SQL service account, I cannot add the IIS virtual accounts back into the policy, nor could I add the IIS virtual accounts to the GPO since it obviously doesn't know of their existence.
Is there a way to add the NT SERVICE\ALL SERVICES with the GPO without wiping out the IIS virtual accounts?
Is there actually a way to manually add the IIS virtual accounts back into the local policy?
Or is there another way to get the SQL Server virtual service account permission to access the Security Log that I'm just not seeing?
