SQLite encryption in Microsoft.EntityFrameWork

Question

I’m currently trying to encrypt SQLite database with official SEE extension when using Microsoft entity framework.

I’m able to encrypt database when using ADO.NET. However getting an error “You specified a password in the connection string, but the native SQLite library ‘e_sqlite3’ doesn’t support encryption” when using entity frame work.

Nuget Packages Used:

[Microsoft.EntityFrameWork.Core Microsoft.EntityFrameWork.Core.SQLite SQLite.Encryption.Extension System.Data.SQLite.Core]

Please can you advise how to fix this error with official SEE extension?

CustomDBContext.cs:

private readonly bool _created = false;

public CustomDbContext(DBContextOptions<CustomDbContext> options):base(options){

if(!_created)
{
_created = true;
Database.EnsureCreated();
}
}

public DbSet<SampleEntity> SampleEntities {get; set;}

Program.cs:

static void Main(string[] args)
{
var services = new ServiceCollection();
ConfigureService(services);
using ServiceProvider provider = services.BuildServiceProvider();
provider.GetService<ICustomDBContext>();
}

private static void ConfigureServices(ServiceCollection services)
{
string password = Convert.ToHexString(Encoding.Default.GetBytes("aes256:test");

SQLiteCommand.Execute("PRAGMA activate_extensions='see-7bb07b8d471d642e'", SQLiteExecuteType.NonQuery,@"Data Source=c:\users\test.db");

SQLiteConnectionStringBuilder connectionStringBuilder = new(){
ConnectionString = @"Data Source=c:\users\test.db;Password="+password};

SQLiteConnection conn = new(connectionStringBuilder.ConnectionString);

connection.Open();
connection.ChangePassword(password);
services.AddDbContext<CustomDBContext>(options => options.UseSqlite(connection));
}

Encryption is something you set in the connection string. It has nothing to do with EF Core. You need to use a library that *does* support encryption — Panagiotis Kanavos, Jul 21 '21 at 14:03
@Serge added code. DataBase.EnsureCreated() is the line of code that am facing an error. — parthasarathy tamilselvam, Jul 21 '21 at 14:03
@parthasarathytamilselvam SQLite itself doesn't support encryption. You need an extension for this. This has nothing to do with EF Core. [Microsoft's docs](https://learn.microsoft.com/en-us/dotnet/standard/data/sqlite/encryption?tabs=netcore-cli) show how to use a custom SQLite version that does support encryption, by using SQLCipher instead of the standard SQLite provider — Panagiotis Kanavos, Jul 21 '21 at 14:07
I have purchased SEE (official extension) from SQLite and there are no issues when am using ADO.NET with the SQLite encryption. However when am passing connection and trying to invoke DataBase.EnsureCreated the exception occurs. I have also gone through Microsoft docs and the sample available only for sql_cipher and the same approach is not working for the official extension. — parthasarathy tamilselvam, Jul 21 '21 at 14:17
@PanagiotisKanavos SEE is chosen by our security team and I have reached the SQLite support and they say this looks like an error due to EF. I have tried plenty of resources with stack overflow but could not find relevant topic with official extension for SQLite. — parthasarathy tamilselvam, Jul 21 '21 at 14:27
Again, EF Core doesn't have anything to do with the SQLite provider. It's ADO.NET that talks to SQLite, through your ADO.NET provider. This isn't up for debate. Which means most of your question is irrelevant. Only the ADO.NET parts matter, and the important parts, like how you actually added and registered SEE, are missing. It looks like you *don't* use SEE in your code at all and connect using the default provider. That's what the error says. Where did you add the SEE provider? — Panagiotis Kanavos, Jul 21 '21 at 14:31
To troubleshoot this, create a new empty console project, use only ADO.NET and add just the lines needed to register SEE, create and open a new connection. `System.Data.Sqlite` is *not* a Microsoft library, despite its name. — Panagiotis Kanavos, Jul 21 '21 at 14:33
Then it works for EF Core as well. Somehow you end up using the wrong provider in the other project. The question's code is mostly DI code that has nothing to do with EF Core, but where does `UseSqlite` come from? The `Microsoft.EntityFrameWork.Core.SQLite` package uses `SQLitePCLRaw.bundle_e_sqlite3`. So you end up configuring EF Core to use the standard provider instead of SEE. — Panagiotis Kanavos, Jul 21 '21 at 15:43
Exactly. UseSqlite method is from the Package Microsoft.EntityFrameWorkCore.SQLite and it uses public version of SQLite libraries. — parthasarathy tamilselvam, Jul 21 '21 at 15:46
SQLite Encryption libraries added as separate package from Nuget cannot be used since Microsoft.EntityFrameWorkCore.SQLite is referring only to public version of SQLite. — parthasarathy tamilselvam, Jul 21 '21 at 15:48
Yes, that's what I said. You installed and used the standard SQLite library when you wanted to use a custom one. As the answer to [this SO question explains](https://stackoverflow.com/questions/47987080/what-is-the-difference-between-microsoft-entityframeworkcore-sqlite-core-and-mic) though, the `Microsoft.EntityFrameWork.Core.SQLite` package is just the `Microsoft.EntityFrameWork.Core.SQLite.Core` package and the files delivered by the SQLite bundle. You may be able to use just `Microsoft.EntityFrameWork.Core.SQLite.Core` if you ensure your own files are copied to the correct folder — Panagiotis Kanavos, Jul 21 '21 at 16:05
Try installing `Microsoft.EntityFrameWork.Core.SQLite.Core` instead of `Microsoft.EntityFrameWork.Core.SQLite`. If `SQLite.Encryption.Extension` and `System.Data.SQLite.Core` deploy DLLs using the same names, in the same locations as `e_sqlite3` you may be able to use SQLite without extra code. Otherwise check [Use dynamic provider](https://learn.microsoft.com/el-gr/dotnet/standard/data/sqlite/custom-versions?tabs=netcore-cli#use-the-dynamic-provider) in the Microsoft docs. You may be able to use `Microsoft.Data.Sqlite` while loading SEE — Panagiotis Kanavos, Jul 21 '21 at 16:07
Tried using Microsoft.EntityFrameWorkCore.SQLite.Core and have landed up in an error “You need to call SQLitePCL.raw.SetProvider()…” and have tried adding nugget packages for bundle and landed up in the same password issue. — parthasarathy tamilselvam, Jul 21 '21 at 16:24
@PanagiotisKanavos 1. Interestingly there is no e_sqlite3 produced when using SQLite.Encryption.Extension and System.Data.SQLite.Core. 2. Microsoft.Data.Sqlite and SEE does not work together since Microsoft.Data.SQLite will produce only public version of SQLite which we cannot use for encryption. — parthasarathy tamilselvam, Jul 21 '21 at 18:42
After analysis and discussion with SQLite team, we understood SQLite supports only until EF6 and there are no support available for EF core yet. — parthasarathy tamilselvam, Jul 27 '21 at 10:40

score 0 · Answer 1 · answered Jul 27 '21 at 10:42

To use official SQLite extension for encryption, please choose the entity framework until EF6 since the System.Data.SQLite library supports only till EF6 and there is no direct support for EF core.

If we still need to use entity framework core then use we can also consider other options like encryption using SQLCipher that supports EF core.

SQLite encryption in Microsoft.EntityFrameWork

1 Answers1