Setting up Dbeaver (The Universal DBMS) to work with SqlCipher Databases

dbeaver_sqlcipher.md

Setting up Dbeaver (The Universal DBMS) to work with SqlCipher Databases

I’ve tried multiple solutions to browse SqlCipher Databases and the only one that seemed to work properly was [DB Sqlite Browser](DB Browser for SQLite (sqlitebrowser.org)) with easy GUI to get into the db. but … the overall experience of that application didn’t really satisfy me!

I knew DBeaver from long time ago, and i wondered if i can use it with SQLCipher, but it was surprisingly a shock that it doesn’t work directly, but with a couple of hours of researching and trying different methods i finally came up with a way to configure it Out-of-the box to make it work!

The reason I wrote this gist was to help others who have the same issue as it seems there is no guide to show how to do so!

So Let's Go !

Download & Install Dbeaver

1. head to https://dbeaver.io/
2. download the one that suits your OS
3. Install !

Getting the right driver to get the Job done

1. head to https://github.com/Willena/sqlite-jdbc-crypt/releases and download the latest sqlite-jdbc-<version>.jar.
2. copy that file in a safe place on your pc that you DON'T ACCIDENTALY REMOVE

Linking Everything together

Well, there are multiple versions of SQLCipher and in this gist i'll target only SqlCipher V3 but it's the same Idea once you grasp it!

Creating a custom driver for dbeaver

1. From Database Menu --> Open DriverManager
2. Hit New, and now let‘s configure it

1. Settings Tab

• Driver Name = SqlCipherV3 (or any name you’d like)
• Driver Type, Select Generic
• Class Name = org.sqlite.JDBC
• URL Template = jdbc:sqlite:{file}

The rest are not mandatory, feel free to setup them up on your own!

2. Libraries Tab

Remember the .jar file that we’ve downloaded recently?

If your answer was no, please have a look [Getting the right driver](#Getting-the-right-driver-to-get the-Job-done)

If your answer was yes, Let‘s continue

• Click Add File and Select that .jar file
• Click Find Class and select org.sqlite.JDBC

3. Driver Prosperities

Here comes the most important part, make sure that you’ve configured that one properly!

To Add a property, Right-Click and press Add

Key	Value
cipher	sqlcipher
fast_kdf_iter	2
hmac_algorithm	0
hmac_pgno	1
hmac_salt_mask	0x3a
hmac_user	1
kdf_iter	64000
legacy	3
legacy_page_size	1024

There are one more PRAGMA which is called key and it is used to pass a passphrase that’s used to decrypt your db, but we won’t set it here as it differs from a database to another

For different version parameters of SqlCipher visit: https://utelle.github.io/SQLite3MultipleCiphers/docs/ciphers/cipher_sqlcipher/ and actually i’ve acquired the above one from there!

Parameter	v4	v3	v2	v1
kdf_iter	256000	64000	4000	4000
fast_kdf_iter	2	2	2	2
hmac_use	1	1	1	0
hmac_pgno	1	1	1	n/a
hmac_salt_mask	0x3a	0x3a	0x3a	n/a
legacy	4	3	2	1
legacy_page_size	4096	1024	1024	1024
kdf_algorithm	2	0	0	0
hmac_algorithm	2	0	0	0
plaintext_header_size	0	n/a	n/a	n/a

Note: -

• for n/a values don’t enter anything!
• V indicates the SqlCipher Version!

Adding DB to DBeaver

• From Database Menu —> Select New Database Connection
• Search for SqlCipherV3 (the one we created recently) and press Next
• Click Browse and Select your SqlCipher Database file
• In Driver properties tab make sure that your defaults has been set correctly
  • In Key Property double click the Value and Enter your Passphrase
• Test Connection, Voila!
• Press Finish

Support

If you encountered any problem, please feel free to comment below and I’ll gladly help!

Thank you very much for that excellent guide. A few months ago I tried to set up the same thing but without success. I now was on the point that I needed it a second time. So I started searching and found the same issue i lastly found. But you commented and so I found your guide. I tried it and it worked very good, except one point. I first created the driver for v3 created a connection and then found out, that my nodejs lib is using v4. So I thought it would be easy, just change the values in the driver. But after changing it doesnt work. So I checked all theses values 4 times until I found out, that I have to recreate the connection so the new values for v4 will be used.

Hello!
There is a problem again.
DBeaver 22.0.4 + Sqlite-jdbc-3.38.jar
No fill "key" = success, no problem. DB File created OK, any table created OK.
Exec "PRAGMA key='123';" = leaves passphrase key empty. Connect with fill options "key=123" => error "file is not a database". Connect with fill options "key=" => No error.
Exec "PRAGMA rekey='123';" = key goes not empty. Connect with fill options "key=123" => error "file is not a database". Connect with fill options "key=" => error "file is not a database"..
Please, help!!!!!

Hi @jet2 , i can't really relate to the whole context. but that error "file is not a database" indicates that Sqlite-jdbc-x.x.x has failed to authorize your SqlCipher db, probably missing parts of your configuration. also note that the above is for Sqlcipher V3, so if you're using another version e.g. SqlCipherV4 another set of parameters is needed that the above.

if you could mention the version of sqlcipher you're targeting, and the configs you've used on setting up the driver properties along with attaching the db maybe i can help.

Database Browser for SQLite version 3.12.2 makes it easy to encrypt a database. It changes the passphrase with PRAGMA rekey='123' without any problem. DBeaver correctly opens an encrypted database with password 123. But, with the PRAGMA rekey='1234' command, DBeaver sets an unknown encryption password, not 1234. And it is no longer possible to open the database. I informed the developers about the problem.

In case anyone has trouble using a raw key instead of a passphrase, I had to put something like this in the key parameter:

x'54686973206973206D792076657279207365637265742070617373776F72642E'

I spend quite some time trying all the cipher versions. In the first table, where the settings for v3 are dispalyed, you have hmac_user as key, but I think it should be hmac_use.

In the second table, where all versions are displayed, the key cipher with value sqlcipher was omitted. This was eventually the problem for me, I didn't add this key for all the other versions because I copy & pasted the whole table. When I also added the cipher key, my v4 was working.

Tested with SQLite-jdbc-3.41.2.0