SpatialiteLoader throws SqliteException: 'SQLite Error 1: 'not authorized'.'

[4nonym0us]

Might be related to #14402 (not calling SpatialiteLoader.Load(connection); results in exception SqliteException: 'SQLite Error 1: 'no such function: InitSpatialMetaData'.' as EF Core fails to load Spatialite itself). As it was suggested in the related issue, I've tried loading Spatialite manually, which results in the exception below.

Exception message: SQLite Error 1: 'not authorized'.
Stack trace: 
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Infrastructure.SpatialiteLoader.Load(DbConnection connection)
   at Playground.EfCore.Sqlite.Program.Main(String[] args) in E:\git\Playground.EfCore.Sqlite\Playground.EfCore.Sqlite\Program.cs:line 16

Steps to reproduce

Complete code listing is included below:

using System;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;

namespace Playground.EfCore.Sqlite
{
    class Program
    {
        static void Main(string[] args)
        {
            var connection = new SqliteConnection("DataSource=:memory:");
            connection.Open();
            SpatialiteLoader.Load(connection); // throws Microsoft.Data.Sqlite.SqliteException: 'SQLite Error 1: 'not authorized'.'

            var options = new DbContextOptionsBuilder<GeoDbContext>().UseSqlite(connection, b => b.UseNetTopologySuite()).Options;

            using (var context = new GeoDbContext(options))
            {
                context.Database.EnsureCreated();
            }

            Console.ReadKey();
        }
    }

    public class GeoDbContext : DbContext
    {
        public GeoDbContext()
        {

        }

        public GeoDbContext(DbContextOptions<GeoDbContext> options) : base(options)
        {

        }
    }
}

Further technical details

EF Core version: 2.2.1 Database Provider: Microsoft.EntityFrameworkCore.Sqlite Operating system: Windows 10 IDE: Visual Studio 2017 15.9.5 Target framework: netcoreapp2.2 Complete List of nuget packages used to reproduce the bug:

<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="2.2.1" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite" Version="2.2.1" />

[tmutton]

I was just about to create this issue when it popped up in the suggested issues box. Thanks!

[bakashinji]

I added the following line after connection.Open(); connection.EnableExtensions();

and it worked

[fdq09eca]

I found the same issue for connecting a .gpkg but it could not be solved by connection.EnableExtensions();

Error

SQLite error (1): statement aborts at 1: [SELECT load_extension('mod_spatialite');] not authorized
Exception thrown: 'System.Data.SQLite.SQLiteException' in System.Data.SQLite.dll
An unhandled exception of type 'System.Data.SQLite.SQLiteException' occurred in System.Data.SQLite.dll
SQL logic error
not authorized

Program.cs

using Microsoft.EntityFrameworkCore.Infrastructure;
using System.Data.SQLite;

class Program {

  static void Main(string[] args) {
    string gpkgPath = "empty.gpkg";
    var connectionStringBuilder = new SQLiteConnectionStringBuilder {
      DataSource = gpkgPath,
      Version = 3
    };

    using (var conn = new SQLiteConnection(connectionStringBuilder.ConnectionString)) {
      conn.Open();
      conn.EnableExtensions(true);
      SpatialiteLoader.Load(conn); // cannot load this line.. unauthorise?? 
                                   //but the test code in NetTopology does fine..
                                   //so the recuse maybe go throught the tests and see what make it passes?
      Console.WriteLine("'mod_spatialite' extension loaded");
    }
  }
}

.csproj

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net6.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite.Core" Version="6.0.7" />
    <PackageReference Include="System.Data.SQLite.Core" Version="1.0.116" />
  </ItemGroup>

</Project>

.gpkg

I downloaded this empty.gpkg

Folder structure

:.
│   ConsoleApp1.csproj
│   ConsoleApp1.sln
│   empty.gpkg
│   Program.cs
│
├───bin
│   └───Debug
│       └───net6.0
│           │   ConsoleApp1.deps.json
│           │   ConsoleApp1.dll
│           │   ConsoleApp1.exe
│           │   ConsoleApp1.pdb
│           │   ConsoleApp1.runtimeconfig.json
│           │   empty.gpkg
│           │   Microsoft.Data.Sqlite.dll
│           │   Microsoft.EntityFrameworkCore.Abstractions.dll
│           │   Microsoft.EntityFrameworkCore.dll
│           │   Microsoft.EntityFrameworkCore.Relational.dll
│           │   Microsoft.EntityFrameworkCore.Sqlite.dll
│           │   Microsoft.Extensions.Caching.Abstractions.dll
│           │   Microsoft.Extensions.Caching.Memory.dll
│           │   Microsoft.Extensions.Configuration.Abstractions.dll
│           │   Microsoft.Extensions.DependencyInjection.Abstractions.dll
│           │   Microsoft.Extensions.DependencyInjection.dll
│           │   Microsoft.Extensions.DependencyModel.dll
│           │   Microsoft.Extensions.Logging.Abstractions.dll
│           │   Microsoft.Extensions.Logging.dll
│           │   Microsoft.Extensions.Options.dll
│           │   Microsoft.Extensions.Primitives.dll
│           │   SQLitePCLRaw.core.dll
│           │   System.Data.SQLite.dll
│           │
│           └───runtimes
│               ├───linux-x64
│               │   └───native
│               │           SQLite.Interop.dll
│               │
│               ├───osx-x64
│               │   └───native
│               │           SQLite.Interop.dll
│               │
│               ├───win-x64
│               │   └───native
│               │           SQLite.Interop.dll
│               │
│               └───win-x86
│                   └───native
│                           SQLite.Interop.dll
│
└───obj
    │   ConsoleApp1.csproj.nuget.dgspec.json
    │   ConsoleApp1.csproj.nuget.g.props
    │   ConsoleApp1.csproj.nuget.g.targets
    │   project.assets.json
    │   project.nuget.cache
    │
    └───Debug
        └───net6.0
            │   .NETCoreApp,Version=v6.0.AssemblyAttributes.cs
            │   apphost.exe
            │   ConsoleApp1.AssemblyInfo.cs
            │   ConsoleApp1.AssemblyInfoInputs.cache
            │   ConsoleApp1.assets.cache
            │   ConsoleApp1.csproj.AssemblyReference.cache
            │   ConsoleApp1.csproj.BuildWithSkipAnalyzers
            │   ConsoleApp1.csproj.CopyComplete
            │   ConsoleApp1.csproj.CoreCompileInputs.cache
            │   ConsoleApp1.csproj.FileListAbsolute.txt
            │   ConsoleApp1.dll
            │   ConsoleApp1.GeneratedMSBuildEditorConfig.editorconfig
            │   ConsoleApp1.genruntimeconfig.cache
            │   ConsoleApp1.GlobalUsings.g.cs
            │   ConsoleApp1.pdb
            │   _IsIncrementalBuild
            │
            ├───ref
            │       ConsoleApp1.dll
            │
            └───refint
                    ConsoleApp1.dll