Thursday, 08 March 2012

Encrypted SQL CE Database in Windows Phone 7

Porting my new Android game QuizBurner to Windows Phone 7.1 (Mango) was really fun. Microsoft created a clean and .NET _friendly_ environment and makes it easy for .NET developers to get some things done. I can’t bring myself to call it “.NET”, because it has too many incompatibilities - it’s more a simplified and in few ways modified copy of the .NET Framework.

Dependency

Access existing Database

Shipping existing databases within the the application is an essential prerequisite for my game. We are able to store the database side by side to the assembly, by using the “appdata” keyword instead of the “isostore” within the connection string.

Data Source = 'appdata:/db.sdf'; File Mode = read only;

Please mention that we only have read only access to that database, so we should also specify the “read only” parameter.

Database Encryption

So far so good – assuming that we are able to encrypt CE databases very easily, we just have to specify a password here too:

Data Source = 'appdata:/db.sdf'; File Mode = read only; Password = 'oni7sak2';

Since it couldn't have been that easy, we are receiving the following exception:

The database encryption mode is invalid.  [ Input Encryption Mode = 0 ]

I did some research and found out that the default encryption method in 3.5 is AES128/SHA1 and the default encryption method in 4.0 is AES128/SHA2.

List of default encryption algorithms used in SQL Compact:

 .---------------------------------------------------------------------------------.
| Version | Default Encryption | Authentication | Provider                         |
|---------+--------------------+----------------+----------------------------------|
| 3.0     | RC4                | MD5            | PROV_RSA_FULL, MS_ENHANCED_PROV  |
| 3.1     | RC4                | MD5            | PROV_RSA_FULL, MS_ENHANCED_PROV  |
| 3.5     | AES128             | SHA1           | PROV_RSA_AES, MS_ENH_RSA_AES_PRO |
| 4.0     | AES128             | SHA2           | ?                                |
`----------------------------------------------------------------------------------'

Further research showed me that WP7 is based on 3.5 and uses some features of 4.0, also the encryption methods. It looks like it’s a mixture of both versions.

Conclusion

That led me to the following conclusion:

Encrypted databases created in Microsoft SQL Server Management Studio (2008) are not compatible with WP7 and created Databases by WP7 are not compatible with the SSMS. It appears that the encrypted database created using Mango WPDT can not be opened in desktop (either using Visual Studio 2010 or SQL Server 2008 Management Studio). Both VS2010 and SQL Server 2008 MS shows an encryption mode is invalid when trying to open it.

There is no (supported) way to use encrypted SQL CE databases in WP7 if they are not created by WP7 itself.

Microsoft declines support to that approach by using the following statements:

  • SQLCE is not directly exposed to 3rd party applications in Windows Phone 7 and beyond. Microsoft exposes LINQ To SQL, which happens to use SQLCE as the underlying storage provider in Windows Phone Mango (7.1).
  • Previous knowledge of how SQLCE worked in one of its many previous incarnations will not help much in WP7 app development, as the build on Windows Phone is slightly different than other flavors.
  • Microsoft does not support loading a database which was created on SQLCE for desktop or any previous version of Windows CE.
  • Microsoft does allow the developer to specify a password on the database through LINQ To SQL.
  • Microsoft does not allow you to specify the encryption settings on the LINQ To SQL connection string.

Source: http://forums.create.msdn.com/forums/t/95939.aspx