Install fails when using a Domain Service account that begins with $

[DaniSQL]

Details of the scenario you tried and the problem that is occurring:
SQL Server install fails when the service account used is a domain account that starts with $ sign. Ex. $SQLSvcAcct.

Here is the error I got when running it from ISE:

VERBOSE: [SQL1]:                            [[xSQLServerSetup]SetupSQL] Adding user 'contoso\sqlInstall' from the parameter 'PsDscRunAsCredential' as the first 
system administrator account for SQL Server.
VERBOSE: [SQL1]:                            [[xSQLServerSetup]SetupSQL] Starting setup using arguments: /ACTION="Install" /AGTSVCACCOUNT="contoso\SQLSvc" /SQLUS
ERDBDIR="C:\MSSQL\SQLDATA3" /AGTSVCSTARTUPTYPE="Automatic" /QUIET="True" /SQLTEMPDBLOGDIR="C:\MSSQL\SQLTEMPDB4\tempLDF" /SQLBACKUPDIR="C:\MSSQL\SQLBACKUP4" /AGT
SVCPASSWORD="********" /INSTANCENAME="DB5" /SQLUSERDBLOGDIR="C:\MSSQL\SQLLOGS3" /SQLSYSADMINACCOUNTS="contoso\dantem" "contoso\sqlInstall" /SQLTEMPDBDIR="C:\MSS
QL\SQLTEMPDB4\tempMDF" /IACCEPTSQLSERVERLICENSETERMS="True" /SQLSVCACCOUNT="**contoso\$SQLSvcAcc**t" /FEATURES=SQLENGINE
VERBOSE: [SQL1]:                            [[xSQLServerSetup]SetupSQL] Started the process with id 1716 using the path 'C:\DSC\SQL2016\setup.exe', and with a t
imeout value of 7200 seconds.
WARNING: [SQL1]:                            [[xSQLServerSetup]SetupSQL] Setup exited with code '-2061893563'. Please see the 'Summary.txt' log file in the 'Setu
p Bootstrap\Log' folder.
VERBOSE: [SQL1]:                        

I have checked setup summary.txt and here is what I got:

Exception type: Microsoft.SqlServer.Chainer.Infrastructure.InputSettingValidationException
    Message: 
        The SQL Server service account login or password is not valid. Use SQL Server Configuration Manager to update the service account.
    HResult : 0x851a0045
        FacilityCode : 1306 (51a)
        ErrorCode : 69 (0045)
    Data: 
      SQL.Setup.FailureCategory = InputSettingValidationFailure
      DisableWatson = true
    Stack: 
        at Microsoft.SqlServer.Chainer.Infrastructure.InputSettingService.LogAllValidationErrorsAndThrowFirstOne(ValidationState vs)
        at Microsoft.SqlServer.Configuration.SetupExtension.ValidateFeatureSettingsAction.ExecuteAction(String actionId)
        at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)
        at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.<>c__DisplayClasse.<ExecuteActionWithRetryHelper>b__b()
        at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(ActionWorker workerDelegate)
    Inner exception type: Microsoft.SqlServer.Configuration.SqlEngine.ValidationException
        Message: 
                The SQL Server service account login or password is not valid. Use SQL Server Configuration Manager to update the service account.
        HResult : 0x851a0045
                FacilityCode : 1306 (51a)
                ErrorCode : 69 (0045)

and here is portion of the Config file generated by setup.exe

  SQLCOLLATION:                  SQL_Latin1_General_CP1_CI_AS
  SQLSVCACCOUNT:                 contoso\$SQLSvcAcct
  SQLSVCINSTANTFILEINIT:         false
  SQLSVCPASSWORD:                <empty>
  SQLSVCSTARTUPTYPE:             Automatic
  SQLSYSADMINACCOUNTS:           contoso\sqlInstall

If I changed the service account from contoso\$SQLSvcAcct to contoso\SQLSvcAcct installation will be successful.

Does anyone run to this issue before? Any idea what is going on?

The DSC configuration that is using the resource (as detailed as possible):
I can't share at this time but I have tried it from simple DSC configuration to moderately complex one
Version of the Operating System, SQL Server and PowerShell the DSC Target Node is running:
Windows 2012 R2, PowerShell 5.1, SQL Server 2016
What module (SqlServer or SQLPS) and which version of the module the DSC Target Node is running:
Tested on both on SQLServer and SQLPS module and got the same issue

Version of the DSC module you're using, or 'dev' if you're using current dev branch:
xSQLServer module V 9.0.0.0

[randomnote1]

If I changed the service account from contoso$SQLSvcAcct to contoso\SQLSvcAcct installation will be successful.

Just double-checking here....
Do you mean if you change contoso\$SQLSvcAcct to contoso\SQLSvcAcct?

[DaniSQL]

Correct. changed the markdown to code and it is now shown correctly.

[johlju]

I see this in the verbose log in your issue description. Does it get outputted like this, or have you done some obfuscation so you added stars '*' in the string?

/SQLSVCACCOUNT="**contoso\$SQLSvcAcc**t"

I think it should have looked like below for it to work. If it looks like the one above, then maybe we need to escape $ characters for this to work (just a hunch).

/SQLSVCACCOUNT="contoso\$SQLSvcAcct"

[DaniSQL]

@johlju I think somehow ** must have been introduced by the markdown here at github after I pasted it. Here is screenshot from the errors:

I got this behavior in two different environments....I appreciate if someone here try to replicate this issue in a lab...

[johlju]

The username seem to be sent correctly to the setup process. Is it that SQL Server does not like a username having '$' character in it. 🤔
Does it work installing SQL Server manually (not using DSC) using that account as the service account?

[DaniSQL]

@johlju that is a good troubleshooting step to rule out certain scenarios.
I have verified manual GUI install of SQL Server 2016 works fine with a service account that has $. Also i have verified that changing service accounts of an already installed instances with a service account that starts with $ works without an issue.

[johlju]

Hmm... 🤔 When you run manually thru the GUI can you see what the Summary.txt file look like for this argument? You find it in the following path "C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\Log\Summary.txt". Change to the correct major version number depending on SQL Server version.
Interested in both what it says on the property 'SQLSYSADMINACCOUNTS' and if it outputed the command line used.
If you can, please add it to a gist, replace any sensitive information.

Could you run the arguments from the screenshot above from the command line? Just cd to the install folder and run setup.exe followed with the arguments above. Although, please remove QUIET from the argument list so that you get the interactive install. I wonder if there is a difference between GUI and command line. Maybe GUI adds something to escape the $ character.

[johlju]

I had time to run this in the lab today. I could reproduce the problem. Confirming that this is a bug.

The problem is that the password argument is not returned by the helper function Get-ServiceAccountParameters. This is because the username is being seen as a Managed Service Account. The switch-statement returns just the username because this regex is true '.*\$'.

https://github.com/PowerShell/SqlServerDsc/blob/29f2fc48f65fdf44d7e98a11dc0bf78de07db4a0/DSCResources/MSFT_SqlSetup/MSFT_SqlSetup.psm1#L2124-L2129

This regex seems wrong since the '$' sign is at the end of the account name, not after the backslash.

• In Account Name enter the domain account and include a “$” after the gMSA name:
  • contoso\gMSAsqlservice$

https://blogs.msdn.microsoft.com/markweberblog/2016/05/25/group-managed-service-accounts-gmsa-and-sql-server-2016/

The previous behavior was to check at the end of the string, but that check could give false positives also. See the change that was made at the diff d2636b3#diff-2031fd07fa2054ae7de513597a3acb0eL1374

I think we should change the regex to '\$$'.

From regex101.com:
\$$
\$ matches the character $ literally (case sensitive)
$ asserts position at the end of the string, or before the line terminator right at the end of the string (if any)

[johlju]

@DaniSQL Thanks for reporting this! I just merged a fix in PR #1060

[DaniSQL]

@johlju Thank you so much for figuring this out. I back ported the fix in older xSQLServer module I am using (version 9.0.0.0 )and tested it today and the fix worked as expected.