How to create a password protected database?

Question

I am trying to create a password protected SQLite database to use within a WPF application using Entity Framework Core.

I know how to generate DbContext and Entities from an existing database but don't know how to create a password protected database. What is the difference between an encrypted database and a password protected database?

you encrypt the DB via `dbCon.SetPassword(myLitePW)` thereafter you use the PW in the connection string: `Data Source=c:\mydb.db;Version=3;Password=myLitePW;` — Ňɏssa Pøngjǣrdenlarp, Oct 06 '16 at 19:10
Unless I am mistaken, this works with EF6 (within the System.Data.SQLite package). But with EF Core (within the Microsoft.Data.SQLite package), I can't find a way to do so ... (there is no SetPassword methods in the SQLiteConnection from the Microsoft.Data.SQLite) — fharreau, Oct 06 '16 at 20:22
"*What is the difference between an encrypted database and a password protected database?*" There is no password without encryption. — user4157124, Nov 20 '22 at 15:19

score 18 · Accepted Answer · edited May 23 '17 at 10:30

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 !

score 1 · Answer 2 · answered Nov 20 '22 at 12:01

I was pleasantly surprised with how smooth the solution described here was for me.

Navigate to the github repo and scroll down to the steps.
Install/remove the NuGet in steps 1 to 4.

Modify your DbContext bit:

public class MyDbContext: DbContext {

  public MyDbContext(): base() {}

  protected override void OnConfiguring(DbContextOptionsBuilder builder) {
      SqliteConnectionStringBuilder builder = new SqliteConnectionStringBuilder();
      builder.DataSource = @"myDatabase.db";
      builder.Password = "myPassword";

      optionsBuilder.UseSqlite(new SqliteConnection(builder.ConnectionString));
  }

  //... and some other unrelated stuff ...

}

Some notes:

If you don't really care for the existing data, it might be easier to just delete the previous database. If you do care, you can give it a shot.
If you already have 'Microsoft.EntityFrameworkCore.Tools' installed, you don't need to install the 'Microsoft.EntityFrameworkCore.Design' NuGet.

How to create a password protected database?

2 Answers2

Linked