Keycloak with Microsoft SQL Server Database (MSSQL)

Keycloak uses the H2 database out-of-the-box, which is not recommended for production use. The Keycloak documentation uses PostgreSQL as an example. Since my team would need to use it with SQL Server, I did some preliminary research on configuring Keycloak with MSSQL.

Create an Empty Database

The first thing to do is to create an empty database in the SQL Server for Keycloak to use. The example below assumes a database named KeycloakDB has been created.

If you need to use an existing database, then you can set up a new default schema for the database user to separate Keycloak data from the existing data. You can also specify the schema in the configuration.

JDBC Driver

The next thing to do is getting the Microsoft SQL Server JDBC driver. If you're using Maven and have used SQL Server in the past, you might have it already, such as in this folder: %USERPROFILE%\.m2\repository\com\microsoft\sqlserver\mssql-jdbc\9.2.0.jre15\. If not, download it from Microsoft, unzip it and locate the jar. Then copy the jar file, e.g., mssql-jdbc-9.2.0.jre15.jar over to the following location, creating subfolders as needed: [Keycloak Root]\modules\system\layers\keycloak\com\microsoft\sqlserver\main. (Note that I'm using JDK 15, which is the latest at the time of this post, but it is not an LTS release.)

Then create the module.xml file in the same folder:

<?xml version="1.0" ?>
<module xmlns="urn:jboss:module:1.3" name="com.microsoft.sqlserver">

  <resources>
    <resource-root path="mssql-jdbc-9.2.0.jre15.jar"/>
  </resources>

  <dependencies>
    <module name="javax.api"/>
    <module name="javax.transaction.api"/>
  </dependencies>
</module>

There's an alternate JDBC driver for MSSQL, the jTDS driver, which is open source. I wouldn't recommend using it since it hasn't been updated for some time now, and may not support newer versions of MSSQL. The official Microsoft driver has been open source for some time.

Update Configuration

Next, we need to update the Keycloak XML configuration file. Add the <driver> element to [Keycloak Root]\standalone\configuration\standalone.xml (I'm using standalone mode, for domain mode, refer to documentation):

<driver name="sqlserver" module="com.microsoft.sqlserver">
  <xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>
</driver>

Note that the module matches the driver module name and the path from what we've set up earlier, and the name will be used below. (The xa-datasource-class name was found from JBoss documentation)

Then search for KeycloakDS in the file and update it to:

<datasource jndi-name="java:jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true" use-java-context="true" statistics-enabled="${wildfly.datasources.statistics-enabled:${wildfly.statistics-enabled:false}}">
  <connection-url>jdbc:sqlserver://localhost;instanceName=[INSTANCE NAME];databaseName=KeycloakDB;integratedSecurity=true</connection-url>
  <driver>sqlserver</driver>
</datasource>

Note that I'm using an instance name. I'm also using integrated security, which means the SQL Server's authentication DLL, e.g., mssql-jdbc_auth-9.2.0.x64.dll should be placed in the bin folder of the Java runtime. This DLL won't be in the .m2 folder mentioned earlier, so you'll need to extract it from the driver zip file.

If you need to use username and password, you can specify the following elements in the <datasource>:

<user-name>[Username]</user-name>
<password>[Password]</password>

If you do end up specifying username and password, refer to JBoss/Wildfly documentation on securely storing the password.

Now start Keycloak (restart if it was running already). You should see the new tables show up in the Keycloak database. (Keycloak uses JPA Hibernate).

After you create the initial admin user in Keycloak, do a select on the USER_ENTITY table, and you should see a new row for the user:

ExampleDS

If you explore the standalone.xml configuration file further, you may have noticed another database named ExampleDS. As per Wildfly documentation, looks like it can be removed. After removing the <datasource> element for ExampleDS, be sure to remove the datasource attribute from the <default-bindings> element as well. Note that when it's set to another SQL Server database, it didn't create any new objects or data. I suppose you can also point it to the Keycloak database, and as long as no other apps running on the same Wildfly instance are relying on <default-bindings>, it should be okay. There seems to be more information on Redhat Knowledgebase thread, but you need a paid subscription to access it... Another option is just leaving it as H2, but might not be desirable if you want to optimize memory in case of containerization.