Enabling Kerberos Authentication for Applications Manager's MS SQL Backend Database

Follow the steps given below to enable Kerberos authentication for the MS SQL backend database:

1. Stop the Applications Manager service. Open the Applications Manager Home directory in the command prompt and run the commands below one by one.

For Windows installation,

shutdownApplicationsManager.bat
shutdownApplicationsManager.bat -force

For Linux installation,

shutdownApplicationsManager.sh
shutdownApplicationsManager.sh-force

2. Go to <Applications Manager Home>\working\conf directory and take a backup of database_params.conf file.

3. Go to <Applications Manager Home>\working\conf\MSSQL\KerberosAuth\MicrosoftJDBC directory, copy database_params.conf file and paste it under <Applications Manager Home>\working\conf directory.

4. Open the database_params.conf file from the <Applications Manager Home>\working\conf directory. Update the JDBC URL with the Kerberos-enabled SQL Server details, including:

Fully Qualified Domain Name (FQDN)
Port number
Instance name (if applicable)

5. To use the keytab file for Kerberos authentication,

Create a keytab file in the SQL Server-installed machine using the steps mentioned at the bottom of the page.
Copy the created keytab file to the Applications Manager machine (Preferably under <Applications Manager Home>/working/conf/KerberosConfigurations/MSSQL/ directory)
Open login.conf file under the <Applications Manager Home>\working\conf\KerberosConfigurations\MSSQL\MicrosoftJDBCDriver directory and update the keytab file location and SQL Server's Principal Name (SPN). The login.conf file entries should be as below for authenticating via keytab:

SQLJDBCDriver {
                com.sun.security.auth.module.Krb5LoginModule required

                useticketCache=false

                doNotPrompt=true

                usekeytab=true

                keytab="C://Users//kerberosuser.MSSQLKERBEROS//Desktop//kerberos//AppManager14//working//conf//KerberosConfigurations//MSSQL//krb5.keytab"

                principal="MSSQLSvc/mssql-kerberos-dc1.mssqlkerberos.com:1433@MSSQLKERBEROS.COM"
                storeKey=false
                debug=false;
};

6. To use ticket cache for Kerberos authentication,

Run the kinit program for the user account to which Kerberos is enabled using the steps mentioned at the bottom of the page. By default, the ticket cache file will be created in the location {user.home}{file.separator}krb5cc_{user.name}
Copy the created ticket cache file to the Applications Manager machine (Preferably under <Applications Manager Home>/working/conf/KerberosConfigurations/MSSQL/ directory)
Open login.conf file under <Applications Manager Home>\working\conf\KerberosConfigurations\MSSQL\MicrosoftJDBCDriver directory and update the ticket cache file location and SQL Server's Principal Name (SPN). The login.conf file entries should be as below for authenticating via ticket cache:

SQLJDBCDriver {
            com.sun.security.auth.module.Krb5LoginModule required
            useticketCache=true
            ticketCache="C://Users//kerberosuser.MSSQLKERBEROS//Desktop//kerberos//AppManager14//working//conf//KerberosConfigurations//MSSQL//krb5cc_kerberosuser"
  doNotPrompt=true
  usekeytab=false
  principal="kerberosuser@MSSQLKERBEROS.COM"
  storeKey=false
            debug=false;
};

7. To connect via password, update the correct username and password in the database_params.conf file.

To update the user name & password, refer to the following link: https://pitstop.manageengine.com/portal/kb/articles/modify-mssql-server-details-currently-used-in-applications-manager
The login.conf file entries should be as follows for authenticating via password:

SQLJDBCDriver {
            com.sun.security.auth.module.Krb5LoginModule required
            useticketCache=false
            doNotPrompt=false
            usekeytab=false
    principal="MSSQLSvc/mssql-kerberos-dc1.mssqlkerberos.com:1433@MSSQLKERBEROS.COM"
            storeKey=false
            debug=true;
};

8. Open the java.security file under <Applications Manager Home>\working\jre\lib\security directory and check for the line (line 141) (#login.config.url.1=file:${user.home}/.java.login.config)

Add the below entry after this line,

login.config.url.1=file:<Applications Manager Home>/working/conf/KerberosConfigurations/MSSQL/MicrosoftJDBCDriver/login.conf

For example: login.config.url.1=file:C:/Users/kerberosuser.MSSQLKERBEROS/Desktop/kerberos/AppManager14/working/conf/KerberosConfigurations/MSSQL/MicrosoftJDBCDriver/login.conf

Reference: https://docs.oracle.com/javase/7/docs/technotes/guides/security/jgss/tutorials/LoginConfigFile.html

9. Open krb5.ini file under <Applications Manager Home>\working\conf\KerberosConfigurations directory and update the domain details. Refer to the attached sample files at the bottom.

10. Now start Applications Manager and verify that Kerberos authentication is being used by executing the following query on the SQL Server. The value returned in the auth_scheme column should be KERBEROS.

SELECT b.session_id,b.login_name, a.auth_scheme, b.host_name,program_name FROM sys.dm_exec_connections AS a JOIN sys.dm_exec_sessions AS b ON a.session_id = b.session_id ORDER BY program_name,host_name

(or)

Use MSSQLDebug.bat to check the Kerberos connection.

To revert to the previous database authentication configuration, replace the modified database_params.conf file with the backup copy and restart Applications Manager

How to create a keytab file?

Open the command prompt and execute the following command to create a keytab file:

ktpass -princ MSSQLSvc/mssql-kerberos-dc1.mssqlkerberos.com:1433@MSSQLKERBEROS.COM -mapuser kerberosuser@MSSQLKERBEROS.COM -crypto RC4-HMAC-NT -ptype KRB5_NT_PRINCIPAL -pass Password!23 -out C:\Users\kerberosuser\krb5.keytab

Creating a ticket cache for Kerberos Authentication

Kerberos Authentication

Reference: https://docs.oracle.com/javase/8/docs/jre/api/security/jaas/spec/com/sun/security/auth/module/Krb5LoginModule.html

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