Password protected SQLite with Entity Framework Core

Question

I am trying to create a password protected SQLite database, in order to used it within a WPF application using Entity Framework Core.

I find out how to generate my DbContext and Entities from an existing SQLite DB (database first approach) but I can't get it working with password protected DB.

Actually, I am not even sure of how to create a password protected SQLite DB. What is the difference between an encrypted db and a password protected db ?

Answer 1

According to this article and this issue, there is no way (yet?) to encrypt the database using the Microsoft.Data.Sqlite assembly (used by EF Core).

Based on this, here is what I've done to get it working with EF Core:

• add the System.Data.SQLite.Core package to the project

• while configuring your dbContext, give the optionsBuilder your own DbConnection:

  var conn = new SQLiteConnection(@"Data Source=yourSQLite.db;");
  conn.Open();
  
  var command = conn.CreateCommand();
  command.CommandText = "PRAGMA key = password;";
  command.ExecuteNonQuery();
  
  optionsBuilder.UseSqlite(conn);
  

It is very important to use the SQLiteConnection (which can manage encrypted database) from the System.Data.SQLite.Core assembly and not the SqliteConnection from Microsoft.Data.Sqlite.

---

According to the article, you could probably used the built in SqliteConnection by replacing the sqlite3.dll shipped within the Microsoft.Data.Sqlite assembly by another one that handle encrypted database (you can find a free one on this repo). But I did not tested it.

Here is how to do this !