SqlServiceAccount: Unable to set the service account

[heaivilin]

SqlServiceAccount: Unable to set the service account

[johlju]

Have you tried using both NETBOIS and FQDN; e.g. DOMAIN\myaccount and [email protected] does neither work?

[johlju]

That SqlSetup says that the domain account does not exist, that I have never seen before I would have assumed it actually do not exist. 🤔 Let the issue focus on SqlServiceAccount that you provided verbose output for, fixing that would probably solve the other one.

[heaivilin]

Have you tried using both NETBOIS and FQDN; e.g. DOMAIN\myaccount and [email protected] does neither work?

Yes, I've attempted both.

[heaivilin]

That SqlSetup says that the domain account does not exist, that I have never seen before I would have assumed it actually do not exist. 🤔 Let the issue focus on SqlServiceAccount that you provided verbose output for, fixing that would probably solve the other one.

I can update manually after the install is done without issue. So the account does exists and is valid. I also agree with you, it's a weird error that I've not seen previously.

Could this a permission error authenticating to Active Directory? Not sure how I could check this either.....

[johlju]

It looks like you running the configuration as SYSTEM which can be an issue. When you do it manually you do it probably as a domain user. Suggest debugging this at your end, for example trying these as the user (SYSTEM) the configuration runs at.
But you should probably run the resource as a domain user to be able to set domain users. 🤔

$managedComputer = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer -ArgumentList '<ServerName>'
$managedComputer.Services.Name
$serviceObject = $managedComputer.Services | Where-Object -FilterScript {
        $_.Name -eq '<ServiceName>'
    }
$serviceObject.SetServiceAccount('<UserName>', '<Password>')

[heaivilin]

It looks like you running the configuration as SYSTEM which can be an issue. When you do it manually you do it probably as a domain user. Suggest debugging this at your end, for example trying these as the user (SYSTEM) the configuration runs at. But you should probably run the resource as a domain user to be able to set domain users. 🤔

$managedComputer = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer -ArgumentList '<ServerName>'
$managedComputer.Services.Name
$serviceObject = $managedComputer.Services | Where-Object -FilterScript {
        $_.Name -eq '<ServiceName>'
    }
$serviceObject.SetServiceAccount('<UserName>', '<Password>')

@johlju, the code above works as long as I've imported the DBATools module for SMO. I attempted running as my personal account and as the SYSTEM user via psexec.exe sysinternal tool. Both work and successful set the service account. So this doesn't appear to be an issue with the SYSTEM account.

I've confirmed that the the DBATools are getting installed before I start the DSC build. How can I validate that the import-module has been run via the DSC to ensure the SMO is available?

[johlju]

See https://github.com/dsccommunity/SqlServerDsc/wiki#powershell-module-containing-smo-assemblies-optional

[heaivilin]

See https://github.com/dsccommunity/SqlServerDsc/wiki#powershell-module-containing-smo-assemblies-optional

I added the following to my DSC to get that variable set. I does get created and I'm still having the issue. This is done before I install SQL Server.

          Environment AddSMOEnvironmentVariable
          {
               Ensure = 'Present'
               Name = 'SMODefaultModuleName'
               Value = 'dbatools'

          }

Also, I've updated the DSC command to a run as account and am using my personal for testing as I know it can appropriately set the credentials:

 SqlServiceAccount setDBEngineRunAsAccount
          {
               InstanceName = 'MSSQLSERVER'
               ServiceType = 'DatabaseEngine'
               ServiceAccount = $ServiceAccount
               RestartService = $true
               PsDscRunAsCredential = $RunAs
               DependsOn = '[Environment]AddSMOEnvironmentVariable'
          }

When running it this way, I get the following error:

Message System.InvalidOperationException: Unable to set the service account for AT14BLDTESTDB01 on MSSQLSERVER. Message ---> System.Management.Automation.MethodInvocationException: Exception calling "SetServiceAccount" with "2" argument(s): "Set service account failed. " ---> Microsoft.SqlServer.Management.Smo.FailedOperationException: Set service account failed. ---> Microsoft.SqlServer.Management.Smo.FailedOperationException: SetServiceAccount failed for Service 'MSSQLSERVER'. ---> System.Runtime.InteropServices.COMException
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
at System.Management.ManagementObject.InvokeMethod(String methodName, ManagementBaseObject inParameters, InvokeMethodOptions options)
at System.Management.ManagementObject.InvokeMethod(String methodName, Object[] args)
at Microsoft.SqlServer.Management.Smo.Wmi.WmiSmoObject.InvokeMgmtMethod(ManagementObject mo, ManagementOperationObserver observer, String methodName, Object[] parameters)
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Smo.Wmi.WmiSmoObject.InvokeMgmtMethod(ManagementObject mo, ManagementOperationObserver observer, String methodName, Object[] parameters)
at Microsoft.SqlServer.Management.Smo.Wmi.Service.SetServiceAccount(String userName, String password)
...

[johlju]

I wonder if this is because it loads the wrong assemblies in a resource prior. Can you confirm that if you just run the resources Environment and SqlServiceAccount it still fails? Can you do this on a newly restarted machine to make sure there are no other session that the LCM has ran.

Also, can you get the verbose output for that new run.

You can also try running the resource using Invoke-DscResource to see if it fails then too.

[github-actions]

This issue has been automatically marked as stale because it has not had activity from the community in the last 30 days. It will be closed if no further activity occurs within 40 days. If the issue is labelled with any of the work labels (e.g bug, enhancement, documentation, or tests) then the issue will not auto-close.

[heaivilin]

I wonder if this is because it loads the wrong assemblies in a resource prior. Can you confirm that if you just run the resources Environment and SqlServiceAccount it still fails? Can you do this on a newly restarted machine to make sure there are no other session that the LCM has ran.

Also, can you get the verbose output for that new run.

You can also try running the resource using Invoke-DscResource to see if it fails then too.

Apologies on the delayed response. We did get it figured out. Weird routing issue with AD sending the request to our Europe AD Servers, causing the auth to timeout. Wish the error logging had been better from MS in the bootstrap log.. Regardless, we can mark this closed.