Windows Phone Local Database tip: Working with encrypted database files

SQL Server Compact on Windows Phone supports encryption of the database file. By specifying a password on the ConnectionString during encryption and subsequent openings, the contents of the database file will be encrypted.
Beware that the encryption will degrade database performance, due to the extra processing required to decrypt and encrypt data.
Also keep in mind when using encryption, that unless you protect your connection string (the password), anyone that knows it can open the database file.

Some Phone developers (see this forum post, for example) have encountered issues when moving databases from device to desktop and vice versa, when encryption is involved. In this blog post I will try to explore what can and cannot be done in this respect.

From the root context menu in the SQL Server Compact Toolbox, select “Add 3.5 connection” and select Create:

Specify a password of “zyx” and click OK. Now we have a connection to a desktop encrypted database file.

Open the SQL Editor, and create a basic table:

CREATE Table Test
(
Id int PRIMARY KEY,
Data nvarchar(10)
);

Now create a Windows Phone 7.1 Application, and add the database file from above as Content:

Right click the database connection in the SQL Server Compact Toolbox, and select “Add Windows Phone DataContext…” and click OK, the WP7EncryptContext.cs file has now been added to the project.

Add a reference to System.Data.Linq, and compile the project.

Add a button called “Connect”, and add a click event handler, with this code:

            using (WP7EncryptContext db = new WP7EncryptContext(WP7EncryptContext.ConnectionStringReadOnly + ";Password=zyx"))
            {
                var list = db.Test.ToList();
            }

Now run the app in the emulator by pressing F5 (debug):

Obviously the desktop encryption is not compatible with the device database engine!

Now remove the database file from the project, and create the database file in isolated storage – change the click event handler code:

using (WP7EncryptContext db = new WP7EncryptContext(WP7EncryptContext.ConnectionString + ";Password=zyx"))
{
    db.CreateIfNotExists();
    var list = db.Test.ToList();
    var test = new Test();
    test.Id = 2;
    test.Data = "ErikEJ";
    db.Test.InsertOnSubmit(test);
    db.SubmitChanges();

}

Now the database will be created in Isolated Storage on the device. Run with F5 again, and no errors will occur. And we have “pre-populated” the database with a single row. Let’s try to move this database file to the desktop, I use Windows Phone Power Tools from CodePlex to do this:

 

When you add a connection to the file form the device, and click “Test Connection”, you will get this error message:

We already know that some version 4.0 features have been made available in the 3.5 Windows Phone database engine, like support for OFFSET/FETCH to allow paging. Has the enhanced version 4.0 encryption also been move to the device?

In the Toolbox, select “Add 4.0 Connection” and try to connect to the database from the device:

(Remember to specify the password!)

Reply Yes! And now we can browse and inspect both schema and data in the file – keep in mind that this file cannot be moved back to the device.

Now, GET the file again from the device via the Power Tools, and add the new copy to the project again, also as Content (as above, but the database file comes “pre-populated” from the device.)

Again, change the button click event handler as follows:

using (WP7EncryptContext db = new WP7EncryptContext(WP7EncryptContext.ConnectionStringReadOnly + ";Password=zyx"))
{
    var list = db.Test.ToList();
    if (list.Count > 0)
        MessageBox.Show("It works " + list[0].Data);

}

Conclusions:

You CANNOT:

- Use a desktop encrypted database on the device
- Open a device encrypted database on the desktop

You CAN:

- Prepopulate an encrypted database (but only on a device)
- Convert a device encrypted database to 4.0 format on the desktop, and browse it.

Credits: Thanks to Marc-Anthon Flohr for prompting me to write this blog post. His overview is nice, although his conclusions are slightly different.