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.