Using a gMSA Account for Applications Manager's SQL Server Backend
​

Important prerequisites before starting the installation:

1. A gMSA account can only be used when Applications Manager is running as a service on Windows platform.
   
   
2. To use gMSA account with the SQL Server in Applications Manager, Applications Manager must be installed on the operating system Windows Server 2012 R2 or later.
   
   
3. The SQL Server version should be SQL Server 2014 or later.

Note: This prerequisites are mandatory for MS SQL monitors as well, if you are going to use gMSA account for monitoring.

---

New Installation (Windows)

1. Install Applications Manager:

   • Proceed with the installation until the DB Configuration page.

   • Provide the Host, Port, DB Name, and gMSA User name with the Domain name (e.g., apmsql\gmsauser$). Leave the Password field empty.

   • Ensure that the gMSA User name ends with the $ character.

2. Download and Extract PSTools:

   • Download PSTools from Microsoft's website.

   • Extract the tools to the <APM_HOME> directory. PSTools are used to check the SQL connectivity with the gMSA account during the installation.

3. Check Connectivity with PSTools:

   • Now proceed with the installation window.

   • If the installation fails at DB Configuration step, open a command prompt and navigate to the <APM_HOME> directory.

   • Run the following command:
     

     D:\ManageEngine\AppManager17>\PSTools\PsExec.exe -i -u sqlkerberos\svc_sql$ -p ~ powershell.exe -Command "powershell.exe"
     

   • In the newly opened PowerShell window, run:

     Invoke-Sqlcmd -ServerInstance 'sql-kerb-1' -Database 'master' -Query 'select getdate()'
     

   • Replace sqlkerberos with your domain name, svc_sql$ with your gMSA account name, and sql-kerb-1 with your SQL Server's hostname in the above commands.

4. Powershell Errors Related to Invoke-Sqlcmd Module:

   • If you encounter the error:

     Invoke-Sqlcmd : The term 'Invoke-Sqlcmd' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. At line:1 char:1 + Invoke-Sqlcmd -ServerInstance 'sql-kerb-1' -Database 'master' -Qu ... + ~~~~~~~~~~~~~ + CategoryInfo : ObjectNotFound: (Invoke-Sqlcmd:String) [], CommandNotFoundException + FullyQualifiedErrorId : CommandNotFoundException
     

   • Install the SqlServer module by running the below command in PowerShell:
     Install-Module -Name SqlServer
     

   • Import the module into your PowerShell session:

     Import-Module SqlServer
     

   • Verify the availability of the Invoke-Sqlcmd cmdlet using below command in PowerShell:

     Get-Command Invoke-Sqlcmd
     

5. Continue Installation:

   • Now check with the installation.
     

   • If it is successful, on the final installation window, uncheck the option to "Launch Applications Manager service now" and finish the installation.
     

6. Service Configuration:

   • Go to Services > Applications Manager Service > Properties > Log On.

   • Choose the gMSA account and leave the password field empty.

   • Click Apply and OK, then start the service. Wait for 5 minutes for the complete startup of Applications Manager.
     

   • Here you go! You have successfully authenticated the gMSA account with the SQL Server in Applications Manager. You can now skip the following steps and proceed with monitoring.

7. Alternative Approach (Without PSTools):

   • Complete the installation using a normal Windows login.

   • On the final installation window, uncheck the option to "Launch Applications Manager service now" and finish the installation.

8. Update Configuration:

   • Open database_params.conf file and modify the JDBC URL parameters:

     • Set authenticationScheme to NativeAuthentication.

     • Set IntegratedSecurity to true.

     • Add/update the domain name.

     • Add/update the user name to the gMSA user name (e.g., svc_sql$).
       
       

       

9. Service Configuration:

   • Go to Services > Applications Manager Service > Properties > Log On.

   • Choose the gMSA account and leave the password field empty.

   • Click Apply and OK, then start the service. Wait for 5 minutes for the complete startup of Applications Manager.
     

   • Here you go! You have successfully authenticated the gMSA account with the SQL Server in Applications Manager.
     

10. Cleanup:

    • Remove PSTools from the <APM_HOME> directory.

Note: The gMSA account must have sufficient permissions to carry out database operations. Refer here for the minimum permissions required.

Existing Setup (Windows)

1. Stop Applications Manager Service.

2. Update Configuration:

   • Open database_params.conf file and modify the JDBC URL parameters:

     • Set authenticationScheme to NativeAuthentication.

     • Set IntegratedSecurity to true.

     • Add/update the domain name.

     • Add/update the user name to the gMSA user name (e.g., svc_sql$).

3. Service Configuration:

   • Go to Services > Applications Manager Service > Properties > Log On.

   • Choose the gMSA account and leave the password field empty.

   • Click Apply and OK, then start the service. Ensure the service starts without any issues. 

   • Wait for 5 minutes for the complete startup of Applications Manager.
     

   • Here you go! You have successfully authenticated the gMSA account with the SQL Server in Applications Manager.
     
     
     

Using a gMSA Account in MS SQL Monitors

Once the Applications Manager service is started with gMSA account as Logon Service account, then by default, gMSA account will work for SQL monitors by enabling the Authentication Type 'Native(gMSA)' as shown below.

Note: The gMSA account must have sufficient permissions to be used for SQL Server monitoring. Refer here for the minimum permissions required.