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

Hi.

I've read the whole thread and used everything as described, but i'm still not able to open a decrypted database. So far System.Data.SQLite worked perfectly with unencrypted databases.

I'm currently testing for perfomance and the general usefulness of SQLite in my private applications. I have encrypted the test database as described by setting the password via System.Data.SQLite. The resulting encrypted database could be opened and manipulated in every SQLite software i could get my hand on, so the database is in order.

• I'm using Visual Studio 2005 Professional, running on Windows XP SP2.
• The Project has a reference to the 1.0.49.0 System.Data.SQLite.dll.
• As i previously blamed the connection faults to a wrongly formed connection string i used the provided SQLiteConnectionStringBuilder class to form the connection string. The resulting string is
  Data Source = D:\Programming\c#\Test\DataEnc2.db3;Password=mypassword
  which is, as far as i can tell correct.

The wrapper class i've written builds the connection in the constructor and stores it in the field _connection, the function performing the query on the database opens and closes the connection to the database as needed (code follows below).

That worked perfectly with an unencrypted database. As soon as i switched to a test using my encrypted database, calling _connection.open() threw an exception giving the error code NotADatabase and the dreaded error message:

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

 I hope that was enough information. I simply don't know what i'm doing wrong.

 Schuck

 Here are the class constructor and the query function used:

        /// <summary>
        /// This function does the actual initialization.
        /// </summary>
        /// <param name="query">The initial query</param>
        protected virtual void ConstructorCore(string query)
        {
            _query = query;
            _affected = 0;

            SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder();
            //Testing with unencrypted Database - passed
            //builder.DataSource = @"D:\Programming\c#\Test\Data.db3";
           

            //This is the same Database only encrypted.
            builder.DataSource = @"D:\Programming\c#\Test\DataEnc2.db3";
            builder.Password = @"mypassword";
            
            // Storing connection
            _connection = new SQLiteConnection(builder.ConnectionString);
            // preparing adapter
            _adapter = new SQLiteDataAdapter();
            // preparing Result Table.
            _table = new DataTable();
        }

        /// <summary>
        /// Executes the stored query on the Database.
        /// </summary>
        protected virtual void PerformQuery()
        {
            // Empty query is empty
            if (_query == String.Empty)
                return;

            SQLiteCommand query = new SQLiteCommand(_query, _connection);
            try
            {
                // Connecting to Database.
                _connection.Open();

                _table.Clear();
                _table.Columns.Clear();


                _adapter.SelectCommand = query;
                _adapter.Fill(_table);

            }
            catch (SQLiteException e)
            {
                MessageBox.Show(e.Message + "\nConnection String: " + _connection.ConnectionString, "Query Error: " + e.ErrorCode.ToString());
            }
            finally
            {
                // Disconnecting from Database
                _connection.Close();
            }

            // Announce Affected Rows:
            _affected = _table.Rows.Count;
            OnPropertyChange("AffectedDatasets");
           
        }
 

I apologize for the double post, but for some reason it would'nt allow me to edit my previous post.

I don't know what went wrong. But i tried to re-encrypt the original unecrypted database again.

Again i was using System.Data.SQLite for this purpose, and this time i could also open it in my own application.

The only thing i changed was, that i installed the .Net Framework 3.5 on my System. But afaik that shouldn't influence the stand-alone .dll. Or does it?

 Hi,

 Encryption it is a good way to protect data ? or the enryption method is not enough ?

thx 

' Encrypts the database. The connection remains valid and usable afterwards.