OpenIDM ms sql connector generation giving error

This topic has 8 replies, 2 voices, and was last updated 6 years, 5 months ago by Bill Nelson.

  • Author
    Posts
  • #6859
     praveenalla
    Participant

    I am trying to generate sqljdbc4-osgi.jar file using steps given in forgerock documentation but getting below error although I didnt give any output file name in my below command.

    I have downloaded this file biz.aQute.bnd-3.2.0.jar and renamed it as bnd.jar before running below command.

    C:\praveen\downloads\openidm-3.1.0\openidm\db\mssql\scripts>java -jar bnd.jar wr
    ap –properties sqljdbc4.bnd sqljdbc4.jar
    —————–
    Errors
    000: Output file C:\praveen\downloads\openidm-3.1.0\openidm\db\mssql\scripts\sql
    jdbc4.jar and source file C:\praveen\downloads\openidm-3.1.0\openidm\db\mssql\sc
    ripts\sqljdbc4.jar are the same file, they must be different

    Please any help will be appreciated as this is a road block for my evaluation of the OpenIDM project to use for our application.

    Thanks,
    Praveen

    #6871
     Bill Nelson
    Participant

    I found problems with later versions of the SQL driver. I would recommend downloading the biz.aQute.bnd.jar jar (not the biz.aQute.bnd-3.2.0.jar) and use that.

    Complete instructions can be found here (I believe this may be a cut/paste, however from FR’s site):

    https://dl.dropboxusercontent.com/u/13293536/openidm/Bundling%20MSSQL%20for%20OpenIDM.docx

    and I have placed my MS SQL OSGI jar file here:

    https://dl.dropboxusercontent.com/u/13293536/openidm/sqljdbc4-osgi.jar

    Connecting with MS SQL is not without it’s challenges, but ultimately, it works fine – either as a Repo or as a connected resource.

    Hope this helps,

    bill

    #6879
     praveenalla
    Participant

    Thanks Bill for the quick response. My project has a requirement of using SQL Server DB as repository and also to connect externally for synchronization of repo data.

    After using the jar file you gave I am runnning into a different issue now when I place the jar in bundle folder and start OpenIDM server.

    In the startup log I am getting below exception although jar file has the driver class not found here. could this be due to some configuration wrong in the repo.jdbc.json ? giving below the errors in log and contents of my repo.jdbc.json as well.

    Log content :
    Jan 15, 2016 9:38:17 AM org.forgerock.openidm.crypto.impl.Activator start
    INFO: Registered Crypto Update Service
    Jan 15, 2016 9:38:18 AM org.forgerock.openidm.config.persistence.ConfigBootstrapHelper getRepoBootConfig
    INFO: Bootstrapping with settings from configuration file C:\praveen\downloads\openidm-3.1.0\openidm\conf\repo.jdbc.json
    Jan 15, 2016 9:38:18 AM org.forgerock.openidm.repo.jdbc.impl.Activator start
    INFO: Bootstrapping JDBC repository
    Jan 15, 2016 9:38:18 AM org.forgerock.openidm.repo.jdbc.impl.JDBCRepoService getDataSource
    INFO: Using DB connection configured via Driver Manager with Driver com.microsoft.sqlserver.jdbc.SQLServerDriver and URL
    Jan 15, 2016 9:38:18 AM org.forgerock.openidm.repo.jdbc.impl.JDBCRepoService getDataSource
    SEVERE: Could not find configured database driver com.microsoft.sqlserver.jdbc.SQLServerDriver to start repository
    java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver not found by org.forgerock.openidm.repo-jdbc [12]
    at org.apache.felix.framework.BundleWiringImpl.findClassOrResourceByDelegation(BundleWiringImpl.java:1532)
    at org.apache.felix.framework.BundleWiringImpl.access$400(BundleWiringImpl.java:75)
    at org.apache.felix.framework.BundleWiringImpl$BundleClassLoader.loadClass(BundleWiringImpl.java:1955)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:248)
    at java.lang.Class.forName0(Native Method)
    at java.lang.Class.forName(Class.java:169)
    at org.forgerock.openidm.repo.jdbc.impl.JDBCRepoService.getDataSource(JDBCRepoService.java:1033)
    at org.forgerock.openidm.repo.jdbc.impl.JDBCRepoService.init(JDBCRepoService.java:877)
    at org.forgerock.openidm.repo.jdbc.impl.JDBCRepoService.getRepoBootService(JDBCRepoService.java:780)
    at org.forgerock.openidm.repo.jdbc.impl.Activator.start(Activator.java:65)
    at org.apache.felix.framework.util.SecureAction.startActivator(SecureAction.java:645)
    at org.apache.felix.framework.Felix.activateBundle(Felix.java:2146)
    at org.apache.felix.framework.Felix.startBundle(Felix.java:2064)
    at org.apache.felix.framework.Felix.setActiveStartLevel(Felix.java:1291)
    at org.apache.felix.framework.FrameworkStartLevelImpl.run(FrameworkStartLevelImpl.java:304)
    at java.lang.Thread.run(Thread.java:619)
    Jan 15, 2016 9:38:18 AM org.forgerock.openidm.repo.jdbc.impl.JDBCRepoService init
    WARNING: Configuration invalid, can not start JDBC repository.
    org.forgerock.openidm.config.enhanced.InvalidException: Could not find configured database driver com.microsoft.sqlserver.jdbc.SQLServerDriver to start repository
    at org.forgerock.openidm.repo.jdbc.impl.JDBCRepoService.getDataSource(JDBCRepoService.java:1037)
    at org.forgerock.openidm.repo.jdbc.impl.JDBCRepoService.init(JDBCRepoService.java:877)
    at org.forgerock.openidm.repo.jdbc.impl.JDBCRepoService.getRepoBootService(JDBCRepoService.java:780)
    at org.forgerock.openidm.repo.jdbc.impl.Activator.start(Activator.java:65)
    at org.apache.felix.framework.util.SecureAction.startActivator(SecureAction.java:645)
    at org.apache.felix.framework.Felix.activateBundle(Felix.java:2146)
    at org.apache.felix.framework.Felix.startBundle(Felix.java:2064)
    at org.apache.felix.framework.Felix.setActiveStartLevel(Felix.java:1291)
    at org.apache.felix.framework.FrameworkStartLevelImpl.run(FrameworkStartLevelImpl.java:304)
    at java.lang.Thread.run(Thread.java:619)
    Caused by: java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver not found by org.forgerock.openidm.repo-jdbc [12]
    at org.apache.felix.framework.BundleWiringImpl.findClassOrResourceByDelegation(BundleWiringImpl.java:1532)
    at org.apache.felix.framework.BundleWiringImpl.access$400(BundleWiringImpl.java:75)
    at org.apache.felix.framework.BundleWiringImpl$BundleClassLoader.loadClass(BundleWiringImpl.java:1955)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:248)
    at java.lang.Class.forName0(Native Method)
    at java.lang.Class.forName(Class.java:169)
    at org.forgerock.openidm.repo.jdbc.impl.JDBCRepoService.getDataSource(JDBCRepoService.java:1033)
    … 9 more
    Jan 15, 2016 9:38:23 AM org.forgerock.openidm.config.persistence.RepoPersistenceManager checkReady
    INFO: Failed to bootstrap repo, returned null
    Jan 15, 2016 9:38:28 AM org.forgerock.openidm.config.persistence.RepoPersistenceManager checkReady
    INFO: Failed to bootstrap repo, returned null
    Jan 15, 2016 9:38:33 AM org.forgerock.openidm.config.persistence.RepoPersistenceManager checkReady
    INFO: Failed to bootstrap repo, returned null
    Jan 15, 2016 9:38:38 AM org.forgerock.openidm.config.persistence.RepoPersistenceManager checkReady
    INFO: Failed to bootstrap repo, returned null
    Jan 15, 2016 9:38:43 AM org.forgerock.openidm.config.persistence.RepoPersistenceManager checkReady
    INFO: Failed to bootstrap repo, returned null
    Jan 15, 2016 9:38:48 AM org.forgerock.openidm.config.persistence.RepoPersistenceManager checkReady
    INFO: Failed to bootstrap repo, returned null
    Jan 15, 2016 9:38:48 AM org.forgerock.openidm.external.rest.RestService activate
    INFO: External REST connectivity started.
    Jan 15, 2016 9:38:48 AM org.forgerock.openidm.info.impl.HealthService activate
    INFO: OpenIDM Health Service component is activated.
    Jan 15, 2016 9:38:49 AM org.forgerock.openidm.provisioner.openicf.impl.ConnectorInfoProviderService activate
    INFO: ConnectorInfoProviderService with OpenICF Version[1.4] is activated.
    Jan 15, 2016 9:38:49 AM org.forgerock.openidm.logging.LogServiceTracker logEntry
    SEVERE: Bundle: com.microsoft.sqlserver.jdbc [165] FrameworkEvent ERROR
    org.apache.felix.log.LogException: org.osgi.framework.BundleException: Unresolved constraint in bundle com.microsoft.sqlserver.jdbc [165]: Execution environment not supported: JavaSE-1.7
    at org.apache.felix.framework.Felix.resolveBundleRevision(Felix.java:3974)
    at org.apache.felix.framework.Felix.startBundle(Felix.java:2037)
    at org.apache.felix.framework.Felix.setActiveStartLevel(Felix.java:1291)
    at org.apache.felix.framework.FrameworkStartLevelImpl.run(FrameworkStartLevelImpl.java:304)
    at java.lang.Thread.run(Thread.java:619)
    Jan 15, 2016 9:38:49 AM org.ops4j.pax.web.service.internal.Activator start
    INFO: EventAdmin support enabled, servlet events will be postet to topics.
    Jan 15, 2016 9:38:49 AM org.ops4j.pax.web.service.internal.Activator start
    INFO: LogService support enabled, log events will be created.
    Jan 15, 2016 9:38:49 AM org.ops4j.pax.web.service.internal.Activator start
    INFO: Pax Web started
    Jan 15, 2016 9:38:53 AM org.forgerock.openidm.config.persistence.RepoPersistenceManager checkReady
    INFO: Failed to bootstrap repo, returned null
    Jan 15, 2016 9:38:58 AM org.forgerock.openidm.config.persistence.RepoPersistenceManager checkReady
    INFO: Failed to bootstrap repo, returned null
    Jan 15, 2016 9:39:03 AM org.forgerock.openidm.config.persistence.RepoPersistenceManager checkReady
    INFO: Failed to bootstrap repo, returned null
    Jan 15, 2016 9:39:04 AM org.forgerock.openidm.info.impl.HealthService$4 run
    SEVERE: OpenIDM failure during startup, ACTIVE_NOT_READY: Not all modules started [] [org.forgerock.openidm.repo-jdbc] []
    Jan 15, 2016 9:39:08 AM org.forgerock.openidm.config.persistence.RepoPersistenceManager checkReady
    INFO: Failed to bootstrap repo, returned null

    repo.jdbc.json:

    {
    “connection” : {
    “dbType” : “SQLSERVER”,
    “jndiName” : “”,
    “driverClass” : “com.microsoft.sqlserver.jdbc.SQLServerDriver”,
    “jdbcUrl” : “jdbc:sqlserver://brdvwscv005.int.mgc.com:1433;databaseName=Sitemndr”,
    “username” : “M1AppUser”,
    “password” : “[email protected]”,
    “maxBatchSize” : 100,
    “maxTxRetry” : 5,
    “enableConnectionPool” : true,
    “connectionTimeoutInMs” : 30000
    }
    }

    #6881
     Bill Nelson
    Participant

    The only thing I see different in your repo configuration and mind is the fact that I specify the “defaultCatalog” param as well (see below). It has been some time since I first configured this but after much trial and error, I finalized on this configuration.

    “connection” : {
    “dbType” : “SQLSERVER”,
    “jndiName” : “”,
    “driverClass” : “com.microsoft.sqlserver.jdbc.SQLServerDriver”,
    “jdbcUrl” : “jdbc:sqlserver://HOST.DOMAIN:1433;databaseName=OpenIDM”,
    “username” : “USER”,
    “password” : “PASSWORD”,
    “defaultCatalog” : “OpenIDM”,
    “maxBatchSize” : 100,
    “maxTxRetry” : 5,
    “enableConnectionPool” : true,
    “connectionTimeoutInMs” : 30000
    },

    I only provide this information to give you a warm/fuzzy regarding your configuration. The error in your log seems to be telling you what the issue is: “SEVERE: Could not find configured database driver”. This indicates OpenIDM cannot find the driver.

    1. Did you copy the driver into the openidm/bundles folder?
    2. Did you restart the server after you did?
    3. Have you made any changes to the configuration which may tell OpenIDM to look elsewhere?

    bill

    #6889
     praveenalla
    Participant

    Tried a lot to figure out if classpath to OpenIDM is not having my jar file but cant figure out.

    I did the same steps as in https://backstage.forgerock.com/#!/docs/openidm/3.1.0/install-guide/chap-repository#repository-mssql but OpenIDM startup is giving ClassNotFoundError when my class and jar file are present in bundle folder .

    To answer your questions .

    1. Yes, I did copy the jar file into openidm/bundles folder
    2. I did restart the server after putting jar into bundles folder
    3. I did not make any other configuration changes which would affect the classpath.

    I tried the same with a brand new OpenIDM-3.1.0 installation and changed only the conf folder and bundle folder and still getting the same exception as below. ClassNotFoundException is confusing for me since the jar file is clearly in the bundle folder. May be it is due to some configuration issue inside repo.jdbc.json but I am not able to understand what is wrong for me to get this error.

    One thing to note is I am using a unix sql server we have and not installed a windows sql server database. But I dont think that should give a ClassNotFoundException I am getting in this case.

    Log file:

    Jan 15, 2016 2:53:10 PM org.forgerock.openidm.config.crypto.ConfigCrypto <init>
    INFO: Using keystore alias openidm-sym-default to handle config encryption
    Jan 15, 2016 2:53:10 PM org.forgerock.openidm.config.persistence.Activator start
    INFO: OpenIDM is starting from C:\praveen\downloads\openidm-3.1.0\openidm
    Jan 15, 2016 2:53:10 PM org.forgerock.openidm.crypto.impl.CryptoServiceImpl activate
    INFO: Activating cryptography service of type: JCEKS provider: location: security/keystore.jceks
    Jan 15, 2016 2:53:10 PM org.forgerock.openidm.crypto.impl.CryptoServiceImpl activate
    INFO: Available cryptography key: openidm-sym-default
    Jan 15, 2016 2:53:10 PM org.forgerock.openidm.crypto.impl.CryptoServiceImpl activate
    INFO: Available cryptography key: openidm-localhost
    Jan 15, 2016 2:53:10 PM org.forgerock.openidm.crypto.impl.CryptoServiceImpl activate
    INFO: CryptoService is initialized with 2 keys.
    Jan 15, 2016 2:53:10 PM org.forgerock.openidm.crypto.impl.Activator start
    INFO: Registered Crypto Service
    Jan 15, 2016 2:53:10 PM org.forgerock.openidm.crypto.impl.Activator start
    INFO: Registered Crypto Update Service
    Jan 15, 2016 2:53:10 PM org.forgerock.openidm.config.persistence.ConfigBootstrapHelper getRepoBootConfig
    INFO: Bootstrapping with settings from configuration file C:\praveen\downloads\openidm-3.1.0\openidm\conf\repo.jdbc.json
    Jan 15, 2016 2:53:10 PM org.forgerock.openidm.repo.jdbc.impl.Activator start
    INFO: Bootstrapping JDBC repository
    Jan 15, 2016 2:53:10 PM org.forgerock.openidm.repo.jdbc.impl.JDBCRepoService getDataSource
    INFO: Using DB connection configured via Driver Manager with Driver com.microsoft.sqlserver.jdbc.SQLServerDriver and URL
    Jan 15, 2016 2:53:10 PM org.forgerock.openidm.repo.jdbc.impl.JDBCRepoService getDataSource
    SEVERE: Could not find configured database driver com.microsoft.sqlserver.jdbc.SQLServerDriver to start repository
    java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver not found by org.forgerock.openidm.repo-jdbc [12]
    at org.apache.felix.framework.BundleWiringImpl.findClassOrResourceByDelegation(BundleWiringImpl.java:1532)
    at org.apache.felix.framework.BundleWiringImpl.access$400(BundleWiringImpl.java:75)
    at org.apache.felix.framework.BundleWiringImpl$BundleClassLoader.loadClass(BundleWiringImpl.java:1955)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:248)
    at java.lang.Class.forName0(Native Method)
    at java.lang.Class.forName(Class.java:169)
    at org.forgerock.openidm.repo.jdbc.impl.JDBCRepoService.getDataSource(JDBCRepoService.java:1033)
    at org.forgerock.openidm.repo.jdbc.impl.JDBCRepoService.init(JDBCRepoService.java:877)
    at org.forgerock.openidm.repo.jdbc.impl.JDBCRepoService.getRepoBootService(JDBCRepoService.java:780)
    at org.forgerock.openidm.repo.jdbc.impl.Activator.start(Activator.java:65)
    at org.apache.felix.framework.util.SecureAction.startActivator(SecureAction.java:645)
    at org.apache.felix.framework.Felix.activateBundle(Felix.java:2146)
    at org.apache.felix.framework.Felix.startBundle(Felix.java:2064)
    at org.apache.felix.framework.Felix.setActiveStartLevel(Felix.java:1291)
    at org.apache.felix.framework.FrameworkStartLevelImpl.run(FrameworkStartLevelImpl.java:304)
    at java.lang.Thread.run(Thread.java:619)
    Jan 15, 2016 2:53:10 PM org.forgerock.openidm.repo.jdbc.impl.JDBCRepoService init
    WARNING: Configuration invalid, can not start JDBC repository.
    org.forgerock.openidm.config.enhanced.InvalidException: Could not find configured database driver com.microsoft.sqlserver.jdbc.SQLServerDriver to start repository
    at org.forgerock.openidm.repo.jdbc.impl.JDBCRepoService.getDataSource(JDBCRepoService.java:1037)
    at org.forgerock.openidm.repo.jdbc.impl.JDBCRepoService.init(JDBCRepoService.java:877)
    at org.forgerock.openidm.repo.jdbc.impl.JDBCRepoService.getRepoBootService(JDBCRepoService.java:780)
    at org.forgerock.openidm.repo.jdbc.impl.Activator.start(Activator.java:65)
    at org.apache.felix.framework.util.SecureAction.startActivator(SecureAction.java:645)
    at org.apache.felix.framework.Felix.activateBundle(Felix.java:2146)
    at org.apache.felix.framework.Felix.startBundle(Felix.java:2064)
    at org.apache.felix.framework.Felix.setActiveStartLevel(Felix.java:1291)
    at org.apache.felix.framework.FrameworkStartLevelImpl.run(FrameworkStartLevelImpl.java:304)
    at java.lang.Thread.run(Thread.java:619)
    Caused by: java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver not found by org.forgerock.openidm.repo-jdbc [12]
    at org.apache.felix.framework.BundleWiringImpl.findClassOrResourceByDelegation(BundleWiringImpl.java:1532)
    at org.apache.felix.framework.BundleWiringImpl.access$400(BundleWiringImpl.java:75)
    at org.apache.felix.framework.BundleWiringImpl$BundleClassLoader.loadClass(BundleWiringImpl.java:1955)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:248)
    at java.lang.Class.forName0(Native Method)
    at java.lang.Class.forName(Class.java:169)
    at org.forgerock.openidm.repo.jdbc.impl.JDBCRepoService.getDataSource(JDBCRepoService.java:1033)
    … 9 more

    Below is the content in my repo.jdbc.json

    {
    “connection” : {
    “dbType” : “SQLSERVER”,
    “jndiName” : “”,
    “driverClass” : “com.microsoft.sqlserver.jdbc.SQLServerDriver”,
    “jdbcUrl” : “jdbc:sqlserver://brdvwscv005.int.mgc.com:1433;instanceName=default;databaseName=Sitemndr”,
    “username” : “M1AppUser”,
    “password” : “[email protected]”,
    “defaultCatalog” : “Sitemndr”,
    “maxBatchSize” : 100,
    “maxTxRetry” : 5,
    “enableConnectionPool” : true,
    “connectionTimeoutInMs” : 30000
    },
    “queries” : {
    “genericTables” : {
    “credential-query” : “SELECT fullobject FROM ${_dbSchema}.${_mainTable} obj INNER JOIN ${_dbSchema}.${_propTable} prop ON obj.id = prop.${_mainTable}_id INNER JOIN ${_dbSchema}.objecttypes objtype ON objtype.id = obj.objecttypes_id WHERE prop.propkey=’/userName’ AND prop.propvalue = ${username} AND objtype.objecttype = ${_resource}”,
    “find-by-id” : “SELECT fullobject FROM ${_dbSchema}.${_mainTable} obj INNER JOIN ${_dbSchema}.objecttypes objtype ON objtype.id = obj.objecttypes_id WHERE obj.objectid = ${id} AND objtype.objecttype = ${_resource}”,
    “get-by-field-value” : “SELECT fullobject FROM ${_dbSchema}.${_mainTable} obj INNER JOIN ${_dbSchema}.${_propTable} prop ON obj.id = prop.${_mainTable}_id INNER JOIN ${_dbSchema}.objecttypes objtype ON objtype.id = obj.objecttypes_id WHERE prop.propkey=’/’ + ${field} AND prop.propvalue = ${value} AND objtype.objecttype = ${_resource}”,
    “get-users-of-direct-role” : “select obj.fullobject from ${_dbSchema}.${_mainTable} obj INNER JOIN ${_dbSchema}.objecttypes objtype ON objtype.id = obj.objecttypes_id INNER JOIN ${_dbSchema}.${_propTable} prop ON obj.id = prop.${_mainTable}_id where objtype.objecttype = ${_resource} AND prop.propkey LIKE ‘/roles/%’ AND prop.propvalue = ${role}”,
    “query-all-ids” : “SELECT obj.objectid FROM ${_dbSchema}.${_mainTable} obj INNER JOIN ${_dbSchema}.objecttypes objtype ON obj.objecttypes_id = objtype.id WHERE objtype.objecttype = ${_resource}”,
    “query-all” : “SELECT obj.fullobject FROM ${_dbSchema}.${_mainTable} obj INNER JOIN ${_dbSchema}.objecttypes objtype ON obj.objecttypes_id = objtype.id WHERE objtype.objecttype = ${_resource}”,
    “query-all-ids-count” : “SELECT COUNT(obj.objectid) AS total FROM ${_dbSchema}.${_mainTable} obj INNER JOIN ${_dbSchema}.objecttypes objtype ON obj.objecttypes_id = objtype.id WHERE objtype.objecttype = ${_resource}”,
    “query-all-count” : “SELECT COUNT(obj.fullobject) AS total FROM ${_dbSchema}.${_mainTable} obj INNER JOIN ${_dbSchema}.objecttypes objtype ON obj.objecttypes_id = objtype.id WHERE objtype.objecttype = ${_resource}”,
    “for-userName” : “SELECT fullobject FROM ${_dbSchema}.${_mainTable} obj INNER JOIN ${_dbSchema}.${_propTable} prop ON obj.id = prop.${_mainTable}_id INNER JOIN ${_dbSchema}.objecttypes objtype ON objtype.id = obj.objecttypes_id WHERE prop.propkey=’/userName’ AND prop.propvalue = ${uid} AND objtype.objecttype = ${_resource}”,
    “credential-internaluser-query” : “SELECT credprop.${_mainTable}_id, credprop.propkey, credprop.propvalue FROM ${_dbSchema}.${_propTable} credprop INNER JOIN ${_dbSchema}.${_mainTable} obj ON obj.id = credprop.${_mainTable}_id INNER JOIN ${_dbSchema}.${_propTable} conditionprop ON obj.id = conditionprop.${_mainTable}_id INNER JOIN ${_dbSchema}.objecttypes objtype ON objtype.id = obj.objecttypes_id WHERE credprop.propkey=’/userPassword’ AND conditionprop.propkey=’/_id’ AND conditionprop.propvalue = ${username} AND objtype.objecttype = ${_resource}”,
    “scan-tasks” : “SELECT fullobject FROM ${_dbSchema}.${_mainTable} obj INNER JOIN ${_dbSchema}.${_propTable} prop ON obj.id = prop.${_mainTable}_id LEFT OUTER JOIN ${_dbSchema}.${_propTable} complete ON obj.id = complete.${_mainTable}_id AND complete.propkey=${taskState.completed} INNER JOIN ${_dbSchema}.objecttypes objtype ON objtype.id = obj.objecttypes_id WHERE ( prop.propkey=${property} AND prop.propvalue < ${condition.before} AND objtype.objecttype = ${_resource} ) AND ( complete.propvalue is NULL )”,
    “query-cluster-failed-instances” : “SELECT fullobject FROM ${_dbSchema}.${_mainTable} obj INNER JOIN ${_dbSchema}.${_propTable} prop ON obj.id = prop.${_mainTable}_id INNER JOIN ${_dbSchema}.${_propTable} state ON obj.id = state.${_mainTable}_id WHERE (prop.propkey = ‘/timestamp’ AND prop.propvalue <= ${timestamp}) AND (state.propkey = ‘/state’ AND (state.propvalue = ‘1’ OR state.propvalue = ‘2’))”,
    “query-cluster-instances” : “SELECT fullobject FROM ${_dbSchema}.${_mainTable} obj INNER JOIN ${_dbSchema}.${_propTable} prop ON obj.id = prop.${_mainTable}_id WHERE (prop.propkey = ‘/type’ AND prop.propvalue = ‘state’)”,
    “query-cluster-events” : “SELECT fullobject FROM ${_dbSchema}.${_mainTable} obj INNER JOIN ${_dbSchema}.${_propTable} prop1 ON obj.id = prop1.${_mainTable}_id INNER JOIN ${_dbSchema}.${_propTable} prop2 ON obj.id = prop2.${_mainTable}_id WHERE (prop1.propkey = ‘/type’ AND prop1.propvalue = ‘event’) AND (prop2.propkey = ‘/instanceId’ AND prop2.propvalue = ${instanceId})”,
    “get-managed-users” : “;DECLARE @dir varchar(4) = ${orderByDir};WITH results AS (SELECT rowNo = ROW_NUMBER() OVER( ORDER BY CASE WHEN @dir = ‘asc’ THEN orderby.propvalue END asc, CASE WHEN @dir = ‘desc’ THEN orderby.propvalue END desc ), obj.fullobject FROM ${_dbSchema}.${_mainTable} obj INNER JOIN ${_dbSchema}.objecttypes objTypes ON obj.objecttypes_id = objTypes.id and objTypes.objecttype = ${_resource} INNER JOIN ${_dbSchema}.${_propTable} orderby on obj.id = orderby.managedobjects_id and orderby.propkey = ‘/’ + ${orderBy}) SELECT * FROM results WHERE rowNo BETWEEN ${int:_pagedResultsOffset}+1 AND ${int:_pagedResultsOffset}+${int:_pageSize}”,
    “get-managed-users-filtered” : “;DECLARE @dir varchar(4) = ${orderByDir};WITH results AS (SELECT rowNo = ROW_NUMBER() OVER( ORDER BY CASE WHEN @dir = ‘asc’ THEN orderby.propvalue END asc, CASE WHEN @dir = ‘desc’ THEN orderby.propvalue END desc ), obj.fullobject FROM ${_dbSchema}.${_mainTable} obj INNER JOIN ${_dbSchema}.objecttypes objTypes ON obj.objecttypes_id = objTypes.id and objTypes.objecttype = ${_resource} INNER JOIN ${_dbSchema}.${_propTable} orderby on obj.id = orderby.managedobjects_id and orderby.propkey = ‘/’ + ${orderBy} INNER JOIN ${_dbSchema}.${_propTable} userName on obj.id = userName.${_mainTable}_id and userName.propkey = ‘/userName’ INNER JOIN ${_dbSchema}.${_propTable} givenName on obj.id = givenName.${_mainTable}_id and givenName.propkey = ‘/givenName’ INNER JOIN ${_dbSchema}.${_propTable} sn on obj.id = sn.${_mainTable}_id and sn.propkey = ‘/sn’ INNER JOIN ${_dbSchema}.${_propTable} mail on obj.id = mail.${_mainTable}_id and mail.propkey = ‘/mail’ INNER JOIN ${_dbSchema}.${_propTable} accountStatus on obj.id = accountStatus.${_mainTable}_id and accountStatus.propkey = ‘/accountStatus’ WHERE userName.propvalue LIKE ${userName} + ‘%’ AND mail.propvalue LIKE ${mail} + ‘%’ AND givenName.propvalue LIKE ${givenName} + ‘%’ AND sn.propvalue LIKE ${sn} + ‘%’ AND accountStatus.propvalue LIKE ${accountStatus} + ‘%’) SELECT * FROM results WHERE rowNo BETWEEN ${int:_pagedResultsOffset}+1 AND ${int:_pagedResultsOffset}+${int:_pageSize}”,
    “get-managed-users-count” : “SELECT ‘{ \”total\”:’ + CAST(COUNT(*) AS VARCHAR(256)) + ‘}’ as fullobject FROM ${_dbSchema}.${_mainTable} obj INNER JOIN ${_dbSchema}.objecttypes objTypes ON obj.objecttypes_id = objTypes.id and objTypes.objecttype = ${_resource}”,
    “get-managed-users-filtered-count” : “SELECT ‘{ \”total\”:’ + CAST(COUNT(*) AS VARCHAR(256)) + ‘}’ as fullobject FROM ${_dbSchema}.${_mainTable} obj INNER JOIN ${_dbSchema}.objecttypes objTypes ON obj.objecttypes_id = objTypes.id and objTypes.objecttype = ${_resource} INNER JOIN ${_dbSchema}.${_propTable} userName on obj.id = userName.${_mainTable}_id and userName.propkey = ‘/userName’ INNER JOIN ${_dbSchema}.${_propTable} givenName on obj.id = givenName.${_mainTable}_id and givenName.propkey = ‘/givenName’ INNER JOIN ${_dbSchema}.${_propTable} sn on obj.id = sn.${_mainTable}_id and sn.propkey = ‘/sn’ INNER JOIN ${_dbSchema}.${_propTable} mail on obj.id = mail.${_mainTable}_id and mail.propkey = ‘/mail’ INNER JOIN ${_dbSchema}.${_propTable} accountStatus on obj.id = accountStatus.${_mainTable}_id and accountStatus.propkey = ‘/accountStatus’ WHERE userName.propvalue LIKE ${userName} + ‘%’ AND mail.propvalue LIKE ${mail} + ‘%’ AND givenName.propvalue LIKE ${givenName} + ‘%’ AND sn.propvalue LIKE ${sn} + ‘%’ AND accountStatus.propvalue LIKE ${accountStatus} + ‘%'”
    },
    “explicitTables” : {
    “get-users-of-direct-role” : “select objectid from ${_dbSchema}.${_table} where roles like ‘$’+${role}+’%'”,
    “query-all-ids” : “SELECT objectid FROM ${_dbSchema}.${_table}”,
    “for-internalcredentials” : “select * FROM ${_dbSchema}.${_table} WHERE objectid = ${uid}”,
    “get-notifications-for-user” : “select * FROM ${_dbSchema}.${_table} WHERE receiverid = ${userId} order by createdate desc”,
    “credential-query” : “SELECT * FROM ${_dbSchema}.${_table} WHERE objectid = ${username}”,
    “credential-internaluser-query” : “SELECT objectid, pwd, roles FROM ${_dbSchema}.${_table} WHERE objectid = ${username}”,
    “links-for-firstId” : “SELECT * FROM ${_dbSchema}.${_table} WHERE linktype = ${linkType} AND firstid = ${firstId}”,
    “links-for-secondId” : “SELECT * FROM ${_dbSchema}.${_table} WHERE linktype = ${linkType} AND secondid = ${secondId}”,
    “links-for-linkType” : “SELECT * FROM ${_dbSchema}.${_table} WHERE linktype = ${linkType}”,
    “scan-tasks” : “SELECT * FROM ${_dbSchema}.${_table} WHERE ${property} < ${condition.before} AND ${taskState.completed} is NULL”,
    “audit-by-mapping” : “SELECT * FROM ${_dbSchema}.auditrecon WHERE mapping = ${mappingName}”,
    “audit-by-recon-id” : “SELECT * FROM ${_dbSchema}.auditrecon WHERE reconid = ${reconId}”,
    “audit-by-recon-id-type” : “SELECT * FROM ${_dbSchema}.auditrecon WHERE reconid = ${reconId} AND entrytype = ${entryType}”,
    “audit-by-recon-id-situation” : “SELECT * FROM ${_dbSchema}.auditrecon WHERE reconid = ${reconId} AND situation = ${situation}”,
    “audit-by-activity-parent-action” : “SELECT * FROM ${_dbSchema}.auditactivity WHERE parentactionid = ${parentActionId}”,
    “query-all” : “SELECT * FROM ${_dbSchema}.${_table}”,
    “get-recons” : “SELECT reconid, activitydate, mapping, message FROM ${_dbSchema}.auditrecon WHERE mapping LIKE ${includeMapping} AND mapping NOT LIKE ${excludeMapping} AND entrytype = ‘summary’ ORDER BY activitydate DESC”,
    “audit-last-recon-for-mapping” : “SELECT TOP 1 * FROM ${_dbSchema}.auditrecon WHERE entrytype = ‘start’ AND mapping LIKE ${mapping} and reconaction <> ‘reconById’ ORDER BY activitydate DESC”,
    “audit-by-recon-id-situations-latest” : “SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY activitydate DESC) AS RowNum FROM ${_dbSchema}.auditrecon WHERE reconid = ${reconId} AND (${completed} = ” OR activitydate <= ${completed}) AND situation IN (${list:situations})) AS t WHERE t.RowNum BETWEEN ${int:_pagedResultsOffset}+1 AND ${int:_pagedResultsOffset}+${int:_pageSize}”,
    “audit-by-recon-id-situations-latest-filtered” : “SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY activitydate DESC) AS RowNum FROM ${_dbSchema}.auditrecon WHERE reconid = ${reconId} AND (${completed} = ” OR activitydate <= ${completed}) AND situation IN (${list:situations}) AND (sourceobjectid IN (${list:filteredIds}) OR targetobjectid IN (${list:filteredIds}))) AS t WHERE t.RowNum BETWEEN ${int:_pagedResultsOffset}+1 AND ${int:_pagedResultsOffset}+${int:_pageSize}”
    }
    },
    “commands” : {
    “genericTables” : { },
    “explicitTables” : {
    “purge-by-recon-ids-to-keep” : “DELETE FROM ${_dbSchema}.auditrecon WHERE mapping LIKE ${includeMapping} AND mapping NOT LIKE ${excludeMapping} AND reconid NOT IN (${list:reconIds})”,
    “purge-by-recon-expired” : “DELETE FROM ${_dbSchema}.auditrecon WHERE mapping LIKE ${includeMapping} AND mapping NOT LIKE ${excludeMapping} AND activitydate < ${timestamp}”
    }
    },
    “resourceMapping” : {
    “default” : {
    “mainTable” : “genericobjects”,
    “propertiesTable” : “genericobjectproperties”,
    “searchableDefault” : true
    },
    “genericMapping” : {
    “managed/*” : {
    “mainTable” : “managedobjects”,
    “propertiesTable” : “managedobjectproperties”,
    “searchableDefault” : true
    },
    “managed/user” : {
    “mainTable” : “managedobjects”,
    “propertiesTable” : “managedobjectproperties”,
    “searchableDefault” : false,
    “properties” : {
    “/userName” : {
    “searchable” : true
    },
    “/givenName” : {
    “searchable” : true
    },
    “/sn” : {
    “searchable” : true
    },
    “/mail” : {
    “searchable” : true
    },
    “/accountStatus” : {
    “searchable” : true
    },
    “/roles” : {
    “searchable” : true
    },
    “/sunset” : {
    “searchable” : true
    }
    }
    },
    “scheduler” : {
    “mainTable” : “schedulerobjects”,
    “propertiesTable” : “schedulerobjectproperties”,
    “searchableDefault” : true
    },
    “cluster” : {
    “mainTable” : “clusterobjects”,
    “propertiesTable” : “clusterobjectproperties”,
    “searchableDefault” : true
    }
    },
    “explicitMapping” : {
    “link” : {
    “table” : “links”,
    “objectToColumn” : {
    “_id” : “objectid”,
    “_rev” : “rev”,
    “linkType” : “linktype”,
    “firstId” : “firstid”,
    “secondId” : “secondid”
    }
    },
    “ui/notification” : {
    “table” : “uinotification”,
    “objectToColumn” : {
    “_id” : “objectid”,
    “_rev” : “rev”,
    “requester” : “requester”,
    “requesterId” : “requesterid”,
    “receiverId” : “receiverid”,
    “createDate” : “createdate”,
    “notificationType” : “notificationtype”,
    “notificationSubtype” : “notificationsubtype”,
    “message” : “message”
    }
    },
    “internal/user” : {
    “table” : “internaluser”,
    “objectToColumn” : {
    “_id” : “objectid”,
    “_rev” : “rev”,
    “password” : “pwd”,
    “roles” : “roles”
    }
    },
    “audit/activity” : {
    “table” : “auditactivity”,
    “objectToColumn” : {
    “_id” : “objectid”,
    “activityId” : “activityid”,
    “timestamp” : “activitydate”,
    “action” : “activity”,
    “message” : “message”,
    “objectId” : “subjectid”,
    “rev” : “subjectrev”,
    “rootActionId” : “rootactionid”,
    “parentActionId” : “parentactionid”,
    “requester” : “requester”,
    “approver” : “approver”,
    “before” : “subjectbefore”,
    “after” : “subjectafter”,
    “status” : “status”,
    “changedFields” : “changedfields”,
    “passwordChanged” : “passwordchanged”
    }
    },
    “audit/recon” : {
    “table” : “auditrecon”,
    “objectToColumn” : {
    “_id” : “objectid”,
    “entryType” : “entrytype”,
    “rootActionId” : “rootactionid”,
    “action” : “activity”,
    “message” : “message”,
    “reconciling” : “reconciling”,
    “reconId” : “reconid”,
    “reconAction” : “reconaction”,
    “situation” : “situation”,
    “sourceObjectId” : “sourceobjectid”,
    “status” : “status”,
    “targetObjectId” : “targetobjectid”,
    “ambiguousTargetObjectIds” : “ambiguoustargetobjectids”,
    “timestamp” : “activitydate”,
    “actionId” : “actionid”,
    “exception” : “exceptiondetail”,
    “mapping” : “mapping”,
    “messageDetail” : {
    “column” : “messagedetail”,
    “type” : “JSON_MAP”
    }
    }
    },
    “audit/sync” : {
    “table” : “auditsync”,
    “objectToColumn” : {
    “_id” : “objectid”,
    “status” : “status”,
    “situation” : “situation”,
    “action” : “activity”,
    “actionId” : “actionid”,
    “rootActionId” : “rootactionid”,
    “sourceObjectId” : “sourceobjectid”,
    “targetObjectId” : “targetobjectid”,
    “timestamp” : “activitydate”,
    “mapping” : “mapping”,
    “exception” : “exceptiondetail”,
    “message” : “message”,
    “messageDetail” : {
    “column” : “messagedetail”,
    “type” : “JSON_MAP”
    }
    }
    },
    “audit/access” : {
    “table” : “auditaccess”,
    “objectToColumn” : {
    “_id” : “objectid”,
    “action” : “activity”,
    “ip” : “ip”,
    “principal” : “principal”,
    “roles” : “roles”,
    “status” : “status”,
    “timestamp” : “activitydate”
    }
    },
    “security” : {
    “table” : “security”,
    “objectToColumn” : {
    “_id” : “objectid”,
    “_rev” : “rev”,
    “storeString” : “storestring”
    }
    },
    “security/keys” : {
    “table” : “securitykeys”,
    “objectToColumn” : {
    “_id” : “objectid”,
    “_rev” : “rev”,
    “keyPair” : “keypair”
    }
    }
    }
    }
    }

    #6914
     praveenalla
    Participant

    Able to resolve this issue. Thanks a lot Bill.
    The jar file you gave worked for me.

    #6916
     Bill Nelson
    Participant

    What did you do to correct the issue, Praveen?

    #6925
     praveenalla
    Participant

    I was using wrong jar in bundle folder which caused the issue. Once I used the sqljdbc4-osgi.jar sql server connection started working fine.

    #6926
     Bill Nelson
    Participant

    Ahhh.. OK, good to know. Glad you are able to connect now.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic.

©2022 ForgeRock - we provide an identity and access platform to secure every online relationship for the enterprise market, educational sector and even entire countries. Click to view our privacy policy and terms of use.

Log in with your credentials

Forgot your details?