How to Connect DBeaver to UKG Pro WFM via a JDBC Driver
The CData API Driver for JDBC implements JDBC standards that enable third-party tools to interoperate, from wizards in IDEs to business intelligence tools. This article shows how to connect to UKG Pro WFM data with wizards in DBeaver and browse data in the DBeaver GUI.
Create a JDBC Data Source for UKG Pro WFM Data
Follow the steps below to load the driver JAR in DBeaver.
- Open the DBeaver application and, in the "Database" menu, select the "Driver Manager" option. Click "New" to open the "Create new driver" form.
- In the Settings tab:
- Set Driver Name to a user-friendly name for the driver (e.g. CData API Driver for JDBC).
- Set Class Name to the class name for the JDBC driver: cdata.jdbc.api.APIDriver.
- Set URL Template to jdbc:api:.
- In the Libraries tab, click "Add File," navigate to the "lib" folder in the installation directory (C:\Program Files\CData\CData API Driver for JDBC XXXX\) and select the JAR file (cdata.jdbc.API.jar).
Create a Connection to UKG Pro WFM Data
Follow the steps below to add credentials and other required connection properties.
- In the "Database" menu, click "New Database Connection."
- In the "Connect to a database" wizard that results, select the driver you just created (e.g. CData API Driver for JDBC) and click "Next >."
- On the Main tab of the configuration wizard, set the JDBC URL, using the required connection properties:
Start by setting the Profile connection property to the location of the UKGProWFM Profile on disk (e.g. C:\profiles\UKGProWFM.apip). Next, set the ProfileSettings connection property to the connection string for UKGProWFM (see below).
UKGProWFM API Profile Settings
UKG Pro Workforce Management uses OAuth 2.0 with the Resource Owner Password Credentials grant (grant_type=password) to authorize access to the API. Unlike most OAuth-based profiles, this does not use a browser redirect/authorization-code step: the driver exchanges your UKG Pro WFM username, password, client ID, and client secret directly for an access token.
Set the following connection properties to authenticate:
- AuthScheme: Set this to OAuthPassword.
- Host: Set this to the hostname of your UKG Pro Workforce Management datacenter/tenant (for example, yourcompany.mykronos.com). Do not include the protocol (https://) or a trailing slash.
- OAuthClientId: Set this to the client ID issued for your UKG Pro Workforce Management OAuth application.
- OAuthClientSecret: Set this to the client secret issued for your UKG Pro Workforce Management OAuth application.
- User: Set this to your UKG Pro Workforce Management username.
- Password: Set this to your UKG Pro Workforce Management password.
Access tokens are obtained from https://{Host}/api/authentication/access_token. Refresh tokens issued by UKG Pro Workforce Management expire after 7 days; if a refresh attempt fails because the refresh token itself has expired, the driver must re-authenticate with your User/Password credentials to obtain a new access token.
Built-in Connection String Designer
For assistance in constructing the JDBC URL, use the connection string designer built into the UKG Pro WFM JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.
java -jar cdata.jdbc.api.jarFill in the connection properties and copy the connection string to the clipboard.
Below is a typical connection string:
jdbc:api:Profile=C:\profiles\UKGProWFM.apip;AuthScheme=OAuthPassword;ProfileSettings='Host=yourcompany.mykronos.com;User=your_username;Password=your_password;';OAuthClientId=your_client_id;OAuthClientSecret=your_client_secret;
- Click "Test Connection ..." to ensure you have configured the connection properly.
- Click "Finish."
Query UKG Pro WFM Data
You can now query information from the tables exposed by the connection: Right-click a Table and then click View Table. The data is available on the Data tab.
