sqlite unable to open database file is encrypted o

Question

I am working on a windows application .net 2.0 with sqlite database, my connection string stays in app.config like

<connectionStrings>
<add name="SQLiteDB" 
     connectionString="Data Source=|DataDirectory|database.s3db;version=3;password=mypassword;" 
     providerName="System.Data.Sqlite"/>
</connectionStrings>

In the connection string i have defined password as "mypassword" if i remove this password everything is working well but when i use password clause, it gives me error in connection.open() syntax that

File opened that is not a database file
file is encrypted or is not a database

I searched on net and found some version issue but i am using version 3 only as i stated in connection string i also tried removing the "version=3" but problem remains the same.

I am doing this first time, what is the solution of it?

Answer 1

When you specify a password in the connection string, and the database already exists, SQLite assumes the database is encrypted and will try to decrypt it with said password. If you haven't set a password on the database yet, this will result in the "file is encrypted" error, because the supplied password can't be used to decrypt an unencrypted database.

You can either delete the database, and SQLite will create a new encrypted database using the password in the connection string. Or, you can encrypt your existing database using the ChangePassword() method:

// Opens an unencrypted database    
SQLiteConnection cnn = new SQLiteConnection("Data Source=c:\\test.db3");    
cnn.Open();    

// Encrypts the database. The connection remains valid and usable afterwards.    
cnn.ChangePassword("mypassword");

Reference: Encrypting, decrypting and attaching to encrypted databases

Answer 2

2Toad's answer is mostly correct, but I wanted to add my own because there are some clarifications to be made. As 2Toad said, this is correct:

When you specify a password in the connection string, and the database already exists, SQLite assumes the database is encrypted and will try to decrypt it with said password. If you haven't set a password on the database yet, this will result in the "file is encrypted" error, because the supplied password can't be used to decrypt an unencrypted database.

But this error can also happen if you try to use conn.SetPassword("something") after you already had another one in the connection string. Or if you do conn.ChangePassword("somethingelse"), but still have Password=something in the connection string.

There are several scenarios to consider:

1. The database has had a password applied, and it is in the connection string.
2. You have a password in the connection string, but the database does not have one applied, or the password in the string does not match the DB.
3. The database has never had a password, and you want to change it.
4. The database has had a password, and you want to change it.

Resolutions:

1. So the code 2Toad provided to perform conn.ChangePassword("somethingelse") is only half-correct and doesn't take into account where you are, what else you've done, and what you want to do in the future. If you have an existing password and you want to change it, this is correct, but you also have to be sure the connection string is updated afterwards, or subsequent connections will fail with the file is encrypted error.

2. This scenario happens if you blank the password using conn.SetPassword("") and then try conn.ChangePassword("somethingelse") without first connecting to the database without the Password=something in the connection string. That Password=something would have to be removed from the connection string, because the password has been removed programmatically from the DB and the DB will try connecting with that. If it's not removed from the connection string at the same time as it's removed from the DB programmatically, you'll get the same file is encrypted error.

3. Because I started out by doing a conn.SetPassword("something") in the very beginning when I didn't have a password applied (and I believe this is the way to do that), I cannot verify the following without creating another SQLite DB, but I do not believe you can call conn.ChangePassword("something") if you never had a password in the first place. You should do conn.SetPassword("something") for the initial set, and then put Password=something in your connection string.

4. The way I did a change of a password was where I did conn.ChangePassword("somethingelse") only after doing conn.SetPassword("") and clearing the Password=something from the connection string:

   // Changes an encrypted database to unencrypted and removes password
   string connString = "Data Source=c:\\test.db3;Password=something";    
   SQLiteConnection conn = new SQLiteConnection(connString);
   conn.SetPassword("");
   //conn.Open();    // doesn't work because connString hasn't been updated
   
   // Update connString
   connString = "Data Source=c:\\test.db3;";    
   conn = new SQLiteConnection(connString);
   conn.Open();  // we've opened the DB without a password
   
   // Re-encrypts the database. The connection remains valid and usable afterwards until closed - then the connection string needs updating.    
   conn.ChangePassword("somethingelse");
   conn.Close();
   
   // Update connString
   connString = "Data Source=c:\\test.db3;Password=somethingelse";   
   conn = new SQLiteConnection(connString); // must re-instantiate!
   conn.Open();  // we've opened the DB with our new password
   

This worked out fine. I suppose you can also not clear it from the connection string and simply do conn.ChangePassword("somethingelse"), and then add Password=somethingelse to your string, afterwards:

    // Opens an encrypted database   
    string connString = "Data Source=c:\\test.db3;Password=something";    
    SQLiteConnection conn = new SQLiteConnection(connString);
    conn.Open();    

    // Encrypts the database. The connection remains valid and usable afterwards until closed - then the connection string needs updating.    
    conn.ChangePassword("somethingelse");
    conn.Close();

    // Update connString
    connString = "Data Source=c:\\test.db3;Password=somethingelse";   
    conn = new SQLiteConnection(connString);
    conn.Open();     // we've opened the DB with our new password

Personally, I store the password as encrypted in an app (web) .config file, and call it into a variable in my application onload and dynamically build my connection string from it.

That I know of, if you delete a SQLite DB and try to call it, you will simply get an error - not a re-created SQLite DB with a new password from your connection string - at least when using and calling it from a C# .NET app.

UPDATE If you need a function that will be used for updating the password after you already have one, you don't want to have .SetPassword(), but .ChangePassword(). I found it is also better to always blank it out, then change it, like in my first example in #4.