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
3:05 a.m.
It looks like the module for your MS SQL jdbc driver lacks visibility of
javax.xml.bind classes.
Try adding the dependency on module 'javax.xml.bind.api' to your MSSQL jdbc
driver module.
In module.xml of the driver module simply add:
<module name="javax.xml.bind.api" />
On Thu, Feb 16, 2017 at 7:38 PM, Reed Lewis <RLewis(a)carbonite.com> wrote:
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
9:28 a.m.
I’m currently running Keycloak on OS X and connecting to MS SQL Server.
I have the following working setup (hopefully the steps below will help you out)
1. Create the jdbc module.xml
File: modules/system/layers/base/com/microsoft/sqlserver/main/module.xml
<?xml version="1.0" encoding="UTF-8"?>
<module xmlns="urn:jboss:module:1.3"
name="com.microsoft.sqlserver">
<resources>
<resource-root path="sqljdbc.jar"/>
</resources>
<dependencies>
<module name="javax.api"/>
<module name="javax.transaction.api"/>
<module name="javax.servlet.api" optional="true"/>
</dependencies>
</module>
2. Get the MS Sql JDBC driver from
https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc...
copy sqljdbc.jar to modules/system/layers/base/com/microsoft/sqlserver/main/
3. Change configuration for keycloak to use MS SQL (ie: standalone.xml or
standalone-ha.xml)
<subsystem xmlns="urn:jboss:domain:datasources:4.0">
<datasources>
.....
<datasource jndi-name="java:jboss/datasources/KeycloakMsDS"
pool-name="KeycloakMsDS" enabled="true"
use-java-context="true">
<connection-url>jdbc:sqlserver://SQL_SERVER_URL:PORT;DatabaseName=keycloak</connection-url>
<driver>mssql</driver>
<security>
<user-name>USERNAME</user-name>
<password>PASSWORD</password>
</security>
<validation>
<background-validation>true</background-validation>
<background-validation-millis>30000</background-validation-millis>
</validation>
</datasource>
<drivers>
<driver name="mssql"
module="com.microsoft.sqlserver">
<xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</xa-datasource-class>
</driver>
.....
</drivers>
</datasources>
</subsystem>
…..
<subsystem xmlns="urn:jboss:domain:keycloak-server:1.1">
.....
<spi name="connectionsJpa">
<provider name="default" enabled="true">
<properties>
<property name="dataSource"
value="java:jboss/datasources/KeycloakMsDS"/>
<property name="initializeEmpty"
value="true"/>
<property name="migrationStrategy"
value="update"/>
<property name="migrationExport"
value="${jboss.home.dir}/keycloak-database-update.sql"/>
</properties>
</provider>
</spi>
</subsystem>
Cameron CROCKETT
Senior Software Engineer
Custom Card Systems, Inc.
On Feb 17, 2017, at 3:05 AM, Marko Strukelj
<mstrukel@redhat.com<mailto:mstrukel@redhat.com>> wrote:
It looks like the module for your MS SQL jdbc driver lacks visibility of
javax.xml.bind classes.
Try adding the dependency on module 'javax.xml.bind.api' to your MSSQL jdbc
driver module.
In module.xml of the driver module simply add:
<module name="javax.xml.bind.api" />
On Thu, Feb 16, 2017 at 7:38 PM, Reed Lewis
<RLewis@carbonite.com<mailto:RLewis@carbonite.com>> wrote:
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@lists.jboss.org<mailto:keycloak-user@lists.jboss.org>
https://lists.jboss.org/mailman/listinfo/keycloak-user
_______________________________________________
keycloak-user mailing list
keycloak-user@lists.jboss.org<mailto:keycloak-user@lists.jboss.org>
https://lists.jboss.org/mailman/listinfo/keycloak-user
10:42 a.m.
Thank you. This helped and it works now.
From: Cameron Crockett <Cameron.Crockett(a)ABCorp.com>
Date: Friday, February 17, 2017 at 10:28 AM
To: Marko Strukelj <mstrukel(a)redhat.com>
Cc: Reed Lewis <RLewis(a)carbonite.com>, "keycloak-user(a)lists.jboss.org"
<keycloak-user(a)lists.jboss.org>
Subject: Re: [keycloak-user] Using Keycloak on Linux with A Microsoft SQL server
I’m currently running Keycloak on OS X and connecting to MS SQL Server.
I have the following working setup (hopefully the steps below will help you out)
1. Create the jdbc module.xml
File: modules/system/layers/base/com/microsoft/sqlserver/main/module.xml
<?xml version="1.0" encoding="UTF-8"?>
<module xmlns="urn:jboss:module:1.3"
name="com.microsoft.sqlserver">
<resources>
<resource-root path="sqljdbc.jar"/>
</resources>
<dependencies>
<module name="javax.api"/>
<module name="javax.transaction.api"/>
<module name="javax.servlet.api" optional="true"/>
</dependencies>
</module>
2. Get the MS Sql JDBC driver from
https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc...
copy sqljdbc.jar to modules/system/layers/base/com/microsoft/sqlserver/main/
3. Change configuration for keycloak to use MS SQL (ie: standalone.xml or
standalone-ha.xml)
<subsystem xmlns="urn:jboss:domain:datasources:4.0">
<datasources>
.....
<datasource jndi-name="java:jboss/datasources/KeycloakMsDS"
pool-name="KeycloakMsDS" enabled="true"
use-java-context="true">
<connection-url>jdbc:sqlserver://SQL_SERVER_URL:PORT;DatabaseName=keycloak</connection-url><sqlserver://SQL_SERVER_URL:PORT;DatabaseName=keycloak%3c/connection-url%3e>
<driver>mssql</driver>
<security>
<user-name>USERNAME</user-name>
<password>PASSWORD</password>
</security>
<validation>
<background-validation>true</background-validation>
<background-validation-millis>30000</background-validation-millis>
</validation>
</datasource>
<drivers>
<driver name="mssql"
module="com.microsoft.sqlserver">
<xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</xa-datasource-class>
</driver>
.....
</drivers>
</datasources>
</subsystem>
…..
<subsystem xmlns="urn:jboss:domain:keycloak-server:1.1">
.....
<spi name="connectionsJpa">
<provider name="default" enabled="true">
<properties>
<property name="dataSource"
value="java:jboss/datasources/KeycloakMsDS"/>
<property name="initializeEmpty"
value="true"/>
<property name="migrationStrategy"
value="update"/>
<property name="migrationExport"
value="${jboss.home.dir}/keycloak-database-update.sql"/>
</properties>
</provider>
</spi>
</subsystem>
Cameron CROCKETT
Senior Software Engineer
Custom Card Systems, Inc.
On Feb 17, 2017, at 3:05 AM, Marko Strukelj
<mstrukel@redhat.com<mailto:mstrukel@redhat.com>> wrote:
It looks like the module for your MS SQL jdbc driver lacks visibility of
javax.xml.bind classes.
Try adding the dependency on module 'javax.xml.bind.api' to your MSSQL jdbc
driver module.
In module.xml of the driver module simply add:
<module name="javax.xml.bind.api" />
On Thu, Feb 16, 2017 at 7:38 PM, Reed Lewis
<RLewis@carbonite.com<mailto:RLewis@carbonite.com>> wrote:
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@lists.jboss.org<mailto:keycloak-user@lists.jboss.org>
https://lists.jboss.org/mailman/listinfo/keycloak-user
_______________________________________________
keycloak-user mailing list
keycloak-user@lists.jboss.org<mailto:keycloak-user@lists.jboss.org>
https://lists.jboss.org/mailman/listinfo/keycloak-user
2799
days inactive
2800
days old
3 comments
3 participants
participants (3)
-
Cameron Crockett -
Marko Strukelj -
Reed Lewis