Full-Text service account

Job details

Name:	Full-Text service account
Platform:	Sqlserver
Category:	Security
Premium package:	Security and compliance package
Description:	Checks if the service account used by the Full-Text service account is not a member of the Windows Administrator group.
Long description:	Checks if the service account used by the Full-Text service account is not a member of the Windows Administrator group.
Version:	1.21
Default schedule:	11 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 Full-Text service account 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 whether the service account used by the SQL Server Full-Text Search service is not a member of the Windows Administrator group, ensuring adherence to security best practices.
Why: This job is important because if the Full-Text service account has administrative privileges, it could pose a security risk, potentially giving elevated rights to services that do not require them. This can lead to unauthorized access or manipulation of database and server-level security settings.
Manual checking: You can check this manually in the database by issuing these SQL commands:


SELECT servicename, service_account FROM sys.dm_server_services WHERE servicename LIKE '%Full-text%'
EXEC xp_cmdshell 'powershell.exe Get-LocalGroupMember -Group "Administrators"'

Execution Details

The job involves checking the service account of the Full-Text Search service against the list of Windows Administrators group members.
It includes SQL code in the form of stored procedures and helper tables to facilitate these checks.
A dedicated procedure called “dbw_full_text_service_account_proc” handles the execution, leveraging dynamic SQL and PowerShell commands to examine system configurations and memberships.

Dependencies and Implementation

There are several dependencies including tables to store current status, historical data, and temporary results for administrative checks:
- “dbw_full_text_service_account_info”, “dbw_full_text_service_account_histr”, “dbw_full_text_service_account_output”, “dbw_full_text_service_account_admin”, and “dbw_full_text_service_account_users”.
Stored procedures and helper SQL commands are employed to manage and query these tables.

Schedules and Alerts

Default schedule for this job is defined to run periodically which ensures continuous monitoring.
Acknowledgements are in place to track if issues are being recognized and addressed after alerts.

Tables and Output

Besides monitoring, this job also provides important details on the configuration and state of the Full-Text Search service in SQL Server.
Alerts and outputs from the job provide actionable insights, ensuring database administrators can secure and optimize the database environment effectively.

Compliance and Security

By confirming that the Full-Text Search service account does not have undue administrative privileges, this job aids in maintaining regulatory compliance and aligns with security frameworks.
It helps reinforce least privilege practices crucial for minimizing potential attack surfaces in SQL Server environments.

The ongoing effectiveness of this job is crucial for upholding database security and operational standards in environments where SQL Server’s Full-Text Search service is used.