SqlServerDsc Account

The PowerShell SqlServerDsc module uses the account [NT AUTHORITY\SYSTEM] to login to SQL Server by default. However, I was puzzled by why it can execute admin commands since it was not in the sysadmin role. It turns out that [NT AUTHORITY\SYSTEM] is like a member of the group [NT SERVICE\Winmgmt] and it will use the group permissions.

For example, to enable CLR on the named instance SERVER01\SQL01, we can use this script

[DSCLocalConfigurationManager()]
Configuration LcmPush 
{    
    Node SERVER01
    {
        Settings 
        {
            AllowModuleOverwrite = $True
            ConfigurationMode = 'ApplyOnly'
            RefreshMode = 'Push'
        }
    }
}

Configuration SqlServerConfig
{
    Import-DscResource -ModuleName PSDesiredStateConfiguration
    Import-DscResource -ModuleName SqlServerDsc

    node SERVER01
    {
        SqlServerConfiguration clr
        {
            ServerName     = 'SERVER01'
            InstanceName   = 'SQL01'
            OptionName     = 'clr enabled'
            OptionValue    = 1
        }
    }
}

$MofPath = 'C:\DSC\LCM'
LcmPush -OutputPath $MofPath
Set-DscLocalConfigurationManager -Path $MofPath -Force 

SqlServerConfig -OutputPath $MofPath
Start-DscConfiguration -ComputerName 'SERVER01' -Path $MofPath -Force -Wait -Verbose 

A SQL trace will show that the account used is [NT AUTHORITY\SYSTEM]

If we remove [NT AUTHORITY\SYSTEM] from the sysadmin server role to and disable CLR

ALTER SERVER ROLE [sysadmin] DROP MEMBER [NT AUTHORITY\SYSTEM]
GO
sp_configure 'clr enabled', 0
reconfigure
GO

And execute the script again, it will still succeed

VERBOSE: [SERVER01]: LCM:  [ Start  Set      ]  [[SqlServerConfiguration]clr]
VERBOSE: [SERVER01]:                            [[SqlServerConfiguration]clr] Found PowerShell module SqlServer already imported in the session.
VERBOSE: [SERVER01]:                            [[SqlServerConfiguration]clr] Connected to SQL instance 'SERVER01\SQL01'.
VERBOSE: [SERVER01]:                            [[SqlServerConfiguration]clr] Configuration option 'clr enabled' has been updated to value '1'.
VERBOSE: [SERVER01]:                            [[SqlServerConfiguration]clr] The option was changed without the need to restart the SQL Server instance.
VERBOSE: [SERVER01]: LCM:  [ End    Set      ]  [[SqlServerConfiguration]clr]  in 0.3290 seconds.

If we remove [NT SERVICE\Winmgmt] from the sysadmin server role to and disable CLR

ALTER SERVER ROLE [sysadmin] DROP MEMBER [NT SERVICE\Winmgmt]
GO
sp_configure 'clr enabled', 0
reconfigure
GO

And execute the script again, it will error out

Exception calling "Alter" with "0" argument(s): "Alter failed. "
    + CategoryInfo          : NotSpecified: (:) [], CimException
    + FullyQualifiedErrorId : FailedOperationException
    + PSComputerName        : SERVER01
 
The PowerShell DSC resource '[SqlServerConfiguration]clr' with SourceInfo '::22::9::SqlServerConfiguration' threw one or more non-terminating errors while running the Set-TargetResource 
functionality. These errors are logged to the ETW channel called Microsoft-Windows-DSC/Operational. Refer to this channel for more details.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : NonTerminatingErrorFromProvider
    + PSComputerName        : SERVER01
The SendConfigurationApply function did not succeed.
    + CategoryInfo          : NotSpecified: (root/Microsoft/...gurationManager:String) [], CimException
    + FullyQualifiedErrorId : MI RESULT 1

There is very little documentation of what [NT SERVICE\Winmgmt] is for, but the experiment above shows that [NT AUTHORITY\SYSTEM] behaves like a member of the group [NT SERVICE\Winmgmt] even though is not actually a group