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’)] |
Parameters
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. |
Job Summary
- 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 (%')
Tables and Objects
- Below are the key tables and their purposes:
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 |
Report Presentations
- “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.
Automatic Monitoring Execution and Handling
- 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.