Sensitive Data Scenarios

For a public-sector role this is the question that decides the round: "This system holds candidate transcripts and personal records. How would you protect that data?" Generic answers ("encrypt everything, use HTTPS") read as junior. The strong answer is a layered walkthrough — data in transit, at rest, in logs, in memory of the org itself (who can see what), and in history (audit) — delivered with the assumption that a breach here isn't a bug ticket, it's a newspaper story and a statutory notification.

Transit, rest, and the layers people forget

In transit: TLS everywhere — including internal hops (app to SQL Server: Encrypt=True in the connection string; app to third parties). HSTS on the web tier. At rest: SQL Server TDE (transparent data encryption) covers files and backups but — say this — protects against stolen disks and backup tapes, not a compromised app or a DBA's query window. For the fields that hurt most (SSNs, DOBs), name Always Encrypted or column-level protection so even db_owner sees ciphertext, and Dynamic Data Masking as a lighter, bypassable convenience — knowing which tool is actual security shows depth. Round it out with backups encrypted and access-controlled — the classic breach is the forgotten backup, not the hardened prod box.

PII in logs — the leak nobody designs

The scenario version: "A developer logged the full request body to debug model binding. What's the problem?" Logs flow to places with weaker access control than the database — App Insights, files, third-party sinks — and they're retained, searched, and exported. Rules worth stating: log identifiers, not identities (candidate Id, never name + SSN); structured logging with an explicit redaction/destructuring policy for sensitive types; EnableSensitiveDataLogging for EF is dev-only, ever; and exception messages count too — a validation exception that echoes the input value is a leak. Bonus: retention limits on log sinks are a compliance control, not housekeeping.

Least privilege and audit trails

Least privilege applies at every seam: the app's SQL login gets CRUD on its schema — not db_owner, not sysadmin; app-tier roles gate who reads transcripts, not just who edits (resource-based authorization from lesson 1); production access for humans goes through just-in-time elevation, not standing rights. Audit trails: for records like transcripts, "who viewed this and when" is often a requirement, not a nicety. Options ladder: application audit table written in the same transaction as changes, EF SaveChanges interception stamping who/when/what-changed, and SQL Server temporal tables for tamper-resistant row history. Key property: the audit log is append-only, and readable by fewer people than the data itself.

Answers that fall flat

• "We encrypt the database" as the entire answer, or claiming TDE protects against SQL injection.
• No reaction to PII-in-logs; treating it as cosmetic.
• Audit = "we have log files somewhere."

Practice prompts

• Deliver the layered answer for transcript records in four minutes: transit, rest, logs, privilege, audit.
• A teammate's PR logs the full candidate object on error — write your review comment.
• Design "who viewed this transcript" auditing: where it's written, who reads it, retention.