The importance of dialect settings in iiq.properties

[#1]

This article discusses configuration options of the iiq.properties configuration file that are often mis-configured or omitted and can cause issues later on when more advanced features of IdentityIQ are exercised. 

The .../WEB-INF/classes/iiq.properties file contains several configuration items for IdentityIQ.  The iiq.properties that ships with the IdentityIQ .war file contains instructions in the comment blocks out at the top of the file for how to correctly edit the file for your specific installation.  The template data in the file and the instructions are updated with every release of IdentityIQ and should be reviewed by deployment professionals when upgrading across major versions of IdentityIQ (e.g. 5.5 to 6.3).  Many times readers gloss over these instructions and simply re-cycle their existing iiq.properties and/or omit some subtle but critical features of the file when editing it.  One of these critical configuration items is the pointer to where IdentityIQ can connect to its back-end supporting database.  Care must be taken when configuring these options so that it is done correctly. 

IdentityIQ can be configured to connect to its supporting database in one of five ways: A connection via JNDI managed by the servlet container surrounding IdentityIQ or via a JDBC direct connection to one of the four supported database technologies (DB2, MS SQL Server, MySQL or Oracle).  Each of the database technologies has its own example block in the iiq.properties configuration file.  Usually other blocks are commented out and the block for the database technology used in a particular install is un-commented and modified with the site specific parameters in the URL like database host, schema name and port number to connect to the database server on.  When configuring these items it is critical to also make certain that the database dialect lines for the supporting database are correctly un-commented.

IdentityIQ requires the custom hibernate dialect be specified for each supported database driver. Having the dialect absent from the "iiq.properties" causes issues that are not always immediately apparent.  IdentityIQ will start and appear to run correctly without the required hibernate dialect configuration entries. Not including or not un-commenting the example entry for the appropriate dialect causes issues with partitioning and multi-threaded Identity Refresh operations that will not be seen when using the single-threaded equivalents.  In fact, omitting the dialect configuration lines causes issues with database level object locking in general.

Please review your iiq.properties files for your installation to make certain that they contain the appropriate dialect line for the database type you are using.  For installations upgrading to 6.2 or 6.3 or newer releases this is critically important.  IdentityIQ will start and appear to work fine correctly on initial inspection even with this mistake but it will not be able to acquire exclusive locks to objects at the relational database level without the appropriate dialect line present in the file.  This will be observed through symptoms like corrupt Task Result objects that are missing "host" attributes on certain partition status reports and an inability to get exclusive locks when calling ObjectUtil.lockObject in BeanShell code.

Examples provided from a kits like the Services Standard Build (SSB) or Services Standard Framework (SSF) can not be trusted to be correct.  Please compare the iiq.properites from an un-zipped copy of IdentityIQ to your local copy to make sure that all of the mandatory fields are correctly uncommented.  Newer versions of these tools do not ship with pre-populated iiq.properties files to force deployment professionals to get the newest and latest template from their IdentityIQ .war files. 

Some common mistakes have been seen in several IdentityIQ installations:

• When using JNDI managed database connections installations often forget to also un-comment the dialect line for the database technology that the JNDI connection points.  Regardless of whether IdentityIQ is using JNDI or direct JDBC connections it is required to have the appropriate dialog option configured.
• When using MS SQL Server installations forget to also un-comment the quartz properties as shown in the examples below.
• Often the dialect lines are left out entirely or commented out for Oracle and SQL server installations.

For MySQL installations the following line must be un-commented:

sessionFactory.hibernateProperties.hibernate.dialect=sailpoint.persistence.MySQL5InnoDBDialect

Note: older versions of IdentityIQ shipped with a different and incompatible dialect setting for MySQL that looks very similar to what is required for releases 6.2 and newer.  If your installation has the example shown below then please replace it with the example shown above; the "org.hibernate.dialect.MySQL5InnoDBDialect" shown below will not work correctly for IdentityIQ 6.2 and newer:

sessionFactory.hibernateProperties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect

For MS SQL Server installations using either Microsoft's driver or the jTDS driver, the following lines must be un-commented in iiq.properties:

sessionFactory.hibernateProperties.hibernate.dialect=sailpoint.persistence.SQLServerUnicodeDialect

scheduler.quartzProperties.org.quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.MSSQLDelegate

scheduler.quartzProperties.org.quartz.jobStore.selectWithLockSQL=SELECT * FROM {0}LOCKS UPDLOCK WHERE LOCK_NAME = ?

For Oracle Installations the following line must be uncommented in iiq.properties:

sessionFactory.hibernateProperties.hibernate.dialect=sailpoint.persistence.Oracle10gDialect

For DB2 installations the following line must be un-commented in iiq.properties:

sessionFactory.hibernateProperties.hibernate.dialect=sailpoint.persistence.DB2Dialect

The following example shows a correctly configured Oracle via JNDI example (omitting other settings like BSFManager settings for brevity):

jndiDataSource.jndiName=java:comp/env/jdbc/jmiiq

configuredDataSource.targetBeanName=jndiDataSource

dataSource.maxWait=10000

dataSource.maxActive=250

dataSource.minIdle=5

sessionFactory.hibernateProperties.hibernate.dialect=sailpoint.persistence.Oracle10gDialect

The following example shows a correctly configured MySQL with JDBC direct example (again, omitting other settings for brevity):

configuredDataSource.targetBeanName=dataSource

dataSource.maxWait=10000

dataSource.maxActive=250

dataSource.minIdle=5

dataSource.username=identityiq

dataSource.password=identityiq

dataSource.url=jdbc:mysql://localhost/iiq63lab?useServerPrepStmts=true&tinyInt1isBit=true&useUnicode=true&characterEncoding=utf8

dataSource.driverClassName=com.mysql.jdbc.Driver

sessionFactory.hibernateProperties.hibernate.dialect=sailpoint.persistence.MySQL5InnoDBDialect

Ensuring that your installation has the correct sailpoint.persistence.* dialect setting will help ensure your installation has a problem free upgrade and smooth transition to partitioned and multi-threaded features included in recent releases of IdentityIQ.

[wes_roberts]

Great article, thanks!

[alex_king]

I am implementing an instance of IdentityIQ with a MS SQL Server database and I am noticing that that in the above post the dialect used is sailpoint.persistence.SQLServerUnicodeDialect whereas the base install of the version 7.3 which we are using sailpoint.persistence.SQLServerPagingDialect.

Is anyone aware of the specific difference -- is there any advantage to using one over the other? My presumption is that the correct one to use would be whichever is provided for my database type out of the box, but my client would like to be sure (and preferably to gain some understanding or justification of the difference).