How can I run a migration with Entity Framework Core on an encrypted SQLite database?

Question

I have an ASP.NET Core console application (targeting version 2.2), using Entity Framework Core. I'm trying to connect to an empty, encrypted SQLite database created with DB Browser for SQLCipher. After creating the connection, I'm attempting to migrate the database, but I'm getting a 'SQLite Error 26: 'file is not a database'.'

Here is my code:

var connection = new SqliteConnection(@"Data Source=Sqlite\test.db;");

connection.Open();

var command = connection.CreateCommand();
var password = "test";
command.CommandText = "SELECT quote($password);";
command.Parameters.AddWithValue("$password", password);
var quotedPassword = (string)command.ExecuteScalar();

command.CommandText = "PRAGMA key = " + quotedPassword;
command.Parameters.Clear();
var result = command.ExecuteNonQuery();

services.AddDbContext<SmartContext>(options =>
        options.UseSqlite(connection));

var serviceProvider = services.BuildServiceProvider();
var context = serviceProvider.GetService<MyContext>();

Console.WriteLine("Migrating sqlite database");

context.Database.Migrate();

I'm able to connect to the database in DB Browser and SQLiteStudio.

I've looked at this repository and I have the SQLitePCLRaw.bundle_sqlcipher (1.1.11), SQLitePCLRaw.bundle_green (1.1.11), Microsoft.EntityFrameworkCore.Design (2.2.6), Microsoft.EntityFrameworkCore.Sqlite.Core (2.2.6) packages added in my project.

I've looked at a lot of posts on a lot of forums, but none of them mention migrations with encrypted databases. I might be missing something really obvious, but any help getting past this error would be greatly appreciated, thanks!

Ci Ty Chen · Answer 1 · 2021-03-06T08:55:56.123

I think you need to add the Password XD

Please check the Specify the key section in the official documentation if you have other problem.

The method for encrypting and decrypting existing databases varies depending on which solution you're using. For example, you need to use the sqlcipher_export() function on SQLCipher. Check your solution's documentation for details.

You can use the SQLCipher API to finish your work:

If you want to build a encrypting database, just set your connection string, like this:Data Source = encryptedName.db; Password=YourPassword

if you want to change password in a encrypting database

// you can use this in startup.cs
using var changePasswordDb = new DBContext(
    new SqliteConnection(
            new SqliteConnectionStringBuilder()
                {
                     DataSource = "encryptedName.db",
                     Mode = SqliteOpenMode.ReadWriteCreate,
                     Password = oldPassword
                }.ToString()
        ));
changePasswordDb.Database.ExecuteSqlRaw($"PRAGMA rekey = {newPassword}");

// or use SqliteConnection
var connection = new SqliteConnection("Data Source =encryptedName.db.db;Password=yourPassword");
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "PRAGMA rekey = " + newPassword;
command.ExecuteNonQuery();

if you want to encrypt a plaintext SQLite database , SQLCipher is not support directly. so you can use sqlcipher_export() to get the goal. look this How to encrypt a plaintext SQLite database to use SQLCipher (and avoid “file is encrypted or is not a database” errors)

// Example
command.CommandText = "ATTACH DATABASE 'encryptedName.db' AS encrypted KEY 'YourNewPassword';SELECT sqlcipher_export('encrypted');DETACH DATABASE encryptedName;";

if you have some Performance Issue in Asp.net Core and Entity Framework Core , you need to check to these

if you have other Performance Issue , you can check this SQLCipher Performance Optimization

How can I run a migration with Entity Framework Core on an encrypted SQLite database?

1 Answers1