dsccommunity
SqlServiceAccount: Resource does not properly handle GMSA
Problem description
When using a pscredential object that contains the GMSA account username 'MYDOMAIN\mygmsaacct$' and a supposedly unused password, the MSSQLSERVER and SQLSERVERAGENT services fail to start with 'Bad username or password' failures in the SYSTEM event log. Opening the services manually and deleting the value in the password fields allows the services to start, until the next boot / restart, which gets reset back to a bad pwd value.
I've done a bit of digging, and it seems like SqlServerDSC should support this based on the Get-ServiceAccount code in the common module however it still looks like the SqlServiceAccount resource still passes both $account.username and $account.password regarless of it being a GMSA or not.
Verbose logs
The SQL Server (MSSQLSERVER) service failed to start due to the following error:
The service did not start due to a logon failure.
DSC configuration
###$myGMSAPSCredential is username: MYDOMAIN\mygmsa$, pwd: supposedlyunusedtxtpwd
SqlServiceAccount SqlDBRunAsGMSA
{
InstanceName = 'MSSQLSERVER'
ServiceType = 'DatabaseEngine'
ServiceAccount = $myGMSAPSCredential
RestartService = $True
Force = $False
}
Suggested solution
Only call $serviceObject.SetServiceAccount($account.UserName) for GMSA accounts instead of the current $serviceObject.SetServiceAccount($account.UserName, $account.Password) from here.
SQL Server edition and version
SQL Server 2019 15.0.4236.7
SQL Server PowerShell modules
Name Version Path
---- ------- ----
SQLPS 15.0 C:\Program Files (x86)\Microsoft SQL Server\150\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1
Operating system
OsName : Microsoft Windows Server 2019 Datacenter
OsOperatingSystemSKU : DatacenterServerEdition
OsArchitecture : 64-bit
WindowsVersion : 1809
WindowsBuildLabEx : 17763.1.amd64fre.rs5_release.180914-1434
OsLanguage : en-US
OsMuiLanguages : {en-US}
PowerShell version
PSVersion 5.1.17763.2931
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.17763.2931
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1
SqlServerDsc version
Name Version Path
---- ------- ----
SqlServerDsc 15.2.0 C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\15.2.0\SqlServerDsc.psd1
It seems the way SqlSetup and SqlServiceAccount handle the GMSA is different. SqlSetup wraps the calls to Get-ServiceAccount into a Get-ServiceAccountParameters which excludes the password.
Happy to see a PR that fixes this.
I'm guessing the call to SetServiceAccount() should be this when the property Password is null (according to SetServiceAccount Method (SqlService Class)).
$serviceObject.SetServiceAccount($account.UserName, '')
And when the property Password is not null then the existing call should be made
https://github.com/dsccommunity/SqlServerDsc/blob/e0bf6a5e127bf4bce95a5c4ac3b6b67b66da08e4/source/DSCResources/DSC_SqlServiceAccount/DSC_SqlServiceAccount.psm1#L261
