Hotfix Digital Signature Validation - BBCWiki/IPos-Internal GitHub Wiki

SQL Hotfix Signing and Validation Documentation

This document describes the process of creating the public certificate (.cer), signing SQL hotfixes, and validating them inside DatabaseUpdater.

1. Export Public Certificate (.cer) from the Signing Certificate

If a .pfx certificate already exists and is used for ClickOnce / Code Signing:

  1. Import the .pfx into Windows:
certmgr.msc
→ Personal
→ Certificates
  1. Right click the certificate:
All Tasks → Export
  1. Select:
NO, do not export private key
  1. Choose format:
DER encoded binary X.509 (.CER)
  1. Save the file as:
hotfix-public.cer

Alternative: PowerShell export

$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2(
    "certificate.pfx",
    "PASSWORD"
)

Export-Certificate `
    -Cert $cert `
    -FilePath "hotfix-public.cer"

2. Signing Hotfixes

Inside the LicenseGenerator utility, open the SignHotfixes tab.

Steps:

  1. Select the hotfix folder
  2. Select the signing certificate (.pfx)
  3. Enter the certificate password
  4. Select hotfix-public.cer
  5. Click Scan files
  6. Click Sign missing signatures

For every SQL file:

001_Fix.sql
    ↓
001_Fix.sql.sig

The .sig file must always be published together with the .sql file on the portal.

Example:

1.2604.0
│
├── 001_Fix.sql
├── 001_Fix.sql.sig
│
├── 002_Fix.sql
└── 002_Fix.sql.sig

3. Validation Certificate Placement

The validation certificate must be added to the DatabaseUpdater project.

Project structure:

DatabaseUpdater
│
├── Certificates
│   └── hotfix-public.cer
│
├── UpdateHelper.cs
└── ...

File properties:

Build Action = Content
Copy to Output Directory = Copy if newer

Certificate loading:

Path.Combine(
    AppDomain.CurrentDomain.BaseDirectory,
    "Certificates",
    "hotfix-public.cer")

4. Validation Flow

During ApplyHotFixes() execution:

  1. SQL file is downloaded
  2. Signature file (.sig) existence is checked
  3. Signature validation is performed
  4. If signature is missing or invalid:
  • UpdateCommand is created
  • Hotfix is NOT executed
  • UpdateCommandStores is marked as Failed
  • Error message is stored in ErrorMessage
  1. If signature is valid:
  • SQL script is executed

Flow:

Download SQL
      ↓
Check .sig existence
      ↓
Validate signature
      ↓
Valid? ───── No ─────→ Failed / Not Executed
      │
     Yes
      ↓
Execute SQL

5. Result Verification

Success

UpdateCommandStores.IsOk = true
ErrorMessage = rows affected

Failure

UpdateCommandStores.IsOk = false
ErrorMessage = error description

Examples:

Missing signature file...

Invalid hotfix signature...

SQL execution errors...

Detailed examples:

Missing signature file for hotfix '001_fix.sql'
Expected signature url:
http://portal/.../Fix001.sql.sig
HTTP Status: 404 Not Found

Invalid hotfix signature.
The SQL file may have been modified after signing.

Invalid object name...

Security Notes

  • The private key (.pfx) must never be distributed.
  • Any modification after signing invalidates the signature.
  • Missing or invalid signatures prevent hotfix execution.