Support encyption

[rowanmiller]

This is to track the high level scenario of encryption. We've speculated that it may already be possible with a custom build of SQLite or using extensions - but we've not actually tested this or provided any guidance.

[bricelam]

It looks like SQLCipher can be used by replacing sqlite3.dll and issuing PRAGMA statements.

[bricelam]

The same is true for SEE.

[bricelam]

This works. I've posted Encryption in Microsoft.Data.Sqlite on my blog.

I don't think there's any additional work to do here. We could consider adding SetPassword and ChangePassword methods to SqliteConnection and allowing Password and HexPassword to be specified in the connection string, but these APIs would only work if you've included a copy of the native sqlite3 library that supports them.

[bricelam]

Also, #148 will improve the overall experience of using encryption.

[rowanmiller]

Any thoughts on how possible this is when using EF? Guessing you have to supply your own connection externally?

[bricelam]

@rowanmiller Yes, until we have an OnConnectionOpened hook, that's the easiest way.

[natemcmaster]

an OnConnectionOpened hook

To be added to EF in dotnet/efcore#626? Or do you mean adding a hook in the ADO layer? I assume you mean EF as I don't know of another ADO implementations with this kind of hook.

[bricelam]

An EF hook, yes. ADO.NET already has a connection state changed event.

[rowanmiller]

Yeah in dotnet/efcore#5024 we discussed the idea of having some sort of "connection preamble" as there are lots of database provider specific features that would benefit from this (SQLite PRAGMA, SQL Server sharding, etc.).

[rowanmiller]

Closing this out, will add a page to the docs that references blog post by @bricelam and shows how to use it with EF.