Using Kerberos Authentication with SQL Server

You can use Kerberos Authentication to establish the connection between the Automic Automation Intelligence server and an SQL Server database. This section describes the configuration necessary for this type of authentication.

This page includes the following:

Supported Platforms

The following platforms are supported for connecting an Automic Automation Intelligence and SQL Server database with Kerberos authentication.

  • Windows
  • Linux

Tested Platforms

  • Windows 10 Enterprise 64-bit with Java 8
  • Ubuntu 16.04 with Java 8 and PowerBroker Identity Services Open (PBIS) 8.5.6.375

Prerequisites

Make sure you meet the following prerequisites:

  • The Automic Automation Intelligence and the SQL Server computers must be part of the same Active Directory domain
  • The Microsoft SQL Server must accept a Kerberos log in
  • An Active Directory user for Automic Automation Intelligence must have permissions on the SQL Server database
  • A Kerberos keytab file for the Automic Automation Intelligence user

Note:

To prevent misuse, change the keytab file permissions.

Installation Verification

Notes:

  • New Automic Automation Intelligence Installations should already include all required changes but can be confirmed through the following steps.

  • If you are upgrading from Automic Automation Intelligence version 5.8.4 or earlier and want to switch to the Kerberos Authentication for your Automic Automation Intelligence database, follow the steps below.

  • The Automic Automation Intelligence database schema should be available and current.

View the content of thejaws.properties file under Automic_Automation_Intelligence/jboss/standalone/configuration on the Automic Automation Intelligence server to ensure that it contains the following:

  1. Look for all lines containing dbms.mssql and make sure that only the following lines remain NOT commented out (no '#' at beginning). Make sure that there are no blank spaces left at the beginning of the line.

    dbms.mssql.name=SQL Server 
dbms.mssql.url=jdbc:jtds:sqlserver://

  2. Any other lines in the file that begin with dbms.mssql should have a "#" inserted as the first character of the line. The following lines may exist in older Automic Automation Intelligence releases and will need to be commented out ('#').

    #dbms.mssql.driver=net.sourceforge.jtds.jdbc.Driver 
#dbms.mssql.url=jdbc:sqlserver:// 
#dbms.mssql.extra=instance=XXX

Note:

Thejaws.properties file contains important information. Do not change anything else without consulting Broadcom technical support.

Windows Specific Installation

In order for the Kerberos Authentication to SQL Server (jTDS Single Sign-On) to work on Windows platforms, an additional native SPPI library (DLL file) must be loaded. This library can be requested from the Broadcom technical support. This additional DLL file must be placed in the Windows\System32 folder on the Windows server machine. The Windows user making the changes must have Administrator permissions.

Configuration

Follow the configuration steps listed here.

  1. Stop the Automic Automation Intelligence service.

  2. Open the Configuration Tool and do the following:

    1. Select the Database tab and then Edit.

      • Check the Kerberos box

      • The Host must include the Realm name. Example: sqlserver.mycompany.com

      • The Port and the DB name must be the same as the Port and DB name used for the SQL authentication

      • The User is the Kerberos principal, for example user@mycompany.com

      • The Password is the path to the Kerberos keytab file. Example: C:\Automic_Automation_Intelligence\YOUR_KEYTAB_FILE or /home/user/YOUR_KEYTAB_FILE

    2. Run the Test Connection

    3. Once the Test Connection is successful, save the changes. Select OK and then Apply.

  3. Start the Automic Automation Intelligence service and confirm that initialization completes and users can log in.

Notes:

  • The response time for the first connection may take minutes depending on the environment. This happens when you start the Configuration Tool or run the Test Connection.

  • The characters of the keytab path are replaced by dots, so input the path carefully.

Troubleshooting

This section lists the most common known issues.

Test connection failed

  • Verify the database connection information.

  • Verify the keytab file.

  • Verify the jaws.properties uses the correct SQL Server URL.

Exception or error after editing database connection using the Configuration Tool and clicking OK

Use the OK button in the Edit Database Connection to test the connection a few times. As long as the final attempt to connect is successful, you can ignore these exceptions.

Automic Automation Intelligence start up exception in server.log

  1. Bring up the Configuration Tool

  2. Select the Params tab

  3. Change the Show drop-down to hidden named parameters

  4. Select the Show hidden param button

  5. Enter featureToggle.subscriptionService in the box.

  6. Select OK

  7. Change the Value column to false

  8. Select Apply

Automic Automation Intelligence start up exception in multiple logs

The error messages are as below:

  • hibernate.log

    WARN                                               [BasicResourcePool]
com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@219b47d4
-- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, 
we failed to succeed more than the maximum number of allowed acquisition attempts (30). 
Last acquisition attempt exception: : java.sql.SQLException: I/O Error: SSO Failed: Native SSPI library not loaded. 
Check the java.library.path system property.

  • server.log

    ERROR [org.jboss.stdio.AbstractLoggingWriter] Exception in thread "Thread-95" org.hibernate.exception.GenericJDBCException: Cannot open connection

  • jaws.log

    WARN [DataManagerModule] Error getting session for AAI DB: com.termalabs.common.JawsException: Error while getting session

    This means the native SPPI library is not found. Please refer to the Windows Specific Installation section.

Automic Automation Intelligence cannot retrieve data

  • Use the Configuration Tool to test the connection.

  • Verify the user has the correct Default schema on the SQL Server.

  • Verify the user does not have sysadmin role on the SQL Server.

The password of the Automic Automation Intelligence database user has expired or changed

  1. Regenerate the keytab file using the current password.

  2. Replace the old keytab file with the new one.

  3. Use the Configuration Tool to test the connection.

  4. Restart the Automic Automation Intelligence server.

Upgrade Failed with the Following Error

Unable to connect: java.sql.SQLException: No suitable driver found for jdbc:sqlserver://SERVER_NAME:PORT;databaseName=DB_NAME

Open the jaws.properties file and make sure the following line is used:

dbms.mssql.url=jdbc:jtds:sqlserver://

See also: