Cannot provide password in connection string

Question

In SQLite with Entity Framework I create the database with :

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();
}

It works; database gets created. But I want to encrypt the database by providing a password in connection string:

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

From EnsureCreated I get :

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

How can I encrypt that SQLite database?

where is your password stored right now, if it is in the web.config then you can use aspnet_regiis -pef to encrypt the config file — lordkain, Jun 16 '16 at 14:22
Check this question http://stackoverflow.com/questions/35060250/protect-sqlite-database-used-by-entityframework-core-application — Nkosi, Jun 16 '16 at 14:23
Does this answer your question? [How to create a password protected database?](https://stackoverflow.com/questions/39903863/how-to-create-a-password-protected-database) — user4157124, Nov 20 '22 at 15:28

score 7 · Answer 1 · answered Jul 14 '16 at 07:45

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:

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

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();
}

score 3 · Accepted Answer · edited May 23 '17 at 12:10

3

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

edited May 23 '17 at 12:10

Community

1
1

answered Jun 16 '16 at 13:57

Nkosi

235,767
35
427
472

That also throws exception "Keyword not supported: 'version'." – Tschareck Jun 16 '16 at 14:03

score -1 · Answer 3 · answered Apr 13 '17 at 07:36

I've used that implementation that Tschareck posted:

 private const string Password = "1234";
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var conn = new SqliteConnection(@"Data Source=test.db;");
            conn.Open();

            using (var command = conn.CreateCommand())
            {
                command.CommandText = $"PRAGMA key = '{Password}';";
                command.ExecuteNonQuery();
            }
            optionsBuilder.UseSqlite(conn);
        }

and managed to put the password to my solution, and solution works fine, but I have another problem - I cannot open database (I'm using DB Browser for SQL Lite) using that password (when I click "Ok" Password gets deleted and nothing happens):

printScreen DB Browser

DB browser cannot open encrypted file. Only way to open it is to unlock database first "manually": copy sqlite3.exe into the folder with database, run command line tool run command: sqlite3 -key YourKey DBname.db (where YourKey is key you have used for encription, and DBname is name of your database) run command: .rekey YourKey "" "" — mamarija, Mar 13 '18 at 14:42
You can use this other SQLite Browser "SQLite2009 Pro Enterprise Manager" http://sqlite2009pro.azurewebsites.net/ If the database is protected by password the browser ask you the password — fedeteka, May 02 '18 at 12:59

Cannot provide password in connection string

3 Answers3

Linked