The Windows Task Scheduler executes the PowerShell script for data collection for both SQLOpsDB data collection and configuration health.
These collections can be completed via the SQL Agent jobs also, however, I chose to leverage the Windows task scheduler so jobs can be executed using gMSA. In the SQL Server, gMSA cannot be used as a proxy account.
As stated in the previous posts, there are three tasks. Each with difficult schedule and intent. All the tasks when deployed using the deployment tool are just a shell. Therefore, the schedule and login credentials must be provided.
- Setup gMSA — this account will execute the tasks. This account requires all the permissions on the collection machine and target servers. I will list the detail requirements in later posts.
- Update the task scheduler job to add trigger, add login, and set it to execute in the background.
How-To Setup gMSA
Follow your organizations naming standards.
# Step 1: Setup Windows Security Group for gMSA
New-ADGroup -Name gsg_SQLgMSA_SQLOpsDB `
-Description “Security group for SQLgMSA_SQLOpsDB Collection Machine” `
-GroupCategory Security -GroupScope Global
# Step 2: Add the collection machine to the security group. SQLCMS$ is the computer account for my collection machine.
Add-ADGroupMember -Identity gsg_SQLgMSA_SQLOpsDB `
-Members SQLCMS$
# Step 3: Create gMSA.
New-ADServiceAccount -Name gMSA_SQLOpsDB `
-PrincipalsAllowedToRetrieveManagedPassword gsg_SQLgMSA_SQLOpsDB `
-Enabled:$true -DNSHostName gMSASQLOpDB.Lab.Local `
-ManagedPasswordIntervalInDays 30 -SamAccountName gMSA_SQLOpDB
How-To Setup Task Scheduler Job
In order to assign gMSA to Task Scheduler job, we have to leverage PowerShell. If you try to set it up in GUI, it will return following error.
# Create Task Credentials for gMSA Account
$gMSAAcct = New-ScheduledTaskPrincipal -UserID LAB\gMSA_SQLOpDB$ -LogonType Password -RunLevel Highest
# Create Schedule for Each Task
$DailyTrigger6AM = New-ScheduledTaskTrigger -Daily -At "6 AM"
$DailyTrigger2AM = New-ScheduledTaskTrigger -Daily -At "2 AM"
$WeeklyTrigger4AM = New-ScheduledTaskTrigger -Weekly -DaysOfWeek Sunday -At "4 AM"
# Assign the Task to Schedule & gMSA
Set-ScheduledTask -TaskName "SQLOpsDB.DataCollection" -Trigger $DailyTrigger6AM -Principal $gMSAAcct
Set-ScheduledTask -TaskName "SQLOpsDB.ConfigurationHealth.Daily" -Trigger $DailyTrigger2AM -Principal $gMSAAcct
Set-ScheduledTask -TaskName "SQLOpsDB.ConfigurationHealth.Weekly" -Trigger $WeeklyTrigger4AM -Principal $gMSAAcct
Keep this script handy, as updating schedule for tasks must be done via PowerShell for gMSA enabled tasks.
SQL Canada (SQLCAN) 