Keycloak and MSSQL Database with Docker

Greetings to everyone.

This content include some tricks about Keycloak and how to customized it aspect of database. Also you will see custom theme implementation in repo but my focusing for this content is database entegration instead of default.

Check files and configurations before the start on Github. Link is below.

https://github.com/mertcanakdeniz/keycloak-sqlserver-docker

So we can start !

There are several methods for installisation of Keycloak. I have deployed with docker by override dockerfile.In default, Keycloak works with PostgreDB and Jboss. By the way, Jboss is also an application server that can be compile with .jar files and developed with Java. So we modify some parts of Jboss codes by override dockerfile for working well with Mssql database.

Here is custom dockerfile.

FROM quay.io/keycloak/keycloak:latest as builder
#FROM jboss/keycloak:latest as builder
# Enable health and metrics support
ENV KC_HEALTH_ENABLED=true
ENV KC_METRICS_ENABLED=true

# Configure a database vendor
ENV KC_DB=mssql


WORKDIR /opt/keycloak
# for demonstration purposes only, please make sure to use proper certificates in production instead
#RUN keytool -genkeypair -storepass password -storetype PKCS12 -keyalg RSA -keysize 2048 -dname "CN=server" -alias server -ext "SAN:c=DNS:localhost,IP:127.0.0.1" -keystore conf/server.keystore
#RUN /opt/keycloak/bin/kc.sh --verbose  build
#RUN /opt/keycloak/bin/kc.sh -Dkc.db.tx-type=enable -Dkc.db-driver=com.microsoft.sqlserver.jdbc.SQLServerDriver --db=mssql
COPY dist/* /opt/keycloak/providers/
# xa module  and quarkus configurations
RUN /opt/keycloak/bin/kc.sh build --db=mssql  --transaction-xa-enabled=true -Dquarkus.debug.enabled=true -Dquarkus.debug.port=5005  --debug
COPY quarkus.properties /opt/keycloak/conf/
COPY keycloak.conf /opt/keycloak/conf/
FROM quay.io/keycloak/keycloak:latest

COPY --from=builder /opt/keycloak/ /opt/keycloak/
# change these values to point to a running postgres instance
ENV KC_DB=mssql
ENV KC_DB_URL=jdbc:sqlserver:/<url>;databaseName=Keycloak;encrypt=false;trustServerCertificate=false;loginTimeout=30
ENV KC_DB_USERNAME=<username>
ENV KC_DB_PASSWORD=<password>
#ENV KC_HOSTNAME=
ENTRYPOINT ["/opt/keycloak/bin/kc.sh"]
#CMD ["start-dev"]

Xa module and quarkus configuration are some of tricky parts that cause can be getting error your environment.

We are doing some extras for these tricky guys. In your DB instance, install xa module on your master db and mapping related role to your Keycloak Db user.

Here is script :

use master;
EXEC sp_sqljdbc_xa_install

EXEC sp_addrolemember [SqlJDBCXAUser], 'keycloakuser';

Please dont forget change ‘keycloakuser’ with your own db user which has db_owner role on your Keycloak DB.

Okey we are good.

We can get the jump to forward.

There are some configuration files and folders for quarkus configuration and other modifications.

I prepared docker-compose.yaml for lots of process is be merged and there are several configuration files. So you can find in Github and link is the top.

Here is the docker-compose.yml

services:
  mssql:
    image: mcr.microsoft.com/mssql/server:2022-latest
    ports:
      - "1433:1433"
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD= <password>
      - MSSQL_PID=Developer
    volumes:
      - ./mssql:/var/opt/mssql
    networks:
      - keycloak-network


  mssqlscripts:
    image: mcr.microsoft.com/mssql-tools
    depends_on:
      - mssql
    command: /bin/bash -c 'if /opt/mssql-tools/bin/sqlcmd -S mssql -U sa -P "password" -Q "SELECT COUNT(*) FROM sys.databases WHERE name = N''Keycloak''" | grep -q "0"; then /opt/mssql-tools/bin/sqlcmd -S mssql -U sa -P "<password>" -Q "create database Keycloak"; fi; /opt/mssql-tools/bin/sqlcmd -S mssql -U sa -P <password>" -Q "use master; EXEC sp_sqljdbc_xa_install;"'
    networks:
      - keycloak-network

  keycloak:
    image: keycloak-medium:v1
    container_name: keycloak
    build:
      context: .
      dockerfile: Dockerfile
    command: ["start  --hostname-url=https://<your-application-url>:8443  --hostname-admin=<your-application-url> --http-enabled=false  --https-port=8443 --https-certificate-file=</dir/file.crt> --https-certificate-key-file=</dir/file.key>"]
    ports:
      - "8443:8443"
    volumes:
      - .</dir/>:</dir/>
#      - ./org.keycloak.keycloak-themes-22.0.1.jar:/opt/keycloak/lib/lib/main/org.keycloak.keycloak-themes-22.0.1.jar
      - ./custom_theme/themes:/opt/keycloak/themes
    environment:
      - KEYCLOAK_ADMIN=admin
      - KEYCLOAK_ADMIN_PASSWORD=<admin_password>
    depends_on:
      - mssql
    networks:
      - keycloak-network
networks:
  keycloak-network:
    driver: bridge

Please dont forget the change the values in sign < > .

Also /dir is your certification directory.

Mssqlscripts service is doing your db processing which are check and create db if not exist and install xa module in instance. It is optinal service for getting simple the works.

And the turn is the giving permission for Sql user /mssql volume folder.

Here is the command, run in the root directory that exist volume folder.

sudo chown 10001 mssql/

Now we can run docker-compose .

sudo docker compose up 

Okey, we are good.

I am trying to explain keycloak deployment with docker and how to work fine with Mssql database.

It will comes more topics related with Keycloak and how can use Keycloak.

Up to date.

Thank you all.