Open in app

Sign in

Write

Sign in

Keycloak

Keycloak with MSSQL in Production

Keycloak in HA Mode

Manas Peçenek
5 min readOct 12, 2023

In this article, I will demonstrate using Keycloak in a Production-like environment. The preparation step is to create a Reverse Proxy to distribute the load between 2 Keycloak instances. The Deployment step is to deploy all necessary services including MSSQL and NGINX.

PREPARATION: Nginx Configuration

To have an HTTPS URL, I will be using a self-signed certificate with the help of mkcert. The Dockerfile.nginx is like this:

FROM nginx

RUN apt update && \
apt install libnss3-tools curl -y && \
curl -JLO "https://dl.filippo.io/mkcert/latest?for=linux/amd64" && \
chmod +x mkcert-v*-linux-amd64 && \
cp mkcert-v*-linux-amd64 /usr/local/bin/mkcert

RUN mkcert -install

RUN mkcert -key-file /root/key.pem -cert-file /root/cert.pem *.localhost.local

And also here is the corresponding nginx.conf file

user  nginx;
worker_processes  auto;
error_log  /var/log/nginx/error.log notice;
pid        /var/run/nginx.pid;
events {
    worker_connections  1024;
}
http {
    include       /etc/nginx/mime.types;
    default_type  application/octet-stream;
    log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
                      '$status $body_bytes_sent "$http_referer" '
                      '"$http_user_agent" "$http_x_forwarded_for"';
    access_log  /var/log/nginx/access.log  main;
    sendfile        on;
    #tcp_nopush     on;
    keepalive_timeout  65;
    #gzip  on;
    include /etc/nginx/conf.d/*.conf;

    upstream keycloak {
      ip_hash;
      server keycloak_az_1:8080 fail_timeout=2s;
      server keycloak_az_2:8080 fail_timeout=2s;
    }

    server {
      server_name keycloak.localhost.local;
      listen 80;
      return 301 https://$host$request_uri;
    }

    server {
      server_name keycloak.localhost.local;
      listen 443 ssl http2;
      ssl_certificate /root/cert.pem;
      ssl_certificate_key /root/key.pem;
      set_real_ip_from 0.0.0.0/0;
      real_ip_header X-Real-IP;
      real_ip_recursive on;
      
      location / {
          proxy_pass http://keycloak;
          proxy_redirect off;
          proxy_set_header Host $host;
          proxy_set_header X-Forwarded-Host $host;
          proxy_set_header X-Forwarded-Server $host;
          proxy_set_header X-Forwarded-Port 443;
          proxy_set_header X-Real-IP $remote_addr;
          # proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
          proxy_set_header X-Forwarded-For $remote_addr;
          proxy_set_header X-Forwarded-Proto $scheme;
          proxy_set_header Upgrade $http_upgrade;
          add_header  X-Robots-Tag "noindex, nofollow, nosnippet, noarchive";
      
          proxy_connect_timeout       600;
          proxy_send_timeout          600;
          proxy_read_timeout          600;
          send_timeout                600;
      }
    }
}

DEPLOYMENT: Keycloak Configuration

After downloading all 4 files — docker-compose, Dockerfile.nginx, nginx.conf, cache-ispn-jdbc-ping.xml — simply run:

docker-compose up -d --build --force-recreate

Here is the main docker-compose file:

version: '3.8'

x-keycloak-common: &keycloak-common
  image: quay.io/keycloak/keycloak:24.0.0
  restart: unless-stopped
  expose:
    - 8080
    - 7800
  volumes:
    - $PWD/cache-ispn-jdbc-ping.xml:/opt/keycloak/conf/cache-ispn-jdbc-ping.xml
  command: 
    - start
  depends_on:
    - mssql-container

x-keycloak-common-variables: &keycloak-common-variables
  KC_PROXY_HEADERS: xforwarded
  KEYCLOAK_ADMIN: admin
  KEYCLOAK_ADMIN_PASSWORD: admin
  KC_HOSTNAME_URL: https://keycloak.localhost.local/
  KC_HOSTNAME_ADMIN_URL: https://keycloak.localhost.local/
  KC_DB: mssql
  KC_DB_USERNAME: SA
  KC_DB_PASSWORD: Admin@1234
  KC_DB_URL_DATABASE: master
  KC_DB_URL_HOST: mssql-container
  KC_DB_URL_PORT: 1433
  KC_DB_SCHEMA: dbo
  KC_DB_URL_PROPERTIES: ";trustServerCertificate=false;encrypt=false"
  KC_LOG_LEVEL: "INFO,org.infinispan:DEBUG,org.jgroups:DEBUG"
  KC_CACHE_CONFIG_FILE: cache-ispn-jdbc-ping.xml
  KC_TRANSACTION_XA_ENABLED: false
  JGROUPS_DISCOVERY_PROTOCOL: JDBC_PING
  CACHE_OWNERS_COUNT: 2
  CACHE_OWNERS_AUTH_SESSIONS_COUNT: 2

services:
  keycloak_az_1:
    <<: *keycloak-common
    container_name: keycloak_az_1
    hostname: keycloak_az_1
    environment:
      <<: *keycloak-common-variables
      JGROUPS_DISCOVERY_EXTERNAL_IP: 172.24.1.1
    networks:
      keycloak:
        ipv4_address: 172.24.1.1

  keycloak_az_2:
    <<: *keycloak-common
    container_name: keycloak_az_2
    hostname: keycloak_az_2
    environment:
      <<: *keycloak-common-variables
      JGROUPS_DISCOVERY_EXTERNAL_IP: 172.24.1.2
    networks:
      keycloak:
        ipv4_address: 172.24.1.2
        
  nginx-container:
    container_name: nginx-container
    build:
      context: .
      dockerfile: Dockerfile.nginx
    privileged: true
    ports:
      - 443:443
      - 80:80
    volumes:
      - $PWD/nginx.conf:/etc/nginx/nginx.conf:ro
    depends_on:
      - keycloak_az_1
      - keycloak_az_2
    networks:
      - keycloak

  mssql-container:
    container_name: mssql-container
    hostname: mssql-container
    image: mcr.microsoft.com/azure-sql-edge    
    volumes:
      - mssql-data:/var/opt/mssql
    ports:
      - 1433:1433
    environment:
      MSSQL_USER: SA
      ACCEPT_EULA: Y
      MSSQL_PID: Developer
      MSSQL_SA_PASSWORD: Admin@1234
    networks:
      - keycloak

networks:
  keycloak:
    driver: bridge
    ipam:
     config:
       - subnet: 172.24.0.0/16
         gateway: 172.24.0.1

volumes:
  mssql-data:
    name: mssql-data
    driver: local

and cache-ispn-jdbc-ping.xml

<?xml version="1.0" encoding="UTF-8"?>
<!--
  ~ Copyright 2019 Red Hat, Inc. and/or its affiliates
  ~ and other contributors as indicated by the @author tags.
  ~
  ~ Licensed under the Apache License, Version 2.0 (the "License");
  ~ you may not use this file except in compliance with the License.
  ~ You may obtain a copy of the License at
  ~
  ~ http://www.apache.org/licenses/LICENSE-2.0
  ~
  ~ Unless required by applicable law or agreed to in writing, software
  ~ distributed under the License is distributed on an "AS IS" BASIS,
  ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  ~ See the License for the specific language governing permissions and
  ~ limitations under the License.
  -->
<infinispan
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="urn:infinispan:config:11.0 http://www.infinispan.org/schemas/infinispan-config-11.0.xsd"
        xmlns="urn:infinispan:config:11.0">
    <jgroups>
        <stack name="mysql-jdbc-ping-tcp" extends="tcp">
            <TCP external_addr="${env.JGROUPS_DISCOVERY_EXTERNAL_IP:127.0.0.1}" />
            <JDBC_PING connection_driver="com.mysql.jdbc.Driver"
                       connection_username="${env.KC_DB_USERNAME}" connection_password="${env.KC_DB_PASSWORD}"
                       connection_url="jdbc:mysql://${env.KC_DB_URL_HOST}:${env.KC_DB_URL_PORT:3306}/${env.KC_DB_URL_DATABASE}${env.KC_DB_URL_PROPERTIES:}"
                       initialize_sql="CREATE TABLE IF NOT EXISTS JGROUPSPING (own_addr varchar(200) NOT NULL, cluster_name varchar(200) NOT NULL, bind_addr varchar(200) NOT NULL, updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, ping_data varbinary(5000) DEFAULT NULL, PRIMARY KEY (own_addr, cluster_name)) ENGINE=InnoDB DEFAULT CHARSET=utf8;"
                       insert_single_sql="INSERT INTO JGROUPSPING (own_addr, cluster_name, bind_addr, updated, ping_data) values (?, ?, '${env.JGROUPS_DISCOVERY_EXTERNAL_IP:127.0.0.1}', NOW(), ?);"
                       delete_single_sql="DELETE FROM JGROUPSPING WHERE own_addr=? AND cluster_name=?;"
                       select_all_pingdata_sql="SELECT ping_data, own_addr, cluster_name FROM JGROUPSPING WHERE cluster_name=?;"
                       info_writer_sleep_time="500"
                       remove_all_data_on_view_change="true"
                       stack.combine="REPLACE"
                       stack.position="MPING" />
        </stack>
        <stack name="mariadb-jdbc-ping-tcp" extends="tcp">
            <TCP external_addr="${env.JGROUPS_DISCOVERY_EXTERNAL_IP:127.0.0.1}" />
            <JDBC_PING connection_driver="com.mysql.jdbc.Driver"
                       connection_username="${env.KC_DB_USERNAME}" connection_password="${env.KC_DB_PASSWORD}"
                       connection_url="jdbc:mysql://${env.KC_DB_URL_HOST}:${env.KC_DB_URL_PORT:3306}/${env.KC_DB_URL_DATABASE}${env.KC_DB_URL_PROPERTIES:}"
                       initialize_sql="CREATE TABLE IF NOT EXISTS JGROUPSPING (own_addr varchar(200) NOT NULL, cluster_name varchar(200) NOT NULL, bind_addr varchar(200) NOT NULL, updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, ping_data varbinary(5000) DEFAULT NULL, PRIMARY KEY (own_addr, cluster_name)) ENGINE=InnoDB DEFAULT CHARSET=utf8;"
                       insert_single_sql="INSERT INTO JGROUPSPING (own_addr, cluster_name, bind_addr, updated, ping_data) values (?, ?, '${env.JGROUPS_DISCOVERY_EXTERNAL_IP:127.0.0.1}', NOW(), ?);"
                       delete_single_sql="DELETE FROM JGROUPSPING WHERE own_addr=? AND cluster_name=?;"
                       select_all_pingdata_sql="SELECT ping_data, own_addr, cluster_name FROM JGROUPSPING WHERE cluster_name=?;"
                       info_writer_sleep_time="500"
                       remove_all_data_on_view_change="true"
                       stack.combine="REPLACE"
                       stack.position="MPING" />
        </stack>
        <stack name="postgres-jdbc-ping-tcp" extends="tcp">
            <TCP external_addr="${env.JGROUPS_DISCOVERY_EXTERNAL_IP:127.0.0.1}" />
            <JDBC_PING connection_driver="org.postgresql.Driver"
                       connection_username="${env.KC_DB_USERNAME}" connection_password="${env.KC_DB_PASSWORD}"
                       connection_url="jdbc:postgresql://${env.KC_DB_URL_HOST}:${env.KC_DB_URL_PORT:5432}/${env.KC_DB_URL_DATABASE}${env.KC_DB_URL_PROPERTIES:}"
                       initialize_sql="CREATE SCHEMA IF NOT EXISTS ${env.KC_DB_SCHEMA:public}; CREATE TABLE IF NOT EXISTS ${env.KC_DB_SCHEMA:public}.JGROUPSPING (own_addr varchar(200) NOT NULL, cluster_name varchar(200) NOT NULL, bind_addr varchar(200) NOT NULL, updated timestamp default current_timestamp, ping_data BYTEA, constraint PK_JGROUPSPING PRIMARY KEY (own_addr, cluster_name));"
                       insert_single_sql="INSERT INTO ${env.KC_DB_SCHEMA:public}.JGROUPSPING (own_addr, cluster_name, bind_addr, updated, ping_data) values (?, ?, '${env.JGROUPS_DISCOVERY_EXTERNAL_IP:127.0.0.1}', NOW(), ?);"
                       delete_single_sql="DELETE FROM ${env.KC_DB_SCHEMA:public}.JGROUPSPING WHERE own_addr=? AND cluster_name=?;"
                       select_all_pingdata_sql="SELECT ping_data, own_addr, cluster_name FROM ${env.KC_DB_SCHEMA:public}.JGROUPSPING WHERE cluster_name=?"
                       info_writer_sleep_time="500"
                       remove_all_data_on_view_change="true"
                       stack.combine="REPLACE"
                       stack.position="MPING" />
        </stack>
        <stack name="mssql-jdbc-ping-tcp" extends="tcp">
            <TCP external_addr="${env.JGROUPS_DISCOVERY_EXTERNAL_IP:127.0.0.1}" />
            <JDBC_PING connection_driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
                       connection_username="${env.KC_DB_USERNAME}" connection_password="${env.KC_DB_PASSWORD}"
                       connection_url="jdbc:sqlserver://${env.KC_DB_URL_HOST}:${env.KC_DB_URL_PORT:1433};databaseName=${env.KC_DB_URL_DATABASE}${env.KC_DB_URL_PROPERTIES:}"
                       initialize_sql="IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = '${env.KC_DB_SCHEMA:dbo}') BEGIN EXEC ('CREATE SCHEMA [${env.KC_DB_SCHEMA:dbo}] AUTHORIZATION [dbo]') END; IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='JGROUPSPING' AND TABLE_SCHEMA='${env.KC_DB_SCHEMA:dbo}') CREATE TABLE ${env.KC_DB_SCHEMA:dbo}.JGROUPSPING (own_addr varchar(200) NOT NULL, cluster_name varchar(200) NOT NULL, bind_addr varchar(200) NOT NULL, updated datetime2 default getdate(), ping_data varbinary(5000), constraint PK_JGROUPSPING PRIMARY KEY (own_addr, cluster_name));"
                       insert_single_sql="INSERT INTO ${env.KC_DB_SCHEMA:dbo}.JGROUPSPING (own_addr, cluster_name, bind_addr, updated, ping_data) values (?, ?, '${env.JGROUPS_DISCOVERY_EXTERNAL_IP:127.0.0.1}', GETDATE(), ?);"
                       delete_single_sql="DELETE FROM ${env.KC_DB_SCHEMA:dbo}.JGROUPSPING WHERE own_addr=? AND cluster_name=?;"
                       select_all_pingdata_sql="SELECT ping_data, own_addr, cluster_name FROM ${env.KC_DB_SCHEMA:dbo}.JGROUPSPING WHERE cluster_name=?;"
                       info_writer_sleep_time="500"
                       remove_all_data_on_view_change="true"
                       stack.combine="REPLACE"
                       stack.position="MPING" />
        </stack>
    </jgroups>
    <cache-container name="keycloak">
        <transport lock-timeout="60000" stack="${env.KC_DB}-jdbc-ping-tcp"/>
        <local-cache name="realms">
            <encoding>
                <key media-type="application/x-java-object"/>
                <value media-type="application/x-java-object"/>
            </encoding>
            <memory max-count="10000"/>
        </local-cache>
        <local-cache name="users">
            <encoding>
                <key media-type="application/x-java-object"/>
                <value media-type="application/x-java-object"/>
            </encoding>
            <memory max-count="10000"/>
        </local-cache>
        <distributed-cache name="sessions" owners="2">
            <expiration lifespan="-1"/>
        </distributed-cache>
        <distributed-cache name="authenticationSessions" owners="2">
            <expiration lifespan="-1"/>
        </distributed-cache>
        <distributed-cache name="offlineSessions" owners="2">
            <expiration lifespan="-1"/>
        </distributed-cache>
        <distributed-cache name="clientSessions" owners="2">
            <expiration lifespan="-1"/>
        </distributed-cache>
        <distributed-cache name="offlineClientSessions" owners="2">
            <expiration lifespan="-1"/>
        </distributed-cache>
        <distributed-cache name="loginFailures" owners="2">
            <expiration lifespan="-1"/>
        </distributed-cache>
        <local-cache name="authorization">
            <encoding>
                <key media-type="application/x-java-object"/>
                <value media-type="application/x-java-object"/>
            </encoding>
            <memory max-count="10000"/>
        </local-cache>
        <replicated-cache name="work">
            <expiration lifespan="-1"/>
        </replicated-cache>
        <local-cache name="keys">
            <encoding>
                <key media-type="application/x-java-object"/>
                <value media-type="application/x-java-object"/>
            </encoding>
            <expiration max-idle="3600000"/>
            <memory max-count="1000"/>
        </local-cache>
        <distributed-cache name="actionTokens" owners="2">
            <encoding>
                <key media-type="application/x-java-object"/>
                <value media-type="application/x-java-object"/>
            </encoding>
            <expiration max-idle="-1" lifespan="-1" interval="300000"/>
            <memory max-count="-1"/>
        </distributed-cache>
    </cache-container>
</infinispan>

RESOURCES

Configuring the database

Keycloak is an open source identity and access management solution

www.keycloak.org

Keycloak Cluster Setup

Keycloak is an open source identity and access management solution

www.keycloak.org

GitHub - ivangfr/keycloak-clustered: Keycloak-Clustered extends quay.io/keycloak/keycloak official…

Keycloak-Clustered extends quay.io/keycloak/keycloak official Keycloak Docker image by adding JDBC_PING discovery…

github.com

https://gist.github.com/dasniko/059dc2a895f585f6f74edaa042a2da48

Keycloak
Mssql
Nginx
Docker Compose
Mkcert

--

--

Manas Peçenek

Written by Manas Peçenek

1.1K Followers

Sr. DevOps Engineer

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams