CiAnywhere → Azure Table Storage Employee Sync — Implementation Guide

Implementation guide for sending CiAnywhere employee data to Azure Functions and Azure Table Storage.

What this builds

CiAnywhere's ETL builds an XML document of employee rows and POSTs it, HMAC-signed, to an Azure Function. The Function verifies the signature, stores the raw payload, and queues it for processing. A second Function picks the message off the queue, parses the XML, and upserts each row into Azure Table Storage — which then becomes a data source for your other automations.

CiAnywhere ETL
     │  HTTP POST (XML, HMAC-signed)
     ▼
[Receiver Function]  ──▶ Blob (raw payload, audit/replay trail)
     │  enqueue message
     ▼
[Storage Queue]
     │
     ▼
[Processor Function] ──▶ Azure Table Storage (upserted rows)
     │
     ▼
Application Insights / Log Analytics ──▶ Alerts (silence, write failures)

The implementation is divided into milestones, each ending with a validate before continuing step. Two details need to be confirmed in the target environment: CiAnywhere's exact HMAC signature format and Az PowerShell module behavior, which can vary across versions. The relevant checks are noted in the steps below.

Table Storage authentication options

An earlier design considered using Managed Identity for Table Storage row-level operations via an Az.Tables module. There is no official Microsoft PowerShell module for Table entity operations. Az.Storage manages tables themselves (create/list/delete), not rows. The community module for row-level work (AzTable) depends on an older SDK that only supports Shared Key authorization, rather than Managed Identity.

The implementation therefore uses:

Blob and Queue access from the Functions: Managed Identity, via Az.Storage's -UseConnectedAccount context (fully supported, no secrets needed).
Table row operations: the AzTable module, authenticated with the storage account's connection string, stored in Key Vault (not a code secret you manage by hand — just not Managed Identity for this one piece).

The storage key remains in Key Vault. A later hardening option is to call the Table REST API directly with a Managed-Identity-issued bearer token and build batch ($batch) requests. This is listed as a Phase 2 option in Milestone 4, not part of the initial implementation.

Prerequisites

Az PowerShell modules installed locally (Install-Module Az -Scope CurrentUser), PowerShell 7.
Azure Functions Core Tools, if you want to develop/test locally before publishing (winget install Microsoft.Azure.FunctionsCoreTools).
Contributor access on the target subscription/resource group.
Access to CiAnywhere's ETL Designer to configure the web call step.
A resource-naming convention — the script below uses placeholders; storage account, Key Vault, and Function App names must be globally unique, so adjust them.

Milestone 0 — Provision the core Azure resources

# --- Adjust these to your naming convention; storage/KV/FunctionApp names must be globally unique ---
$resourceGroup   = "rg-cia-employee-sync"
$location        = "australiaeast"
$storageAccount  = "stciaemployeesync01"     # lowercase, no hyphens, globally unique
$keyVaultName    = "kv-cia-employee-sync"    # globally unique
$functionAppName = "func-cia-employee-sync"  # globally unique
$appInsightsName = "$functionAppName-ai"

New-AzResourceGroup -Name $resourceGroup -Location $location

New-AzStorageAccount -ResourceGroupName $resourceGroup -Name $storageAccount `
    -Location $location -SkuName Standard_LRS -Kind StorageV2 -MinimumTlsVersion TLS1_2

# RBAC-authorization Key Vault (not the legacy access-policy model)
New-AzKeyVault -ResourceGroupName $resourceGroup -VaultName $keyVaultName `
    -Location $location -EnableRbacAuthorization $true

# Function App — PowerShell worker, Consumption plan to start.
# Check the current supported PowerShell version for the Functions runtime when you run this —
# it changes over time; use the latest 7.x offered.
New-AzFunctionApp -ResourceGroupName $resourceGroup -Name $functionAppName `
    -StorageAccountName $storageAccount -Location $location `
    -Runtime PowerShell -RuntimeVersion 7.4 -FunctionsVersion 4 `
    -OSType Windows -PlanType Consumption

# System-assigned managed identity
Update-AzFunctionApp -ResourceGroupName $resourceGroup -Name $functionAppName -IdentityType SystemAssigned

# Confirm/link Application Insights — recent Az.Functions versions auto-provision this;
# if it's not already linked (check the portal), create and wire it explicitly:
$ai = New-AzApplicationInsights -ResourceGroupName $resourceGroup -Name $appInsightsName -Location $location
Update-AzFunctionAppSetting -ResourceGroupName $resourceGroup -Name $functionAppName -AppSetting @{
    "APPLICATIONINSIGHTS_CONNECTION_STRING" = $ai.ConnectionString
}

# Create the blob container, queue, and table (run this as an identity with Storage Data
# Contributor roles on this account — e.g. your own, if you provisioned it)
$adminCtx = New-AzStorageContext -StorageAccountName $storageAccount -UseConnectedAccount
New-AzStorageContainer -Name "cia-raw-payloads" -Context $adminCtx -Permission Off
New-AzStorageQueue -Name "cia-employee-ingest" -Context $adminCtx
New-AzStorageTable -Name "Employees" -Context $adminCtx

# Role assignments for the Function App's managed identity
$principalId = (Get-AzFunctionApp -ResourceGroupName $resourceGroup -Name $functionAppName).IdentityPrincipalId
$storageId   = (Get-AzStorageAccount -ResourceGroupName $resourceGroup -Name $storageAccount).Id
$vaultId     = (Get-AzKeyVault -VaultName $keyVaultName).ResourceId

New-AzRoleAssignment -ObjectId $principalId -RoleDefinitionName "Key Vault Secrets User"       -Scope $vaultId
New-AzRoleAssignment -ObjectId $principalId -RoleDefinitionName "Storage Blob Data Contributor"  -Scope $storageId
New-AzRoleAssignment -ObjectId $principalId -RoleDefinitionName "Storage Queue Data Contributor" -Scope $storageId

✅ Validate before continuing: in the portal, confirm the Function App shows "Running," has an Identity blade with a populated Object ID, and Application Insights appears under its Overview. Confirm the container, queue, and table all exist in Storage Explorer.

Milestone 1 — Generate and store the HMAC secret

# Cryptographically random 256-bit secret
$bytes = New-Object 'System.Byte[]' 32
[System.Security.Cryptography.RandomNumberGenerator]::Fill($bytes)
$hmacSecret = [Convert]::ToBase64String($bytes)

Set-AzKeyVaultSecret -VaultName $keyVaultName -Name "CiAnywhereHmacSecret" `
    -SecretValue (ConvertTo-SecureString $hmacSecret -AsPlainText -Force) `
    -Expires (Get-Date).AddDays(365) `
    -ContentType "HMAC shared secret — CiAnywhere webhook integration"

Write-Host "COPY THIS INTO A CiAnywhere VARIABLE NOW (it will not be shown again): $hmacSecret"

# Also store the storage account connection string, for the AzTable-based writes in Milestone 4
$storageKey = (Get-AzStorageAccountKey -ResourceGroupName $resourceGroup -Name $storageAccount)[0].Value
$connString = "DefaultEndpointsProtocol=https;AccountName=$storageAccount;AccountKey=$storageKey;EndpointSuffix=core.windows.net"
Set-AzKeyVaultSecret -VaultName $keyVaultName -Name "StorageAccountConnectionString" `
    -SecretValue (ConvertTo-SecureString $connString -AsPlainText -Force)

# Wire both into the Function App as Key Vault references (resolved automatically at runtime)
$kvUri = (Get-AzKeyVault -VaultName $keyVaultName).VaultUri
Update-AzFunctionAppSetting -ResourceGroupName $resourceGroup -Name $functionAppName -AppSetting @{
    "HmacSharedSecret"        = "@Microsoft.KeyVault(SecretUri=$($kvUri)secrets/CiAnywhereHmacSecret/)"
    "StorageConnectionString" = "@Microsoft.KeyVault(SecretUri=$($kvUri)secrets/StorageAccountConnectionString/)"
    "StorageAccountName"      = $storageAccount
}

✅ Validate before continuing: in the portal, open the Function App's Configuration → Application Settings — HmacSharedSecret and StorageConnectionString should show a green "Key Vault Reference" resolution status, not an error icon. If it shows an error, the role assignment from Milestone 0 likely hasn't propagated yet (can take a few minutes) or the SecretUri is malformed.

Milestone 2 — Deploy a logging-only receiver (to observe CiAnywhere's real HMAC format)

We don't yet know exactly how CiAnywhere's "HMAC Authentication" type signs a request — which header it uses, and whether the signature is hex or base64. Rather than guess, deploy a version that logs everything first.

Receiver/function.json

{
  "bindings": [
    { "authLevel": "function", "type": "httpTrigger", "direction": "in", "name": "Request", "methods": ["post"] },
    { "type": "http", "direction": "out", "name": "Response" }
  ]
}

Receiver/run.ps1

using namespace System.Net
param($Request, $TriggerMetadata)

Write-Host "=== Headers ==="
$Request.Headers.GetEnumerator() | ForEach-Object { Write-Host "$($_.Key): $($_.Value)" }

Write-Host "=== Body (first 2000 chars) ==="
$bodyText = [string]$Request.Body
Write-Host $bodyText.Substring(0, [Math]::Min(2000, $bodyText.Length))

Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
    StatusCode = [HttpStatusCode]::OK
    Body       = "Logged."
})

Note: exact property shapes on $Request can vary slightly by Functions host version — if .Headers.GetEnumerator() doesn't behave as expected, run $Request.Headers | Get-Member in a quick test to see what you're actually working with.

Configure CiAnywhere:

Create a variable holding the HMAC secret value (the one generated in Milestone 1).
On the Call Web Resource step: Authentication → Type = HMAC Authentication, Credentials → point at that variable.
Set the URL to the Function's endpoint, including its function key (https://<app>.azurewebsites.net/api/Receiver?code=<function-key>) — found under the function's "Function Keys" blade after first deploy.
Run the ETL process.

✅ Validate before continuing: pull the invocation in Application Insights (Live Metrics or Logs → search for "Headers") and identify:

The exact header name carrying the signature (e.g. X-Signature, Authorization, something else entirely).
Whether the value looks like hex (a3f9...) or base64 (o/n5...==).
Whether there's a timestamp or nonce header alongside it (relevant for replay protection).

Write these down — Milestone 3 depends on them.

Milestone 3 — Harden the receiver: real signature verification, blob write, queue enqueue

Receiver/run.ps1 (replace the logging-only version)

using namespace System.Net
param($Request, $TriggerMetadata)

# --- 1. Verify signature --------------------------------------------------
# Adjust the header name and comparison format below to match what Milestone 2 revealed.
$signatureHeader = $Request.Headers["X-Signature"]     # <-- confirm real header name
$secret          = $env:HmacSharedSecret
$rawBody         = [string]$Request.Body

$hmac = [System.Security.Cryptography.HMACSHA256]::new([Text.Encoding]::UTF8.GetBytes($secret))
$computedBytes = $hmac.ComputeHash([Text.Encoding]::UTF8.GetBytes($rawBody))
$computedHex   = ($computedBytes | ForEach-Object { $_.ToString("x2") }) -join ""
$computedB64   = [Convert]::ToBase64String($computedBytes)

$isValid = ($signatureHeader -eq $computedHex) -or ($signatureHeader -eq $computedB64)

if (-not $isValid) {
    Write-Host "IngestRejected: signature mismatch"
    Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
        StatusCode = [HttpStatusCode]::Unauthorized
        Body       = "Invalid signature."
    })
    return
}

# --- 2. Persist raw payload to blob (audit / replay trail) ---------------
$context  = New-AzStorageContext -StorageAccountName $env:StorageAccountName -UseConnectedAccount
$blobName = "raw/{0:yyyy/MM/dd}/{1}.xml" -f (Get-Date), [Guid]::NewGuid()
$tempFile = Join-Path $env:TEMP "$([Guid]::NewGuid()).xml"
Set-Content -Path $tempFile -Value $rawBody -Encoding UTF8
Set-AzStorageBlobContent -Context $context -Container "cia-raw-payloads" -File $tempFile -Blob $blobName -Force | Out-Null

# --- 3. Enqueue for processing ---------------------------------------------
$queue   = Get-AzStorageQueue -Name "cia-employee-ingest" -Context $context
$message = (@{ blob = $blobName } | ConvertTo-Json -Compress)
# If AddMessageAsync isn't found on your installed Az.Storage version, run
# `$queue.CloudQueue | Get-Member` to find the equivalent — the underlying SDK type has shifted
# across module versions.
$queue.CloudQueue.AddMessageAsync([Microsoft.Azure.Storage.Queue.CloudQueueMessage]::new($message)).GetAwaiter().GetResult() | Out-Null

# --- 4. Heartbeat telemetry --------------------------------------------------
Write-Host "IngestSuccess: blob=$blobName"

Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
    StatusCode = [HttpStatusCode]::Accepted
    Body       = "Accepted for processing."
})

✅ Validate before continuing, two tests:

Send a request with a deliberately wrong secret (e.g. Invoke-RestMethod from your own machine with a bogus signature) → expect 401, and an IngestRejected trace in App Insights.
Run the real CiAnywhere ETL → expect 202, a new blob under cia-raw-payloads, and a new message in cia-employee-ingest.

Milestone 4 — Processor function (queue-triggered): parse XML, upsert to Table Storage

Processor/function.json

{
  "bindings": [
    { "name": "QueueItem", "type": "queueTrigger", "direction": "in", "queueName": "cia-employee-ingest", "connection": "AzureWebJobsStorage" }
  ]
}

AzureWebJobsStorage is the Function App's own required storage connection — already present for the runtime to function at all, so it's the path of least resistance here. Converting this specific binding to a fully identity-based connection is possible (extension bundles support it) but not necessary for this build.

Processor/run.ps1

param($QueueItem, $TriggerMetadata)

$context  = New-AzStorageContext -StorageAccountName $env:StorageAccountName -UseConnectedAccount
$blobName = $QueueItem.blob

$tempFile = Join-Path $env:TEMP "$([Guid]::NewGuid()).xml"
Get-AzStorageBlobContent -Context $context -Container "cia-raw-payloads" -Blob $blobName -Destination $tempFile -Force | Out-Null
[xml]$xml = Get-Content $tempFile -Raw

$tableCtx = New-AzStorageContext -ConnectionString $env:StorageConnectionString
$table    = Get-AzStorageTable -Name "Employees" -Context $tableCtx -ErrorAction Stop

# PartitionKey/RowKey design — confirm this fits how you'll actually query this table later.
# Fixed partition keeps batching simple at this scale; shard by e.g. school/department if you'll
# need partition-scoped queries. RowKey must be unique per row — employeeId alone isn't, given
# the duplicate employeeIds seen in test data, so it's combined with jobTitle here as a stand-in.
# Swap in whatever field combination is actually guaranteed unique per row (e.g. position code).
$partitionKey = "Employees"
$rows = $xml.Employees.Employee
$failures = 0

foreach ($row in $rows) {
    $rowKey = "{0}_{1}" -f $row.employeeId, ($row.jobTitle -replace '\s+', '')
    try {
        # Confirm -UpdateExisting is the correct current parameter for upsert behaviour via
        # `Get-Help Add-AzTableRow -Full` — this has been known to shift across module versions.
        Add-AzTableRow -Table $table.CloudTable -PartitionKey $partitionKey -RowKey $rowKey `
            -Property @{ EmployeeId = $row.employeeId; JobTitle = $row.jobTitle } `
            -UpdateExisting
    }
    catch {
        $failures++
        Write-Host "TableWriteFailed: employeeId=$($row.employeeId) rowKey=$rowKey error=$($_.Exception.Message)"
    }
}

if ($failures -gt 0) {
    # Throwing lets the queue trigger's built-in retry (default 5 attempts) and poison-queue
    # handling take over, rather than silently swallowing a partial failure.
    throw "ProcessorPartialFailure: $failures of $($rows.Count) rows failed for blob $blobName"
}

Write-Host "ProcessSuccess: blob=$blobName rows=$($rows.Count)"

requirements.psd1 (shared across both functions)

@{
    'Az.Accounts' = '3.*'
    'Az.Storage'  = '7.*'
    'AzTable'     = '2.*'
}

profile.ps1 (enables Managed Identity auth at cold start)

if ($env:MSI_SECRET) {
    Disable-AzContextAutosave -Scope Process | Out-Null
    Connect-AzAccount -Identity | Out-Null
}

host.json (extend the timeout if 10,000-row batches run long on Consumption; 10 min is the Consumption-plan ceiling — move to a Premium plan if you outgrow it)

{
    "version": "2.0",
    "functionTimeout": "00:10:00"
}

Phase 2 hardening (optional, not needed to go live): replace the AzTable/connection-string path with direct Table REST API calls authenticated by a Managed-Identity bearer token (https://storage.azure.com/ resource), using $batch entity-group transactions (up to 100 entities sharing a partition per batch) for real throughput at 1,000–10,000 rows. This removes the storage-key secret entirely but is meaningfully more code to get right — worth doing once the simpler version is stable and proven, not before.

✅ Validate before continuing:

Confirm rows appear correctly in Table Storage (Storage Explorer).
Re-run the same queue message a second time — confirm rows update in place rather than duplicate (proves the upsert is working).
Deliberately corrupt one row's data in a test payload and confirm it shows up as a TableWriteFailed trace, and that the message ends up retried and eventually in cia-employee-ingest-poison if it keeps failing.

Milestone 5 — Logging and alerting

Application Insights is already wired via APPLICATIONINSIGHTS_CONNECTION_STRING; Write-Host calls above land there as traces automatically.

KQL — silence detection ("no data received"):

traces
| where message startswith "IngestSuccess"
| where timestamp > ago(10h)   // set to comfortably cover one missed run once you know the real schedule
| count

Alert condition: fire when this returns 0.

KQL — write failures:

traces
| where message startswith "TableWriteFailed"
| where timestamp > ago(15m)

Alert condition: fire when count > 0.

Poison-queue alert: a metric alert on the Storage Account, metric "Queue Message Count," dimension filtered to cia-employee-ingest-poison, threshold "greater than 0."

Action group:

New-AzActionGroup -ResourceGroupName $resourceGroup -Name "ag-cia-sync-alerts" -ShortName "ciasync" `
    -Receiver @(New-AzActionGroupReceiver -Name "PrimaryEmail" -EmailReceiver -EmailAddress "you@yourdomain.edu.au")

For the scheduled-query (log search) alert rules themselves: the PowerShell cmdlet for these (New-AzScheduledQueryRule) has changed its parameter shape across Az.Monitor versions enough that I'd rather not hand you a snippet likely to be stale. Build these two via the Portal wizard (Monitor → Alerts → Create → Alert rule → scope to the Function App / Log Analytics workspace → paste the KQL above) the first time — it's a guided flow and hard to get subtly wrong. Once you've got a working rule, you can export it and script future ones from that as a template.

✅ Validate before continuing: trigger each alert condition once deliberately — pause the CiAnywhere schedule briefly to trip the silence alert, and force a row failure to trip the write-failure alert — and confirm the email actually arrives.

Milestone 6 — CiAnywhere-side configuration recap

Variable holding the HMAC secret (Milestone 1), pointed to by the Call Web Resource step's Authentication → Credentials.
Authentication Type = HMAC Authentication.
URL = Function endpoint including the function-level key (?code=...) — a second layer on top of the HMAC check, per the earlier "stack both layers" reasoning.
Content Type = text/xml.
Save Xml step feeding the built XML into the variable referenced by Call Web Resource.

Milestone 7 — Rotation and ongoing maintenance

The HMAC secret has no forced expiry (unlike App Registration secrets) — it's stored in Key Vault with a self-imposed 365-day expiry as a reminder, not a hard cutoff.
Optional: set up a Key Vault "near expiry" Event Grid subscription to email you ~30 days before the secret's Expires date, so rotation doesn't rely on remembering.
To rotate: generate a new secret, update the Key Vault entry, update the CiAnywhere variable, confirm one successful run, then let the old value lapse.
Revisit the silence-alert threshold once CiAnywhere's real run schedule is confirmed in production — the 10-hour placeholder above was chosen for a "several times a day" cadence and should be tightened once you know the actual interval.