[keycloak-user] Using Keycloak on Linux with A Microsoft SQL server
Reed Lewis
RLewis at carbonite.com
Fri Feb 17 11:42:17 EST 2017
Thank you. This helped and it works now.
From: Cameron Crockett <Cameron.Crockett at ABCorp.com>
Date: Friday, February 17, 2017 at 10:28 AM
To: Marko Strukelj <mstrukel at redhat.com>
Cc: Reed Lewis <RLewis at carbonite.com>, "keycloak-user at lists.jboss.org" <keycloak-user at 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-driver-for-sql-server
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 at redhat.com<mailto:mstrukel at 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 at carbonite.com<mailto:RLewis at 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 at lists.jboss.org<mailto:keycloak-user at lists.jboss.org>
https://lists.jboss.org/mailman/listinfo/keycloak-user
_______________________________________________
keycloak-user mailing list
keycloak-user at lists.jboss.org<mailto:keycloak-user at lists.jboss.org>
https://lists.jboss.org/mailman/listinfo/keycloak-user
More information about the keycloak-user
mailing list