Task 14: Configure database connection in the IdM service

Now, you need to configure database connection according to the IdM database that you created previously. The example database name IDM-SM is used in the following steps.

To do this, follow these steps:

1. Open the <idm-service>/WEB-INF/spring/applicationContext.properties file with a text editor.

2. Uncomment your database section, and comment out the other database sections.

   By default, the PostgreSQL database type is used and other database types are commented out.

3. Configure the database connection.

   SQL Server

   IdM needs a SQL Server JDBC driver to connect to the database. To configure the database connection using a SQL Server JDBC driver, follow these steps:

   1. Download Microsoft JDBC Driver 6.0 for SQL Server (or a higher version) from the Microsoft website.

   2. Extract the downloaded file, and then copy the sqljdbc42.jar file to the <idm-service>\WEB-INF\lib directory.

      Tip If the sqljdbc42.jar file cannot be found, copy a higher version of the sqljdbcxx.jar file.

   3. Set the idm.persistence.hbm2ddl.auto parameter to false.

      idm.persistence.hbm2ddl.auto = false

   4. Update the SQL Server database connection parameters as described in the following table.

      Parameter	Description
      idm.persistence.flyway.type	Set it to sqlserver.
      idm.persistence.dialect	Change it to sqlserver.db.specific.IdmSQLServer2012Dialect.
      idm.persistence.connection.driver.class	Change it to com.microsoft.sqlserver.jdbc.SQLServerDriver.
      idm.persistence.connection.url	Enter a value with this format: jdbc:sqlserver://<IdM database server host>:<port>;DatabaseName=<IdM database name> For example: jdbc:sqlserver://localhost:1433;DatabaseName=IDM-SM
      idm.persistence.connection.username idm.persistence.connection.password	Enter the login credentials that you specified for the IdM database.

   Oracle

   Perform the following steps:

   1. From the Oracle website, download the Oracle JDBC driver appropriate for your database.
   2. Copy the driver to the <idm service>\WEB-INF\lib folder.

   3. Update the Oracle database connection parameters.

      Parameter	Description
      idm.persistence.flyway.type	Set it to oracle.
      idm.persistence.dialect	Set it to org.hibernate.dialect.Oracle10gDialect.
      idm.persistence.connection.driver.class	Set it to oracle.jdbc.OracleDriver.
      idm.persistence.connection.url	Enter a value with this format: jdbc:oracle:thin:@<IdM database server host>:<port>:<IdM database name> For example: jdbc:oracle:thin:@localhost:1521:IDM-SM
      idm.persistence.connection.username idm.persistence.connection.password	Enter the login credentials that you specified for the IdM database.

   4. Add a key to the applicationContext.xml file.
      1. Open the <idm service>\WEB-INF\spring\applicationContext.xml file in a text editor.

      2. Add the hibernate.default_schema key to the following property, as shown below:

         <property name="hibernateProperties" >
             <props>
                 <prop key="hibernate.jdbc.batch_size">${idm.persistence.jdbc.batch_size}</prop>
                 <prop key="hibernate.dialect">${idm.persistence.dialect}</prop>
                 <prop key="hibernate.cache.provider_class">${idm.persistence.cache.provider_class}</prop>
                 <prop key="hibernate.show_sql">${idm.persistence.show_sql}</prop>
                 <prop key="hibernate.hbm2ddl.auto">${idm.persistence.hbm2ddl.auto}</prop>
                 <prop key="hibernate.connection.pool_size">${idm.persistence.connection.pool_size}</prop>
                 <prop key="hibernate.enable_lazy_load_no_trans">true</prop>
         	<prop key="hibernate.default_schema"><db username></prop>
             </props>
         </property>

         Where: <db username> should be replaced with the login account user name of the IdM database. For example:

         <prop key="hibernate.default_schema">IDM-SM</prop>

   PostgreSQL

   Update the following PostgreSQL database connection parameters.

   Parameter	Description
   idm.persistence.flyway.type	Set it to postgresql.
   idm.persistence.dialect	Set it to org.hibernate.dialect.PostgreSQL9Dialect.
   idm.persistence.connection.driver.class	Set it to org.postgresql.Driver.
   idm.persistence.connection.url	Specify a value that uses the following format: jdbc:postgresql://<PostgreSQL database server host>:<port>/<IdM database name> Tip The default port of PostgreSQL is 5432. Example value: jdbc:postgresql://localhost:5432/IDM-SM
   idm.persistence.connection.username idm.persistence.connection.password	Enter the login credentials that you created for the PostgreSQL database.

4. Check the log for errors.
   1. Open the <idm-service>\WEB-INF\classes\log4j.properties file in a text editor.

   2. Locate the following line:

      log4j.rootLogger=INFO, consoleAppender, jettyAppender

   3. Change jettyAppender to tomcatAppender, as shown below:

      log4j.rootLogger=INFO, consoleAppender, tomcatAppender

      Note This will make IdM logging information be written to the following log file defined in the log4j.properties file:

      log4j.appender.tomcatAppender.File=${catalina.home}/logs/hpcloud-idm-service.log

   4. Start the Tomcat instance on which the IdM service is deployed.
   5. Open the hpcloud-idm-service.log file in a text editor to check for errors, and fix them if any.