Keycloak Custom User Storage SPI with SQL Server Database

If you have an application with its own database containing users, and you want to integrate with Keycloak, then one approach you can take is creating a custom User Storage SPI (Service Provider Interface) to expose those users to Keycloak.

A Proof-of-Concept demo can be found in my Github repo, and you can try it out using docker-compose.

Here are some of the features it implements:

• Allows Keycloak to access users stored in a custom MS SQL database.
• Allows configuring MS SQL connection from Keycloak's admin user interface. (Note: see section below about password storage.)
• Exposes custom data as attributes in Keycloak, which can then be mapped as claims in the tokens.
• Validates user passwords within the Custom User SPI, using the pbkdf2 algorithm with the same hashing configuration as Keycloak's default values.

Some additional notes are discussed below.

Database Configuration Password Storage

Keycloak has a feature to allow custom user storage SPIs to provide configuration items that can be rendered and handled on Keycloak's admin screen. The configuration interface provided by Keycloak has ProviderConfigProperty.PASSWORD, which I'm using to store the database connection password. This field shows up as a masked textbox in the UI, but it is not stored securely. If you check the Keycloak's database, you can see how it's being stored:

So for production use, consider implementing a secure way of storing the password.

Keycloak's Official Quick-Start Repo

After going through the documentation and getting started on implementing the user storage SPI, searching on the web brought up several examples, and I was following one in particular since it used ear packaging (which I thought would be helpful since I'd probably need to reference SQL Server JDBC package in my project). It wasn't until later that I found Keycloak's official quick-start repo that had two complete examples of implementing a user storage SPI. Knowing this earlier would've saved me some time, especially when I was pulling my hair out trying to figure out why some things were not working (one instance of that ended when I came across this getUserAdapter method).

Since the official quick-start repo is not mentioned in the user storage SPI documentation, I created a pull-request to add it. Not sure if it will be merged as is, but hopefully some form of it will show up in the documentation so that it can help future readers.