InformixHQ H2 existing database encryption

Hello Can be h2 database encrypted after it was created without encryption and InformixHQ configured? Or it should be created encrypted as an empty db? Thanks for tips. Milan Rafaj -- Unless stated otherwise above: Kyndryl Česká republika, spol. s r. o. Sídlo: V Parku 2308/8, Chodov, 148 00 Praha 4, IČ: 096 28 886 Zapsaná v obchodním rejstříku, vedeném Městským soudem v Praze (oddíl C, vložka 339277) Registered address: V Parku 2308/8, Chodov, 148 00 Prague 4 Company ID: 096 28 886 Registered in the Commercial Register maintained by the Municipal Court in Prague (Part C, Entry 339277)

Hello, You can only encrypt h2db as an empty DB for the first time. Once the h2db is created without encryption, you cannot encrypt it, and vice versa. ------------------------------ Shubham Kapoor ------------------------------

Thank you Shubham for confirmation on this behaviout. Is it possinle or are there some plans to export/import content of h2 database for avoiding complete reconfiguration of InfornixHQ server if we need to either change of encryption or move for example from test to production or vice verse? Thanks a lot. Milan Rafaj Senior Lead, Infrastructure/Cloud Architecture Kyndryl Consult +420 737 264 248 www.kyndryl.cz Planned absence/Plánovaná nepřítomnost: 23-31.12.2024 Kyndryl Česká republika, spol. s r. o. Sídlo: Praha 4, Chodov, V Parku 2308/8, PSČ: 148 00, IČ: 14890992 Zapsaná v obchodním rejstříku, vedeném Městským soudem v Praze (oddíl C, vložka 339277) Registered address: Prague 4, Chodov, V Parku, 2308/8, Zip code: 148 00 Company ID: 14890992 Entered in the Commercial Register maintained by the Municipal Court in Prague (Part C, Entry 339277) --

We do have a workaround for this, provided you are running InformixHQ server 2.3.4 and above. Also, you need to terminate/kill/stop the already running InformixHQ server and agents that are using the h2db file. If you are running any other version of the InformixHQ server do let me know. Below export and import commands are from h2 database. Steps: 1) After terminate/kill/stop the already running InformixHQ server and agents take a backup of the existing h2db.mv.db using the below command cp h2db.mv.db h2db.mv.db_backup. 2) Download h2 jar version 2.2.224. You can download it from Maven Repository 3) Once you have the backup and the downloaded h2 jar version 2.2.224 run the below command java -cp {PATH_TO_DOWNLOADED_H2_JAR}/h2-2.2.224.jar org.h2.tools.Script -url "jdbc:h2:./h2db" -script h2db.zip -options compression zip With this command, you can export all your h2db data that are not encrypted into h2db.zip file. 4) Now run the command java -cp {PATH_TO_DOWNLOADED_H2_JAR}/h2-2.2.224.jar org.h2.tools.RunScript -url "jdbc:h2:./h2db; CIPHER=AES " -password "password123 " -script h2db.zip -options compression zip The above command will import all the data from file h2db.zip to the new h2db.mv.db file. Please note in the above command I have used the sample password(you can use your own password). Your password should need to have a space in the end while running the above command. 5) Once you are done with the above steps, edit your InformixHQ server properties file add the below properties, and save the file h2.encrypt.enable=true h2.encrypt.algorithm=AES h2.encrypt.password=password123 Please note while adding these properties you don't need to mention extra space in the "h2.encrypt.password" property. InformixHQ server will handle of its own. Now you can run the InformixHQ server with the encrypted h2db and then the agents. Important Note : Please make sure to take the backup of h2db.mv.db file before running the above operations. Let me know if you face any difficulty. Thanks, ------------------------------ Shubham Kapoor ------------------------------ Thank you Shubham for confirmation on this behaviout. Is it possinle or are there some plans to export/import content of h2 database for avoiding complete reconfiguration of InfornixHQ server if we need to either change of encryption or move for example from test to production or vice verse? Thanks a lot. Milan Rafaj Senior Lead, Infrastructure/Cloud Architecture Kyndryl Consult +420 737 264 248 www.kyndryl.cz Planned absence/Plánovaná nepřítomnost: 23-31.12.2024 Kyndryl Česká republika, spol. s r. o. Sídlo: Praha 4, Chodov, V Parku 2308/8, PSČ: 148 00, IČ: 14890992 Zapsaná v obchodním rejstříku, vedeném Městským soudem v Praze (oddíl C, vložka 339277) Registered address: Prague 4, Chodov, V Parku, 2308/8, Zip code: 148 00 Company ID: 14890992 Entered in the Commercial Register maintained by the Municipal Court in Prague (Part C, Entry 339277) -- Original Message: Sent: 12/16/2024 1:24:00 AM From: Shubham Kapoor Subject: RE: InformixHQ H2 existing database encryption Hello, You can only encrypt h2db as an empty DB for the first time. Once the h2db is created without encryption, you cannot encrypt it, and vice versa. ------------------------------ Shubham Kapoor ------------------------------

Thanks a lot, Shubham I am using InformixHQ 3.0.0.0 Thank you for a procedure. And is it possible somehow to hidden encryption password from InformixHQ server properties file as it was possible witth admin password in earlier versions of IHQ? Milan Rafaj Senior Lead, Infrastructure/Cloud Architecture Kyndryl Consult +420 737 264 248 www.kyndryl.cz Planned absence/Plánovaná nepřítomnost: 23-31.12.2024 Kyndryl Česká republika, spol. s r. o. Sídlo: Praha 4, Chodov, V Parku 2308/8, PSČ: 148 00, IČ: 14890992 Zapsaná v obchodním rejstříku, vedeném Městským soudem v Praze (oddíl C, vložka 339277) Registered address: Prague 4, Chodov, V Parku, 2308/8, Zip code: 148 00 Company ID: 14890992 Entered in the Commercial Register maintained by the Municipal Court in Prague (Part C, Entry 339277) --

3.0.0.0 or 3.0.0? For now, it's not possible as the password is required each time when you start the InformixHQ Server as encrypted h2db to create the connection. Thanks, ------------------------------ Shubham Kapoor ------------------------------

Hello Shubham Of course 3.0.0 thanks for correcting me, well currently password is in plain text, good to know. I hope in future version this security weakness will be solved. Thanks for all explanations and help. Milan Rafaj Senior Lead, Infrastructure/Cloud Architecture Kyndryl Consult +420 737 264 248 www.kyndryl.cz Planned absence/Plánovaná nepřítomnost: 23-31.12.2024 Kyndryl Česká republika, spol. s r. o. Sídlo: Praha 4, Chodov, V Parku 2308/8, PSČ: 148 00, IČ: 14890992 Zapsaná v obchodním rejstříku, vedeném Městským soudem v Praze (oddíl C, vložka 339277) Registered address: Prague 4, Chodov, V Parku, 2308/8, Zip code: 148 00 Company ID: 14890992 Entered in the Commercial Register maintained by the Municipal Court in Prague (Part C, Entry 339277) --

Hello Step 3) was without error but step 4) finished with error: Exception in thread "main" org.h2.jdbc.JdbcSQLNonTransientConnectionException: Encryption error in file "/informix/ifxserver.14.10.FC10W1/hq.3.0.0/hq/h2db.mv.db" [90049-224] at org.h2.message.DbException.getJdbcSQLException(DbException.java:690) at org.h2.message.DbException.getJdbcSQLException(DbException.java:489) at org.h2.message.DbException.get(DbException.java:212) at org.h2.mvstore.db.Store.convertMVStoreException(Store.java:161) at org.h2.mvstore.db.Store.<init>(Store.java:142) at org.h2.engine.Database.<init>(Database.java:326) at org.h2.engine.Engine.openSession(Engine.java:92) at org.h2.engine.Engine.openSession(Engine.java:222) at org.h2.engine.Engine.createSession(Engine.java:201) at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:343) at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:125) at org.h2.util.JdbcUtils.getConnection(JdbcUtils.java:288) at org.h2.util.JdbcUtils.getConnection(JdbcUtils.java:270) at org.h2.tools.RunScript.processRunscript(RunScript.java:270) at org.h2.tools.RunScript.runTool(RunScript.java:137) at org.h2.tools.RunScript.main(RunScript.java:66) Caused by: org.h2.mvstore.MVStoreException: Store header is corrupt: /informix/ifxserver.14.10.FC10W1/hq.3.0.0/hq/h2db.mv.db [2.2.224/6] at org.h2.mvstore.DataUtils.newMVStoreException(DataUtils.java:996) at org.h2.mvstore.RandomAccessStore.readStoreHeader(RandomAccessStore.java:222) at org.h2.mvstore.FileStore.start(FileStore.java:916) at org.h2.mvstore.MVStore.<init>(MVStore.java:289) at org.h2.mvstore.MVStore$Builder.open(MVStore.java:2035) at org.h2.mvstore.db.Store.<init>(Store.java:133) ... 11 more I tried with Java 1.8 and semeru-21 What can be a problem (ihq.3.0.0)? Thank you. Milan Rafaj Senior Lead, Infrastructure/Cloud Architecture Kyndryl Consult +420 737 264 248 www.kyndryl.cz Planned absence/Plánovaná nepřítomnost: 23.-31.12.2024 Kyndryl Česká republika, spol. s r. o. Sídlo: Praha 4, Chodov, V Parku 2308/8, PSČ: 148 00, IČ: 14890992 Zapsaná v obchodním rejstříku, vedeném Městským soudem v Praze (oddíl C, vložka 339277) Registered address: Prague 4, Chodov, V Parku, 2308/8, Zip code: 148 00 Company ID: 14890992 Entered in the Commercial Register maintained by the Municipal Court in Prague (Part C, Entry 339277)

I hope you have already taken the backup before performing the operations. I forgot to mention that before step 4, you need to delete the h2db.mv.db and then perform step 4. But please back up this file before deleting it. Step 4 will create a new h2db.mv.db file with the data in the h2db.zip. ------------------------------ Shubham Kapoor ------------------------------

Thank you, Yes I did not remove h2 db before step 4) Now h2db is encrypted and GUI is working. Milan Rafaj Senior Lead, Infrastructure/Cloud Architecture Kyndryl Consult +420 737 264 248 www.kyndryl.cz Planned absence/Plánovaná nepřítomnost: 23.-31.12.2024 Kyndryl Česká republika, spol. s r. o. Sídlo: Praha 4, Chodov, V Parku 2308/8, PSČ: 148 00, IČ: 14890992 Zapsaná v obchodním rejstříku, vedeném Městským soudem v Praze (oddíl C, vložka 339277) Registered address: Prague 4, Chodov, V Parku, 2308/8, Zip code: 148 00 Company ID: 14890992 Entered in the Commercial Register maintained by the Municipal Court in Prague (Part C, Entry 339277)