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?

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

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

215,613
32
363
426

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

score 1 · Answer 3 · edited May 23 '17 at 12:25

1

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

Take a look at it.

edited May 23 '17 at 12:25

Community

1
1

answered Oct 07 '16 at 13:32

fharreau

1,925
1
17
42

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

-1

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

answered Apr 13 '17 at 07:36

mamarija

9
1

1

Did you by chance ever figure this out? – Stephen Vernyi Jan 29 '18 at 18:06
2

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 for SQLite

4 Answers4

Linked