Trying to use keycloak with SQL Server #12271

akloster · 2022-05-31T10:50:31Z

akloster
May 31, 2022

I'm trying to use Keycloak with SQL Server. I had to create the database I specify in the connectionstring, and now I am getting the following errors:

keycloak_1  | 2022-05-31 10:43:53,913 WARN  [com.arjuna.ats.jta] (main) ARJUNA016061: TransactionImple.enlistResource - XAResource.start returned: XAException.XAER_RMERR for < formatId=131077, gtrid_length=35, bqual_length=36, tx_uid=0:ffffac120005:9d47:6295f169:0, node_name=quarkus, branch_uid=0:ffffac120005:9d47:6295f169:3f, subordinatenodename=null, eis_name=0 >: javax.transaction.xa.XAException: com.microsoft.sqlserver.jdbc.SQLServerException: Failed to create the XA control connection. Error: "The connection is closed."
keycloak_1  |   at com.microsoft.sqlserver.jdbc.SQLServerXAResource.DTC_XA_Interface(SQLServerXAResource.java:738)
keycloak_1  |   at com.microsoft.sqlserver.jdbc.SQLServerXAResource.start(SQLServerXAResource.java:772)
keycloak_1  |   at io.agroal.narayana.BaseXAResource.start(BaseXAResource.java:150)
keycloak_1  |   at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.enlistResource(TransactionImple.java:661)
keycloak_1  |   at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.enlistResource(TransactionImple.java:422)
keycloak_1  |   at io.agroal.narayana.NarayanaTransactionIntegration.associate(NarayanaTransactionIntegration.java:93)
keycloak_1  |   at io.agroal.pool.ConnectionPool.getConnection(ConnectionPool.java:245)
keycloak_1  |   at io.agroal.pool.DataSource.getConnection(DataSource.java:86)
keycloak_1  |   at io.quarkus.hibernate.orm.runtime.customized.QuarkusConnectionProvider.getConnection(QuarkusConnectionProvider.java:23)
keycloak_1  |   at org.hibernate.internal.NonContextualJdbcConnectionAccess.obtainConnection(NonContextualJdbcConnectionAccess.java:38)
keycloak_1  |   at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:108)
keycloak_1  |   at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:138)
keycloak_1  |   at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.connection(StatementPreparerImpl.java:50)
keycloak_1  |   at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:149)
keycloak_1  |   at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:176)
keycloak_1  |   at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:151)
keycloak_1  |   at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:2122)
keycloak_1  |   at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2059)
keycloak_1  |   at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2037)
keycloak_1  |   at org.hibernate.loader.Loader.doQuery(Loader.java:956)
keycloak_1  |   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:357)
keycloak_1  |   at org.hibernate.loader.Loader.doList(Loader.java:2868)
keycloak_1  |   at org.hibernate.loader.Loader.doList(Loader.java:2850)
keycloak_1  |   at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2682)
keycloak_1  |   at org.hibernate.loader.Loader.list(Loader.java:2677)
keycloak_1  |   at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:540)
keycloak_1  |   at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:400)
keycloak_1  |   at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219)
keycloak_1  |   at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1454)
keycloak_1  |   at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1649)
keycloak_1  |   at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1617)
keycloak_1  |   at org.hibernate.query.Query.getResultList(Query.java:165)
keycloak_1  |   at org.keycloak.models.jpa.MigrationModelAdapter.init(MigrationModelAdapter.java:59)
keycloak_1  |   at org.keycloak.models.jpa.MigrationModelAdapter.<init>(MigrationModelAdapter.java:42)
keycloak_1  |   at org.keycloak.models.jpa.JpaRealmProvider.getMigrationModel(JpaRealmProvider.java:93)
keycloak_1  |   at org.keycloak.migration.MigrationModelManager.migrate(MigrationModelManager.java:105)
keycloak_1  |   at org.keycloak.quarkus.runtime.storage.database.jpa.QuarkusJpaConnectionProviderFactory.migrateModel(QuarkusJpaConnectionProviderFactory.java:266)
keycloak_1  |   at org.keycloak.quarkus.runtime.storage.database.jpa.QuarkusJpaConnectionProviderFactory.initSchema(QuarkusJpaConnectionProviderFactory.java:191)
keycloak_1  |   at org.keycloak.models.utils.KeycloakModelUtils.runJobInTransaction(KeycloakModelUtils.java:239)
keycloak_1  |   at org.keycloak.quarkus.runtime.storage.database.jpa.QuarkusJpaConnectionProviderFactory.postInit(QuarkusJpaConnectionProviderFactory.java:136)
keycloak_1  |   at org.keycloak.quarkus.runtime.integration.QuarkusKeycloakSessionFactory.init(QuarkusKeycloakSessionFactory.java:96)
keycloak_1  |   at org.keycloak.quarkus.runtime.integration.jaxrs.QuarkusKeycloakApplication.initializeKeycloakSessionFactory(QuarkusKeycloakApplication.java:68)
keycloak_1  |   at org.keycloak.quarkus.runtime.integration.jaxrs.QuarkusKeycloakApplication.startup(QuarkusKeycloakApplication.java:49)
keycloak_1  |   at org.keycloak.quarkus.runtime.integration.QuarkusLifecycleObserver.onStartupEvent(QuarkusLifecycleObserver.java:37)
keycloak_1  |   at org.keycloak.quarkus.runtime.integration.QuarkusLifecycleObserver_Observer_onStartupEvent_b0e82415b143738dc1f986a5fa4668e83d0a5dea.notify(Unknown Source)
keycloak_1  |   at io.quarkus.arc.impl.EventImpl$Notifier.notifyObservers(EventImpl.java:320)
keycloak_1  |   at io.quarkus.arc.impl.EventImpl$Notifier.notify(EventImpl.java:302)
keycloak_1  |   at io.quarkus.arc.impl.EventImpl.fire(EventImpl.java:73)
keycloak_1  |   at io.quarkus.arc.runtime.ArcRecorder.fireLifecycleEvent(ArcRecorder.java:128)
keycloak_1  |   at io.quarkus.arc.runtime.ArcRecorder.handleLifecycleEvents(ArcRecorder.java:97)
keycloak_1  |   at io.quarkus.deployment.steps.LifecycleEventsBuildStep$startupEvent1144526294.deploy_0(Unknown Source)
keycloak_1  |   at io.quarkus.deployment.steps.LifecycleEventsBuildStep$startupEvent1144526294.deploy(Unknown Source)
keycloak_1  |   at io.quarkus.runner.ApplicationImpl.doStart(Unknown Source)
keycloak_1  |   at io.quarkus.runtime.Application.start(Application.java:101)
keycloak_1  |   at io.quarkus.runtime.ApplicationLifecycleManager.run(ApplicationLifecycleManager.java:103)
keycloak_1  |   at io.quarkus.runtime.Quarkus.run(Quarkus.java:67)
keycloak_1  |   at org.keycloak.quarkus.runtime.KeycloakMain.start(KeycloakMain.java:86)
keycloak_1  |   at org.keycloak.quarkus.runtime.cli.command.AbstractStartCommand.run(AbstractStartCommand.java:34)
keycloak_1  |   at picocli.CommandLine.executeUserObject(CommandLine.java:1939)
keycloak_1  |   at picocli.CommandLine.access$1300(CommandLine.java:145)
keycloak_1  |   at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2358)
keycloak_1  |   at picocli.CommandLine$RunLast.handle(CommandLine.java:2352)
keycloak_1  |   at picocli.CommandLine$RunLast.handle(CommandLine.java:2314)
keycloak_1  |   at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:2179)
keycloak_1  |   at picocli.CommandLine$RunLast.execute(CommandLine.java:2316)
keycloak_1  |   at picocli.CommandLine.execute(CommandLine.java:2078)
keycloak_1  |   at org.keycloak.quarkus.runtime.cli.Picocli.parseAndRun(Picocli.java:84)
keycloak_1  |   at org.keycloak.quarkus.runtime.KeycloakMain.main(KeycloakMain.java:77)
keycloak_1  |   at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
keycloak_1  |   at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
keycloak_1  |   at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
keycloak_1  |   at java.base/java.lang.reflect.Method.invoke(Method.java:566)
keycloak_1  |   at io.quarkus.bootstrap.runner.QuarkusEntryPoint.doRun(QuarkusEntryPoint.java:60)
keycloak_1  |   at io.quarkus.bootstrap.runner.QuarkusEntryPoint.main(QuarkusEntryPoint.java:31)
keycloak_1  | 
keycloak_1  | 2022-05-31 10:43:53,917 WARN  [com.arjuna.ats.jta] (main) ARJUNA016138: Failed to enlist XA resource io.agroal.narayana.BaseXAResource@ea0c99c: javax.transaction.SystemException: TransactionImple.enlistResource - XAResource.start ARJUNA016054: could not register transaction: < formatId=131077, gtrid_length=35, bqual_length=36, tx_uid=0:ffffac120005:9d47:6295f169:0, node_name=quarkus, branch_uid=0:ffffac120005:9d47:6295f169:3f, subordinatenodename=null, eis_name=0 >
keycloak_1  |   at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.enlistResource(TransactionImple.java:714)
keycloak_1  |   at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.enlistResource(TransactionImple.java:422)
keycloak_1  |   at io.agroal.narayana.NarayanaTransactionIntegration.associate(NarayanaTransactionIntegration.java:93)
keycloak_1  |   at io.agroal.pool.ConnectionPool.getConnection(ConnectionPool.java:245)
keycloak_1  |   at io.agroal.pool.DataSource.getConnection(DataSource.java:86)
keycloak_1  |   at io.quarkus.hibernate.orm.runtime.customized.QuarkusConnectionProvider.getConnection(QuarkusConnectionProvider.java:23)
keycloak_1  |   at org.hibernate.internal.NonContextualJdbcConnectionAccess.obtainConnection(NonContextualJdbcConnectionAccess.java:38)
keycloak_1  |   at org.hibernate.resourc
10000
e.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:108)
keycloak_1  |   at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:138)
keycloak_1  |   at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.connection(StatementPreparerImpl.java:50)
keycloak_1  |   at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:149)
keycloak_1  |   at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:176)
keycloak_1  |   at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:151)
keycloak_1  |   at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:2122)
keycloak_1  |   at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2059)
keycloak_1  |   at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2037)
keycloak_1  |   at org.hibernate.loader.Loader.doQuery(Loader.java:956)
keycloak_1  |   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:357)
keycloak_1  |   at org.hibernate.loader.Loader.doList(Loader.java:2868)
keycloak_1  |   at org.hibernate.loader.Loader.doList(Loader.java:2850)
keycloak_1  |   at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2682)
keycloak_1  |   at org.hibernate.loader.Loader.list(Loader.java:2677)
keycloak_1  |   at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:540)
keycloak_1  |   at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:400)
keycloak_1  |   at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219)
keycloak_1  |   at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1454)
keycloak_1  |   at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1649)
keycloak_1  |   at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1617)
keycloak_1  |   at org.hibernate.query.Query.getResultList(Query.java:165)
keycloak_1  |   at org.keycloak.models.jpa.MigrationModelAdapter.init(MigrationModelAdapter.java:59)
keycloak_1  |   at org.keycloak.models.jpa.MigrationModelAdapter.<init>(MigrationModelAdapter.java:42)
keycloak_1  |   at org.keycloak.models.jpa.JpaRealmProvider.getMigrationModel(JpaRealmProvider.java:93)
keycloak_1  |   at org.keycloak.migration.MigrationModelManager.migrate(MigrationModelManager.java:105)
keycloak_1  |   at org.keycloak.quarkus.runtime.storage.database.jpa.QuarkusJpaConnectionProviderFactory.migrateModel(QuarkusJpaConnectionProviderFactory.java:266)
keycloak_1  |   at org.keycloak.quarkus.runtime.storage.database.jpa.QuarkusJpaConnectionProviderFactory.initSchema(QuarkusJpaConnectionProviderFactory.java:191)
keycloak_1  |   at org.keycloak.models.utils.KeycloakModelUtils.runJobInTransaction(KeycloakModelUtils.java:239)
keycloak_1  |   at org.keycloak.quarkus.runtime.storage.database.jpa.QuarkusJpaConnectionProviderFactory.postInit(QuarkusJpaConnectionProviderFactory.java:136)
keycloak_1  |   at org.keycloak.quarkus.runtime.integration.QuarkusKeycloakSessionFactory.init(QuarkusKeycloakSessionFactory.java:96)
keycloak_1  |   at org.keycloak.quarkus.runtime.integration.jaxrs.QuarkusKeycloakApplication.initializeKeycloakSessionFactory(QuarkusKeycloakApplication.java:68)
keycloak_1  |   at org.keycloak.quarkus.runtime.integration.jaxrs.QuarkusKeycloakApplication.startup(QuarkusKeycloakApplication.java:49)
keycloak_1  |   at org.keycloak.quarkus.runtime.integration.QuarkusLifecycleObserver.onStartupEvent(QuarkusLifecycleObserver.java:37)
keycloak_1  |   at org.keycloak.quarkus.runtime.integration.QuarkusLifecycleObserver_Observer_onStartupEvent_b0e82415b143738dc1f986a5fa4668e83d0a5dea.notify(Unknown Source)
keycloak_1  |   at io.quarkus.arc.impl.EventImpl$Notifier.notifyObservers(EventImpl.java:320)
keycloak_1  |   at io.quarkus.arc.impl.EventImpl$Notifier.notify(EventImpl.java:302)
keycloak_1  |   at io.quarkus.arc.impl.EventImpl.fire(EventImpl.java:73)
keycloak_1  |   at io.quarkus.arc.runtime.ArcRecorder.fireLifecycleEvent(ArcRecorder.java:128)
keycloak_1  |   at io.quarkus.arc.runtime.ArcRecorder.handleLifecycleEvents(ArcRecorder.java:97)
keycloak_1  |   at io.quarkus.deployment.steps.LifecycleEventsBuildStep$startupEvent1144526294.deploy_0(Unknown Source)
keycloak_1  |   at io.quarkus.deployment.steps.LifecycleEventsBuildStep$startupEvent1144526294.deploy(Unknown Source)
keycloak_1  |   at io.quarkus.runner.ApplicationImpl.doStart(Unknown Source)
keycloak_1  |   at io.quarkus.runtime.Application.start(Application.java:101)
keycloak_1  |   at io.quarkus.runtime.ApplicationLifecycleManager.run(ApplicationLifecycleManager.java:103)
keycloak_1  |   at io.quarkus.runtime.Quarkus.run(Quarkus.java:67)
keycloak_1  |   at org.keycloak.quarkus.runtime.KeycloakMain.start(KeycloakMain.java:86)
keycloak_1  |   at org.keycloak.quarkus.runtime.cli.command.AbstractStartCommand.run(AbstractStartCommand.java:34)
keycloak_1  |   at picocli.CommandLine.executeUserObject(CommandLine.java:1939)
keycloak_1  |   at picocli.CommandLine.access$1300(CommandLine.java:145)
keycloak_1  |   at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2358)
keycloak_1  |   at picocli.CommandLine$RunLast.handle(CommandLine.java:2352)
keycloak_1  |   at picocli.CommandLine$RunLast.handle(CommandLine.java:2314)
keycloak_1  |   at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:2179)
keycloak_1  |   at picocli.CommandLine$RunLast.execute(CommandLine.java:2316)
keycloak_1  |   at picocli.CommandLine.execute(CommandLine.java:2078)
keycloak_1  |   at org.keycloak.quarkus.runtime.cli.Picocli.parseAndRun(Picocli.java:84)
keycloak_1  |   at org.keycloak.quarkus.runtime.KeycloakMain.main(KeycloakMain.java:77)
keycloak_1  |   at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
keycloak_1  |   at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
keycloak_1  |   at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
keycloak_1  |   at java.base/java.lang.reflect.Method.invoke(Method.java:566)
keycloak_1  |   at io.quarkus.bootstrap.runner.QuarkusEntryPoint.doRun(QuarkusEntryPoint.java:60)
keycloak_1  |   at io.quarkus.bootstrap.runner.QuarkusEntryPoint.main(QuarkusEntryPoint.java:31)
keycloak_1  | 
keycloak_1  | 2022-05-31 10:43:56,920 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (main) SQL Error: 2627, SQLState: 23000
keycloak_1  | 2022-05-31 10:43:56,921 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (main) Verletzung der UNIQUE KEY-Einschr?nkung "UK_CLI_SCOPE". Ein doppelter Schl?ssel kann in das dbo.CLIENT_SCOPE-Objekt nicht eingef?gt werden. Der doppelte Schl?sselwert ist (master, role_list).
keycloak_1  | 2022-05-31 10:43:56,928 WARN  [io.agroal.pool] (main) Datasource '<default>': Closing open connection after completion
keycloak_1  | 2022-05-31 10:43:56,928 WARN  [io.agroal.pool] (main) Datasource '<default>': JDBC resources leaked: 1 ResultSet(s) and 1 Statement(s)
keycloak_1  | 2022-05-31 10:43:57,114 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Failed to start server in (production) mode
keycloak_1  | 2022-05-31 10:43:57,115 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement
keycloak_1  | 2022-05-31 10:43:57,115 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: org.hibernate.exception.ConstraintViolationException: could not execute statement
keycloak_1  | 2022-05-31 10:43:57,115 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: could not execute statement
keycloak_1  | 2022-05-31 10:43:57,116 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Verletzung der UNIQUE KEY-Einschr?nkung "UK_CLI_SCOPE". Ein doppelter Schl?ssel kann in das dbo.CLIENT_SCOPE-Objekt nicht eingef?gt werden. Der doppelte Schl?sselwert ist (master, role_list).
keycloak_1  | 2022-05-31 10:43:57,116 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) For more details run the same command passing the '--verbose' option. Also you can use '--help' to see the details about the usage of the particular command.

Looking at the database, Keycloak does create a lot of tables already, so some of the connection string must be alright. I am guessing that the user doesn't have the correct permissions somehow, but I'm using the same user with a few other databases and softwares (in a testing environment) so I'm a bit at a loss what permissions might be missing!

Any ideas?

XcrigX · 2022-06-06T22:52:12Z

XcrigX
Jun 6, 2022

@akloster - - Any luck with this? I'm facing the EXACT same error.

In my case I'm running a keycloak docker image in Kubernetes in Azure attempting to connect to Azure SQL.

The documentation here indicates that Azure SQL doesn't support XA Transactions:
https://www.keycloak.org/server/db

I customized the kc.sh build command as noted in the docs to disable these:
RUN /opt/keycloak/bin/kc.sh build --db=mssql --transaction-xa-enabled=false

And I set the environment variable as well:
ENV KC_TRANSACTION_XA_ENABLED=false

No luck - same error.

In case it helps the error messages just under the stack trace from akloster in English read:
2022-06-06 22:44:40,263 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (main) SQL Error: 2627, SQLState: 23000 2022-06-06 22:44:40,265 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (main) Violation of UNIQUE KEY constraint 'UK_CLI_SCOPE'. Cannot insert duplicate key in object 'keycloak.CLIENT_SCOPE'. The duplicate key value is (dcd13423-e422-4360-aefe-2797880ef79d, role_list). 2022-06-06 22:44:40,271 WARN [io.agroal.pool] (main) Datasource '<default>': Closing open connection after completion
I also dropped all the keycloak tables and re-deployed - it recreated them, but still gives the same XA error and UNIQUE KEY error.

Would appreciate any help.

0 replies

XcrigX · 2022-06-07T14:58:58Z

XcrigX
Jun 7, 2022

NOTE: opened this bug related to this:
#12379

4 replies

XcrigX Jun 7, 2022

@akloster - if you're still having this issue, looks like the build command options are what you need:
RUN /opt/keycloak/bin/kc.sh build --db=mssql --transaction-xa-enabled=false

tiagopsantos Nov 18, 2022

let me pick on this thread, i'm running KC over Azure SQL
Using the build configuration in Env variables

ENV KC_DB=mssql
ENV KC_TRANSACTION_XA_ENABLED=false

Sometimes we get the
io.agroal.narayana.LocalXAResource.start(LocalXAResource.java:47)

I really don't understand how using the CLI is different than using the ENV variables
RUN /opt/keycloak/bin/kc.sh build --db=mssql --transaction-xa-enabled=false

Is there any bug?

XcrigX Nov 18, 2022

@tiagopsantos - I don't recall the specifics - but I can report that I run Keycloak against Azure SQL without any problems now. I'm using this image: quay.io/keycloak/keycloak:18.0.2
I'm setting the DB and turning off XA transactions using the build command, not using ENV variables, FWIW. I don't recall if that was necessary to make it work though. I set everything else via ENV vars, like the DB connection string.

tiagopsantos Nov 20, 2022

Should be the same via build command and ENV, but found out that some configs are not right in the docs (#15571).

I'm having some random LocalXA errors in a cluster node.

ARJUNA016061: TransactionImple.enlistResource - XAResource.start returned: XAException.XAER_RMERR for 
com.microsoft.sqlserver.jdbc.SQLServerConnection.setAutoCommit(SQLServerConnection.java:4178)
io.agroal.pool.ConnectionHandler.transactionStart(ConnectionHandler.java:324)
io.agroal.narayana.LocalXAResource.start(LocalXAResource.java:47)

My configuration is a cluster with 4 nodes cluster, using Infinispan (default config), set the pool size of 10 in each node (EDIT: will remove this limit... just in case).
Azure SQL is Standard S2: 50 DTUs - accept 120 connections

Followed #https://www.keycloak.org/keycloak-benchmark/kubernetes-guide/latest/load-behavior
#For the best performance, the values for the initial, minimal and maximum DB connection pool size should all be equal. This avoids creating new DB connections when a new request comes in which is costly.

Navigation Menu

Repository navigation

Trying to use keycloak with SQL Server #12271

Uh oh!

{{title}}

Uh oh!

Replies: 2 comments 4 replies

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{editor}}'s edit

{{editor}}'s edit

Uh oh!

Select a reply

Uh oh!

Trying to use keycloak with SQL Server #12271

Uh oh!

akloster May 31, 2022

Replies: 2 comments · 4 replies

Uh oh!

XcrigX Jun 6, 2022

Uh oh!

XcrigX Jun 7, 2022

Uh oh!

XcrigX Jun 7, 2022

Uh oh!

tiagopsantos Nov 18, 2022

Uh oh!

XcrigX Nov 18, 2022

Uh oh!

Uh oh!

tiagopsantos Nov 20, 2022

akloster
May 31, 2022

Replies: 2 comments 4 replies

XcrigX
Jun 6, 2022

XcrigX
Jun 7, 2022