ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

How to Configure Identity Access Manager to use an external MS SQL Server Database connecting as a Windows Account

book

Article ID: 213109

calendar_today

Updated On:

Products

Service Virtualization

Issue/Introduction

After preforming a fresh installation of DevTest 10.6 on a Windows 2019 Server, we would like to use an external SQL Server 2019 database. As a requirement, we must configure DevTest to connect to the SQL Server 2019 database using Windows Integrated Security. After setting up the iam.properties to connect to the SQL Server 2019 database using integratedSecurity=true, the IAM Server is failing to start.

The error we are seeing is:

2021-04-05 12:09:05,118 ERROR [org.jboss.msc.service.fail] (ServerService Thread Pool -- 49) MSC000001: Failed to start service jboss.undertow.deployment.default-server.default-host./auth: org.jboss.msc.service.StartException in service jboss.undertow.deployment.default-server.default-host./auth: java.lang.RuntimeException: RESTEASY003325: Failed to construct public org.keycloak.services.resources.KeycloakApplication(javax.servlet.ServletContext,org.jboss.resteasy.core.Dispatcher)
 at org.wildfly.extension.undertow.deployment.UndertowDeploymentService$1.run(UndertowDeploymentService.java:84)

...

Caused by: java.lang.RuntimeException: RESTEASY003325: Failed to construct public org.keycloak.services.resources.KeycloakApplication(javax.servlet.ServletContext,org.jboss.resteasy.core.Dispatcher)
 at org.jboss.resteasy.core.ConstructorInjectorImpl.construct(ConstructorInjectorImpl.java:162)

...

Caused by: java.lang.RuntimeException: Failed to update database
 at org.keycloak.connections.jpa.updater.liquibase.LiquibaseJpaUpdaterProvider.update(LiquibaseJpaUpdaterProvider.java:102)

...

Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set META-INF/jpa-changelog-1.1.0.Beta1.xml::1.1.0.Beta1::[email protected]:
     Reason: liquibase.exception.UnexpectedLiquibaseException: liquibase.exception.CustomChangeException: Failed to add realm code secret
 at liquibase.changelog.ChangeSet.execute(ChangeSet.java:584)

...

Caused by: liquibase.exception.UnexpectedLiquibaseException: liquibase.exception.CustomChangeException: Failed to add realm code secret
 at liquibase.change.custom.CustomChangeWrapper.generateStatements(CustomChangeWrapper.java:185)

...

Caused by: liquibase.exception.CustomChangeException: Failed to add realm code secret
 at org.keycloak.connections.jpa.updater.liquibase.custom.AddRealmCodeSecret.generateStatements(AddRealmCodeSecret.java:78)
 at liquibase.change.custom.CustomChangeWrapper.generateStatements(CustomChangeWrapper.java:178)
 ... 62 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '\'.
 at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)

...

021-04-05 12:09:05,208 ERROR [org.jboss.as.controller.management-operation] (Controller Boot Thread) WFLYCTL0013: Operation ("add") failed - address: ([("deployment" => "keycloak-server.war")]) - failure description: {"WFLYCTL0080: Failed services" => {"jboss.undertow.deployment.default-server.default-host./auth" => "java.lang.RuntimeException: RESTEASY003325: Failed to construct public org.keycloak.services.resources.KeycloakApplication(javax.servlet.ServletContext,org.jboss.resteasy.core.Dispatcher)
    Caused by: java.lang.RuntimeException: RESTEASY003325: Failed to construct public org.keycloak.services.resources.KeycloakApplication(javax.servlet.ServletContext,org.jboss.resteasy.core.Dispatcher)
    Caused by: java.lang.RuntimeException: Failed to update database
    Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set META-INF/jpa-changelog-1.1.0.Beta1.xml::1.1.0.Beta1::[email protected]:
     Reason: liquibase.exception.UnexpectedLiquibaseException: liquibase.exception.CustomChangeException: Failed to add realm code secret
    Caused by: liquibase.exception.UnexpectedLiquibaseException: liquibase.exception.CustomChangeException: Failed to add realm code secret
    Caused by: liquibase.exception.CustomChangeException: Failed to add realm code secret
    Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '\\'."}}

Cause

 The failure is caused by the '\' character getting escaped during the internal java execution, thus causing the user property to be passed as iam.db.user=DomainUser instead of Domin\User.

 

Environment

Release : 10.6

Component : CA Service Virtualization

Resolution

1: Keep sqljdbc_auth.dll into the java library classpath:  (i.e. java.library.path = c:\program files\ca\devtest\jre\bin => Identified based on server.log)

2: Update user details in the iam.properties as follows:  

          iam.db.user=Domain\User  should be   iam.db.user=Domain\\User

   

Now the updated properties will look like:

iam.db.vendor=mssql
iam.db.url=jdbc:sqlserver://<FQDN_Hostname>:<Port>;integratedSecurity=true;databaseName=<Database_Name>
iam.db.user=<Domain>\\<Username>
iam.db.password=<Password>  //enter a dummy password
iam.db.jdbc.driver.path=${IAM_HOME}/database/drivers/sqljdbc4-4.0.jar

      

3: Restart IdentityAccessManger and verify the connection and webpage.