Re: [keycloak-user] Using Keycloak on Linux with A Microsoft SQL server

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(SemaphoreConcurrentLinkedDeque ManagedConnectionPool.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(SemaphoreConcurrentLinkedDeque ManagedConnectionPool.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 _______________________________________________ keycloak-user mailing list keycloak-user(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/keycloak-user