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?
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:
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 !
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:
dbCon.SetPassword(myLitePW)thereafter you use the PW in the connection string:Data Source=c:\mydb.db;Version=3;Password=myLitePW;