Use Cases

Connecting PingFederate to a Microsoft SQL JDBC datastore with Windows authentication

Create a Microsoft SQL server Java Database Connectivity (JDBC)-connected datastore in PingFederate and configure it for Windows authentication.

If your organization primarily uses a Microsoft Windows platform, you can have your PingFederate nodes on Windows servers, and you can use Microsoft SQL Server for your databases. One example use case for this type of datastore is storing OAuth grants in a clustered environment.

High availability requirements for this database should follow your organization’s procedures and are outside the scope of this document. Any database maintenance tasks are also not addressed in this document.

Component

PingFederate 9.3 or later

Before you begin

You must have:

  • An SQL server on the network, accessible from the PingFederate nodes on its assigned port

    Port 1433 is the default port for SQL server. You can test connectivity to the server:port with the telnet command line utility.

  • Access to a database on the server with the correct tables

    Work with the database administrator to determine an appropriate name for your database, such as “PingFederate”.

    For storing OAuth grants, you can find the table creation scripts (access-grant-sqlserver.sql and access-grant-attribute-sqlserver.sql) in <pf_install>/pingfederate/server/default/conf/access-grant/sql-scripts.

  • A user account in the Active Directory (AD) domain you can use as a service account

    It does not need any special domain privileges, but it receives local permissions on your PingFederate nodes.

    Work with your database administrators to ensure the user account in the AD has permissions to access and write to the database.

Adding a new user

About this task

Perform these steps on each PingFederate node.

Steps

  1. On your Windows machine, open the folder where PingFederate is installed.

    Screen capture illustrating the version folder on a Windows machine.

  2. Right-click the folder and select Properties.

  3. Click the Security tab.

  4. To add a user, click Edit and Add.

  5. Add the user account and click OK.

    Screen capture illustrating the window to add a user account. There are three fields: Select this object type, From this location, and Enter the object names to select.

  6. From the user account Permissions section, select the Modify checkbox.

    Screen capture illustrating the Modify checkbox being selected on the permissions window.

  7. To save your changes, click Apply.

Assigning the log on as a service policy to the new user

About this task

Perform these steps on each PingFederate node.

Steps

  1. On your Windows machine, open the Local Security Policy menu. You can search for "local security policy" from the Windows Start menu.

  2. From the Local Security Policy window, go to Local Policies > User Rights Assignment > Log on as a service.

    Screen capture illustrating the Local Security Policy window with the Log on as a service policy highlighted.

  3. In the Log on as a service Properties window, click Add User or Group…​.

    Screen capture illustrating the Log on as a service Properties window.

  4. Add the user information as needed.

  5. Click Apply, and then click OK.

Editing the sign-on tab for the PingFederate service

About this task

Perform these steps on each PingFederate node.

Steps

  1. On your Windows machine, open the Services menu.

  2. Right-click the PingFederate service that is running on your machine and select Properties.

    Screen capture illustrating the Services window showing the right-click menu.

  3. On the Log On tab, click This account.

    Screen capture illustrating the engine properties window open to the Log On tab.

  4. In the This account field, enter the entire UPN name of the account, such as svc.pingfed@<your-domain>.

  5. Enter a password for the account.

  6. To save your changes, click Apply, and then click OK.

  7. To restart the service, right-click the PingFederate service and select Restart.

    Screen capture illustrating the right-click menu for a local engine on the Windows Services menu.

Deploying the required JDBC driver files and DLLs

About this task

Perform these steps on each PingFederate node.

Steps

  1. Go to .microsoft.com/en-us/sql/connect/jdbc/release-notes-for-the-jdbc-driver?view=sql-server-ver15//[Microsoft release notes] and download the correct .zip file for your Java version.

    Package 6.4 is known to work properly.

  2. Extract the files and find the .jar file that corresponds to your Java version.

  3. Place the .jar file in <pf_install>/pingfederate/server/default/lib

  4. In the auth folder of your JDBC driver download folder, find the appropriate folder for your Java virtual machine (JVM) version (x32 or x64-based), and copy the DLL file to your /windows/system32 folder.

  5. Restart the PingFederate service.

Creating the JDBC datastore connection in PingFederate

Steps

  1. From the PingFederate administrative console, go to System > Data Stores and click Add New Data Store.

  2. On the Data Store Type tab, in the Name field, enter a name.

  3. In the Type list, select Database (JDBC). Click Next.

  4. On the Database Config tab, in the JDBC URL field enter jdbc:sqlserver://<databaseservername>;<databaseName=databasename>;integratedSecurity=true.

  5. Click Add.

    Use the fully qualified domain name for your server. Port 1433 is the default port for SQL server. If you are using port 1433, then you can omit it from the JDBC URL. For any non-standard ports, specify them in the URL.

    integratedSecurity=true makes the connection use Windows authentication. Without that, it attempts SQL authentication.

  6. In the Driver Class field, enter com.microsoft.sqlserver.jdbc.SQLServerDriver.

  7. Complete the Username and Password fields with the same service account credentials from step 1.

  8. In the Validate Connection SQL field, enter SELECT getdate().

    SELECT getdate() is used to re-establish the JDBC connection if it gets disconnected.

  9. Click Next, and then click Save.

  10. Replicate the cluster configuration.

Testing the newly created external datastore

Steps

  1. Follow the steps in Configure external databases for grant storage.

  2. Issue a grant in PingFederate.

    If you do not have an OAuth client application readily available, you can use the OAuth Playground authorization code flow to obtain a code and exchange it for an access and refresh token.

  3. Work with the database administrator (if necessary) to view the tables in the database that were created by the script.

    Result:

    You should see an entry for the newly issued grant.