Greetings all,

I'm having a bit of a problem and I hope that someone might be able to
assist. I find this issue rather strange because it is not as though there
is a lot of code involved between the encrypted and unencrypted DB handling
yet the unecrypted DB works just fine and the encrypted DB does not.

Here's the situation...

We have a C# back end synchronizing a database with an IOS (iPad and
iPhone). The database is streamed to the device in large packets though the
details aren't important. It's been working for a few years now. The IOS
side of things is the sqlcipher community edition and the C# back end is
the professional edition.

Recently, someone requested that the DB be encrypted on the server side
presumably to save cycles on the device. The Docs seem pretty straight
forward so I downloaded the System.Data.Sqlite.dll ADO.Net provider from
here and wired it up into the server side slapped in a test Key and two
lines of code later had an encrypted database.

I do note that my registered copy of Sqlite Expert cannot open this
database. Though I don't know why (different encryption algo maybe?).

Because of this I wrote a quick UI tool to allow me to look at the
encrypted database. This opens it up just fine, displays table structures
and data.

Once the DB gets transferred to the device, however, the device's SqlCipher
isn't able to successfully query the database, always returning *error 26 -
file is encrypted or is not a database.*
*
*
I have spent the last day or so poking around the site and the net trying
to figure out why and I'm coming up dry.

On the server I am currently using the following line of code to encrypt
the database:

tempConnection.ExecuteNonQuery(string.Format("PRAGMA key=\"x'{0}'\"",
"2DD29CA851E7B56E4697B0E1F08507293D761A05CE4D1B628663F411A8086D99"));

ExecuteNonQuery is an extension method on IDbConnection that wraps running
a query against an already open connection. I did note that in order to run
a query against a connection the connection has to be open. This is fine
for creating a new DB but for opening an existing encrypted DB in C#-land
you cannot call connection.Open() without a password. I've also used the
example code's casting of

((SQLiteConnection)tempConnection).SetPassword(<<the key>>);

but that didn't work either. I've tried it with and without the escaped
quotes... with and without the x... with a simple password of 'test123' and
none of it has worked.

The key is copy/pasted right out of the docs here.

On the device side I use the following block of code to try to access the
DB.

int sqlResult = SQLITE_OK;

sqlite3* database = NULL;

const char* foo = "PRAGMA
key=\"x'2DD29CA851E7B56E4697B0E1F08507293D761A05CE4D1B628663F411A8086D99'\" ";

sqlResult = sqlite3_open([databasePath UTF8String], &database);

// This returns SQLITE_OK and the pointer is valid.

const char* statement = "<<insert valid query string here>>";
sqlite3_stmt *compiledStatement;
sqlResult = sqlite3_prepare_v2(database, sqlStatement, -1,
&compiledStatement, NULL);

Unfortunately this call always returns error 26 - SQLITE_NOTADB - file is
encrypted or is not a database.

I've gone so far as to pull the database off of the simulator and bring it
within firing range of my sqlite DB tool and open it with the same key..
the DB opens and I can see all the table structures and data therein. It is
only on the device that this is failing.

There's not a whole lot of code to go wrong here. Am I doing something
wrong server side? Or device side? or both? I do know that the files for
the device side are probably out of date whereas the C# side of things is
the freshest & latest. Could there be some sort of versioning problem?

Any help you can render would be appreciated.
Thanks,
EK

Hi EK,

It sounds like you've been running the iOS version for some time, so it's
very likely that you're app is using SQLCipher 1.1.x on iOS, while your C#
app is using the newer SQLCipher 2 ADO.NET provider. By default these have
slightly different and incompatible database formats, by virtue of the
latter's MAC extensions.

The easiest way to fix this is to have the C# application generate a 1.1.x
compatible database. To do this, you'll need to make 2 small changes:

1. Add "Synchronous=Full;" to your connections string, i.e. :"Data
Source=sqlcipher.db;Pooling=false;Synchronous=Full;"
2. Call "PRAGMA cipher_use_hmac = OFF;" after opening the database but
before creating any schema / data.

This will cause the ADO.NET provider to create a SQLCipher 1.1.x compatible
database with HMAC disabled, so it will be interoperable with the older
version of SQLCipher you're using on iOS.

There is a complete code example in this post as well, for reference:

https://groups.google.com/forum/#!msg/sqlcipher/0mlJdejzgJ8/syIViTJ1quYJ

An alternative, but more involve approach, would be to upgrade the iOS
application to SQLCipher 2, but that's more involved because you might need
to migrate existing databases, etc.

Please let us know if that works for you. Thanks!

Cheers,
Stephen