Can't open a password protected SQLite 3 database with C#

Question

I'm having an issue using a password protected SQLite database, using System.Data.SQLite.

I'm using DB Browser for SQLite to create the database and set the password. With DB Browser I have no issues opening, entering the password viewing data, then closing the database.

So with .NET 4.6.2 and System.Data.SqLite 1.0.105.2 the following code snippet does not work I keep getting a "file is encrypted or is not a database" error.

namespace licensekeygeneration
{
    using NLog;
    using NLog.Extensions.AzureTableStorage;
    using System;
    using System.Data.SQLite;
    using System.Linq;
    using System.Windows;

/// <summary>Interaction logic for App.xaml</summary>
public partial class App : Application
{
    /// <summary>Make sure that NLog is running</summary>
    private static Logger logger = LogManager.GetCurrentClassLogger();

    /// <summary>Run before the application starts up</summary>
    void App_Startup(object sender, StartupEventArgs e)
    {
        try
        {
            // Set link to the SQLite database and grab the logging endpoint
            string dataSource = @"Data Source=c:\users\fred\desktop\database.db;Version=3;Page Size=1024;Password=ABCD";
            SQLiteConnection conn = new SQLiteConnection(dataSource);

            DataContext LocalDB = new DataContext(conn);

            // Sets the target for NLog in code 
            string strNlog = LocalDB.GetTable<TblS3Settings>().Where(item => item.StrSettingName.Equals("NlogEndPoint") && item.BoolIsValid.Equals(true)).ToList().FirstOrDefault().StrSettingValue;
            var azureStorageTarget = (AzureTableStorageTarget)LogManager.Configuration.FindTargetByName("AzureTableStorage");
            azureStorageTarget.ConnectionString = strNlog;
        }
        catch (Exception ex)
        {
            // Problem with the database or the connection so error out
            MessageBox.Show("There is an issue with the internal database\n" + ex.Message, "Application", MessageBoxButton.OK, MessageBoxImage.Hand);
            Current.Shutdown();
        }

        // Logging OK and we have an attached database so lets start
        MainWindow.Show();
    }
}

If I remove the password from the database using DB Browser for SQLite and I change the following line:

string dataSource = @"Data Source=c:\users\fred\desktop\database.db;Version=3;Page Size=1024;";
SQLiteConnection conn = new SQLiteConnection(dataSource);

I get the information I expect and life is good, So am I missing something with System.Data.SQLite as I just can't get it to work as I expected.

If it matters I'm using Visual Studio 2017 on Windows 10 64Bit.

Thanks.

Rene, this maybe correct. But... Why is there always a but?

Ashley B
– Ashley B

2017-06-28 14:32:37 +00:00
Commented Jun 28, 2017 at 14:32 — Ashley B
– Ashley B, Commented Jun 28, 2017 at 14:32

Rene · Accepted Answer · 2017-06-28 20:27:43Z

3

SQLiteConnection and DB Browser for SQLite use different kinds of encryption. You have to encrypt the DB using SQLiteConnection itself. Unfortunately you cannot use DB Browser afterwards

See encrypt with c#

I believe there is no solution instead of implementing the encryption by yourself into DB Browser. There is already a discussion on this topic, and the developers don't seem to plan an implementation: discussion here

edited Jun 28, 2017 at 20:27

answered Jun 27, 2017 at 21:05

Rene

707 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Risky Pathak Over a year ago

"SQLiteConnection and DB Browser for SQLite use different kinds of encryption." You saved me. I was scratching my head since last 2 hours

Ashley B · Accepted Answer · 2017-06-28 14:50:09Z

The accepted way to set the password seams to be as follows:

string dataSource = @"Data Source=c:\users\Fred\desktop\test.db;Version=3;Page Size=1024;";
            SQLiteConnection conn = new SQLiteConnection(dataSource);
            conn.Open();
            conn.ChangePassword("ABCD");
            conn.Close();

This "should" set the password to the database to ABCD thus:

string dataSource = @"Data Source=c:\users\Fred\desktop\test.db;Version=3;Page Size=1024;Password=ABCD;";
            SQLiteConnection conn = new SQLiteConnection(dataSource);
            conn.Open();

And this works which would appear to be correct but the following also works:

string dataSource = @"Data Source=c:\users\Fred\desktop\test.db;Version=3;Page Size=1024;";
            SQLiteConnection conn = new SQLiteConnection(dataSource);
            conn.Open();

Please note that after setting the password I can open a password protected database in any utility without supplying the password.

So unless I'm missing the point it would appear that password setting in the System.Data.SQLite is suspect.

Shuaib · Accepted Answer · 2018-10-20 04:53:03Z

0

Try this:

 var command = connection.CreateCommand();
        command.CommandText = "SELECT quote($password);";
        command.Parameters.AddWithValue("$password", _password);
        var quotedPassword = (string)command.ExecuteScalar();
        command.CommandText = "PRAGMA key = " + quotedPassword;
        command.Parameters.Clear();
        command.ExecuteNonQuery();

answered Oct 20, 2018 at 4:53

Shuaib

1,5793 gold badges20 silver badges28 bronze badges

Collectives™ on Stack Overflow

Can't open a password protected SQLite 3 database with C#

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related