12:38 p.m.
Has anyone configured Keycloak to use Microsoft SQL server where Keycloak is running on a
linux machine? I can make it work correctly with Postgres, but cannot get it to work
with Microsoft SQL.
Here is my part of the standalone-ha.xml file:
<subsystem xmlns="urn:jboss:domain:datasources:4.0">
<datasources>
<datasource jndi-name="java:/MSSQLDS"
pool-name="MSSQLDS" enabled="true">
<connection-url>jdbc:sqlserver://(IP
ADDRESS):1433;databaseName=keycloak</connection-url>
<driver>sqlserver</driver>
<security>
<user-name>username</user-name>
<password>password</password>
</security>
<validation>
<valid-connection-checker
class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"></valid-connection-checker>
</validation>
</datasource>
<drivers>
<driver name="sqlserver"
module="com.microsoft">
<xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>
</driver>
</drivers>
</datasources>
</subsystem>
and here is where I use the datasource.
<spi name="connectionsJpa">
<provider name="default" enabled="true">
<properties>
<property name="dataSource"
value="java:/MSSQLDS"/>
<property name="initializeEmpty"
value="true"/>
<property name="migrationStrategy"
value="manual"/>
<property name="migrationExport"
value="${jboss.home.dir}/keycloak-database-update.sql"/>
</properties>
</provider>
</spi>
and I am using JDBC_PING to handle multiple systems since the environment I want to use
does not support multicast.
<protocol type="JDBC_PING">
<property name="datasource_jndi_name">java:/MSSQLDS</property>
<property name="initialize_sql">
CREATE TABLE IF NOT EXISTS jgroupsping (
own_addr VARCHAR(200) NOT NULL,
cluster_name VARCHAR(200) NOT NULL,
ping_data BYTEA DEFAULT NULL,
PRIMARY KEY (own_addr, cluster_name)
)
</property>
</protocol>
And this is the error when I start it up.
13:39:48,758 WARN [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (MSC
service thread 1-6) IJ000604: Throwable while attempting to get a new connection: null:
javax.resource.ResourceException: IJ031084: Unable to create connection
at
org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createLocalManagedConnection(LocalManagedConnectionFactory.java:343)
at
org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.getLocalManagedConnection(LocalManagedConnectionFactory.java:350)
at
org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:285)
at
org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreConcurrentLinkedDequeManagedConnectionPool.createConnectionEventListener(SemaphoreConcurrentLinkedDequeManagedConnectionPool.java:1319)
at
org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreConcurrentLinkedDequeManagedConnectionPool.getConnection(SemaphoreConcurrentLinkedDequeManagedConnectionPool.java:496)
at
org.jboss.jca.core.connectionmanager.pool.AbstractPool.getSimpleConnection(AbstractPool.java:626)
at
org.jboss.jca.core.connectionmanager.pool.AbstractPool.getConnection(AbstractPool.java:598)
at
org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:590)
at
org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.getManagedConnection(TxConnectionManagerImpl.java:429)
at
org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:747)
at
org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:138)
at
org.jboss.as.connector.subsystems.datasources.WildFlyDataSource.getConnection(WildFlyDataSource.java:66)
at org.jgroups.protocols.JDBC_PING.getConnection(JDBC_PING.java:348)
at
org.jgroups.protocols.JDBC_PING.attemptSchemaInitialization(JDBC_PING.java:298)
at org.jgroups.protocols.JDBC_PING.init(JDBC_PING.java:130)
at
org.jgroups.stack.ProtocolStack.initProtocolStack(ProtocolStack.java:860)
at org.jgroups.stack.ProtocolStack.setup(ProtocolStack.java:481)
at org.jgroups.JChannel.init(JChannel.java:853)
at org.jgroups.JChannel.<init>(JChannel.java:159)
at
org.jboss.as.clustering.jgroups.JChannelFactory$1.run(JChannelFactory.java:95)
at
org.jboss.as.clustering.jgroups.JChannelFactory$1.run(JChannelFactory.java:92)
at
org.wildfly.security.manager.WildFlySecurityManager.doChecked(WildFlySecurityManager.java:636)
at
org.jboss.as.clustering.jgroups.JChannelFactory.createChannel(JChannelFactory.java:98)
at
org.wildfly.clustering.jgroups.spi.service.ChannelBuilder.start(ChannelBuilder.java:78)
at
org.jboss.msc.service.ServiceControllerImpl$StartTask.startService(ServiceControllerImpl.java:1948)
at
org.jboss.msc.service.ServiceControllerImpl$StartTask.run(ServiceControllerImpl.java:1881)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.NoClassDefFoundError: javax/xml/bind/DatatypeConverter
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:4098)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:3160)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.access$100(SQLServerConnection.java:43)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:3123)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1981)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1628)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1459)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:773)
at
com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1168)
at
org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createLocalManagedConnection(LocalManagedConnectionFactory.java:319)
... 28 more
Caused by: java.lang.ClassNotFoundException: javax.xml.bind.DatatypeConverter from [Module
"com.microsoft:main" from local module loader @66133adc (finder: local module
finder @7bfcd12c (roots:
/opt/keycloak/modules,/opt/keycloak/modules/system/layers/keycloak,/opt/keycloak/modules/system/layers/base))]
at
org.jboss.modules.ModuleClassLoader.findClass(ModuleClassLoader.java:198)
at
org.jboss.modules.ConcurrentClassLoader.performLoadClassUnchecked(ConcurrentClassLoader.java:363)
at
org.jboss.modules.ConcurrentClassLoader.performLoadClass(ConcurrentClassLoader.java:351)
at
org.jboss.modules.ConcurrentClassLoader.loadClass(ConcurrentClassLoader.java:93)
... 40 more
13:39:48,760 ERROR [org.jgroups.protocols.JDBC_PING] (MSC service thread 1-6) Could not
open connection to database: java.sql.SQLException: javax.resource.ResourceException:
IJ000453: Unable to get managed connection for java:/MSSQLDS
at
org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:146)
at
org.jboss.as.connector.subsystems.datasources.WildFlyDataSource.getConnection(WildFlyDataSource.java:66)
at org.jgroups.protocols.JDBC_PING.getConnection(JDBC_PING.java:348)
at
org.jgroups.protocols.JDBC_PING.attemptSchemaInitialization(JDBC_PING.java:298)
at org.jgroups.protocols.JDBC_PING.init(JDBC_PING.java:130)
at
org.jgroups.stack.ProtocolStack.initProtocolStack(ProtocolStack.java:860)
at org.jgroups.stack.ProtocolStack.setup(ProtocolStack.java:481)
at org.jgroups.JChannel.init(JChannel.java:853)
at org.jgroups.JChannel.<init>(JChannel.java:159)
at
org.jboss.as.clustering.jgroups.JChannelFactory$1.run(JChannelFactory.java:95)
at
org.jboss.as.clustering.jgroups.JChannelFactory$1.run(JChannelFactory.java:92)
at
org.wildfly.security.manager.WildFlySecurityManager.doChecked(WildFlySecurityManager.java:636)
at
org.jboss.as.clustering.jgroups.JChannelFactory.createChannel(JChannelFactory.java:98)
at
org.wildfly.clustering.jgroups.spi.service.ChannelBuilder.start(ChannelBuilder.java:78)
at
org.jboss.msc.service.ServiceControllerImpl$StartTask.startService(ServiceControllerImpl.java:1948)
at
org.jboss.msc.service.ServiceControllerImpl$StartTask.run(ServiceControllerImpl.java:1881)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: javax.resource.ResourceException: IJ000453: Unable to get managed connection
for java:/MSSQLDS
at
org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:656)
at
org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.getManagedConnection(TxConnectionManagerImpl.java:429)
at
org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:747)
at
org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:138)
... 18 more
Caused by: javax.resource.ResourceException: IJ031084: Unable to create connection
at
org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createLocalManagedConnection(LocalManagedConnectionFactory.java:343)
at
org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.getLocalManagedConnection(LocalManagedConnectionFactory.java:350)
at
org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:285)
at
org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreConcurrentLinkedDequeManagedConnectionPool.createConnectionEventListener(SemaphoreConcurrentLinkedDequeManagedConnectionPool.java:1319)
at
org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreConcurrentLinkedDequeManagedConnectionPool.getConnection(SemaphoreConcurrentLinkedDequeManagedConnectionPool.java:496)
at
org.jboss.jca.core.connectionmanager.pool.AbstractPool.getSimpleConnection(AbstractPool.java:626)
at
org.jboss.jca.core.connectionmanager.pool.AbstractPool.getConnection(AbstractPool.java:598)
at
org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:590)
... 21 more
Caused by: java.lang.NoClassDefFoundError: javax/xml/bind/DatatypeConverter
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:4098)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:3160)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.access$100(SQLServerConnection.java:43)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:3123)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1981)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1628)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1459)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:773)
at
com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1168)
at
org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createLocalManagedConnection(LocalManagedConnectionFactory.java:319)
... 28 more
Caused by: java.lang.ClassNotFoundException: javax.xml.bind.DatatypeConverter from [Module
"com.microsoft:main" from local module loader @66133adc (finder: local module
finder @7bfcd12c (roots:
/opt/keycloak/modules,/opt/keycloak/modules/system/layers/keycloak,/opt/keycloak/modules/system/layers/base))]
at
org.jboss.modules.ModuleClassLoader.findClass(ModuleClassLoader.java:198)
at
org.jboss.modules.ConcurrentClassLoader.performLoadClassUnchecked(ConcurrentClassLoader.java:363)
at
org.jboss.modules.ConcurrentClassLoader.performLoadClass(ConcurrentClassLoader.java:351)
at
org.jboss.modules.ConcurrentClassLoader.loadClass(ConcurrentClassLoader.java:93)
... 40 more