MS SQL service account

Job details

Name:	MS SQL service account
Platform:	Sqlserver
Category:	Security
Premium package:	Security and compliance package
Description:	Checks if the service account used by the MSSQLSERVER service is not a member of the Windows Administrator group.
Long description:	Checks if the service account used by the MSSQLSERVER service is not a member of the Windows Administrator group.
Version:	1.21
Default schedule:	17 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’)]

Name	Default value	Description
return status	1	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when the MSSQLSERVER service is a member of the Windows Administrator.
history threshold	365	The maximum number of days to keep statistics for in the historic tables.

Purpose: The purpose of this job is to monitor and ensure that the service account used by the MSSQLSERVER service is not a member of the Windows Administrator group, maintaining a secure service environment.
Why: This job is important because if the MSSQLSERVER service account has administrator privileges, it could pose significant security risks such as unauthorized access or manipulation of server data and settings. Ensuring the service account is limited to necessary privileges helps adhere to the principle of least privilege, enhancing the security posture of the MS SQL Server environment.
Manual checking: You can check this manually in the database by issuing the following SQL commands:

xp_cmdshell 'powershell.exe Get-LocalGroupMember -Group "Administrators"'
and

select servicename, service_account from sys.dm_server_services where upper(servicename) like upper('%SQL Server (%')

Table Name	Description
dbw_mssql_service_account_info	Stores current status of the service account
dbw_mssql_service_account_histr	Maintains history of checks performed over time
dbw_mssql_service_account_output	Temporary helper table for command outputs
dbw_mssql_service_account_admin	Stores output related to the administrators’ group check from the command line
dbw_mssql_service_account_users	Helper table storing the type and name of users processed during the check

“Service details” presentation captures various service details from MS SQL Server using the sys.dm_server_services table.
“Administrator users” presentation displays the users in the Administrators group, fetched using the PowerShell command ‘Get-LocalGroupMember’.
“Aggregated statistics” presentation shows historical data of how frequently the MS SQL Service Account has been checked.

Scheduled Execution: The default schedule for this monitoring job is set to execute at 1:17 AM every month, ensuring regular validation without manual intervention.
Automated Outcome Management: The procedure is designed to update job status and interaction records dynamically, allowing seamless reflection of the current security state in terms of service account usage in the MS SQL server environment.
Error Handling: The SQL procedures encompass try-catch blocks for robust error management, ensuring clarity in exception handling and error messaging feedback, thus aiding troubleshooting processes.