Migrating h2 to keycloak 17.0.0

1

I’m having a h2 keycloak database from previous keycloak.x version 14.0.0 and trying to migrate it to version 17.0.0.

First I copied the db from keycloak_old/data to the new directory keycloak_17/data/h2.

Previous configuration, no username and password is set:

db=h2-file
#db.username = sa
#db.password = keycloak

Configuration of Keycloak 17.0.0:

db=dev-file
#db-username=sa
#db-password=keycloak

I get the following error starting up (wrong username and password):

2022-02-14 13:51:30,110 power QuarkusEntryPoint[31312] WARN  [io.agr.pool] (agroal-11) Datasource '<default>': Falscher Benutzer Name oder Passwort
Wrong user name or password [28000-197]
2022-02-14 13:51:30,110 power QuarkusEntryPoint[31312] WARN  [org.hib.eng.jdb.env.int.JdbcEnvironmentInitiator] (JPA Startup Thread: keycloak-default) HHH000342: Could not obtain connection to query metadata: org.h2.jdbc.JdbcSQLException: Falscher Benutzer Name oder Passwort
Wrong user name or password [28000-197]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
	at org.h2.message.DbException.get(DbException.java:179)
	at org.h2.message.DbException.get(DbException.java:155)
	at org.h2.message.DbException.get(DbException.java:144)
	at org.h2.engine.Engine.validateUserAndPassword(Engine.java:341)
	at org.h2.engine.Engine.createSessionAndValidate(Engine.java:165)
	at org.h2.engine.Engine.createSession(Engine.java:140)
	at org.h2.engine.Engine.createSession(Engine.java:28)
	at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:351)
	at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:124)
	at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:103)
	at org.h2.Driver.connect(Driver.java:69)
	at org.h2.jdbcx.JdbcDataSource.getJdbcConnection(JdbcDataSource.java:189)
	at org.h2.jdbcx.JdbcDataSource.getXAConnection(JdbcDataSource.java:352)
	at io.agroal.pool.ConnectionFactory.createConnection(ConnectionFactory.java:216)
	at io.agroal.pool.ConnectionPool$CreateConnectionTask.call(ConnectionPool.java:513)
	at io.agroal.pool.ConnectionPool$CreateConnectionTask.call(ConnectionPool.java:494)
	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
	at io.agroal.pool.util.PriorityScheduledExecutor.beforeExecute(PriorityScheduledExecutor.java:75)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.base/java.lang.Thread.run(Unknown Source)

Does keycloak 17.0.0 use a default username and password? How should this be migrated?

2

I came up with this solution, which creates a new user sa with default password:

java -cp h2-1.4.197.jar org.h2.tools.Shell -url "jdbc:h2:./keycloakdb" -sql "create user if not exists sa password 'password' admin;"

I tried a generated password instead of the default string “password” and configured the credentials but that didn’t work, it had to be the default password:

db=dev-file
db-username=sa
db-password=GENERATED
3

Thank you @flaviodonze. This was very helpful. I’m going to add to this for anyone else interested. I was updating from version 16 to 19.

The default H2 database username/password is sa/sa in v16, which is what mine was set to. If you try to use v19 and connect with sa/sa, you get ERROR: Wrong user name or password [28000-197].
I’m guessing this is for security reasons? Anyway, to change the password I first connected to the DB using

java -cp h2-1.4.197.jar org.h2.tools.Shell -url "jdbc:h2:./keycloakdb" -user sa -password sa

Then set the password for the connected user with

SET PASSWORD 'password';

Then in my docker-compose:

      - KC_DB_USERNAME=sa
      - KC_DB_PASSWORD=password

After startup, I saw this in the log which seems to indicate the DB migration was successful.

INFO [org.keycloak.quarkus.runtime.storage.legacy.liquibase.QuarkusJpaUpdaterProvider] (main) Updating database. Using changelog META-INF/jpa-changelog-master.xml