Starting with Microsoft.Data.Sqlite.Core 5.0.0 it is no longer possible to set cipher key options

[jonreis]

It used to be possible to configure the cipher key see: #21491. As of version 5.0.0 this is no longer possible. Instead one gets the : 'SQLite Error 26: 'file is not a database'.' exception.

In the previous code SqliteConnection.Open() did not perform any database queries. One could open the database, execute the necessary pragmas, then run a query on the database. This is no longer possible because of the exception mentioned above that is occurring in Open().

Of hand, I would guess the following code in SqliteConnetion.Open() will throw before the correct cipher key options have had a chance to be set.

    if (SQLitePCLExtensions.EncryptionSupported() != false)
    {
      this.ExecuteNonQuery("SELECT COUNT(*) FROM sqlite_master;");
    }

Perhaps there could be OnOpening event on the SqlConnection that would allow custom pragmas to be executed when a connection is being created?

[ajcvickers]

/cc @bricelam

[bricelam]

Good call. We shouldn't force decryption if a password isn't set in the connection string. This will make the experience a little worse when you forget to provide a password for an encrypted database, but I think it's worth it to enable scenarios like yours.

[jonreis]

@bricelam thanks for taking a look

[bricelam]

I may be stating the obvious here, but I think fixing this in 5.0.1 is also important to enable existing applications already using encryption to upgrade to 5.0 without changes.

[sandrohanea]

Hi!
I'm running into the same problem when trying to upgrade to ef core 5.0.
I was thinking maybe besides the fix provided by @bricelam to not force decryption if the password is not provided, it will be nice if for ef core 5.0 we'll have an option to configure these parameters using SqliteConnectionStringBuilder.

We can: add a collection of pragmas to be executed before the database is decrypted, or we can add all parameter types in the builder using typed values. These parameters can be found here: https://github.com/sqlcipher/sqlcipher/blob/master/src/crypto.c

I'd prefer the option with typed parameters, add a new class: SqlitePasswordOptionBuilder and configure it from SqliteConnectionStringBuilder like this:

What do you think?
If you think that this will bring any value, please, let me know and I can implement it and create a PR to add these capabilities.

[bricelam]

@sandrohanea Microsoft.Data.Sqlite is designed to work with any SQLite implementation. Given that these things are specific to SQLCipher, I think they would best live in a library built on top of Microsoft.Data.Sqlite