9.5.4 Configuring SQL Server database connectivity

Configuring the SQL Server data source requires you to install the SQL Server database drivers, create a SQL Server JDBC provider on WebSphere, create the data source on WebSphere, and then configure the corresponding connection pool.

Install the SQL Server database driver

If you have not done so already, copy the SQL Server JDBC Driver from [LiveCycle root]\lib\db\mssql to the [appserver root]\idplib\ directory.

Note: Make a note of the directory location where you install the driver on your system.

Create the SQL Server JDBC provider

  1. In the WebSphere Administrative Console navigation tree, click Environment > WebSphere Variables and, in the right pane, click one of the following:

    • MICROSOFT_JDBC_DRIVER_PATH

  2. Under General Properties, in the Value box, type the path to the sqljdbc4.jar file that you created in the Install the SQL Server database driver section, and then click OK.

  3. In the Messages box, click Save directly to master configuration.

  4. In the navigation tree, click Resources > JDBC > JDBC Providers.

  5. In the Scope drop-down list in the right pane, select Node=NodeName, Server=ServerNameas the level, and then click New.

  6. In the Create new JDBC provider pane, set the following configurations and then click Next:

    • In the Database type list, select SQL Server.

    • In the Provider Type list, select Microsoft SQL Server JDBC Driver.

    • In the Implementation type list, select Connection Pool Data Source.

    • In the Name box, type Microsoft SQL Server JDBC Driver, or accept the default value.

  7. In the Enter database class path information pane, replace the existing entry with the following, and then click Next:

    • ${MICROSOFT_JDBC_DRIVER_PATH}/sqljdbc4.jar

    Note: For WebSphere, if you have set the WebSphere variable MICROSOFT_JDBC_DRIVER_PATH, the database class path information is populated automatically.
  8. In the Summary pane, click Finish and then click Save directly to master configuration.

Create the SQL Server data source for LiveCycle

Follow the steps below to create the SQL Server data source for your application server version.

  1. In the navigation tree, click Resources > JDBC > JDBC Providers and, in the right pane, click the provider that you created in the Create the SQL Server JDBC provider section.

  2. Under Additional Properties, click Data sources and then click New.

  3. In the Enter basic data source information pane, set the following configurations and then click Next:

    • In the Data source name box, type Livecycle - SQLServer - IDP_DS.

    • In the JNDI name box, type IDP_DS.

  4. In the Enter database specific properties for the data source pane, enter the database name, server name, and port.

  5. In the Setup security aliases pane, set the following, and click Next.

  6. In the Summary pane, click Finish, and then click Save directly to the master configuration.

  7. Set the data store helper class for the data source. Do the following tasks:

    • In the navigation tree, click Resources > JDBC > Data sources and, in the right pane, click the data source that you created.

    • In the next screen, under Data store helper class name, select Specify a user-defined data store helper, and replace the existing entry with the following text:

      com.ibm.websphere.rsadapter.GenericDataStoreHelper

  8. Change the statement cache size. Do the following tasks:

    • In WebSphere Administrative Console, click JDBC > Data sources.

    • Click the data source you just created and under Additional Properties, click WebSphere Application Server data source properties.

    • Change the value of the Statement cache size field to 80.

    • Click OK or Apply and the click Save directly to the master configuration.

Configure LiveCycle - SQLServer - IDP_DS connection pools

  1. In the navigation tree, click Resources > JDBC > JDBC Providers and, in the right pane, click the provider that you created earlier for WebSphere.

    • Microsoft SQL Server JDBC Driver.

  2. Under Additional Properties, click Data sources and then select Livecycle - SQLServer - IDP_DS.

  3. On the next screen, under Additional Properties, click Connection Pool Properties and, in the Maximum connections box, type 30.

  4. 9. Click OK or Apply and then click Save directly to master configuration.

Configure the custom property for SQL Server

  1. In the navigation tree, click Resources > JDBC > Data sources and, in the right pane, click the data source that you created in the Create the SQL Server data source for LiveCycle section.

  2. Under Additional Properties, click Custom properties and then click New.

  3. In the Name box, type useRRASetEquals and in the Value box, type true.

  4. Click OK or Apply and then click Save directly to master configuration.

Create SQL Server data source for Rights Management

Follow the steps below to create the SQL Server data source for your application server version.

  1. In the navigation tree, click Resources > JDBC > JDBC Providers and, in the right pane, click the provider that you created in the Create the SQL Server JDBC provider section.

  2. Under Additional Properties, click Data sources and then click New.

  3. In the Enter basic data source information pane, set the following configurations and then click Next:

    • In the Data source name box, typeLivecycle - SQLServer - RM_DS.

    • In the JNDI name box, type EDC_DS.

  4. In the Enter database specific properties for the data source pane, in the Data store helper class name box, replace the existing entry with the following:

    com.ibm.websphere.rsadapter.GenericDataStoreHelper

  5. In the Setup security aliases pane, set the following, and click Next.

  6. In the Summary pane, click Finish, and then click Save directly to the master configuration.

  7. Change the statement cache size. Do the following tasks:

    • In WebSphere Administrative Console, click JDBC > Data sources.

    • Click the data source you just created and under Additional Properties, click WebSphere Application Server data source properties.

    • Change the value of the Statement cache size field to 80.

    • Click OK or Apply and the click Save directly to the master configuration.

Configure LiveCycle - SQLServer - RM_DS connection pools

  1. In the navigation tree, click Resources > JDBC > JDBC Providers and, in the right pane, click the provider that you created earlier for WebSphere.

    • SQL Server Provider.

  2. Under Additional Properties, click Data sources and then select Livecycle - SQLServer - RM_DS.

  3. On the next screen, under Additional Properties, click Connection Pool Properties and, in the Maximum connections box, type 20.

  4. Click OK or Apply and then click Save directly to master configuration.

Configure the custom property for SQL Server

  1. In the navigation tree, click Resources > JDBC > Data sources and, in the right pane, click the data source that you created in the Create SQL Server data source for Rights Management section.

  2. Under Additional Properties, click Custom properties and then click New.

  3. In the Name box, type useRRASetEquals and in the Value box, type true.

  4. Click OK or Apply and then click Save directly to master configuration.

Map the Windows login to the LiveCycle database user

Note: You must already have a user account created and associated with your Windows domain.
Note: When you run Configuration Manager, database validation will fail if you use Windows authentication as the authentication mode for your database. You can safely ignore this error during the configuration steps.
  1. Using Microsoft SQL Server Management Studio, connect to the database server that hosts the LiveCycle database.

  2. Set the Authentication mode to Windows authentication.

  3. Under Security > Logins, create a new account for the Windows domain user and select Windows authentication.

  4. Click User Mapping on the Login - New screen and set the database and default schema for the new user.

  5. Select db_owner as the Database role and click OK.

To verify that you created the user, expand the LiveCycle database in the navigation tree and open Security > Users. The new user is listed there.

Configure integrated security on Windows to make a trusted connection with SQL Server

  1. Start the application server by using the Windows domain user. If WebSphere Application Server is running as a service, it should be started by using the Windows domain user account.

  2. Start the WebSphere Administrative Console by typing http://[host]:[port]/IBM/console in the URL line of a web browser.

  3. In the navigation tree, click Resources > JDBC > Data Sources and, in the right pane, click IDP_DS.

  4. In the right pane, under Additional Properties, click Custom Properties, and on the next screen, click integratedSecurity.

  5. On the next screen, under General Properties, type true in the Value box.

  6. Click OK or Apply and then click Save directly to the master configuration.

  7. In the navigation tree, click Resources > JDBC > Data Sources and, in the right pane, click RM_DS.

  8. In the right pane, under Additional Properties, click Custom Properties, and on the next screen, click integratedSecurity.

  9. On the next screen, under General Properties, type true in the Value box.

  10. Click OK or Apply and then click Save directly to the master configuration.

  11. Open the Services control panel and stop the IBM WebSphere Application Server <version> - <node> service.

  12. Right-click the service name and click Properties > Log On.

  13. Change the default Log On property from Local System to the Windows domain user account that you configured in the Map the Windows login to the LiveCycle database user section and then restart the service.

  14. On the computer where the application server is installed, add sqljdbc_auth.dll to the Windows systems path (C:\Windows).

    Note: The sqljdbc_auth.dll file is in the same location as the Microsoft SQL JDBC 3.0 driver installation (default is [InstallDir]/sqljdbc_3.0/enu/auth/x86).

// Ethnio survey code removed