How to use Keycloak with SQL Server

[christiannagel]

What needs to be done to run keycloak using SQL Server successfully? I've tried this app-model specifying the connection string as used by #6084

var sqlServer = builder.AddSqlServer("keycloaksql")
    .WithDataVolume("keycloakdata");

var sqlDb = sqlServer.AddDatabase("keycloakdb", "keycloakidentity");

var keycloak = builder.AddKeycloak("keycloak")
    .WithEnvironment(context =>
    {
        context.EnvironmentVariables.Add("KC_DB", "mssql");
        context.EnvironmentVariables.Add("KC_DB_URL", 
            $"jdbc:sqlserver://{sqlServer.Resource.PrimaryEndpoint.ContainerHost};databaseName={sqlDb.Resource.DatabaseName};trustServerCertificate=true;user=sa;password={sqlServer.Resource.PasswordParameter.Value};");
    })
    .WaitFor(sqlDb);

Keycloak fails with this error:

[org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator] (JPA Startup Thread) HHH000342: Could not obtain connection to query metadata: java.lang.NullPointerException: Cannot throw exception because the return value of "java.util.function.BiFunction.apply(Object, Object)" is null

Did I miss any configuration required with Keycloak?

[davide-podda]

Hi, try to view my sample code

AppHost/Program.cs
var builder = DistributedApplication.CreateBuilder(args);

var passwordSql = builder.AddParameter("sqlAdminPassword", secret: true);
var db1 = builder.AddSqlServer("sql1",passwordSql).AddDatabase("db1");

builder.AddProject<Projects.DatabaseMigrations_ApiService>("api")
.WithReference(db1);

builder.AddProject<Projects.DatabaseMigrations_MigrationService>("migration")
.WithReference(db1);

var adminKeycloakUsername = builder.AddParameter("adminUsername", secret: true);
var adminKeycloakPassword = builder.AddParameter("adminPassword", secret: true);

var keycloak = builder.AddKeycloak("keycloak", adminUsername: adminKeycloakUsername, adminPassword: adminKeycloakPassword)
.WithExternalHttpEndpoints()

                  .WithDockerfile("./KeycloakConfiguration", "Dockerfile")
                  //.WithHttpEndpoint(port: 8888, targetPort: 8888, name: "http")
                  .WithHttpsEndpoint(port: 8443, targetPort: 8443, name: "https")
                  //.WithDataVolume()
                  .WithReference(db1)
                  .WithEnvironment("KC_DB_VENDOR", "mssql")
                  .WithEnvironment("KC_DB", "mssql")
                  .WithEnvironment("KC_DB_USERNAME", "sa")
                  .WithEnvironment("KC_DB_PASSWORD", passwordSql)
                  .WithEnvironment("KC_DB_URL", JdbcExpression.Create(db1))
                  //start to enable admin in production
                  .WithEnvironment("KC_HOSTNAME_STRICT", "false")
                  .WithEnvironment("KC_HOSTNAME_STRICT_HTTPS", "false")
                  .WithEnvironment("KC_HTTP_ENABLED", "true")
                  .WithEnvironment("KC_PROXY", "edge")
                  //end to enable admin in production
                  .WithRealmImport("./KeycloakConfiguration/realms")

Dockerfile
FROM quay.io/keycloak/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 build

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_USERNAME=keycloak
ENV KC_DB_PASSWORD=keycloak
ENV KC_DB_URL=jdbc:postgresql://localhost:5432/identity

Other settings you may need to set.

ENV KC_HOSTNAME=localhost
ENV KC_HOSTNAME_STRICT: "false"
ENV KC_HOSTNAME_STRICT_HTTPS: "false"
ENV KC_HTTP_ENABLED: "true"
ENV KC_PROXY: edge

ENTRYPOINT ["/opt/keycloak/bin/kc.sh"]

JdbcExpression.cs

internal static ReferenceExpression Create(IResourceBuilder dbResourceBuilder)
{
SqlServerDatabaseResource dbResource = dbResourceBuilder.Resource;
SqlServerServerResource dbServer = dbResource.Parent;
EndpointReference endpoint = dbServer.PrimaryEndpoint;

    if (dbResourceBuilder.ApplicationBuilder.ExecutionContext.IsRunMode)
    {
        return ReferenceExpression.Create($"jdbc:sqlserver://host.docker.internal:{endpoint.Property(EndpointProperty.Port)}/{dbResource.Name}");
    }
    else
    {
        return ReferenceExpression.Create($"jdbc:sqlserver://{endpoint.Property(EndpointProperty.Host)}:{endpoint.Property(EndpointProperty.Port)};instanceName={endpoint.Property(EndpointProperty.Host)};databaseName={dbResource.Name};encrypt=false;trustServerCertificate=false;loginTimeout=30"); 
    }
}