Problem
A customer recently reported that they tried to build a SQL Server running on Windows Server 2022 but found that the SQL Server Agent failed to start. Below is the error popup when starting the SQL Server Agent in SQL Server Configuration Manager.
There are no other errors in ERRORLOG, SQLAGENT, and Windows event logs.
We worked with the customer and collected TTD (Time Travel Debugging) traces on several services. Finally, we found out the cause of the problem.
Cause
In Windows, there is a group policy named “Configure log access” under “Computer Configuration” -> “Administrative Templates” -> “Windows Components” -> “Event Log Service” -> “Application”. You can use this policy to set an SDDL (Security Descriptor Definition Language) string to grant users access to the Application event log. An example of the SDDL string that can be used here is “O:BAG:SYD:(A;;0xf0005;;;SY)(A;;0x7;;;BA)(A;;0x1;;;S-1-5-32-573)”, for which the parts in the 3 parentheses grant the following accesses:
- (A;;0xf0005;;;SY) grants Full Control to the Local System account.
- (A;;0x7;;;BA) grants read and write access to the Built-in Administrators group.
- (A;;0x1;;;S-1-5-32-573) grants read access to the Event Log Readers group.
However, there was a problem in old versions of Windows up to Windows Server 2019 where this policy had not been strictly enforced. As a result, a user account not granted write access to the Application event log by this policy could still write to the said event log. In the versions of Windows released after Windows Server 2019, this problem has been fixed. Therefore, the restriction of access to the Application event log by this policy is correctly enforced. This has caused the problem of SQL Server Agent not starting on Windows Server 2022 in our case.
Upon service start, the SQL Server Agent calls a Windows API to register its own event source to the Application event log, which requires write access to it. Because the SQL Server Agent runs under an account that is not granted write access to the Application event log by the policy, the API call fails and SQL Server Agent quits itself because of the error.
Solution
There are two ways to solve this problem:
- Set the “Configure log access” policy to “Not Configured” or “Disabled”.
- If you need to enable the “Configure log access” policy, then you need to grant both read and write access to the account used by the SQL Server Agent service by adding the SDDL string for that account. The additional SDDL string should start with A;;0x7;;; and end with the SID string for that account. You should also add parentheses around the SDDL string.
As an example, the SID for the “NT Service\SQLSERVERAGENT” is S-1-5-80-344959196-2060754871-2302487193-2804545603-1466107430. The SDDL string that needed to be added is (A;;0x7;;;S-1-5-80-344959196-2060754871-2302487193-2804545603-1466107430) . Below is the whole SDDL string after this addition:
O:BAG:SYD:(A;;0xf0005;;;SY)(A;;0x7;;;BA)(A;;0x1;;;S-1-5-32-573)(A;;0x7;;;S-1-5-80-344959196-2060754871-2302487193-2804545603-1466107430)
How to check if you are hitting the same problem
1. The SQL Server Agent fails to start, but there are no errors logged in ERRORLOG, SQLAgent logs and event logs
2. The “Configure log access” policy under “Computer Configuration” -> “Administrative Templates” -> “Windows Components” -> “Event Log Service” -> “Application” is enabled. You can check this policy by running gpedit.msc.
Other known issues with SQL Server agent not starting
Thank you!!