Which tool can open encrypted DB

Does anyone know a tool that can open the encrypted Duplicati database file?

My “standard” Browser Tool Database Browser ELT-tools.com does not support encrypted SQLite.

And DB Browser for SQLite http://sqlitebrowser.org/ asks for password but does not open the database.


image

Depending on what OS is in use you may need something with RC4 support built in:

I think I’ve seen some people mention specific tools they’ve used but I’m not sure how to search the forum for them, sorry.

2 Likes

Oh that’s interesting. I thought it was unencrypted on all OS’es (I guess I never checked Windows).

To decrypt the database, quit all running instances, then start either Duplicati.GUI.TrayIcon.exe or Duplicati.Server.exe from the commandline with the argument --unencrypted-database and Duplicati will decrypt the database for you.

There is an older issue discussing this here:

1 Like

At the time there was RC4 support on the Windows build, but almost no Linux distros have that in SQLite (it does probe for it).

Requiring a custom SQLite binary on Windows would be a showstopper for many users.

It’s on windows.

Thanks to @kenkendk for the the decryption hint. I have made the decryption by coping the database into the debug/bin folder. I had a look inside it and it is a very easy and self explaining database structure!

So if someone knows a tool (for windows) an answer is still welcome.

I found this thread via Google, sorry that i’m reviving this dead body:
I’m running the Windows GUI Version with basically no custom settings.
Does that mean that the Database itself can be considered hardened?
Because recreating the Databases is such a pain, I considered syncing them via the cloud.
I hesitated because i was afraid of basically publishing a list of all my files, and encrypt those again would really make it difficult, becuase i planned on just using symlinks.

I don’t think so. Probably anyone with Duplicati who gets your database can open it with theirs, using --unencrypted-database (just as you could – and it’s not tied specifically to a secret only YOU know).

Clear text password stored in Duplicati-server.sqlite gets into the specifics:

Yes, that was the idea with the RC4 encryption. It is not strong, and has a known password, but at least you cannot do string scanning on the harddisk to find the contents.

For good encryption on database backups, Duplicati itself can do that, but orchestration is important, because backing up the database of currently running backups risks locking and consistency trouble.

Wouldnt it for an expierienced developer a ten minute job to create a option of just copying the database and the lastest job.export to the destination?
That way you’re half way there for distributed deduplication and disaster recovery is greatly improved.

I was just planning “mklink” hardlinking it to my OneDrive, but i guess that is not adviseable then.

I doubt anything happens in ten minutes… If you mean copying the database of a job as part of the job, this runs into the problems I mentioned earlier – it might be locked when needed, and in any event gets instantly obsolete. If you mean putting up a job export including everything needed for restores, including passwords:

Entire configuration backup

And I’m not the one making such a call, but you can see from the above that some key people have thought.

Bumping up that old thread.

Is the --unencrypted-database still working and is it something meant to work from Linux as well?
If not what is the new recommended way to decrypt the databases?

Thank you

If you’re using Linux, your Duplicati-server.sqlite database is already unencrypted.

It appears to be encrypted nowadays but this is not the file I am trying to access
I want to open the database for one of the backup.

The job-specific database? Should also be unencrypted. In my experience it’s only the Duplicati-server.sqlite file that is encrypted by default, and that’s only on the Windows platform. The job-specific databases are not encrypted on any platform.

Hm. I am on Windows not Linux but I just use SQLite DatabaseBrowser (a free tool of the PortableApps Suite) and I also wrote some code to read the DBs of the single jobs and I could open them in C# without any encryption/decryption?

That is embarrassing. Just realised that I had installed sqlite v2 command-line tools (rather than v3).
Thank you for bearing with clumsiness

As far as I can tell, the Duplicati-server.sqlite is encrypted using the “SQLite Encryption Extension” (SEE), which is actually a paid extension to SQLite. However, there is a freely available implementation in the System.Data.SQLite .NET library, which is of course distributed with Duplicati. So I found the easiest way to query this database (assuming you don’t want to have Duplicati permanently unencrypt it) is to copy the file somewhere and use this Powershell code on Windows to decrypt that copy, which you can then open with your SQLite browser/tool of choice.

Here is the Powershell code to decrypt:

[Reflection.Assembly]::LoadFile("C:\Program Files\Duplicati 2\System.Data.SQLite.dll")

$sDatabasePath="C:\Temp\duplicati-test\Duplicati-server2.sqlite"
$sDatabaseConnectionString=[string]::Format("data source={0}",$sDatabasePath)
$oSQLiteDBConnection = New-Object System.Data.SQLite.SQLiteConnection
$oSQLiteDBConnection.ConnectionString = $sDatabaseConnectionString
$oSQLiteDBConnection.SetPassword("Duplicati_Key_42")
$oSQLiteDBConnection.open()

$oSQLiteDBConnection.ChangePassword("")
$oSQLiteDBConnection.close()

Of course, you should replace the path in the LoadFile line to correspond to your Duplicati install.
And replace the path in the value of $sDatabasePath with the place where you’ve put the copy of the database.

Yes, the default password (unless you changed it) is “Duplicati_Key_42”

Credit: Got some example code from here: Powershell: Working with a SQLite Database

Ancient C# example: Encrypting, decrypting and attaching to encrypted databases - System.Data.SQLite
Code that does this in Duplicati: duplicati/SQLiteLoader.cs at de13cbcbd0f85492e8b8603def0ced7d7472a8e4 · duplicati/duplicati · GitHub

I guess the method was removed in later releases (see below)

Duplicati 2.0.6.3 is distributed with System.Data.SQLite 1.0.111.0

For historical auditing purposes, it seems that starting with System.Data.SQLite version 1.0.113.0, support for encryption was entirely removed in this checkin:
https://system.data.sqlite.org/index.html/info/56170d1316782f1b
by modifying the file /Targets/SQLite.NET.Settings.targets, and setting the value of InteropCodec to false.
specifically the comment says “Merge all changes needed for the SQLite core library 3.32.0 release.”
Which reveals to us that the actual culprit is SQLite itself, where in release 3.32.0, they removed support for encryption as well, in this commit:
https://www.sqlite.org/src/info/5a877221ce90e752
Where the comment says “simplify the code by removing the unsupported and undocumented SQLITE_HAS_CODEC compile-time option

Neither the change in System.Data.SQLite nor in SQLite are documented in the release notes for these projects.

2 Likes

Some other discussion about it:

Password-protected databases no longer work with 1.113, no rollback possible

SQLITE_HAS_CODE is gone?

Done as part of some Aug 2019 work. Dodged this encryption issue by chance…
Next time will not be that lucky. If I understand you, Windows Duplicati will break.

Would you like to open a GitHub Issue so that it can be tracked by development?
You look like you probably know C#. Would you be able to help with the solution?

Duplicati exists and improves thanks to volunteers. More are welcome anywhere.

1 Like