PingFederate Server

Configuring a JDBC connection

You can establish a Java Database Connectivity (JDBC) connection to your database server.

About this task

include:::partial$pf_rc_notes.adoc[tags=pf_ph_testedJdbcDriver]To obtain the database driver .jar file, contact your database vendor. Install the database driver file to the <pf_install>/pingfederate/server/default/lib directory, and then restart the server.

Steps

  1. Go to System → Data & Credential Stores → Data Stores.

  2. On the Data Stores window, click Add New Data Store.

  3. On the Data Store Type tab, type a name for the datastore.

  4. From the Type list, select Database (JDBC). Click Next.

  5. Optional: To mask attribute values returned from this datastore in PingFederate logs, select the Mask Values in Log check box.

  6. Click Next.

  7. In the Database Config window, configure your JDBC connection. Information about each field is provided in the following table.

    Field Description

    JDBC URL

    The location of the database server and the database. The structure of the JDBC URL varies depending on the vendor. You can add multiple JDBC URLs. You can also specify which node is the default by clicking Set as Default under Action.

    For regional deployments, specify region-specific URLs on different rows, along with the node tags for the region. Tag the nodes that will use the URL with the node.tags property in the run.properties file. Failover may be supported within a single region via driver-specific URL parameters. Failover is not supported across different rows.

    For Oracle MySQL, to enable automatic reconnection attempts when the connection is not available at runtime, enter a SQL statement in the Validate Connection SQL field and add the following query string to the JDBC URL:

    ?autoReconnect=true

    Tags

    Tags are defined in the node.tags property in the <pf_install>/pingfederate/bin/run.properties file. For a description of the node.tags property, see Deploying cluster servers.

    In PingFederate deployments that are regional, you can enter one or more tags for a JDBC URL, which specifies with which datastore that particular PingFederate node should communicate. If none of the tags match what is defined for the node.tags property, the default node is used.

    The following rules apply to tags:

    • You must separate multiple tags specified for one node with spaces.

    • You can’t use a tag more than once per datastore.

    • Tags are optional. If needed, you can configure a non-default node without tags. Doing this is useful if you are not yet ready to tag the node, or if you are still in the planning stage but want to enter the address for the node now.

    Driver Class

    The name of the driver class used to communicate with the source database. The driver class name should be supplied by the database software vendor in a JAR file.

    Username

    The name that identifies the user when connecting to the database.

    Password

    The password needed to access the database.

    Validate Connection SQL

    (Optional but recommended)

    A simple SQL statement used by the PingFederate runtime server to verify that the database connection is still active and to reconnect if needed.

    If a SQL statement is not provided here, PingFederate might not reconnect to the database if the connection is broken.

    Ensure that the SQL statement is valid for your database. For example:

    • SELECT 1 from dual (for Oracle Database or Oracle MySQL)

    • SELECT getdate() (for Microsoft SQL Server)

    • SELECT 1 (for PostgreSQL)

    To use this feature for Oracle MySQL, you must also add the ?autoReconnect=true query parameter to the JDBC URL.

    Mask Values in Log

    Determines whether all attribute values returned through this datastore should be masked in PingFederate logs.

    Applicable only when editing an existing datastore.

    Allow Multi-Value Attributes

    When selected, indicates that the JDBC datastore can select more than one record from a column and return the results as a multivalued attribute. Otherwise, a query returns only the first value in the column.

  8. Click Test Connection to determine whether the administrative node can communicate with the specified datastore.

    Datastore validation is no longer enabled during configuration. This feature lets you configure datastores without requiring a successful connection between the administrative node and the datastore. You can also save the datastore even if the connection is not currently successful.

  9. Click Advanced to configure additional settings.

    1. On the Advanced Database Options window, click Apply Defaults to view or restore default values.

      The default values are conservative based on the server thread pool settings configured in the <pf_install>/pingfederate/etc/jetty-runtime.xml file. If any changes are made to thread pooling, we recommend updating settings as outlined in the next step.

    2. Configure advanced settings.

      For more information about each field, see the following table.

      Field Description

      Minimum Pool Size

      The smallest number of database connections that can remain in the pool for the given datastore. A minimum value of 0 means that the minimum number of connections in the pool is zero.

      For optimal performance, the value for this setting should equal 50% of the maxThreads value in the Jetty server configuration (see Configuring connection pools to datastores).

      Note that PingFederate does not establish the connection pool for the given datastore until it receives a request that requires one or more attributes from that datastore.

      The default value (after clicking on Apply Defaults)is 10.

      Maximum Pool Size

      The largest number of database connections that can remain in the pool for the given datastore.

      For optimal performance, the value for this setting should equal 75% to 100% of the maxThreads value in the Jetty server configuration (see Configuring connection pools to datastores).

      The default value (after clicking on Apply Defaults)is 100.

      Blocking Timeout (ms)

      The amount of time a request waits to get a connection from the connection pool before it fails. A value of -1 means that a request waits indefinitely for the connection pool to return a connection.

      The default value (after clicking on Apply Defaults) is 5000.

      Idle Timeout (min)

      The length of time the connections can sit idle in the pool before it closes them. A value of -1 means that the connection pool does not close its connections (once established).

      Note that PingFederate maintains the minimum connection pool for the given datastore once the pool is established.

      The default value (after clicking on Apply Defaults)is 5.

  10. Click Save to save your configuration.