Running keycloak.x with Azure SQL (XA transactions not supported)

[Matysiak Joerg (IOC/PAU1)]

Hi,

 

after mssql support has been added to Keycloak.x in main branch I tried to get it working in our environment.

When running locally with mssql in docker it works fine after installing and configuring the XA extensions (EXEC sp_sqljdbc_xa_install).

 

Unfortunately XA transactions are not supported by Azure SQL, but there is a different kind of distributed transactions supported, called “elastic database transactions”.

See https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-transactions-overview for details.

 

I didn’t manage to get keycloak.x working with these kind of transactions and since the Azure SQL documentation states in the limitations section:

 

“Only client-coordinated transactions from a .NET application are supported.”

 

it seems to be impossible at all.

 

Would there be a way to run keycloak.x safely without XA transactions? (The wildfly based Keycloak didn’t require these extension.)

And if - how is this configured?

 

Mit freundlichen Grüßen / Best regards

Jörg Matysiak

he/him/his
Product Area User Management (IOC/PAU1)
Bosch.IO GmbH | Postfach 30 02 40 | 70442 Stuttgart | GERMANY | www.bosch.io
Besucheradresse: Grönerstraße 5/1 | 71636 Ludwigsburg


Sitz: Berlin, Registergericht: Amtsgericht Charlottenburg; HRB 148411 B
Aufsichtsratsvorsitzender: Stefan Koss; Geschäftsführung: Dr. Stefan Ferber, Dr. Aleksandar Mitrovic, Yvonne Reckling

​

[Thomas Darimont]

Hello Jörg,

could you check which jdbc driver is selected by keycloak.x when you run it against Azure SQL?

According to https://github.com/quarkusio/quarkus/blob/main/extensions/jdbc/jdbc-mssql/deployment/src/main/java/io/quarkus/jdbc/mssql/deployment/MsSQLProcessor.java#L33

the quarkus-jdbc-mssql extension seems to emit two potential driver classes "com.microsoft.sqlserver.jdbc.SQLServerDriver", "com.microsoft.sqlserver.jdbc.SQLServerXADataSource"

Did you try to explicitly use a non xa-jdbc driver class via `quarkus.datasource.jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver`?

Cheers,

Thomas

--
You received this message because you are subscribed to the Google Groups "Keycloak Dev" group.
To unsubscribe from this group and stop receiving emails from it, send an email to keycloak-dev...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/keycloak-dev/DB8PR10MB3829F6D01AB2034587161F50ED699%40DB8PR10MB3829.EURPRD10.PROD.OUTLOOK.COM.

[Matysiak Joerg (IOC/PAU1)]

 

Thanks Thomas, that pointed me into the right direction :)

 

Setting the driver explicitly and also changing the transaction type makes it work with Azure SQL.

 

In case someone has the same problem: I used these environment variables to achieve that:

 

export KC_DB_TX_TYPE=enabled
export KC_DB_DRIVER=com.microsoft.sqlserver.jdbc.SQLServerDriver

 

Kind regards

Jörg