Errors in JAS log -
Sometimes you could be unlucky and find the following errors in the log of the Jazz Authentication server:
com.ibm.ws.security.oauth20.plugins.db.CachedDBOidcTokenStore E Internal error adding token: A truncation error was encountered trying to shrink VARCHAR ‘{xor}OiYVNz0YPDYQNhUWCiUWbhE2FWZxOiYVJTsIFjYQNhVtBQcRNxMxDSk&’ to length 2048.
java.sql.SQLDataException
java.sql.SQLDataException
How to fix that?
Accepted answer
The root cause of the problem is that the derby database field containing the TOKENSTRING is not large enough to hold the data.
This is likely caused by the information received from the LDAP where users potentially has a lot of group memberships.
The quick fix is to extend the field. This can simply be done by using an SQL tool and run an ALTER TABLE:
ALTER TABLE oauthdbschema.OAUTH20CACHE MODIFY TOKENSTRING VARCHAR(16384)
The original value was 2048. In derby the max size for VARCHAR is 32768
If this is not enough and you now end up where your tokenstrings are larger than the 32768 you need to switch to a new database type.
The following sample is for replacing your existing JAS Derby DB with Oracle:
In short you need to export your JAS registration data, create an oracle database, modify the keys of the exported data and of your teamserver.properties and reimport this data in your new oracle DB
The guideline also includes steps for unregistering lqe and ldx and re-registering them after the operation as they do not have a client secret but get the information from JTS
First create the new oracle DB:
--- Adaptation required !!!!!! ---
CREATE TABLESPACE OAUTH DATAFILE '/home/oracle/tablespaces/OAUTH.dbf' SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE TEMPORARY TABLESPACE OAUTH_TMP TEMPFILE '/home/oracle/tablespaces/OAUTH_TMP.dbf' SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
--- Please adapt the above statements to create the tablespace according to your environment ---
CREATE USER OAUTHDBSCHEMA IDENTIFIED BY <your password> DEFAULT TABLESPACE OAUTH QUOTA UNLIMITED ON OAUTH TEMPORARY TABLESPACE OAUTH_TMP;
GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CREATE VIEW TO OAUTHDBSCHEMA;
---- CREATE TABLES ----
CREATE TABLE OAuthDBSchema.OAUTH20CACHE
(
LOOKUPKEY VARCHAR(256) NOT NULL,
UNIQUEID VARCHAR(128) NOT NULL,
COMPONENTID VARCHAR(256) NOT NULL,
TYPE VARCHAR(64) NOT NULL,
SUBTYPE VARCHAR(64),
CREATEDAT NUMBER(19,0),
LIFETIME INT,
EXPIRES NUMBER(19,0),
TOKENSTRING CLOB DEFAULT ‘{}’ NOT NULL,
CLIENTID VARCHAR(64) NOT NULL,
USERNAME VARCHAR(64) NOT NULL,
SCOPE VARCHAR(512) NOT NULL,
REDIRECTURI VARCHAR(2048),
STATEID VARCHAR(64) NOT NULL,
EXTENDEDFIELDS CLOB DEFAULT '{}' NOT NULL
)
TABLESPACE OAUTH STORAGE(INITIAL 50K);
CREATE TABLE OAuthDBSchema.OAUTH20CLIENTCONFIG
(
COMPONENTID VARCHAR(256) NOT NULL,
CLIENTID VARCHAR(256) NOT NULL,
CLIENTSECRET VARCHAR(256),
DISPLAYNAME VARCHAR(256),
REDIRECTURI VARCHAR(2048),
ENABLED INT,
CLIENTMETADATA CLOB DEFAULT '{}' NOT NULL
) TABLESPACE OAUTH STORAGE(INITIAL 50K);
CREATE TABLE OAuthDBSchema.OAUTH20CONSENTCACHE (
CLIENTID VARCHAR(256) NOT NULL,
USERID VARCHAR(256),
PROVIDERID VARCHAR(256) NOT NULL,
SCOPE VARCHAR(1024) NOT NULL,
EXPIRES NUMBER(19,0),
EXTENDEDFIELDS CLOB DEFAULT '{}' NOT NULL
) TABLESPACE OAUTH STORAGE(INITIAL 50K);
---- ADD CONSTRAINTS ----
ALTER TABLE OAuthDBSchema.OAUTH20CACHE
ADD CONSTRAINT PK_LOOKUPKEY PRIMARY KEY (LOOKUPKEY);
ALTER TABLE OAuthDBSchema.OAUTH20CLIENTCONFIG
ADD CONSTRAINT PK_COMPIDCLIENTID PRIMARY KEY (COMPONENTID,CLIENTID);
---- CREATE INDEXES ----
CREATE INDEX OAUTH20CACHE_EXPIRES ON OAuthDBSchema.OAUTH20CACHE (EXPIRES ASC);
---- GRANT PRIVILEGES ----
---- UNCOMMENT THE FOLLOWING IF YOU USE AN ACCOUNT OTHER THAN ADMINISTRATOR FOR DB ACCESS ----
-- Change dbuser to the account you want to use to access your database
GRANT ALL ON OAuthDBSchema.OAUTH20CACHE TO OAUTHDBSCHEMA;
GRANT ALL ON OAuthDBSchema.OAUTH20CLIENTCONFIG TO OAUTHDBSCHEMA;
GRANT ALL ON OAuthDBSchema.OAUTH20CONSENTCACHE TO OAUTHDBSCHEMA;
---- END OF GRANT PRIVILIGES ----
Now follow these steps to replace the database:
1.) navigate to the CLI directory, [JazzAuthServer]/cli
2.) backup/export the existing client registration
./lsclient -u [username]:[password] -a https://[FQDN or localhost]:[port]/oidc/endpoint/jazzop/registration > /path/to/registration.json
sample:
./lsclient -u admin:admin -a https://myfulldomain.com/oidc/endpoint/jazzop/registration > /opt/ibm/jas-exported-registration.json
3.) backup your entire JAS install directory -just in case
4.) for LDX and LQE:
a. Remove the Data Sources
b. Run Compaction
c. Un-register the applications from JTS > Admin page
d. Stop your applications
5.) navigate to your JAS install directory, [JazzAuthServer]
./stop-jazz
6.) navigate to your jazzopp server directory, [JazzAuthServer]/wlp/usr/servers/jazzopp
7.) update your configuration to use the new database
In App config xml replace the derby library and datasource with your oracle database:
appConfig.xml
<jdbcDriver id="oracle" libraryRef="ORAJCC4LIB"/>
<library id="ORAJCC4LIB" filesetRef="orajcc4" apiTypeVisibility="spec,ibm-api,third-party"/>
<fileset dir="${shared.config.dir}/lib/global" id="orajcc4" includes="ojdbc6.jar"/>
<dataSource id="OAUTH2ORA" jndiName="jdbc/oauthProvider" jdbcDriverRef="oracle">
<properties.oracle password="******" databaseName="ORCL" user="OAUTHDBSCHEMA" portNumber="1521" serverName="yourOracle.com"/>
</dataSource>
<oauthProvider id="JazzOP" authorizationGrantLifetime="604801" accessTokenLifetime="7201" customLoginURL="/jazzop/form/login" autoAuthorize="true" httpsRequired="true">
<autoAuthorizeClient>client01</autoAuthorizeClient>
<databaseStore dataSourceRef="OAUTH2ORA"/>
</oauthProvider>
Remember to copy in the ojdbc8.jar driver to the /opt/IBM/JazzAuthServer/wlp/usr/shared/config/lib/global (linux)
8.) update your registration backup to have a secret for each application, this secret needs to be used in the future so it should be something written down. Please use a length of 32 or more characters for client secret.
change the line:
"client_secret" : "*",
to
"client_secret" : "NewClientSecret1234567890abcefgh",
9.) navigate to your JAS install directory, [JazzAuthServer]
10.) start JAS
./start-jazz
Check the logs carefully!!! - if there are any errors it could indicate problems with your new oracle DB configuration - this must be fixed before proceeding
11.) navigate to the cli directory, [JazzAuthServer]/cli
12.) import the registration backup
./ldclient -u [username]:[password] -a https://[FQDN or localhost]:[port]/oidc/endpoint/jazzop -c /path/to/registartion.json
´13.) update your applications' teamserver.properties to match the client secret as defined earlier and cycle the applications to confirm any changes are picked up by the JVM
com.ibm.team.repository.servlet.sso_clientSecret=NewClientSecret1234567890abcefgh
In some cases this value will be in the app.properties and will be called jsa.client.secret
14.) start your applications and log in, navigate to the server settings and update the client registry with the updated secret again and save to obfuscate the secret in the properties file
15.) on LDX and LQE:
a. Change the Config Mode Parameter to True in lqe.properties file , configMode=true to enable the apps to be re-registered
b. start your applications
c. Register LQE and LDX with JTS (use the jts setup and yes the user is called lqe_user for both applications)
d. Add the Data Sources
These instructions were created with the help of IBM Support and Matthias Buettgen