Kerberos Authentication Setup for MS SQL Monitor

Applications Manager Configuration Guide

Table of Contents

• Step 1: Register & Verify the SPN
• Step 2: Generate & Verify the Keytab File
• Step 3: Configure the login.conf File
• Step 4: Configure apmKrb5.conf
• Step 5: Activate Kerberos for the Back-End Database
• Step 6: Add MS SQL Monitors Using Kerberos Authentication

❶  Register & Verify the SPN

An SPN (Service Principal Name) links your SQL Server service to your chosen account.

Where to Run

Run the following commands on the Domain Controller using a Command Prompt as Administrator.

Verify Existing SPN

setspn -L YOURDOMAIN\AccountName

Add SPN (If Missing)

setspn -A MSSQLSvc/yourhost.domain.com:1433 YOURDOMAIN\AccountName

❷  Generate & Verify the Keytab File

A Keytab file allows Applications Manager to authenticate automatically with SQL Server.

2.1 Generate the Keytab File

Where to Run

Log in to the Domain Controller using a Domain Administrator account.

Choose one of the options below based on your SQL Server configuration.

ktpass -out C:\ms_sql.keytab ^
-mapuser YOUR_SERVICE_ACCOUNT@YOURDOMAIN.COM ^
-princ MSSQLSvc/SQL_SERVER_FQDN:1433@YOURDOMAIN.COM ^
-pass ACCOUNT_PASSWORD ^
-ptype KRB5_NT_PRINCIPAL ^
-crypto AES256-SHA1 ^
-mapOp set

ktpass -out C:\ms_sql.keytab ^
-mapuser Administrator@YOURDOMAIN.COM ^
-princ MSSQLSvc/SQL_SERVER_FQDN:1433@YOURDOMAIN.COM ^
-pass ADMIN_PASSWORD ^
-ptype KRB5_NT_PRINCIPAL ^
-crypto AES256-SHA1 ^
-mapOp set

Example:

sql01.yourdomain.com

2.2 Move the Keytab File to Applications Manager

The command above creates the file: C:\ms_sql.keytab

Copy the File To

<APM_HOME>\working\conf\KerberosConfigurations\MSSQL\

2.3 Verify the Keytab File

Where to Run

Run this on the server where Applications Manager is installed.

Navigate to

cd <APM_HOME>\working\jre\bin\

Verify Authentication

kinit.exe -k -t ..\..\conf\KerberosConfigurations\MSSQL\ms_sql.keytab YOUR_PRINCIPAL_NAME@YOURDOMAIN.COM

Use the same principal name specified after -princ during ktpass.

Expected Result

If authentication fails:

1. Delete the existing keytab
2. Regenerate it using the correct account and password

❸  Configure the login.conf File

3.1 Locate the File

Navigate to:

<APM_HOME>\working\conf\KerberosConfigurations\MSSQL\MicrosoftJDBCDriver\

3.2 Edit login.conf

Open the file using Notepad or any text editor. Choose one of the following configurations.

SQLJDBCDriver {
  com.sun.security.auth.module.Krb5LoginModule required
  useKeyTab=true
  keyTab="/working/conf/KerberosConfigurations/MSSQL/ms_sql.keytab"
  principal="YOUR_PRINCIPAL_NAME@YOURDOMAIN.COM"
  doNotPrompt=true
  storeKey=false
  useTicketCache=false
  debug=false;
};

kinit -c <APM_HOME>\working\conf\KerberosConfigurations\MSSQL\krb5cc_user YourUser@YOURDOMAIN.COM

SQLJDBCDriver {
  com.sun.security.auth.module.Krb5LoginModule required
  useTicketCache=true
  ticketCache="/working/conf/KerberosConfigurations/MSSQL/krb5cc_user"
  principal="YourUser@YOURDOMAIN.COM"
  doNotPrompt=true
  useKeyTab=false
  debug=false;
};

Important Rules

C:/AppManager/working/conf/

@YOURDOMAIN.COM

3.3 Register login.conf

Open:

<APM_HOME>\working\jre\lib\security\java.security

Find:

#login.config.url.1=file:${user.home}/.java.login.config

Add the following line below it:

login.config.url.1=file:/working/conf/KerberosConfigurations/MSSQL/MicrosoftJDBCDriver/login.conf

❹  Configure apmKrb5.conf

Navigate to:

<APM_HOME>\working\conf\KerberosConfigurations\

Create a file named:

apmKrb5.conf

[libdefaults]
  default_realm = YOURDOMAIN.COM
  dns_lookup_realm = false
  dns_lookup_kdc = true
  ticket_lifetime = 24h
  forwardable = yes
  udp_preference_limit = 1

[realms]
  YOURDOMAIN.COM = {
    kdc = dc-hostname.yourdomain.com
    admin_server = dc-hostname.yourdomain.com
    default_domain = yourdomain.com
  }

[domain_realm]
  .yourdomain.com = YOURDOMAIN.COM
  yourdomain.com = YOURDOMAIN.COM

[libdefaults]
  default_realm = APM-DOMAIN.COM
  dns_lookup_realm = false
  dns_lookup_kdc = true
  ticket_lifetime = 24h
  forwardable = yes
  udp_preference_limit = 1

[realms]
  SQL-DOMAIN.COM = {
    kdc = sql-dc.sql-domain.com
    admin_server = sql-dc.sql-domain.com
  }

  APM-DOMAIN.COM = {
    kdc = apm-dc.apm-domain.com
    admin_server = apm-dc.apm-domain.com
  }

[domain_realm]
  .sql-domain.com = SQL-DOMAIN.COM
  .apm-domain.com = APM-DOMAIN.COM

[capaths]
  APM-DOMAIN.COM = {
    SQL-DOMAIN.COM = .
  }

  SQL-DOMAIN.COM = {
    APM-DOMAIN.COM = .
  }

❺  Activate Kerberos for the Back-End Database

5.1 Stop Applications Manager

cd <APM_HOME>

shutdownApplicationsManager.bat
shutdownApplicationsManager.bat -force

cd <APM_HOME>

sh shutdownApplicationsManager.sh
sh shutdownApplicationsManager.sh -force

5.2 Backup & Replace Configuration

Navigate to:

<APM_HOME>\working\conf\

Backup Existing File

Rename: database_params.conf

To: database_params.conf.bak

Copy Kerberos Template

Copy:

<APM_HOME>\working\conf\MSSQL\KerberosAuth\MicrosoftJDBC\database_params.conf

Paste into:

<APM_HOME>\working\conf\

5.3 Update Connection Details

Edit the new database_params.conf and update:

5.4 Start & Verify

Start Applications Manager.

Once the web console is accessible, log in to Applications Manager and navigate to:

Settings → Tools → Support → Database Summary

Now execute the following SQL query:

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;

You can also use the following script located under: APPMANAGER_HOME/bin/troubleshooting/

MSSQLDebug.bat

to verify Kerberos connectivity.

Reverting the Configuration (If Needed)

If Applications Manager fails to start or connect:

1. Delete the modified database_params.conf
2. Rename database_params.conf.bak back to database_params.conf
3. Restart Applications Manager

This restores standard password-based authentication.

❻  Add MS SQL Monitors Using Kerberos Authentication

6.1 Add the Monitor

Navigate to Add Monitor → Choose Monitor Type as MS SQL

Configuration

If using "Keytab authentication" or "Ticket cache authentication", you may leave the password field blank.

Click Test Credential and then Add Monitor

6.2 Verification

Wait for the first data collection cycle.

Successful Authentication

Monitor status becomes:

Expected Log Entries in stdout.txt

MSSQL::CheckAuthentication()::Connection successfully established via Kerberos

MSSQL[MSSQL_Monitor_ResourceID]::getCollectedData()::Connection checking query executed in MSSQL after N ms.