Keycloak MSSQL Database Integration

Keycloak uses H2 as default database. It is an embedded Java based relational database system. But you might want to use another database like MSSQL or MySQL. In this post I will explain how to install Keycloak database on MSSQL .

Requirements :

• MSSQL Server
• Java SDK
• TCP/IP must be enabled for SQL Server

Installation :

• Connect to SQL server via management studio and create a blank database and name it “Keycloak”.
• Navigate to keycloak installation folder (modules\system\layers\keycloak\com) and create a new folder. Rename it “microsoft” and then create a new folder inside the microsoft folder and name it “main”.

• Download Microsoft JDBC Driver for SQL Server inside main folder. Then create an xml file inside the main folder and name it “module.xml”.

• Open module.xml and write this xml code in it.

<?xml version=”1.0" ?>
<module xmlns=”urn:jboss:module:1.5" name=”com.microsoft”>
 <resources>
 <resource-root path=”mssql-jdbc-7.2.2.jre8.jar”/>
 </resources>
 <dependencies>
 <module name=”javax.api”/>
 <module name=”javax.transaction.api”/>
 </dependencies>
</module>

• Navigate to keycloak standalone folder and edit “standalone.xml”. Search for <drivers> tag and add configuration for sql server here.

<drivers>
 <driver name=”h2" module=”com.h2database.h2">
 <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
 </driver>
 <driver name=”sqlserver” module=”com.microsoft”>
 <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
 <xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>
 </driver>
</drivers>

• Find the <datasources> section and paste these code.

<datasources>
 <datasource jndi-name=”java:jboss/datasources/ExampleDS” pool-name=”ExampleDS” enabled=”true” use-java-context=”true” statistics-enabled=”${wildfly.datasources.statistics-enabled:${wildfly.statistics-enabled:false}}”>
 <connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE</connection-url>
 <driver>h2</driver>
 <security>
 <user-name>sa</user-name>
 <password>sa</password>
 </security>
 </datasource>
 <datasource jndi-name=”java:jboss/datasources/KeycloakDS” pool-name=”KeycloakDS” enabled=”true” use-java-context=”true”>
 <connection-url>jdbc:sqlserver://[YOUR SERVER NAME]:1433;DatabaseName=[KEYCOAK DATABASE NAME];</connection-url>
 <driver>sqlserver</driver>
 <security>
 <user-name>sa</user-name>
 <password>123</password>
 </security>
 </datasource>
 <drivers>
 <driver name=”h2" module=”com.h2database.h2">
 <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
 </driver>
 <driver name=”sqlserver” module=”com.microsoft”>
 <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
 <xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>
 </driver>
 </drivers>
</datasources>

• Boot keycloak by running this command on a command prompt window.
• >{KEYCLOAK_SERVER_DIR}\bin\standalone.bat -Djboss.socket.binding.port-offset=100

Thats it. Thank you for reading.