How do I troubleshoot gMSA account issues in Applications Manager?

How do I troubleshoot gMSA account issues in Applications Manager?

Applies to: Applications Manager on Windows, connecting to SQL Server with a Group Managed Service Account (gMSA) - as the back-end database at installation time, or as an MS SQL monitor.

This guide covers the errors Applications Manager reports when a gMSA connection fails, what each one means, and the steps to resolve it.

1. How Applications Manager uses a gMSA

Three things about the way the product uses a gMSA explain most of the failures below.

  • No user name or password is sent. When the authentication type is Native authentication, Applications Manager removes the user and password from the connection and asks the driver to use the identity of the process instead (integratedSecurity=true). The connection therefore authenticates as whatever account the Applications Manager service runs as. If that is not the gMSA, nothing else in this article will help.
  • It is Windows only. There is no gMSA support on a Linux installation.
  • The connectivity test uses PsExec. At installation, and in the MS SQL Self-Help tool, the product tests the account by running sqlcmd on the SQL Server host as the gMSA, through PsExec:
    <APM_HOME>\PSTools\PsExec.exe \\<sql_server> -u <domain>\<gmsa>$ -p ~ sqlcmd -S <sql_server> -d master -Q "<query>"
    The ~ is a placeholder, not a password: a gMSA password is managed by Active Directory and is never typed. The product reports the result purely from the exit code of that command, and prints the command it ran, so you can repeat it by hand.

A gMSA connection still uses Kerberos underneath. If the errors point at tickets, SPNs or realms rather than at the account itself, use How do I troubleshoot SQL Server Kerberos configuration issues? alongside this article.

2. Errors you may see and what they mean

MessageWhere it appearsSection
To verify SQL connectivity using the provided gMSA account, PsExec.exe from the PSTools suite is requiredInstallation, Self-Help tool3.1
Connection to the SQL Server using the provided gMSA account via PowerShell has failedInstallation, Self-Help tool3.2
Invoke-Sqlcmd is not recognized as the name of a cmdletManual PowerShell test3.3
Test-ADServiceAccount returns FalsePowerShell on the Applications Manager host3.4
Native authentication/gMSA account is supported only on Windows based Applications Manager InstallationSelf-Help tool3.5
Access denied when the service startsWindows service control manager4.1
Integrated authentication failed ... Cannot get any of properties: [user, USER]StartUpLogs_out.txt4.2
Windows authentication with the configured service account failedMS SQL monitor using Native authentication4.2
Unable to login with database 'AMDB' due to SQL authentication failureStartUpLogs_out.txt4.3
Please verify that the service account is correct and ensure that this account has the required permissionsSelf-Help tool, monitor error4.3

3. Troubleshooting during installation

3.1 PsExec is required but was not found

Error messageTo verify SQL connectivity using the provided gMSA account, PsExec.exe from the PSTools suite is required.

What it means: the gMSA check could not run at all. Applications Manager does not ship PsExec, and it was not found in the installation folder.

Resolution: download the PSTools package from Microsoft and place PsExec.exe at <APM_HOME>\PSTools\PsExec.exe, then continue the installation. If PsExec has never been run on this host, accept its licence agreement once by running it manually - it waits for that agreement on first use, which otherwise looks like a hang.

3.2 Connection using the gMSA account has failed

Error messageConnection to the SQL Server using the provided gMSA account via PowerShell has failed. Please verify the gMSA account details and ensure that the account has the necessary privileges.

What it means: the PsExec command returned a non-zero exit code. Applications Manager reports the failure from that exit code alone, so the message says nothing about the cause - the cause is in the output of the command itself, which is why the message prints the exact command it ran.

Resolution: run that command by hand, from <APM_HOME> in an elevated command prompt, and read what it says. The common answers:

What the command saysWhat to do
Access is denied / the user name could not be foundThe gMSA is not usable on this host. Go to 3.4.
Login failed for user '<domain>\<gmsa>$'The account reached SQL Server and has no login there. Go to 4.3.
sqlcmd is not recognizedThe SQL Server command line utilities are not installed on the SQL Server host. Install them, or test with Invoke-Sqlcmd as in 3.3.
The network path was not found / could not start PsExec servicePsExec could not reach the SQL Server host. It needs file and printer sharing (TCP 445) and administrative rights on that host.
Nothing, and it waitsThe PsExec licence agreement has not been accepted on this host. Accept it once, then retry.

A named instance is tested as -S <sql_server>\<instance>. If the instance name is wrong, this is where it shows.

3.3 Invoke-Sqlcmd is not recognized

Error messageInvoke-Sqlcmd : The term 'Invoke-Sqlcmd' is not recognized as the name of a cmdlet, function, script file, or operable program.

What it means: this is a missing PowerShell module on the machine you are testing from, not an authentication problem.

Resolution:

Install-Module -Name SqlServer
Import-Module SqlServer
Get-Command Invoke-Sqlcmd

Then test the connection as the gMSA. Open a PowerShell window running as the account, and query from it:

<APM_HOME>\PSTools\PsExec.exe -i -u <domain>\<gmsa>$ -p ~ powershell.exe

Invoke-Sqlcmd -ServerInstance '<sql_server>' -Database 'master' -Query 'select getdate()'

3.4 Test-ADServiceAccount returns False

What it means: this host cannot retrieve the account's managed password, so it cannot run anything as the gMSA. Two separate things are needed: the domain must allow this host to retrieve the password, and the account must be installed on the host.

Resolution. On a domain controller, or any machine with the Active Directory PowerShell module and the rights to use it:

Set-ADServiceAccount -Identity <gmsa> -PrincipalsAllowedToRetrieveManagedPassword "<host>$"

Get-ADServiceAccount <gmsa> -Properties PrincipalsAllowedToRetrieveManagedPassword | Select-Object PrincipalsAllowedToRetrieveManagedPassword

The second command must list the machine account of the Applications Manager host, ending in $. To add a host without replacing the existing list, pass every host that needs it to Set-ADServiceAccount, since the parameter replaces rather than appends.

Then, on the Applications Manager host itself, in an elevated PowerShell:

Install-ADServiceAccount <gmsa>
Test-ADServiceAccount <gmsa>

Test-ADServiceAccount must return True before anything else in this article can work. If it still returns False immediately after the change, the host's Kerberos ticket is older than the change: restart the host, or wait for replication to reach the domain controller it is using.

3.5 gMSA is supported only on Windows

Error messageNative authentication/gMSA account is supported only on Windows based Applications Manager Installation.

What it means: exactly what it says - the installation is on Linux. A gMSA depends on Windows retrieving the managed password from Active Directory, which has no equivalent here.

Resolution: use Kerberos authentication with a keytab instead, which is supported on Linux. See Enabling Kerberos Authentication for SQL Server in Applications Manager.

4. Troubleshooting when starting Applications Manager

4.1 Access denied when the service starts

What it means: Windows refused to start the service as the gMSA. The account has not been granted the right to log on as a service.

Resolution:

  1. Open gpedit.msc on the Applications Manager host and go to Local Computer Policy → Computer Configuration → Windows Settings → Security Settings → Local Policies → User Rights Assignment.
  2. Add the gMSA to Log on as a service, in the form <DOMAIN>\<gmsa>$, including the $.
  3. Set the service itself to run as the account: in services.msc, open the Applications Manager service, Log On tab, enter <DOMAIN>\<gmsa>$ and leave both password boxes empty.
  4. Start the service.

If the policy is managed centrally, a domain policy may overwrite a local change at the next refresh. In that case the right has to be granted in the domain policy that applies to this host.

4.2 Integrated authentication failed at startup

In StartUpLogs_out.txtException Message: Kerberos Login failed: Integrated authentication failed. ClientConnectionId:... due to javax.security.auth.login.LoginException (Cannot get any of properties: [user, USER] from con properties not available to garner authentication information from the user)

What it means: the connection was made with no user name and no password, as a gMSA connection is meant to be, and the process had no Windows identity that SQL Server would accept. Either the service is not running as the gMSA, or the account it is running as has no login on SQL Server.

Resolution:

  1. Check which account the service actually runs as - services.msc, the Log On As column. A service left on Local System authenticates as the machine account, not as the gMSA, and this is the most common cause.
  2. If it is not the gMSA, set it as described in 4.1 and restart the service.
  3. If it is the gMSA, the account has no usable login on SQL Server - go to 4.3.

The same message on an MS SQL monitor. A monitor added with Native authentication fails the same way and for the same reason, with one thing worth knowing: the identity used is the Applications Manager service account, not anything entered on the monitor. A monitor cannot carry a gMSA of its own. So the first check is the same - which account the service runs as - and the second is whether that account has a login on the monitored instance (4.3).

Applications Manager cannot start while it cannot reach its own database, so the product stays down until this is resolved. If it must be brought up before then, revert <APM_HOME>\working\conf\database_params.conf to the authentication method used previously.

4.3 The login does not exist in SQL Server

Error messageUnable to login with database 'AMDB' due to SQL authentication failure.
Please verify that the service account <account> is correct and ensure that this account has the required permissions to access the database.

What it means: the account authenticated against the domain, and SQL Server has no login for it, or the login cannot reach the database.

Resolution. In SQL Server Management Studio, connected to the instance:

  1. Expand Security, right-click Logins, choose New Login.
  2. Enter the login name as <DOMAIN>\<gmsa>$, including the $, and select Windows authentication. A login created without the $ will never match.
  3. Grant what the role actually needs:
    • Back-end database: the account creates and owns the Applications Manager database, so it needs dbcreator at installation, and db_owner on AMDB afterwards.
    • MS SQL monitor: VIEW SERVER STATE on the server and CONNECT on each monitored database. sysadmin is not required.
  4. Restart the Applications Manager service, or re-test the monitor's credentials.

4.4 SPNs are missing or on the wrong account

What it means: the client could not get a ticket for the SQL Server service, so the connection fell back to NTLM or failed outright. This is a Kerberos registration problem that a correct gMSA does not avoid.

Which account holds the SPNThe MSSQLSvc SPN belongs to the account SQL Server runs as, which is not necessarily the gMSA Applications Manager connects with. Register it on the gMSA only when SQL Server itself runs as that gMSA.

Resolution:

  1. Confirm the account SQL Server runs as, in SQL Server Configuration Manager → SQL Server Services → Log On As.
  2. List what is registered, and check for duplicates across the forest:
    setspn -L <domain>\<sql_service_account>
    setspn -Q MSSQLSvc/<sql_server_fqdn>:1433
    setspn -X
    Two entries are expected, one with the port and one without:
    MSSQLSvc/<sql_server_fqdn>
    MSSQLSvc/<sql_server_fqdn>:1433
  3. Remove anything registered on the wrong account, then register it on the right one:
    setspn -D MSSQLSvc/<sql_server_fqdn>:1433 <domain>\<wrong_account>
    setspn -S MSSQLSvc/<sql_server_fqdn>:1433 <domain>\<sql_service_account>
    Use -S rather than -A: it refuses to create a duplicate, and a duplicate SPN breaks Kerberos for every client of that server.
  4. Restart the SQL Server service, then the Applications Manager service.

For the errors that follow from an SPN problem, and for anything that mentions tickets, realms or clock skew, see How do I troubleshoot SQL Server Kerberos configuration issues?

5. Confirm that it is working

Run this on the SQL Server while Applications Manager is connected. It is the same query the product's own gMSA check uses:

SELECT a.auth_scheme, b.login_name
FROM sys.dm_exec_connections AS a
JOIN sys.dm_exec_sessions AS b ON a.session_id = b.session_id
WHERE b.program_name = 'ApplicationsManager';

login_name must be <DOMAIN>\<gmsa>$ and auth_scheme should read KERBEROS. A different login name means the service is not running as the gMSA; NTLM means the SPN is not being matched (4.4).

The MS SQL Self-Help tool reports the same check as gMSA Account Authentication Result, with the command it ran and its output, which is usually quicker than reproducing the test by hand.

6. If the issue persists

Run every command below as an administrator, and replace the placeholders with your own values. Share the following:

  • The output of Test-ADServiceAccount <gmsa> on the Applications Manager host
  • The output of the PsExec command the product printed, run by hand
  • The output of setspn -L for the SQL Server service account, and of setspn -X
  • The Log On As value of both the Applications Manager service and the SQL Server service
  • StartUpLogs_out.txt, and the SQL Server error log covering the same period
  • The MS SQL Self-Help tool report, if the product starts
  • The Applications Manager Support Information File (SIF)

Refer to our feature page to learn more about SQL Server monitoring in Applications Manager.