Hotfix Digital Signature Validation

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

2. Right click the certificate:

All Tasks → Export

3. Select:

NO, do not export private key

4. Choose format:

DER encoded binary X.509 (.CER)

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

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