PII Scrub Pipeline

Production database copies intended for developer use must have all personally identifiable information (PII) removed before access is granted. This page describes the scrub process and the runbooks that implement it.

See Azure Automation Runbooks for the full runbook catalog.

Active Runbooks

Runbook	Use Case	Auth
ar-eshars-database-scrub	Primary — prod → developer access server	Managed Identity
aa-eshars-prod-to-qa-scrub	Prod → QA/Regression with environment-specific config	RunAs (legacy)
ar-eshars-qa-copy-for-dev	QA → developer access (audit-table-only scrub)	Managed Identity

Legacy runbooks — do not use

These contain hardcoded storage account keys and outdated auth patterns:

- `ar-eshars-proddb-copy-and-scrub-for-devs`
- `Test-latestprodcopyto-sqldeveloperaccess`
- `ar-eshars-drop-db-eshars-backupscrub` — misleading name; actually a full copy+scrub

Pipeline Steps

The primary runbook (ar-eshars-database-scrub) follows this workflow:

1.  Authenticate (Managed Identity)
2.  Retrieve SQL credentials from Automation
3.  Verify source database exists (db-eshars-prod)
4.  Delete target if it already exists (db-eshars-backupscrub on db-eshars-developeraccess)
5.  Copy production database to developer server
6.  Poll until copy is Online
7.  Run PII scrub (see below)
8.  Export scrubbed database as .bacpac to esharsdbstorage/db-storage
9.  Verify export completed
10. Drop the temporary scrub database (automatic cleanup)

Step 10 — auto-cleanup

As of March 2026, the runbook automatically drops the temp database after a successful export. If the drop fails, a warning is logged but the bacpac is still available. This replaced a manual cleanup step that was frequently forgotten, leaving an expensive temp DB running.

What Gets Scrubbed

Configuration (OrgPolicyValue)

Setting	Replacement
TMHP Production ID	Dummy value
FTP/SFTP host, username, password, paths	Dummy/disabled values
Billing SFTP credentials	Dummy values
”Send to TMHP” flag	False

Student Data

Table: Student

• FirstName / LastName — shuffled randomly across all rows (ORDER BY NEWID())
• SSN — replaced with deterministic formula based on row ID (grouped: students sharing the same SSN get the same replacement)
• PeimsId — same approach as SSN
• MedicaidId — same approach as SSN
• AddressLine1 — set to '{StudentId} Scrubbed Street'
• AddressLine2 — set to NULL
• DateOfBirth — masked via dbo.fnMaskDate()
• City, State, Zip — set to dummy values

Table: StudentTmhpInfo — Students matched to Student inherit randomized SSN/PEIMS/MedicaidId. Unmatched rows get independent randomization.

Table: EligibilityResponseStudent — SubscriberSSN, MedicaidId randomized.

Table: ImportStaging_Student — FirstName, LastName, DateOfBirth, SSN, PeimsId, MedicaidId randomized.

User Accounts

Table: Users

• UserName — set to 'Trainer{Id}'
• Password — reset to a fixed hash (universal training password)
• Email — masked via dbo.fnMaskEmail()
• DefaultOrganization — set to NULL

Financial Data

Tables: finance.TmhpVisitDataExtract, finance.CentricityVisitPayment — Truncated entirely (prod-to-QA scrub only).

Temporal / Audit Tables

Before scrubbing, the scripts disable system versioning on temporal tables, truncate the auditlog.*_History tables, then re-enable versioning. Affected tables:

Appeal, StudentArdDetail, StudentCampusEnrollment, StudentMedicaidEffectiveDate, Student, StudentTmhpInfo, EligibilityResponseStudent, OrgPolicyValue, StudentService, StudentParentalConsent, Users, Visit

Database Functions Used

Function	Purpose
dbo.fnMaskDate(date)	Returns a masked/shifted date
dbo.fnMaskEmail(email)	Returns a masked email address

These functions must exist in the target database before the scrub runs. They are part of the HisdDb schema (maintained in the HISD repo).

Hybrid Worker Requirement

The primary scrub runbook must run on the Hybrid Runbook Worker group run-bacpacs-group (VM: vm-sql-access in rg-eshars-nonprod). The copy + scrub + export pipeline can exceed 2 hours, which surpasses Azure Automation’s sandbox fair share limit.

See Hybrid Worker for VM details, migration plan, and cost analysis.

Network Configuration

The Hybrid Worker VM (vm-sql-access, East US 2) connects to db-eshars-developeraccess (Central US) via SQL Server firewall rule allow-vm-sql-access for the VM’s outbound public IP (20.49.60.151). A VNet service endpoint was not viable — Azure SQL VNet rules cannot cross regions.

Environment-Aware Scrub (aa-eshars-prod-to-qa-scrub)

Adjusts OrgPolicyValue settings based on the target environment:

• Regression — SFTP paths set to regression-specific endpoints
• QA/Other — SFTP paths set to dev/QA endpoints
• Both disable TMHP submission and replace credentials with dummies

QA-to-Dev Scrub (ar-eshars-qa-copy-for-dev)

Since QA data is already PII-scrubbed, this runbook only:

1. Copies QA → developer access server
2. Clears temporal/audit history tables (truncates auditlog.*_History)
3. Re-enables system versioning
4. Exports .bacpac as db-eshars-backupscrub-qa-dev.bacpac

No PII randomization is performed.