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

Friday, 17 February 2017

Thank you.   This helped and it works now.

From: Cameron Crockett <Cameron.Crockett(a)ABCorp.com&gt;
Date: Friday, February 17, 2017 at 10:28 AM
To: Marko Strukelj <mstrukel(a)redhat.com&gt;
Cc: Reed Lewis <RLewis(a)carbonite.com&gt;, &quot;keycloak-user(a)lists.jboss.org&quot;
<keycloak-user(a)lists.jboss.org&gt;
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

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

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