System.Data.SQLite

To encrypt an existing unencrypted database, or to change the password of an encrypted database, open the database and then use the ChangePassword() function of SQLiteConnection:

// 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");

To decrypt an existing encrypted database call ChangePassword() with a NULL or "" password:

// Opens an encrypted database
SQLiteConnection cnn = new SQLiteConnection("Data Source=c:\\test.db3;Password=mypassword");
cnn.Open();
// Removes the encryption on an encrypted database.
cnn.ChangePassword(null);

To open an existing encrypted database, or to create a new encrypted database, specify a password in the ConnectionString as shown in the previous example, or call the SetPassword() function before opening a new SQLiteConnection. Passwords specified in the ConnectionString must be cleartext, but passwords supplied in the SetPassword() function may be binary byte arrays.

// Opens an encrypted database by calling SetPassword()
SQLiteConnection cnn = new SQLiteConnection("Data Source=c:\\test.db3");
cnn.SetPassword(new byte[] { 0xFF, 0xEE, 0xDD, 0x10, 0x20, 0x30 });
cnn.Open();
// The connection is now usable

By default, the ATTACH keyword will use the same encryption key as the main database when attaching another database file to an existing connection. To change this behavior, you use the KEY modifier as follows:

If you are attaching an encrypted database using a cleartext password:

// Attach to a database using a different key than the main database
SQLiteConnection cnn = new SQLiteConnection("Data Source=c:\\test.db3");
cnn.Open();
cmd = new SQLiteCommand("ATTACH DATABASE 'c:\\pwd.db3' AS [Protected] KEY 'mypassword'", cnn);
cmd.ExecuteNonQuery();

To attach an encrypted database using a binary password:

// Attach to a database encrypted with a binary key
SQLiteConnection cnn = new SQLiteConnection("Data Source=c:\\test.db3");
cnn.Open();
cmd = new SQLiteCommand("ATTACH DATABASE 'c:\\pwd.db3' AS [Protected] KEY X'FFEEDD102030'", cnn);
cmd.ExecuteNonQuery();

To open an existing encrypted database, or to create a new encrypted database, specify a password in the ConnectionString as shown in the previous example, or call the SetPassword() function after opening a new SQLiteConnection. Passwords specified in the ConnectionString must be cleartext, but passwords supplied in the SetPassword() function may be binary byte arrays.

// Opens an encrypted database by calling SetPassword()
SQLiteConnection cnn = new SQLiteConnection("Data Source=c:\\test.db3");
cnn.Open();
cnn.SetPassword(new byte[] { 0xFF, 0xEE, 0xDD, 0x10, 0x20, 0x30 });
// The connection is now usable

To open an existing encrypted database you cannot call SetPassword after opening the connection because open fails with SqliteException:

Message: "File opened that is not a database file\r\nfile is encrypted or is not a database"

ErrorCode: NotADatabase

Additionally you cannot call SetPassword before openning the connection because SetPassword requires an opened connection.

I'm using your lastest code in CVS.

Regards:

Jesús López

I haven't experienced this issue in testing.  As a matter of fact, both the connectionstring method and the SetPassword methods go straight to the same underlying function on the connection.

Can you show me your code?

Robert

I have the following code in a Windows Form:

private void CreateAndEncryptButton_Click(object sender, EventArgs e)
{
 SQLiteConnection.CreateFile("test.db3");
 SQLiteConnection cn = new SQLiteConnection("Data Source=test.db3");
 cn.Open();
 cn.ChangePassword("MyPassword");
 SQLiteCommand cmd = new SQLiteCommand("create table foo(id INTEGER PRIMARY KEY)",cn);
 cmd.ExecuteNonQuery();
 cn.Close();
}

private void ShowTablesButton_Click(object sender, EventArgs e)
{
 SQLiteConnection cn = new SQLiteConnection("Data Source=test.db3");
 try
 {
  cn.Open();
  cn.SetPassword("MyPassword");
  DataTable tables = cn.GetSchema("Tables");
  this.dataGridView1.DataSource = tables;
  cn.Close();
 }
 catch (SQLiteException ex)
 {
  MessageBox.Show(ex.ToString());
 }
}

First I click on CreateAndEncryptButton, then on ShowTablesButton and I receive an SqliteException on cn.Open:

System.Data.SQLite.SQLiteException: File opened that is not a database file
file is encrypted or is not a database
   at System.Data.SQLite.SQLite3.Execute(String strSql) in C:\Documents and Settings\Jesús\Mis documentos\SQLiteCvs\SQLite.NET\System.Data.SQLite\SQLite3.cs:line 99
   at System.Data.SQLite.SQLiteConnection.Open() in C:\Documents and Settings\Jesús\Mis documentos\SQLiteCvs\SQLite.NET\System.Data.SQLite\SQLiteConnection.cs:line 667
   at WinTest.Form1.ShowTablesButton_Click(Object sender, EventArgs e) in C:\Documents and Settings\Jesús\Mis documentos\SQLiteCvs\SQLite.NET\WinTest\Form1.cs:line 38

Regards:

Jesús López

I'll have a closer look at this in the code to see if there's an error I can throw or something I can clarify.  You should not be calling "changepassword" on a newly-created database. You should be calling SetPassword.  The only time you should call ChangePassword is if you have a database that already exists and has data in it, and you want to either encrypt it or decrypt it.

Robert

I have replaced ChangePassword with SetPassword, but I'm getting the same error.

Incidentally, there are some things which apear strange to me:

1. Having an invalid opened connection. If I open a connection, and I do not receive any error, I expect the connection to be valid
2. Providing the password after opening the connection. I'm used to provide the password before opening a connection, and if the password is invalid then receiving an error.
3. If you do the same thing with password in the connection string  and SetPassword. Why password in the connection string is set before opening the connection and SetPassword must be called after opening the connection?

So, I think SetPassword should be recoded to be called before opening the connection. Also, I think there is no need for two methods, SetPassword and ChangePassword, just a property Password. SetPassword and ChangePassword would be internal methods. You would call internally SetPassword when Password property was set before opening the connection, and call ChangePassword when Password was set after opening the connection.

I've duplicated the error and am working on a fix for it.

Robert

I'm open to suggestions on what might be done to clarify things.

For now the solution to the problem as I've implemented it (still pending CVS checkin) is to require that you either use the Password=xxxx syntax in the ConnectionString, or call SetPassword() before you call Open() instead of after.

The problem stems from the fact that right after a connection is opened, I have to execute 3 pragma's on the database file to set the cache size, page size and synchronous mode.  If the password isn't already set for an existing database when I do this, then SQLite throws an error.

There are several important scenarios that have to be covered in any change to the password methods:

1. A new database is created and you want to encrypt it
2. You have a database already populated and you need to encrypt/decrypt it
3. You have an encrypted database that you want to open

For cases 1 and 3, you can use SetPassword().  Additionally, under the new changes I just made, you could call ChangePassword() for scenario #1 as well.

For case 2, you *have* to call ChangePassword() on an open database, which means the database must've been opened and SetPassword() must've already been called.

Robert, is there any sure-fired way to tell if a given file is an Encrypted Database? I have so a lot modules in my app that connect to a database for one reason or another. Now that we support encrypted databases, I need to be able to determine if I'm about to open an ACTUAL database file and not just some random binary file. That way I can prompt the user for a password if it is a database, or puke on the DAU if it is not. 

Good question ... the answer is "I have no idea" ... I think SQLite will throw some kind of "not a database" error, but I don't think there's any way to tell because all the pages are encrypted, including the header blocks.

Robert