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

powershell.exe Get-LocalGroupMember -Group "Administrators"

Implementation Details

This task involves various operations such as:

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:

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:

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.