Setting Up Keycloak with MSSQL Server Using Docker

In this guide, I will walk you through setting up Keycloak with MSSQL Server using Docker containers. This setup will allow you to run Keycloak with MSSQL as its backend database, useful for development or integration with other applications.

Prerequisites

Before starting, ensure you have Docker installed and running on your system. We'll be pulling images for Keycloak and MSSQL Server and setting up a Docker network to connect both containers.

---

Step 1: Pull Docker Images for Keycloak and MSSQL Server

Start by pulling the latest Keycloak and MSSQL Server images from their respective repositories.

docker pull quay.io/keycloak/keycloak:23.0
docker pull mcr.microsoft.com/mssql/server:2019-latest

---

Step 2: Create a Docker Network

We will create a dedicated Docker network for Keycloak and the MSSQL Server container so they can communicate with each other.

docker network create keycloak_network

---

Step 3: Spin Up MSSQL Server

Next, run the MSSQL Server container. Replace "yourstrongpassword" with a strong password for the sa (System Administrator) account.

docker run \
  -e 'ACCEPT_EULA=Y' \
  -e "MSSQL_SA_PASSWORD=yourstrongpassword" \
  -e 'MSSQL_RPC_PORT=135' \
  -e 'MSSQL_DTC_TCP_PORT=51000' \
  -p 1433:1433 -p 135:135 -p 51000:51000 \
  --net keycloak_network \
  --name sqlserver1 \
  --hostname sqlserver1 \
  -d mcr.microsoft.com/mssql/server:2019-latest

---

Step 4: Connect to the MSSQL Server Container

Once the container is up and running, you can access the container's shell using the following command:

docker exec -it sqlserver1 "bash"

Login to the MSSQL server with sqlcmd:

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "yourstrongpassword"

---

Step 5: Configure Keycloak Database in MSSQL Server

After logging into the MSSQL server, run the following SQL command to install support for XA transactions (required by Keycloak):

EXEC sp_sqljdbc_xa_install
GO

Create a database for Keycloak:

CREATE DATABASE keycloak;
GO
SELECT Name from sys.databases;
GO

---

Step 6: Run Keycloak Container in Development Mode

Finally, spin up the Keycloak container. Replace the values for KEYCLOAK_ADMIN_PASSWORD and KC_DB_PASSWORD with your own strong passwords.

docker run --name keycloak_mssql -d -p 8080:8080 --net keycloak_network \
  -e KEYCLOAK_ADMIN=keyadmin \
  -e KEYCLOAK_ADMIN_PASSWORD=yourkeycloakpass \
  -e KC_DB=mssql \
  -e KC_DB_URL='jdbc:sqlserver://sqlserver1:1433;instanceName=sqlserver1;databaseName=keycloak;encrypt=false;' \
  -e KC_DB_USERNAME=sa \
  -e KC_DB_PASSWORD=yourstrongpassword \
  -e KC_HOSTNAME=localhost \
  -e KC_TRANSACTION_XA_ENABLED=true \
  quay.io/keycloak/keycloak:23.0 start-dev

Keycloak should now be running in development mode and connected to the MSSQL database. You can access the Keycloak admin console by navigating to http://localhost:8080 in your browser.

Conclusion

You’ve now set up Keycloak with MSSQL Server as the backend database using Docker. This setup is ideal for development purposes and can easily be extended for production environments by adjusting the configurations. If you run into any issues, make sure to check the logs for both Keycloak and MSSQL Server containers using docker logs.

Let me know if you found this guide helpful!