Cannot provide password in Connection String for SQLite

Question

I use SQLite with Entity Framework.

I create the DB with following code:

class MyContext : DbContext
{   
    // DbSets, OnModelCreating(), etc.

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite("Data Source=c:\\test.db");
    }
}

//this is in my test method
 using (var db = new MyContext())
{
    db.Database.EnsureCreated();
}

The above code works. The DB gets created. But I want to encrypt the DB by providing password in connection string:

optionsBuilder.UseSqlite("Data Source=c:\\test.db;Password=mypassword");

In this case, after EnsureCreated is called, I get exception

An unhandled exception of type 'System.ArgumentException' occurred in System.Data.dll Additional information: Keyword not supported: 'password'.

What is wrong with using password? How can I encrypt that SQLite database?

Answer 1

The "pure", open source sqlite3.dll does not support encryption. There are other implementations, including licenced and unlicensed. For my solution I have used rindeal/SQLite3-Encryption project from GitHub

My solution:

1. Download compiled binaries
2. Copy sqlite3.dll to BIN folder
   • add the DLL to project in Visual Studio
   • set Copy to Output Directory - Copy always

3. Use this code in context's constructor:

   string password;
   var connection = Database.GetDbConnection();
   connection.Open();
   using (var command = connection.CreateCommand())
   {
       command.CommandText = $"PRAGMA key='{password}';";
       command.ExecuteNonQuery();
   }
   

Answer 2

According to this answer Protect SQLite database used by EntityFramework Core Application

EF Core uses Microsoft.Data.Sqlite, which does not currently support encryption out-of-box. See https://github.com/aspnet/Microsoft.Data.Sqlite/issues/184. You could add encryption yourself using SQLite extensions.

At the GitHub link provided there were other links to an alternative at

Encryption in Microsoft.Data.Sqlite

Answer 3

I found a solution that does not involve copying the dll within the BIN folder.

Take a look at it.