About System.Data.SQLite with the Enterprise Key Management plugin

The Enterprise Key Management plugin for System.Data.SQLite with the SQLite Encryption Extension allows connection secrets (a.k.a. "passwords") to be managed via a centralized server, which can either be self-hosted or hosted by Mistachkin Solutions LLC . The server is used to provision, manage, and fetch secrets for use with opened database connections. All secrets stored on the server are encrypted using key material that is not available on that server. Optionally, all secrets may be signed and verified with an RSA key pair.

How to use System.Data.SQLite with the Enterprise Key Management plugin

In order to successfully make use of the Enterprise Key Management plugin 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. Add a reference to the SQLite.Enterprise.KeyManagement NuGet package.

Double-check the selected version of the " SQLite.Enterprise.KeyManagement " 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.Enterprise.KeyManagement 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.Enterprise.KeyManagement 1.2.3.5 ".

4. 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");

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

Please note that the " SDS-SEE.exml " license certificate file is shared by the SQLite Encryption Extension and the Enterprise Key Management plugin; however, they are available for purchase separately, and purchasing the Enterprise Key Management plugin always requires purchasing the SQLite Encryption Extension as well.

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

6. Prior to accessing an encrypted database, 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.

7. Then, set the " SdsSeeApiKey ", " SdsSeeKeyPrefix ", and " SdsSeeKeyIds " AppDomain properties to enable encryption for all database connections, using some code similar the following:

System.AppDomain.CurrentDomain.SetData( "SdsSeeApiKey", "00000000000000000000000000000000"); System.AppDomain.CurrentDomain.SetData( "SdsSeeKeyPrefix", "aes256"); System.AppDomain.CurrentDomain.SetData( "SdsSeeKeyIds", "00000000-0000-0000-0000-000000000000");

In the above code block, the (all zero) values should be replaced with the actual values, e.g. for the read-only API key and the comma-delimited list of connection secret identifiers.

Alternatively, if more than one AppDomain is used by the application, these values may be specified using environment variables, using some code similar the following:

System.Environment.SetEnvironmentVariable( "SdsSeeApiKey", "00000000000000000000000000000000"); System.Environment.SetEnvironmentVariable( "SdsSeeKeyPrefix", "aes256"); System.Environment.SetEnvironmentVariable( "SdsSeeKeyIds", "00000000-0000-0000-0000-000000000000");

Alternatively, these values may be specified within connection string properties using code similar to the following:

SQLiteConnection connection = new SQLiteConnection(); connection.ConnectionString = "Data Source=test.db;SdsSeeApiKey=00000000000000000000000000000000;SdsSeeKeyPrefix=aes256;SdsSeeKeyIds=00000000-0000-0000-0000-000000000000;"; connection.Open();

Each of the connection secret identifiers supplied will be checked, from last to first, until the database connection is opened successfully. This allows for easier (offline) rotation of database passwords. When more than one connection secret identifier is used, the newer connection secret identifiers should be appended to the end of the " SdsSeeKeyIds " value using a single comma as the delimiter between them, e.g.:

// NOTE: Example when using the environment variable. System.Environment.SetEnvironmentVariable( "SdsSeeKeyIds", "00000000-0000-0000-0000-000000000000,00000000-0000-0000-0000-000000000001"); // NOTE: Example when using the connection string property. connection.ConnectionString = "Data Source=test.db;SdsSeeApiKey=00000000000000000000000000000000;SdsSeeKeyPrefix=aes256;SdsSeeKeyIds=00000000-0000-0000-0000-000000000000,00000000-0000-0000-0000-000000000001;";

When using a self-hosted server, the read-only and read-write API keys should be obtained from your server administrator. When using a server hosted by Mistachkin Solutions LLC, the read-only and read-write API keys will be provided along with your license certificate.

8. 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>\ System.Data.SQLite.SEE.EKM.dll <bin>\ Eagle.dll <bin>\ Harpy.dll <bin>\ Badge.dll <bin>\ sds-see.eagle <bin>\ sds-see.eagle.b64sig <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.

When the encrypted secrets returned from the server are signed with a trusted key pair, the following additional files also are required to be present in the application binary directory:

<bin>\ keyRing.ekm-secrets1.eagle <bin>\ keyRing.ekm-secrets1.eagle.harpy

The keyRing.ekm-secrets1.eagle key ring file and its associated signature file will be provided based on the " public.snk " strong name public key file generated in step #9.

If your organization needs to provision and use signed secrets, please send the " public.snk " strong name public key file (generated in step #9) along with a support request to the Enterprise Key Management plugin support team with a subject line of "EKM trusted key ring".

Some files copied into the application binary directory during the build process are not used by the Enterprise Key Management NuGet packages for System.Data.SQLite (i.e. included via transitive NuGet package dependencies), they include:

<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\ 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>

9. Creating new connection secrets requires using the secret provisioning tool, which requires the following additional files:

<bin>\ EagleShell.dll (only when using .NET Core) <bin>\ EagleShell.exe (only when using .NET Framework) <bin>\ ekm-provision.eagle <bin>\ ekm-provision.eagle.harpy <bin>\ license.data.eagle <bin>\ license.data.eagle.harpy <bin>\ private.snk (only when provisioning signed secrets)

The license.data.eagle license data file and its associated signature file will be provided along with your license certificate.

When provisioning signed secrets, the " private.snk " strong name private key file should be generated using the Microsoft Strong Name Tool from the Visual Studio Command Prompt by using commands similar to the following:

sn.exe -k 4096 private.snk sn.exe -p private.snk public.snk

To run the secret provisioning tool for the .NET Framework, execute the following command from the directory containing all the required files:

EagleShell.exe -preInitialize "set apiKey 00000000000000000000000000000000" -file ekm-provision.eagle

To run the secret provisioning tool for the .NET Core, execute the following command from the directory containing all the required files:

dotnet exec EagleShell.dll -preInitialize "set apiKey 00000000000000000000000000000000" -file ekm-provision.eagle

In the above secret provisioning tool command lines, the read-write API key value should be replaced with the actual read-write API key value provided by your server administrator or the Enterprise Key Management plugin support team.

Upon successful completion, the output of the secret provisioning tool command should be the newly created unique identifier to use within the " SdsSeeKeyIds " connection string property value or environment variable value.

10. 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" />

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