SQL Agent service account
Job details
| Name: | SQL Agent service account | |
| Platform: | Sqlserver | |
| Category: | Security | |
| Premium package: | Security and compliance package | |
| Description: | Checks if the service account used by the SQL Agent service is not a member of the Windows Administrator group. | |
| Long description: | Checks if the service account used by the SQL Agent service is not a member of the Windows Administrator group. | |
| Version: | 1.21 | |
| Default schedule: | 28 1 1 * | |
| Requires engine install: | Yes | |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & hasengine=‘YES’ & eng_inst_priv = 0 & (engine_edition = ‘Microsoft SQL Server’ | engine_edition = ‘Azure SQL Managed Instance’)] |
Parameters
| Name | Default value | Description |
|---|---|---|
| return status | 1 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when the SQL Agent service is a member of the Windows Administrator. |
| history threshold | 365 | The maximum number of days to keep statistics for in the historic tables. |
Job Summary
- Purpose: The purpose of this job is to ensure that the service account used by SQL Agent is not a member of the Windows Administrator group, in order to abide by the principle of least privilege and enhance security.
- Why: This job is critical because having the SQL Agent service account as a member of the Windows Administrator group can pose significant security risks, such as unauthorized system changes or access to sensitive data. Ensuring it is not part of this group limits potential security threats.
- Manual checking: You can check the configuration manually by using PowerShell or querying the system views directly in SQL Server:
powershell.exe Get-LocalGroupMember -Group "Administrators"Implementation Details
This task involves various operations such as:
- Checking the existence and configuration settings of SQL Server Agent service.
- Ensuring advanced options and command shell functionalities are enabled for the duration of the check.
- Gathering information about the SQL Agent service account and any related administrator privileges.
Tables involved in this operation include:
| Table Name | Purpose |
|---|---|
| dbw_sqlagent_service_account_info | Stores current usage information of the SQL Agent service account |
| dbw_sqlagent_service_account_histr | Stores historical data about the service account usage |
| dbw_sqlagent_service_account_output | Temporary table to hold command outputs |
| dbw_sqlagent_service_account_admin | Temporary table to hold output of administrator group checks |
| dbw_sqlagent_service_account_users | Used to store user types associated with the SQL Agent service |
Stored procedures are used to perform validations:
| Procedure Name | Purpose |
|---|---|
| dbw_sqlagent_service_account_proc | Main procedure to check SQL Agent service account’s group memberships |
| xp_cmdshell | Executing shell commands from within SQL (used conditionally) |
Dependencies
Following dependencies are configured for the monitoring job:
- Main procedure (dbw_sqlagent_service_account_proc)
- Current status table (dbw_sqlagent_service_account_info)
- History table to store historical data (dbw_sqlagent_service_account_histr)
- Temporary tables to assist in checks (dbw_sqlagent_service_account_output, dbw_sqlagent_service_account_admin, dbw_sqlagent_service_account_users)
Each of these dependencies has a cleanup action on failure, ensuring system stability and data integrity during the job execution.
Presentation
The results of this monitoring job are used to generate reports and visual representations:
- Service details with current configuration and status.
- Listed members of the Administrators group.
- Historical statistics and occurrences of account usage which might indicate potential unauthorized attempts or misconfigurations.
Presentation elements are structured as tables and, potentially, graphical representations such as bar charts for easy interpretation.
Compliance and Security Relevance
The task plays an essential role in maintaining compliance with security policies that mandate strict access controls for database service accounts. The job verifies compliance to prevent any privilege escalations or unauthorized administrative access through service accounts.