SQLite Encryption Error in Safety Designer: Resolving “HexPassword” Logic Issues

Issue Overview: Understanding the SEE Dependency Conflict

The core issue at hand revolves around the SICK Safety Designer software’s reliance on the SQLite Encryption Extension (SEE), a licensed add-on for SQLite that enables database encryption. The error message encountered—"SQL logic error – Cannot use ‘HexPassword’ connection string property: library was not built with encryption support"—directly indicates that the SQLite library being used by the software lacks SEE capabilities. This error typically surfaces when an application attempts to access an encrypted SQLite database using a standard (non-SEE) build of SQLite.

The Safety Designer software likely stores project data in encrypted SQLite databases, requiring SEE for decryption. The sudden appearance of this error after a software upgrade suggests one of two scenarios: either the upgraded version of Safety Designer introduced a new dependency on SEE without properly bundling an SEE-enabled SQLite library, or environmental changes on the user’s Windows 10 machine caused the software to load an incompatible SQLite library. The latter is supported by the observation that the same software functions correctly on a Windows 11 machine, implying environment-specific factors are at play.

Key technical details include the role of the HexPassword connection string property, which is specific to SEE. This property allows applications to supply an encryption key for database access. When the underlying SQLite library lacks SEE support, any reference to HexPassword will fail, as standard SQLite builds do not recognize encryption-related parameters. The error message’s reference to https://www.sqlite.org/see further confirms that the software expects SEE functionality.

Possible Causes: Environmental Conflicts and Version Mismatches

1. DLL Search Path Conflicts
   Windows applications locate dynamically linked libraries (DLLs) using a predefined search order: the application directory, system directories, and paths specified in the PATH environment variable. If multiple SQLite DLLs exist on the system—e.g., a non-SEE version in C:\Windows\System32 and an SEE-compatible version in the Safety Designer installation folder—the software might inadvertently load the wrong DLL. This is exacerbated by Windows updates or third-party software installs that add SQLite libraries to global locations. The user’s observation of SQLite-related changes in C:\Windows\Microsoft.NET\assembly\GAC_64 on April 3, 2024, suggests a .NET assembly update that could have introduced a conflicting SQLite version.

2. Incomplete or Misconfigured Software Upgrade
   The Safety Designer upgrade might have failed to deploy an SEE-enabled SQLite DLL or incorrectly modified registry entries/DLL manifests. If the software’s installer did not properly overwrite existing SQLite installations or adjust the application’s DLL search order, it could result in the legacy SQLite library being prioritized. This would explain why downgrading to an older Safety Designer version did not resolve the issue: if the underlying SQLite environment was altered independently (e.g., via a Windows update), even older software versions would now reference the updated—and incompatible—SQLite library.

3. Windows Version-Specific Behavior
   Differences between Windows 10 and Windows 11 in handling DLL loading or security policies could contribute to the discrepancy. For instance, Windows 11 might enforce stricter isolation for application directories, preventing system-wide SQLite installations from interfering with Safety Designer’s bundled DLL. Alternatively, group policies or antivirus software on the Windows 10 machine might block the loading of unsigned or non-standard DLLs, forcing the software to fall back to a system-provided SQLite library.

Troubleshooting Steps, Solutions & Fixes: Resolving SEE Dependency Issues

Step 1: Identify Loaded SQLite DLLs Using SysInternals Tools
Download SysInternals Process Monitor and Process Explorer. Launch Process Monitor, set a filter for Path containing sqlite*.dll, and reproduce the error by opening a project in Safety Designer. Inspect the captured events to identify the full path of the SQLite DLL being loaded. Compare this with the DLLs present in the Safety Designer installation directory. If the loaded DLL is from a system directory (e.g., C:\Windows\System32) rather than the application’s folder, this indicates a search path conflict. Process Explorer can also be used to inspect the loaded modules of the Safety Designer process, revealing the exact SQLite DLL version in use.

Step 2: Enforce Application-Specific DLL Loading
To force Safety Designer to use its bundled SQLite DLL, modify the DLL search order using one of the following methods:

• Manifest File Modification: If Safety Designer includes an application manifest (SafetyDesigner.exe.manifest), ensure it specifies <dependency> sections that prioritize private DLLs. This requires editing the manifest to include:

  <dependency>
    <dependentAssembly>
      <assemblyIdentity type="win32" name="SQLite.SEE" version="3.42.0" processorArchitecture="amd64" />
    </dependentAssembly>
  </dependency>
  

  Note: Version and assembly identity must match the SEE DLL provided by SICK.

• Environment Variable Adjustment: Temporarily prepend the Safety Designer installation path to the PATH variable before launching the software:

  SET PATH=C:\Program Files\SICK\Safety Designer;%PATH%
  

  This ensures the application directory is searched first for DLLs.

Step 3: Clean Up Conflicting SQLite Installations
If a system-wide SQLite installation is conflicting, relocate or rename non-SEE DLLs. For example:

• Navigate to C:\Windows\System32 and C:\Windows\Microsoft.NET\assembly\GAC_64, search for sqlite3.dll or System.Data.SQLite.dll.
• Rename any matches to sqlite3.dll.bak or similar.
  Caution: This may affect other applications relying on these DLLs. Create a system restore point beforehand.

Step 4: Reinstall Safety Designer with Clean Environment
Uninstall Safety Designer, then manually remove any residual SQLite DLLs or registry entries associated with it. Use a tool like Revo Uninstaller to scan for leftovers. Reinstall the software while offline to prevent automatic Windows updates from reintroducing conflicting components. Post-installation, verify that the application directory contains an SEE-enabled SQLite DLL (e.g., sqlite3-see.dll). If not, manually replace it with a version provided by SICK support.

Step 5: Engage Vendor for SEE Licensing Verification
Escalate the issue to SICK’s technical support with evidence from Steps 1–4. Request confirmation that their software bundle includes a valid SEE-enabled SQLite build and that their licensing covers the user’s deployment. If SICK’s installation process does not properly deploy SEE components, they may need to issue a corrected installer or provide manual steps to integrate their licensed SQLite version.

Step 6: Fallback to Virtualized Environment
If environmental conflicts prove intractable, deploy Safety Designer within a Windows 11 virtual machine (VM) where it functions correctly. Use hypervisors like Hyper-V or VMware Workstation to create a VM with minimal software footprint, reducing the risk of DLL conflicts. This workaround ensures continuity while awaiting a permanent fix from SICK.

By systematically addressing DLL conflicts, enforcing correct library loading, and collaborating with the vendor to rectify SEE licensing or deployment issues, users can resolve the SQL logic error and restore full functionality to SICK Safety Designer.