Trying to encrypt my h2 database in Spring Boot with version 1.4.195

[Peter wein.peter]

Hi Thomas,

I was following your description:

Connecting to an Encrypted Database

The encryption algorithm is set in the database URL, and the file password is specified in the password field, before the user password. A single space separates the file password and the user password; the file password itself may not contain spaces. File passwords and user passwords are case sensitive. Here is an example to connect to a password-encrypted database:

Class.forName("org.h2.Driver");
String url = "jdbc:h2:~/test;CIPHER=AES";
String user = "sa";
String pwds = "filepwd userpwd";
conn = DriverManager.
    getConnection(url, user, pwds);

Encrypting or Decrypting a Database

To encrypt an existing database, use the ChangeFileEncryption tool. This tool can also decrypt an encrypted database, or change the file encryption key. The tool is available from within the H2 Console in the tools section, or you can run it from the command line. The following command line will encrypt the database test in the user home directory with the file password filepwd and the encryption algorithm AES:

java -cp h2*.jar org.h2.tools.ChangeFileEncryption -dir ~ -db test -cipher AES -encrypt filepwd

But as java command line failed (database is in cygwin home folder):
$ java -cp h2-1.4.195.jar org.h2.tools.ChangeFileEncryption -dir ~ -db psMetricsServerMaintain -cipher AES -encrypt #?!psMetricsServerMaintain!?#ll
Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: 7
        at org.h2.tools.ChangeFileEncryption.runTool(ChangeFileEncryption.java:89)
        at org.h2.tools.ChangeFileEncryption.main(ChangeFileEncryption.java:67)

I was using your web interface under Options/Encryption and filled in the following dialog (in Win 10):

With the passwords "decrypt" and "encrypt" to test just plain ascii words without any special characters.
I can encrypt the database and I can decrypt the database with the tool and using the database normally without encryption.

But as long as I try to access it in your web interface, with dbeaver5 or with Spring Boot in the encrypted mode, it does not work!

I followed you above instructions and the password is here "encrypted password" without quotation marks. The same with spring boot:
application.properties:
# H2 Database config and JPA 
spring.datasource.url=jdbc:h2:./psMetricsServerMaintain;CIPHER=AES
#spring.datasource.url=jdbc:h2:./psMetricsServerMaintain
#spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=pvcsad01
spring.datasource.password =encrypted password
#spring.datasource.password =password
spring.datasource.testWhileIdle=true
spring.datasource.validationQuery=SELECT 1
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.hibernate.naming.strategy=org.hibernate.cfg.ImprovedNamingStrategy
#spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2dialect
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
#server config
server.port=6008
#logging
logging.level.root=WARN
logging.level.org.springframework.web=DEBUG
logging.level.org.hibernate=DEBUG
spring.profiles.active=development

The example for dbeaver datbase explorer I skip, but the result is the same, it does not work. There is no access. After decrypting it with the decrypting password in your webapp everything works (after changing back the url and the password of course) fine again.

So what might go wrong here? I also tried it out with the actual driver which is 1.4.196 of h2...

Any suggestions?

Best regards
Peter

[Peter wein.peter]

If you just open a plain new database test with the h2 console and then finally encrypt it by command line or the web tool, it could not be accessed via h2 console.

By the way: Isn't it possible to access an encrypted data base with the h2 (maintaing webapp) console at Port 8082? This would explain while my test fails , even with a fresh Database.

Then in this case the only open question would be, how to implement the encryption into spring boot +jpa+hibernate app. Otherwise I would assume, that encryption is not working with the 196 driver....

Thanks for any hints!

If I will have the time I will program a basic acces without jpa and hibernate...so for that it would be of help to know, if the h2 console is not capable of decrypting it.