About System.Data.SQLite with the SQLite Encryption Extension

The SQLite Encryption Extension allows SQLite to read and write encrypted database files. All database content, including the metadata, is encrypted so that to an outside observer the database appears to be white noise.

How to use System.Data.SQLite with the SQLite Encryption Extension

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.

2. 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 ".

3. When building for .NET Core, set the CopyLocalLockFileAssemblies MSBuild property in your project file.

Apparently, there are several distributions of Linux that disable use of RSA signatures that make use of SHA1 and doing so will cause strong name signature checks to fail. More details are available in the GitHub issue OpenSslCryptographicException: error:03000098:digital envelope routines::invalid digest on CentOS Stream 9 .

The following workaround appears to resolve the issue:

* NOTE: Forcibly enable use of SHA1 with RSA signatures; * otherwise, things will not work correctly. System.Environment.SetEnvironmentVariable( "OPENSSL_ENABLE_SHA1_SIGNATURES", "1");

4. 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.

Alternatively, starting with release 1.0.117.0 , when building the application for deployment purposes, your " SDS-SEE.exml " license certificate file can be embedded within your primary appliation assembly by using something similar to the following in your MSBuild project file:

Internet access is required for license certificate verification. Any certificates that do not have an infinite duration may require access to NTP and/or HTTPS. Certificates may also be subject to online revocation checking via HTTPS. Organizations that require offline-only license certificate verification will require additional contract terms.

<ItemGroup> <EmbeddedResource Include=" Project\Relative\Path\To\SDS-SEE.exml "> <LogicalName>SDS-SEE.exml</LogicalName> </EmbeddedResource> </ItemGroup>

When embedding your license certificate file as described above, the following snippet of code must be executed:

* NOTE: Use only the file name here, which indicates * that an embedded assembly resource is being * used. System.Environment.SetEnvironmentVariable( "Override_SEE_Certificate", "SDS-SEE.exml");

Generally, the above code snippet will be located right next to the code snippet described in the next item (#5).

5. 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:;"); System.Data.SQLite.SQLiteCommand.Execute( "SELECT COUNT(*) FROM sqlite_schema;", System.Data.SQLite.SQLiteExecuteType.Scalar, "Data Source=:memory:;Password=1234;");

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.

6. 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();

7. 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 (not when using embedded resource)

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 (release 1.0.118.0 or before) <bin>\lib\Badge1.0\ pkgIndex.eagle.harpy (release 1.0.118.0 or before) <bin>\lib\Badge1.0\ pkgIndex_8bf43b4749e46a0b.eagle (release 1.0.119.0 or later) <bin>\lib\Badge1.0\ pkgIndex_8bf43b4749e46a0b.eagle.harpy (release 1.0.119.0 or later) <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 (release 1.0.118.0 or before) <bin>\lib\Harpy1.0\ pkgIndex.eagle.harpy (release 1.0.118.0 or before) <bin>\lib\Harpy1.0\ pkgIndex_8bf43b4749e46a0b.eagle (release 1.0.119.0 or later) <bin>\lib\Harpy1.0\ pkgIndex_8bf43b4749e46a0b.eagle.harpy (release 1.0.119.0 or later) <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 (release 1.0.116.0 or before) <bin>\lib\Harpy1.0\Configurations\ Harpy.v1.eagle.b64sig (release 1.0.116.0 or before) <bin>\lib\Harpy1.0\Configurations\ Harpy.v1.eeagle (release 1.0.117.0 or 1.0.118.0 ) <bin>\lib\Harpy1.0\Configurations\ Harpy.v1.eeagle.b64sig (release 1.0.117.0 or 1.0.118.0 ) <bin>\lib\Harpy1.0\Configurations\ Harpy.Debugger.v1.eeagle (release 1.0.119.0 or later) <bin>\lib\Harpy1.0\Configurations\ Harpy.Debugger.v1.eeagle.b64sig (release 1.0.119.0 or later)

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>

8. 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 (release 1.0.116.0 or before) <bin>\ Configurations\Harpy.v1.eagle.b64sig (release 1.0.116.0 or before) <bin>\ Configurations\Harpy.v1.eeagle (release 1.0.117.0 or 1.0.118.0 ) <bin>\ Configurations\Harpy.v1.eeagle.b64sig (release 1.0.117.0 or 1.0.118.0 ) <bin>\ Configurations\Harpy.Debugger.v1.eeagle (release 1.0.119.0 or later) <bin>\ Configurations\Harpy.Debugger.v1.eeagle.b64sig (release 1.0.119.0 or later)

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.

If you see an error message containing " Eagle._Components.Public.Interpreter.DemandCertificate ", make sure the application configuration file does not disable generation of publisher evidence , i.e. if you see an XML snippet similar to the following in the application configuration file, it should be removed:

<generatePublisherEvidence enabled="false" />

9. 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.

In certain scenarios, e.g. troubleshooting deployments, it may be simpler to capture diagnostic output using the (formerly " SysInternals ") Microsoft DebugView tool, which can be downloaded here:

Microsoft DebugView Download Page

When using the DebugView tool to capture diagnostic trace output, setting the " ForceEnableTraceLogFile " environment variable is unnecessary.

10. The SQLiteConnection class includes a DecryptLegacyDatabase method that can be used to decrypt databases that were encrypted using the legacy CryptoAPI codec.

The DecryptLegacyDatabase method is static; there is no need to create a SQLiteConnection instance to call it. It should be called once per (legacy encrypted) database to decrypt. The first parameter is the fully qualified name of the (legacy) encrypted database to decrypt. The second parameter is the UTF-8 encoded byte array of the password that corresponds to the (legacy) encrypted database. The third parameter should almost always be null, which means "automatically detect page size"; otherwise, it would be the page size in bytes, which must be an integer power of 2 between 512 and 65536. The fourth parameter is the optional progress callback to use, if any. Generally, the fourth parameter will be null. A fairly typical call to this method would look something like this:

SQLiteConnection.DecryptLegacyDatabase( "C:\\full\\path\\to\\some\\legacy.db", System.Text.Encoding.UTF8.GetBytes("the legacy password"), null, null);

The return value is the fully qualified name of the decrypted database, which will exist in the same directory as the original encrypted (legacy encrypted) database. Once decrypted, the database can be re-encrypted with the SQLite Encryption Extension by using the ChangePassword method or the command line shell tool .