SQLite Encryption Extension

System.Data.SQLite with SQLite Encryption Extension via NuGet
Login

This project makes use of Harpy, provided by Mistachkin Systems.
Harpy: Secure Software Provisioning

In order to successfully make use of the SQLite Encryption Extension with System.Data.SQLite via the published NuGet packages:

  1. Add a reference to the System.Data.SQLite.Core NuGet package OR one of the (parent) packages that have a dependency on it, e.g. System.Data.SQLite.
Be sure that the core managed assembly for System.Data.SQLite (i.e. the file "System.Data.SQLite.dll") is not present in the Global Assembly Cache.
Various third-party software may attempt to install it there; however, that style of deployment is not officially supported.
  1. Add a reference to the SQLite.Encryption.Extension NuGet package.
Double-check the selected version of the "SQLite.Encryption.Extension" NuGet package against the selected version of the "System.Data.SQLite.Core" NuGet package.
The versions must either match exactly, e.g. "System.Data.SQLite.Core 1.2.3.4" and "SQLite.Encryption.Extension 1.2.3.4" or match with (at least) their first three components, e.g. "System.Data.SQLite.Core 1.2.3.4" and "SQLite.Encryption.Extension 1.2.3.5".
  1. When building for .NET Core, set the CopyLocalLockFileAssemblies MSBuild property in your project file.
  1. Make sure your "SDS-SEE.exml" license certificate file is copied into the application directory during the build process and included with the application deployment files.
  1. Prior to accessing an encrypted database, i.e. one that uses one of the "Password" connection string properties, the following snippet of code must be executed:
      System.AppDomain.CurrentDomain.SetData(System.String.Format(
          "Id_from_License_Certificate_{0}",
          System.Diagnostics.Process.GetCurrentProcess().Id),
          "EntityName_from_License_Certificate");

      System.Data.SQLite.SQLiteCommand.Execute(
          "PRAGMA activate_extensions='see-7bb07b8d471d642e';",
          System.Data.SQLite.SQLiteExecuteType.NonQuery,
          "Data Source=:memory:;");
In the above code, the strings "Id_from_License_Certificate" and "EntityName_from_License_Certificate" must match the text of the "Id" and "EntityName" values from your license certificate file, respectively, and will be provided with your license certificate file.
Care should be taken to retain the trailing literal underscore, between the "Id_from_License_Certificate" placeholder and the remainder of the format string.
The "System.Data.SQLite.SQLiteCommand.Execute" method call above must be used verbatim.
In cases where a non-default application domain (AppDomain) is in use, e.g. Microsoft Office, other third-party applications, test frameworks, web services, etc, some code similar the following may be required as well:
      /*
       * NOTE: The .NET Core (and later) runtimes support only
       *       one application domain.  On those runtimes, the
       *       following environment variable has no effect.
       */
      System.Environment.SetEnvironmentVariable(
          "LicenseOtherAppDomain", "1");

      /*
       * NOTE: Depending on exactly how the application domain
       *       has been configured, the following environment
       *       variable may not be necessary; however, as long
       *       as it is set to the directory containing the
       *       correct "System.Data.SQLite.SEE.License.dll"
       *       file, setting it should be harmless.
       */
      System.Environment.SetEnvironmentVariable(
          "LicenseAssemblyPath",
          System.AppDomain.CurrentDomain.BaseDirectory);
To determine if code is executing in a non-default application domain, check the System.AppDomain.CurrentDomain.IsDefaultAppDomain property. If the resulting value is not true, the application domain in use is not the default application domain.
  1. Then, use the connection string property "Password", "HexPassword", or "TextPassword" to enable encryption for a database connection.
Please consult the System.Data.SQLite Documentation for further details.
Per section "8.1 Encryption algorithm selection using a key prefix" of the README, the specific encryption algorithm to use can be selected by using a short prefix on the connection string property value, e.g. to use the AES-256 encryption algorithm, prefix the string "aes256:" to the desired password.
Here is a short example:
      SQLiteConnection connection = new SQLiteConnection();

      connection.ConnectionString =
          "Data Source=test.db;Password=aes256:secret;";

      connection.Open();
Here is another example that uses connection pooling to help reduce connection setup overhead when repeatedly opening a given database:
      /*
       * NOTE: Setting the following environment variable is optional;
       *       when set, it prevents the use of weak references in the
       *       connection pool, which then prevents pooled connections
       *       from being cleaned out too aggressively.  The handling
       *       enabled by setting this environment variable is supported
       *       starting with the 1.0.116.0 release of System.Data.SQLite.
       */
      System.Environment.SetEnvironmentVariable(
          "SQLite_StrongConnectionPool", "1");

      SQLiteConnection connection = new SQLiteConnection();

      connection.ConnectionString =
          "Data Source=test.db;Password=aes256:secret;Pooling=true;";

      connection.Open();
  1. When deploying your application, the following files are required to be present in the application binary directory:
      <bin>\System.Data.SQLite.dll
      <bin>\x86\SQLite.Interop.dll
      <bin>\x64\SQLite.Interop.dll
      <bin>\System.Data.SQLite.SEE.License.dll
      <bin>\Eagle.dll
      <bin>\Harpy.dll
      <bin>\SDS-SEE.exml
When using the NuGet packages within Visual Studio, these files should be copied into the application binary directory automatically, via the project build process.
Some files copied into the application binary directory during the build process are not used by the SQLite Encryption Extension NuGet packages for System.Data.SQLite (i.e. included via transitive NuGet package dependencies), they include:
      <bin>\lib\Badge1.0\Badge.dll
      <bin>\lib\Badge1.0\pkgIndex.eagle
      <bin>\lib\Badge1.0\pkgIndex.eagle.harpy
      <bin>\lib\Harpy1.0\keyRing.General.demo.eagle
      <bin>\lib\Harpy1.0\keyRing.General.demo.eagle.harpy
      <bin>\lib\Harpy1.0\keyRing.zero.eagle
      <bin>\lib\Harpy1.0\keyRing.zero.eagle.harpy
      <bin>\lib\Harpy1.0\pkgIndex.eagle
      <bin>\lib\Harpy1.0\pkgIndex.eagle.harpy
      <bin>\lib\Harpy1.0\test.eagle
      <bin>\lib\Harpy1.0\test.eagle.harpy
      <bin>\lib\Harpy1.0\Certificates\trial-certificate.xml
      <bin>\lib\Harpy1.0\Configurations\Harpy.v1.eagle
      <bin>\lib\Harpy1.0\Configurations\Harpy.v1.eagle.b64sig
Starting with release 1.0.117.0, when building the application for deployment purposes, they can be excluded by copying the following snippet into your MSBuild project file prior to "<Import>" elements that refer to any "Harpy.*.targets" files:
      <PropertyGroup>
        <CopyBadgeCoreFiles Condition="'$(CopyBadgeCoreFiles)' == ''">false</CopyBadgeCoreFiles>
        <CopyHarpyLibraryFiles Condition="'$(CopyHarpyLibraryFiles)' == ''">false</CopyHarpyLibraryFiles>
      </PropertyGroup>
  1. When debugging your application (e.g. in Visual Studio) OR if you see an error message containing "native method forbidden by license" or "managed method forbidden by license", the following additional files are also required to be present within the application binary directory:
      <bin>\Eagle.Eye.dll
      <bin>\Configurations\Harpy.v1.eagle
      <bin>\Configurations\Harpy.v1.eagle.b64sig
If ASP.NET (or any other framework) that makes use of "shadow copying" is in use, the following environment variable may also be necessary in order for Harpy to find its configuration files:
      System.Environment.SetEnvironmentVariable(
          "ConfigurationDirectory", System.IO.Path.Combine(
          System.AppDomain.CurrentDomain.BaseDirectory,
          "lib\\Harpy1.0\\Configurations"));
It should be noted that even when the above environment variable is used, the "Eagle.Eye.dll" assembly file should be located in the same directory as the "Eagle.dll" assembly file (i.e. the application binary directory). Alternatively, the "StubPath" may be used to override its parent directory, e.g.:
      System.Environment.SetEnvironmentVariable(
          "StubPath", System.AppDomain.CurrentDomain.BaseDirectory);
The "StubPath" environment variable should only be used when ASP.NET or something else is moving things around, e.g. in order to support "shadow copying".
When using the NuGet packages within Visual Studio, these files should be copied into the application binary directory automatically, via the project build process.
  1. If the System.Data.SQLite.SQLiteExtra.InnerVerify method throws an exception, it may be useful to set the following environment variables in order to capture relevant diagnostic information:
      System.Environment.SetEnvironmentVariable(
          "ForceEnableTrace", "1");

      System.Environment.SetEnvironmentVariable(
          "ForceEnableTraceLogFile", "1");

      System.Environment.SetEnvironmentVariable(
          "TracePriorities", "HasPrioritiesMask");
When the above environment variables are set, it should cause a log file to be generated in the temporary directory for the current user (i.e. in "%TEMP%") with a name like "HarpyLicensingSdk_<pid>_<aid>.log", where <pid> is the integer identifier for the current process and <aid> is the integer identifier for the current AppDomain.