How to setup Keycloak 20+ and MS-SQL Server

Configuring Keycloak 20 with MS-SQL Server

Hello everyone, I decided to create this post because I just got somewhat tired while trying to create a Keycloak 20.0.2 instance with --a recent-- SQL Server as a Backed using docker, so, before I get to tell you my life history, this is the --somewhat-- complete list of steps that you need to perform for this to work:

1 You need to create a certificate for a FQDN for your sql server instance (mssql.mydomain.com) or a wildcard certificate (*.mydomain.com), you can get this one from Let’s-Encrypt, a self-signed certificate or directly buying one from Komodo or any other SSL certificate authority (for non self-signed certificates you might need to buy a domain name), also, I find that having a separate certificate specifically for SQL Server is generally a good idea, this will separate the security from your other webapps/applications from your database. If you have SQL Server in a Windows Environment with AD, the domain controller should give you a valid SSL Certificate.

2 Deploy your SQL Server instance in docker and secure it using your new certificate and key (Secure SQL Server Linux containers - SQL Server | Microsoft Learn), this will ensure that SSL connections work and that they’re properly encrypted. In a windows environment, use MMC to define the certificate at the network level

3 You need to define the hostname of your docker container to the FQDN that you defined for your certificate ( -hostname: fqdn.mydomain.com- in docker compose), this is necesary for internal usage (and generally a good idea), for external usage, you need to define your DNS entry at your domain (or in your hosts file).

4 --I have not tested this using a bought certificate… –, You need to add your certificate to your cacerts store in java, basically, even if you disable encryption at in the jdbc connection string –encrypth=false;, encrypt=true;trsutServerCertificate=true;,– Java, the executable is still monitoring your connections and will block it if it doesn’t seem it as secure, so, we need to:

4.1 Convert our pem/crt certificate file to a der file, run this command:

x509 -outform der -in mycert.pem/.crt -out mycert.pem

If running in docker

4.2 In your custom Keycloak dockerfile, copy your certificate to an accessible path:

COPY ./mycert.der /opt/keycloak

4.3 Use the keytool utility to installed in your keycloak image to import the certificate with

USER root
RUN keytool \
    -import -cacerts \
    -alias "mssql" -file ./opt/keycloak/fullchain.der \
    # -keystore $(readlink -f /usr/bin/java | sed "s:bin/java::")lib/security/cacerts \
    -storepass changeit \
    -noprompt
USER 1000

If running locally

4.2 Trust your SQL Sertificate in your server, this is easier if you have the JAVA_HOME variable setup, consult your JRE/JDK documentation to accomplish this

 $JAVA_HOME/bin/keytool \ 
	 -import -cacerts \ 
	 -alias "mssql" \
	 -file ./opt/keycloak/fullchain.der \
   -keystore $JAVA_HOME/lib/security/cacerts \
   -storepass changeit \
   -noprompt

5 Once you have the certificate installed, make sure that you’re configuring keycloak to use your FQDN name, Java will also throw an exception to you if you try to use a different FQDN

Wrapping-up

I hope everyone who is struggling to configure keycloak with MSSQL Server finds this little “article” useful, if you have any doubts post a comment and I’ll try to answer it in a few hours/days.

4 Likes