SQL Server

Microtec ERP uses a SQL Server VM in the dedicated mic-backend-shared-sql-rg resource group. This single SQL Server instance hosts all tenant databases, the admin database, and the Keycloak database across all environments except production (which uses a separate, hardened instance).

---

Server Details

Property	Value
Resource Group	mic-backend-shared-sql-rg
VM Name	mic-shared-sql
Public IP	20.50.120.95
SSH Access	ssh -i ~/.ssh/mic-shared-sql sqladmin@20.50.120.95
SQL Port	1433
SQL Edition	SQL Server 2022 Developer / Standard
Region	UK South

Shared Across Environments

This SQL Server VM is shared by dev, stage, preprod, and UAT environments. It is intentionally NOT managed by the per-environment Bicep infrastructure (it predates the Bicep refactor). Do NOT include it in deprovision.yml — destroying it would affect all environments simultaneously.

---

Database Inventory

Database Name	Purpose	Used By
microtec_admin	Cross-tenant admin data (companies, branches, users)	All backend services, Keycloak mappers
keycloak_dev	Keycloak SSO data for dev	Keycloak (dev)
keycloak_stage	Keycloak SSO data for stage	Keycloak (stage)
keycloak_preprod	Keycloak SSO data for preprod	Keycloak (preprod)
keycloak_uat	Keycloak SSO data for UAT	Keycloak (UAT)
tenant_{id}	Per-tenant ERP data (one DB per company)	Tenant-specific services

The tenant databases follow the pattern tenant_{tenantId} where tenantId is the integer primary key from microtec_admin.companies.

---

Database-per-Tenant Pattern

The backend's ITenantProvider reads the JWT companyId claim and resolves the connection string from a lookup table in microtec_admin:

// TenantProvider.cs
public async Task<string> GetConnectionStringAsync(int companyId)
{
    // Admin DB lookup: returns "Server=...;Database=tenant_{id};..."
    return await _adminRepository.GetTenantConnectionStringAsync(companyId);
}

---

Connection String Format

Admin Database

Server=20.50.120.95,1433;Database=microtec_admin;
User Id=admin_svc;Password={from-kv};
Encrypt=True;TrustServerCertificate=True;
MultipleActiveResultSets=True;Connection Timeout=30;

Tenant Database (resolved at runtime)

Server=20.50.120.95,1433;Database=tenant_{id};
User Id=tenant_svc;Password={from-kv};
Encrypt=True;TrustServerCertificate=True;
MultipleActiveResultSets=True;Connection Timeout=30;
Application Name={ServiceName};

TrustServerCertificate

TrustServerCertificate=True is required because the SQL VM uses a self-signed certificate. All traffic is encrypted in transit (SSL/TLS) — this setting only bypasses CN validation, not encryption.

Key Vault Secret Names

Secret Name	Connection String
ConnectionStrings--Admin	Admin database
ConnectionStrings--Default	Template for tenant DB (resolved at runtime)
Keycloak--DbPassword	Keycloak service account password

---

SQL Service Accounts

Account	Permissions	Used By
sqladmin	sysadmin (VM OS account)	SSH access, DBA operations
admin_svc	db_datawriter, db_datareader on microtec_admin	Backend services (admin queries)
tenant_svc	db_owner on all tenant_* DBs	Backend services (tenant operations)
sa	db_owner on Microtec.SSO DB	Keycloak (schema management)
readonly_svc	db_datareader on all DBs	Reporting, Seq, analytics

Creating a New Tenant Database

When a new company is provisioned, the Business Owner portal runs a migration script to create tenant_{newId} and grant tenant_svc ownership. This is handled by the Microtec provisioning workflow — do not create tenant databases manually.

---

DataProtection Key Storage

DataProtection Keys Are Machine-Specific

ASP.NET Core DataProtection keys are stored in the admin database (microtec_admin.DataProtectionKeys table). These keys encrypt cookies and session tokens. Keys encrypted on one server cannot be decrypted on a different server unless the key ring is shared.

If you migrate to a new SQL VM, export the DataProtectionKeys table and import it into the new instance before switching connection strings. Failure to do so will immediately invalidate all active user sessions.

---

Backup and Recovery

Backup Type	Frequency	Retention
Full backup	Daily (2:00 AM GST)	30 days
Differential	Every 6 hours	7 days
Transaction log	Every 15 minutes	3 days

Backups are stored in Azure Blob Storage in the mic-backend-shared-sql-rg storage account. Point-in-time restore is possible up to 3 days back.

# SSH to SQL Server VM
ssh -i ~/.ssh/mic-shared-sql sqladmin@20.50.120.95

# Check last backup time
/opt/mssql-tools/bin/sqlcmd -S localhost -U sqladmin -Q \
  "SELECT database_name, backup_finish_date, type
   FROM msdb.dbo.backupset
   WHERE backup_finish_date > DATEADD(DAY,-1,GETDATE())
   ORDER BY backup_finish_date DESC"

---

Common DBA Operations

Create a New Tenant Database

-- Run via SSH: sqlcmd -S localhost -U sqladmin
CREATE DATABASE tenant_99;
GO
USE tenant_99;
CREATE USER tenant_svc FOR LOGIN tenant_svc;
ALTER ROLE db_owner ADD MEMBER tenant_svc;
GO

Check Active Connections

SELECT DB_NAME(dbid) AS DatabaseName, COUNT(*) AS ConnectionCount
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY DB_NAME(dbid)
ORDER BY ConnectionCount DESC;

Kill Blocking Sessions

# Note: SA password contains '!' — use Python to avoid shell escaping issues
python3 -c "
import subprocess
subprocess.run([
    '/opt/mssql-tools/bin/sqlcmd', '-S', 'localhost', '-U', 'sqladmin',
    '-P', 'ActualPassword!',
    '-Q', 'KILL {blocking_spid}'
])
"

Shell Escaping — SA Password

If the sqladmin password contains special characters (!, $, etc.), do NOT pass it directly in a bash heredoc or double-quoted string. Use Python's subprocess.run() with a list argument (shown above) or a single-quoted bash string with no variable interpolation.

---

Connectivity from Container Apps

Container Apps reach the SQL VM via private endpoint or direct IP:

• Dev/Stage: Direct IP (20.50.120.95) over the internet with TLS
• PrepProd/UAT: Via VNet peering to the SQL VM VNet
• Production: Via dedicated private endpoint in mic-backend-shared-sql-rg

The SQL port 1433 is open in the NSG only to Container App outbound IPs (NAT Gateway public IP per environment).

---

Related Documentation

• Key Vault — Where connection string secrets are stored
• Multi-Tenancy Architecture — Tenant isolation design
• Keycloak Deployment — Keycloak database setup
• Resource Groups — mic-backend-shared-sql-rg scope