SQLite Encryption Support

[austindimmer]

It would be nice to be able to easily specify an encrypted connection for SQLite. I'd love to see built in support for some of the popular encryption providers for SQLite

http://www.hwaci.com/sw/sqlite/see.html
http://sqlite-crypt.com/index.htm
http://sqlcipher.net/

My experience of using these technologies in conjunction with EF6 CodeFirst has been pretty underwhelming. With SqlCE it was much easier, but now that we are migrating away from SqlCE and towards SQLite it would be nice for encryption support to be just as easy as it used to be with SqlCE.

[bricelam]

This may be a duplicate of aspnet/Microsoft.Data.Sqlite#13.

I agree that encryption is much easier in SQL Server Compact. However, because it's implemented as SQLite extensions, I'm not sure adding first-class API is appropriate. Perhaps it could be done in a separate library via extension methods.

[rowanmiller]

EF Team Triage: Agree with @bricelam 's assessment.

[austindimmer]

I am not really sure why this issue has been closed so quickly. Surely the .Net experience should aim to be the best in the industry. By lowering our expectations in such a fashion the net result will be that endless hours of developer productivity will be lost and developers will have ever more reasons to jump ship into other ecosystems.

In my investigations I came across the following Github repository

https://github.com/ericsink/SQLitePCL.raw

In the readme for that repository it is stated

Is this compatible with SQLCipher?

Yes. But I want to make that use case painless, and I haven't decided how to do that yet. It's a slippery slope with no obvious place to stop. If I slide all the way to the bottom of the hill, I'll be providing platform assemblies that include SQLCipher.

Now I can't say I understand the technical implications in depth, I am migrating my systems from SqlCE so have limited experience with SQLite. But surely encryption support would be a top requirement for the types of applications Rob Tiffany is talking about building in the excellent talk he gave at TechEd

http://robtiffany.com/teched-north-america-2014-empower-your-demanding-mobile-line-of-business-apps-with-sqlite-and-offline-data-sync-on-windows-8-1/

In my opinion this issue should be re-opened and given a top priority. If it's done separately via an extension methods library, great. Either way we need to make an effort to make this as easy as it should be.

[bricelam]

@systemsymbiosis The bug was closed as a duplicate of aspnet/Microsoft.Data.Sqlite#13 since we need to figure out how all extensions (including encryption) will work.

We're strongly considering building on top of @ericsink's SQLite.raw (See aspnet/Microsoft.Data.Sqlite#21). Thus, anything made easier there, would be available from the ADO.NET provider and EF. You could imagine the layers like so:

var ef = new MyDbContext();
var adoNet = ef.Database.AsSQLite().Connection.DbConnection;
var raw = adoNet.sqlite3;

[austindimmer]

Thanks for the clarification @bricelam I have an operational solution for the time being but it meant I have pretty much had to strip out EF (I was using EF6, CodeFirst and SqlCe but migrated to SQLite) and run raw SQL against the db. The reason why I raised this issue here is that I did not find anything related to SQLite encryption when I ran a search in this repo. I was hoping EF 7 would help address some of the pain points I've experienced and help make the developer experience as seamless as possible when running against encrypted SQLite. I am happy to hear that you are thinking deeply about this issue. I appreciate your efforts.

[teh-sHr0oMaN]

So given that aspnet/Microsoft.Data.Sqlite#13 has been completed, is there now some encryption support for SQLite using EF? Perhaps via a library?

[bricelam]

See Encryption in Microsoft.Data.Sqlite:

Install-Package 'Microsoft.EntityFrameworkCore.Sqlite.Core'
Install-Package 'SQLitePCLRaw.bundle_sqlcipher'

connection.Open();

var command = connection.CreateCommand();
command.CommandText = "PRAGMA key = 'password';";
command.ExecuteNonQuery();

var db = new MyDbContext(connection);

[PulkitSethi1984]

This does not seem to work with Xamarin Forms, in a .Net 2.0 standard project. I am testing this code in OnConfiguring method

[bricelam]

You may need to update your SQLitePCL.raw packages to the latest version