InformixHQ H2 existing database encryption

5. RE: InformixHQ H2 existing database encryption

Like

Milan Rafaj

Posted 12/16/24 04:13 AM

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)

--

Original Message

Original Message:
Sent: 12/16/2024 3:56:00 AM
From: Shubham Kapoor
Subject: RE: InformixHQ H2 existing database encryption

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
------------------------------

Original Message:
Sent: Mon December 16, 2024 02:59 AM
From: Milan Rafaj
Subject: InformixHQ H2 existing database encryption

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)

--

6. RE: InformixHQ H2 existing database encryption

Like

Shubham Kapoor

Posted 12/16/24 05:46 AM

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
------------------------------

Original Message

Original Message:
Sent: Mon December 16, 2024 04:12 AM
From: Milan Rafaj
Subject: InformixHQ H2 existing database encryption

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)

--

Original Message:
Sent: 12/16/2024 3:56:00 AM
From: Shubham Kapoor
Subject: RE: InformixHQ H2 existing database encryption

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
------------------------------

Original Message:
Sent: Mon December 16, 2024 02:59 AM
From: Milan Rafaj
Subject: InformixHQ H2 existing database encryption

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

7. RE: InformixHQ H2 existing database encryption

Like

Milan Rafaj

Posted 12/16/24 05:51 AM

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)

--

Original Message

Original Message:
Sent: 12/16/2024 5:46:00 AM
From: Shubham Kapoor
Subject: RE: InformixHQ H2 existing database encryption

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
------------------------------

Original Message:
Sent: Mon December 16, 2024 04:12 AM
From: Milan Rafaj
Subject: InformixHQ H2 existing database encryption

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)

--

Original Message:
Sent: 12/16/2024 3:56:00 AM
From: Shubham Kapoor
Subject: RE: InformixHQ H2 existing database encryption

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
------------------------------

Original Message:
Sent: Mon December 16, 2024 02:59 AM
From: Milan Rafaj
Subject: InformixHQ H2 existing database encryption

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)

--

8. RE: InformixHQ H2 existing database encryption

Like

Milan Rafaj

Posted 12/17/24 09:01 AM

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)

Original Message

Original Message:
Sent: 12/16/2024 3:56:00 AM
From: Shubham Kapoor
Subject: RE: InformixHQ H2 existing database encryption

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
------------------------------

Original Message:
Sent: Mon December 16, 2024 02:59 AM
From: Milan Rafaj
Subject: InformixHQ H2 existing database encryption

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)

--

9. RE: InformixHQ H2 existing database encryption

Like

Shubham Kapoor

Posted 12/17/24 09:11 AM

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
------------------------------

Original Message

Original Message:
Sent: Tue December 17, 2024 09:00 AM
From: Milan Rafaj
Subject: InformixHQ H2 existing database encryption

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)

Original Message:
Sent: 12/16/2024 3:56:00 AM
From: Shubham Kapoor
Subject: RE: InformixHQ H2 existing database encryption

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
------------------------------

Original Message:
Sent: Mon December 16, 2024 02:59 AM
From: Milan Rafaj
Subject: InformixHQ H2 existing database encryption

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

10. RE: InformixHQ H2 existing database encryption

Like

Milan Rafaj

Posted 12/17/24 09:20 AM

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)

Original Message

Original Message:
Sent: 12/17/2024 9:11:00 AM
From: Shubham Kapoor
Subject: RE: InformixHQ H2 existing database encryption

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
------------------------------

Original Message:
Sent: Tue December 17, 2024 09:00 AM
From: Milan Rafaj
Subject: InformixHQ H2 existing database encryption

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)

Original Message:
Sent: 12/16/2024 3:56:00 AM
From: Shubham Kapoor
Subject: RE: InformixHQ H2 existing database encryption

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
------------------------------

Original Message:
Sent: Mon December 16, 2024 02:59 AM
From: Milan Rafaj
Subject: InformixHQ H2 existing database encryption

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)

--

Informix

Informix