Enabling Kerberos Authentication for SQL Server in Applications Manager

Enabling Kerberos Authentication for SQL Server in Applications Manager

I. How to implement Kerberos Authentication for Applications Manager's MS SQL Back-End Database?

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

            If Windows installation,

            shutdownApplicationsManager.bat
            shutdownApplicationsManager.bat -force

            If Linux installation,

            shutdownApplicationsManager.bat
            shutdownApplicationsManager.bat -force

       2.Go to <AppManager_HOME>\working\conf and take a backup of database_params.conf file.

       3.Go to <AppManager_HOME>\working\conf\MSSQL\KerberosAuth\MicrosoftJDBC and copy database_params.conf to <APM_HOME>\working\conf

       4.Open database_params.conf from <APM_HOME>\working\conf and update the Kerberos protocol enabled SQL server credentials such as host name (FQDN),Port, instance name in JDBC url.

       5.To use keyTab file for kerberos authentication

            i) Create keytab file in SQL Server installed machine using the steps mentioned in the bottom of the page.

            ii) Copy the created keyTab file to Applications Manager machine (Preferably to the location : (AppManager_Home/working/conf/KerberosConfigurations/MSSQL/)

            iii) Open <APM_HOME>\working\conf\KerberosConfigurations\MSSQL\MicrosoftJDBCDriver\login.conf file
                 
                  a. Update keytab file location and SQL Server's principal name(SPN)
                  b. login.conf file entries should be as below for authenticating via keyTab file

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 ticketCache for kerberos authentication

            i) Run kinit program for the user account to which kerberos is enabled using the steps mentioned in the bottom of the page. By default ticket cache file will be created in the location {user.home}{file.separator}krb5cc_{user.name}

            ii) Copy the created ticket cache file to Applications Manager machine (Preferably to the location : AppManager_Home/working/conf/KerberosConfigurations/MSSQL/)

            iii) Open <APM_HOME>\working\conf\KerberosConfigurations\MSSQL\MicrosoftJDBCDriver\login.conf file
                  
                  a. Update ticket cache file location and SQL Server's principal name(SPN)
                  b. login.conf file entries should be as below for authenticating via ticketCache

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 user name and password in database_params.conf file.  

            a) Refer this link to update the user name & password 

             b) login.conf file entries should be as below 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 <APM_HOME>\working\jre\lib\security\java.security file 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:<APM_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



       9. Open <APM_HOME>\working\conf\KerberosConfigurations\krb5.ini file and update the domain details. Refer the attached the sample files.

       10. Now start Applications Manager and check kerberos authentication using below query in the respective SQL server. (auth_scheme should be returned as "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 kerberos connection.
​
To revert back from kerberos authentication replace the database_params.conf  file with the backup file in <AppManager_Home>\working\conf folder and restart Applications Manager


II. How to implement Kerberos Authentication for MS SQL Monitors?

   
       1. Follow the steps 5,6,7,8,9 mentioned above. Because monitors refers the same configuration files for kerberos connection. Restart required if you change anything on java.security file (Step 7)

       2. For mssql monitors to use kerberos authentication we have provided a new option 'Enable Kerberos Authentication' in add monitor page. 

       3. Host name should be entered as Fully Qualified Domain Name (for example : mssql-kerberos-dc1.mssqlkerberos.com)

       4. If you configured the monitors to use ticketCache or keyTab then password can be passed empty for this case. 

       5. Add the monitor and check for the below print in stdout file

              MSSQL DataCollection : KERBEROS Authentication => Going to connect with MSSQL using microsoft JDBC driver (KERBEROS) for resource id ...



How 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






How to create a ticket to use Ticket Cache for Kerberos Authentication?



ticket