SQLite Error 26: 'file is not a database' when using EF Core 3.1.2 and SQLitePCLRaw.bundle_e_sqlcipher

[wjvii]

I feel sure it is me doing something wrong but I have been trying this for hours and no matter what I try I keep getting the exception below about the file is not a database. I have quadruple checked the location and password and they are correct and my older EF Core 2.1.X code can open the file and read from it just. It is the EF Core 3.1.2 project that I cannot get working. I also tried calling Batteries_V2 like the older code but that did not help.

Any suggestions?

I am using two nugets in my .NET Standard 2.0 project with EF Core 3.1.2 to created my entities assembly.

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite.Core" Version="3.1.2" />
    <PackageReference Include="SQLitePCLRaw.bundle_e_sqlcipher" Version="2.0.2" />
  </ItemGroup>

The Entities DbContext Code:

public class MyDbContext : DbContext
{
  public MyDbContext(DbContextOptions<MyDbContext> options) : base(options)
  {
  }
  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
  {
  SQLitePCL.Batteries.Init();
  }

  public DbSet<Form> Forms { get; set; }
}

The code that uses the Entities is a .NET Core 3.1 MSTest app.

var connectionStringBuilder = new Microsoft.Data.Sqlite.SqliteConnectionStringBuilder
{
    DataSource = @"Data Source=C:\Temp\MYDbLite.db",
    Password = "KDSLKJLSDFJLSKDFKLDSKLFJSDFJKLSDFJKLSDF"
};
var dcob = new DbContextOptionsBuilder<MyDbContext>()
    .UseSqlite(connectionStringBuilder.ConnectionString);
var db = new MyDbContext(dcob.Options);
var forms = await db.Forms.ToListAsync();

Message I get:

Test method HealthWare.Entities.POCLite.Tests.UnitTests.InstantiateTest threw exception: 
Microsoft.Data.Sqlite.SqliteException: SQLite Error 26: 'file is not a database'.

Stack Trace: SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db) SqliteCommand.PrepareAndEnumerateStatements(Stopwatch timer)+MoveNext() SqliteCommand.GetStatements(Stopwatch timer)+MoveNext() SqliteDataReader.NextResult() SqliteCommand.ExecuteReader(CommandBehavior behavior) SqliteCommand.ExecuteReader() SqliteCommand.ExecuteNonQuery() SqliteConnectionExtensions.ExecuteNonQuery(SqliteConnection connection, String commandText, SqliteParameter[] parameters) SqliteConnection.Open() DbConnection.OpenAsync(CancellationToken cancellationToken) --- End of stack trace from previous location where exception was thrown --- RelationalConnection.OpenDbConnectionAsync(Boolean errorsExpected, CancellationToken cancellationToken) RelationalConnection.OpenDbConnectionAsync(Boolean errorsExpected, CancellationToken cancellationToken) RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected) RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken) AsyncEnumerator.MoveNextAsync() EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken) EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken) UnitTests.InstantiateTest() line 25 ThreadOperations.ExecuteWithAbortSafety(Action action)

[wjvii]

Out of curiosity I tried a database that was not encrypted and got the same exception but I was able to open that database in the SQLite Toolbox in Visual Studio so I know the database is a valid SQLite database.

If I open an unencrypted database with a connection string containing a password, it will be encrypted using that password the next time it is opened, correct?

[ericsink]

If this problem showed up when you upgraded from EF Core 2 to 3, then you probably have some dependencies mixed up.

EF Core 2.x was based SQLitePCLRaw 1.x.

EF Core 3.x is based on SQLitePCLRaw 2.x.

SQLitePCLRaw 2.x contains breaking changes and is not compatible with 1.x. Mixing packages from 1.x and 2.x can cause problems.

Exactly what packages do you have?

[wjvii]

@ericsink I had put that in the information above but I didn't format it as code so it didn't show up for some reason after I posted it. I corrected that above but also posting here as well. The two packages I am using I believe are supposed to work together based on what I read. Then following are the MS Test project packages in case I am creating some conflict. Also, I actually created a brand new .NET Standard 2.0 class for this and shared the entities classes from using a shared project and created a new dbcontext in the new project, which I pasted above.

Entities Class

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite.Core" Version="3.1.2" />
    <PackageReference Include="SQLitePCLRaw.bundle_e_sqlcipher" Version="2.0.2" />
  </ItemGroup>

Unit Test

  <ItemGroup>
    <PackageReference Include="Microsoft.NET.Test.Sdk" Version="16.5.0" />
    <PackageReference Include="MSTest.TestAdapter" Version="2.1.0" />
    <PackageReference Include="MSTest.TestFramework" Version="2.1.0" />
  </ItemGroup>

[wjvii]

Still trying to figure this out so first thing today I grabbed an unencrypted copy of the same database and removed the password from the SqliteConnectionStringBuilder and the same code works. I am thinking that indicates the packages I have do work together.

[wjvii]

So taking from this https://www.bricelam.net/2016/06/13/sqlite-encryption.html, I wrote a quick .NET Core 3.1 Console app and created a new unencrypted SQLite database with one table and tried to encrypt it by passing in the password in the connection string as shown above. When I do this I get

Microsoft.Data.Sqlite.SqliteException: 'SQLite Error 26: 'file is not a database'.'

If I remove the password from the connection string it opens the database and executes the query just fine.

So maybe I am just totally misunderstanding but I thought based on what I read above that opening an unencrypted database with a password would then key that database to the password supplied in the connection string and then that password would be required from then on.

I attached the example console app.

Do I need to call something other than SQLitePCL.Batteries.Init() or call it in a different place? Should I use the Batteries_V2? I tried a number of combinations, but they all had the same results but I definitely could be doing it wrong.

Hope the sample helps and thank you so much for looking at this.

SQLiteEncryption.zip

[bricelam]

I'm able to repro this without Microsoft.Data.Sqlite:

using System.Diagnostics;
using static SQLitePCL.raw;

class Program
{
    static void Main()
    {
        SQLitePCL.Batteries_V2.Init();

        var rc = sqlite3_open("TestEncrypted.db", out var db);
        Debug.Assert(rc == SQLITE_OK);

        rc = sqlite3_exec(db, "PRAGMA key = 'br549';");
        Debug.Assert(rc == SQLITE_OK);

        rc = sqlite3_exec(db, "SELECT count(*) FROM sqlite_master;");
        Debug.Assert(rc == SQLITE_OK); // Fails, SQLITE_NOTADB
    }
}

[ericsink]

Humph.

Okay, so this ball is in my court.

I'll take a closer look. It's not immediately obvious how code as simple as that can fail when my test suite passes.

[sjlombardo]

@bricelam in your test program is TestEncrypted.db a preexisting database and if so how was it created?

[wjvii]

@sjlombardo assuming he used the database I sent in the test project above it was created with the latest version of ErikEJ's SQLite/SQL Server Compact Toolbox extension from the latest version of Visual Studio 2019.

[sjlombardo]

The problem here is that you are trying to open a SQLCipher 3 database (the version used in the 1.x bundle) using SQLCipher 4 (the version used in the 2.x bundle) without properly migrating or setting backwards compatibility. Try either option on the upgrading page to resolve the issue.

[ericsink]

Ah yes, I had wondered about that SQLCipher 3-vs-4 issue, but I hadn't yet looked closely enough to verify.

[wjvii]

@sjlombardo so is this migration required even though that database has not yet been encrypted at all? TestEncrypted.db was not previously encrypted with any version of SQLCipher.

[bricelam]

cc @ErikEJ

[wjvii]

Not sure if this is required since this database has never been encrypted before but just to be sure I modified the code from @bricelam example above and I added the following before trying to open the database with the Microsoft.Data.Sqlite with a password.

            var rc = sqlite3_open(file, out var db);
            Debug.Assert(rc == SQLITE_OK);

            rc = sqlite3_exec(db, "PRAGMA cipher_migrate;");
            Debug.Assert(rc == SQLITE_OK);

            db.Close();

I then ran the sample again and still get the following when opening the connection.

Microsoft.Data.Sqlite.SqliteException: 'SQLite Error 26: 'file is not a database'.'

If I remove the password from the connection string, it works.

[ErikEJ]

@bricelam I am using System.Data.Sqlite 1.0.110 currently, but I guess I should update to 1.0.113 https://github.com/ErikEJ/SqlCeToolbox/issues/835

[sjlombardo]

@wjvii The original problem you mentioned, opening an encrypted database created with SQLCipher 3 using SQLCipher 4, would require migration or backwards compatibility.

The second issue you mentioned herein, opening a standard SQLite (plaintext) database with SQLCipher is separate and unrelated. You can't open an unencrypted database using SQLCipher by supplying a password, that is not the way SQLCipher works. If you want to encrypt an unencrypted database you should attach and export.

[wjvii]

@sjlombardo that appears to be the answer here. I was mistakenly under the impression that it encrypted the data once the password was set, at least on an empty database with only schema, but the attach and export article makes that super clear, it must be brand new in order to encrypt it with SQLCipher. I have not done a lot more testing yet but in a quick test I was able to attach my example unencrypted database, export it to a new encrypted database per the instructions in the article above, and then open the encrypted database using Microsoft.Data.Sqlite with the password supplied in its connection string. AWESOME.

@bricelam maybe you can update the article on your website to make it clear that you cannot encrypt an existing unencrypted database by only supplying the password in the connection string and point them to the attach and export article if they are trying to encrypt an existing database.

My sincere thank you to everyone that responded here and for the fact that the responses were so quick!

[bricelam]

Did all this change in SQLCipher 4? I swear I read that you could do that (and unencrypt with PRAGMA rekey = NULL). ...but maybe that was just for SEE.

[sjlombardo]

Hello @bricelam, no, that behavior did not change with SQLCipher 4. The library has worked this way since the initial release.

[wjvii]

@bricelam I can definitely say this was a source of confusion for me as well and I have read many articles on the Internet about SQLite encryption with SQLCipher that never mention having to use the attach and export method with an existing database. Glad to get that cleared up!

[sjlombardo]

@wjvii I'm not really sure which articles you are referring to, however it is a significant oversimplification to say that all SQLite encryption is the same. Every implementation is different, independent, and incompatible with each other. Functionality and security features differ very substantially between products. About the only commonality is that encryption can usually be enabled using PRAGMA key.

With respect to SQLCipher, the FAQ on how to encrypt a plaintext database is in the top two or three google for searches similar to "encrypting a database with SQLCipher". Likewise the details on migration and backwards compatibility were widely communicated and documented for the SQLCipher 4 release (and have also been for every major version release prior).

[wjvii]

@sjlombardo I was referring to SQLCipher in particular and I am very glad you directed me to the FAQ and other articles on the Zetetic website as they definitely cleared things up for me and I will now use it as a primary resource for SQLite encryption information. I am sure the fact that I had used SQL CE for many years influenced my thinking on this because it encrypted an existing database when you supplied a password and since many articles did not specifically mention the need to attach and export an existing database to encrypt it, it never occurred to me it could be a requirement.

Thank you again for your help and all the great information.

[tblack2200]

I know this is an older post, but I just started using SQLCipher and happened to come across this error while performing a "bad password" test. I created the database using a password and then opened with the same password. I then intentionally used a bad password to verify it would fail and it did. However, it failed with the SQLite Error 26: 'file is not a database' error. Just thought I'd pass this on in case someone in the future is searching for something similar.

[ericsink]

Closing old/stale issue.