Protecting Personal Data: Always Encrypted, Application-Level Encryption, Pseudonymisation, Masking and Test Data

Version note: The code in this post was built and run with .NET 10, Entity Framework Core 10.0.15 (SQLite), Bogus 35.6 and xUnit 2.9. The SQL Server-only features Always Encrypted and TDE were not run in this post; they are described from the Microsoft Learn documentation.

This series is a developer's view, not legal advice. Which data is protected with which measure is decided by the security and legal teams together.

Guess First

You encrypted the invoice national ID column in Bookstore's orders; the database now holds only meaningless text. What happens if a database user copies that encrypted value into another column, say the customer's phone field, and the application reads that field and tries to decrypt it? A second question: you show the support team only the last two digits of the national ID; is that a security control or a UI decision?

Encryption Layers: What Protects Against Whom

Article 12 of KVKK, Turkey's Law No. 6698, requires the controller to take the technical and administrative measures that provide an appropriate level of security to prevent unlawful processing of and access to personal data and to safeguard it. The law does not name an algorithm; you have to show what each measure protects against whom. In the previous post we marked the BillingNationalId field as SensitiveData in the inventory of Bookstore, the online bookstore used as the shared example across this site's series. There are four ways to protect it, each against a different threat:

We ran TDE and Dynamic Data Masking in the SQL Server Deep Dive series post "The SQL Server Security Model: Logins, Users, Schemas, Roles, Least Privilege and Row-Level Security"; there we saw that a masked e-mail column can be guessed character by character with WHERE and LIKE. I did not run Always Encrypted in this series; what follows is based on the Microsoft Learn documentation. Columns are encrypted by the driver on the client; the column encryption key is stored encrypted in the database, and the column master key that encrypts it lives in a key store outside the database, such as Azure Key Vault, the Windows certificate store or an HSM. Deterministic encryption supports equality lookups, IN, GROUP BY and DISTINCT, but leaks patterns in columns with few distinct values; randomized encryption allows no searching without a secure enclave. Two restrictions in the documentation matter for Bookstore: a column with Dynamic Data Masking cannot be encrypted with Always Encrypted, and columns with default constraints are not supported.

For Bookstore's needs we choose application-level AEAD: it is independent of the database engine, works the same on SQLite and SQL Server, and the key never touches the database. The price is that we solve searching ourselves.

Application-Level Encryption: AES-GCM and Purpose

AES-GCM's nonce rules, key length and envelope encryption are covered in detail in the Cryptography for Developers series post "Symmetric Encryption and AEAD: AES-GCM, ChaCha20-Poly1305, Nonce Management and Envelope Encryption"; here we apply that primitive to a personal data field:

public sealed class PiiProtector(PiiProtectionOptions options)
{
    private const int NonceSize = 12;
    private const int TagSize = 16;

    // Ciphertext format: "<keyId>.<base64(nonce | ciphertext | tag)>"
    public string Encrypt(string plaintext, string purpose)
    {
        byte[] key = options.EncryptionKeys[options.ActiveKeyId];
        byte[] input = Encoding.UTF8.GetBytes(plaintext);
        byte[] output = new byte[NonceSize + input.Length + TagSize];
        Span<byte> nonce = output.AsSpan(0, NonceSize);
        RandomNumberGenerator.Fill(nonce);

        using var aes = new AesGcm(key, TagSize);
        aes.Encrypt(nonce, input, output.AsSpan(NonceSize, input.Length), output.AsSpan(NonceSize + input.Length), Encoding.UTF8.GetBytes(purpose));
        return $"{options.ActiveKeyId}.{Convert.ToBase64String(output)}";
    }

    public string Decrypt(string protectedValue, string purpose)
    {
        int dot = protectedValue.IndexOf('.');
        byte[] key = options.EncryptionKeys[protectedValue[..dot]];
        byte[] data = Convert.FromBase64String(protectedValue[(dot + 1)..]);
        int length = data.Length - NonceSize - TagSize;
        byte[] plaintext = new byte[length];

        using var aes = new AesGcm(key, TagSize);
        aes.Decrypt(data.AsSpan(0, NonceSize), data.AsSpan(NonceSize, length), data.AsSpan(NonceSize + length), plaintext, Encoding.UTF8.GetBytes(purpose));
        return Encoding.UTF8.GetString(plaintext);
    }
}

There are three design decisions. First, the key ID at the start of the ciphertext (k1.): when the key is rotated, old rows keep being read with the old key and new ones are written with the active key. Second, a random nonce for every value; the same national ID encrypted twice gives two different texts, so someone looking at the column cannot match two orders of the same person. Third, the purpose parameter: we pass the column name as AES-GCM's associated data. It is not encrypted but it goes into the tag; decrypting under another purpose fails authentication. That answers the opening question.

The keys arrive through PiiProtectionOptions, and none of them lives in the database or in appsettings.json. The demo command generates the keys in memory on every run; in production they come from a secret store. How to set that up is covered in the Configuration and Secret Management series.

Searching with a Blind Index

A random nonce also makes equality search impossible: when accounting says "find the orders for this national ID", there is no fixed value to compare in a WHERE. The fix is an HMAC computed with a separate key, a blind index:

// Deterministic keyed hash for equality lookups; without the key it cannot be recomputed.
public string BlindIndex(string normalized, string purpose)
{
    byte[] mac = HMACSHA256.HashData(options.BlindIndexKey, Encoding.UTF8.GetBytes($"{purpose}:{normalized}"));
    return Convert.ToHexStringLower(mac.AsSpan(0, 16));
}

public void SetBillingNationalId(Order order, string nationalId)
{
    order.BillingNationalIdEncrypted = protector.Encrypt(nationalId, Purpose);
    order.BillingNationalIdIndex = protector.BlindIndex(nationalId, Purpose);
}

public Task<List<int>> FindOrderIdsAsync(string nationalId, CancellationToken ct = default)
{
    string index = protector.BlindIndex(nationalId, Purpose);
    return db.Orders.Where(o => o.BillingNationalIdIndex == index).Select(o => o.Id).ToListAsync(ct);
}

The demo command writes three synthetic customers and orders, then prints what someone reading the database from outside the application sees, the blind index lookup and the view per role:

cd tools/Bookstore.PrivacyLab
dotnet run -- protect
Ada Testwell     ada.testwell.000@example.com   fake id 99906863934 valid=False
Grace Demoray    grace.demoray.001@example.com  fake id 23515709358 valid=False
Alan Sampleton   alan.sampleton.002@example.com fake id 80171107056 valid=False
raw row: 1 | k1.X7TCmnOGfoZiEW5y9ZfbyR3UpNCAjR1D9kPJbO8+eHvu35PAllRb | 6edb142b53daf80ff35e7058040b550b
orders for 23515709358: [2]
StoreStaff: SupportOrderView { OrderId = 2, BillingName = Grace Demoray, NationalId = *********58 }
Accounting: SupportOrderView { OrderId = 2, BillingName = Grace Demoray, NationalId = 23515709358 }
wrong purpose: AuthenticationTagMismatchException: The computed authentication tag did not match the input authentication tag.

The national IDs are fake: 11 digits, but the last check digit is deliberately wrong, which valid=False shows. The raw row line stands for what a backup, a read replica or someone connected straight to the database sees: ciphertext and a 32-character index. The last line shows that the encrypted value cannot be decrypted under another column's purpose.

The blind index has a price too. As we saw in post 01, the value space of a national ID is small; if the HMAC key leaks, an attacker can compute the index of every possible value in a few minutes and match the table back. That is why the index key is kept separate from the encryption key and protected at least as well. Truncating the index to 16 bytes saves storage, but collisions at that length do not happen in practice; a shorter index produces collisions on purpose and requires decrypting and checking after the lookup.

Showing It Masked to the Support Team

Accounting needs the full number to issue the invoice, while the support team only needs the last two digits to verify the customer on a call. Masking happens in the application, after the role check:

// Accounting needs the full number for the invoice; store staff only see the last two digits.
public async Task<SupportOrderView?> GetForRoleAsync(int orderId, string role, CancellationToken ct = default)
{
    Order? order = await db.Orders.AsNoTracking().SingleOrDefaultAsync(o => o.Id == orderId, ct);
    if (order is null)
    {
        return null;
    }

    string? nationalId = order.BillingNationalIdEncrypted is { } encrypted
        ? protector.Decrypt(encrypted, Purpose)
        : null;
    return new SupportOrderView(order.Id, order.BillingName, role == "Accounting" ? nationalId : Mask(nationalId));
}

public static string? Mask(string? value) =>
    value is null ? null : new string('*', value.Length - 2) + value[^2..];

The answer to the second half of the opening question: masking alone is not a security control; it is a UI decision that reflects the result of authorisation. What provides the security is that the full number is returned only to the Accounting role, and that this decision is made on the server. Masking in Angular means sending the full number to the browser and hiding it with CSS. In a real application the role parameter is not passed by hand; it comes from the authenticated user's claims, and we built that authorisation model in the Authentication and Authorization series.

Pseudonymisation or Anonymisation

The blind index is in fact a pseudonym: whoever knows the key can go from the index back to the person. GDPR Art. 4(5) defines pseudonymisation as processing data so that it can no longer be attributed to a specific person without additional information, and requires that information to be kept separately and protected by technical and organisational measures; Recital 26 says such data is still personal data. On the KVKK side, Article 10 of the Regulation on the Erasure, Destruction or Anonymisation of Personal Data defines anonymisation as making data impossible to associate with a person even when matched with other data, and requires it to be irreversible, including against reverse engineering and data matching. An HMAC whose key sits at Bookstore does not clear that bar.

The practical consequence: sending analytics an HMAC pseudonym instead of the customer number reduces risk and is good practice, but it does not stop that data set from being personal data. It belongs in the inventory, the access request and the deletion plan. A truly anonymous report is usually produced at aggregate level rather than row level, such as order counts per city, with small groups suppressed. If a district has a single customer, that row gives the person away.

Test and Staging Data: Synthetic Data with Bogus

Taking a copy of the production database into staging means bypassing all of these protections with a less protected copy of personal data. In the previous post's inventory, staging would have to be listed as a copy. There are two options: a masked copy of production, or synthetic data with no real rows at all. With a masked copy every new column has to be added to the masking script, and the forgotten column leaks; synthetic data has no such risk. For Bookstore we generate synthetic data with Bogus:

public static List<(Customer Customer, string FakeNationalId)> Customers(int count, int seed = 2026)
{
    Randomizer.Seed = new Random(seed);
    var faker = new Faker();
    List<(Customer, string)> result = [];
    for (int i = 0; i < count; i++)
    {
        (string first, string last, string city) = faker.PickRandom(FakePeople.All);
        var customer = new Customer
        {
            FullName = $"{first} {last}",
            Email = $"{first}.{last}.{i:000}".ToLowerInvariant() + "@example.com",
            Phone = $"+90 000 000 {i / 100 % 100:00} {i % 100:00}",
            City = city,
            AdultConfirmedAtUtc = faker.Date.Past(2, new DateTime(2026, 11, 1, 0, 0, 0, DateTimeKind.Utc)),
            CreatedAtUtc = new DateTime(2026, 1, 1, 0, 0, 0, DateTimeKind.Utc),
        };
        result.Add((customer, FakeNationalId(faker)));
    }
    return result;
}

// FAKE test value: 11 digits whose last check digit is deliberately wrong, so it can never be a real ID.
public static string FakeNationalId(Faker faker)
{
    string firstNine = faker.Random.Int(1, 9) + faker.Random.ReplaceNumbers("########");
    string firstTen = firstNine + NationalIdChecksum.ExpectedTenthDigit(firstNine);
    int wrongLast = (NationalIdChecksum.ExpectedEleventhDigit(firstTen) + faker.Random.Int(1, 9)) % 10;
    return firstTen + wrongLast;
}

I deliberately do not use Bogus's own name and e-mail generators: the e-mail generator can use real mail providers' domains, and a randomly generated full name can match a real person. Names come from a fixed made-up list, addresses from the reserved example.com domain, and national IDs from numbers with a broken check digit. Tests pin these rules down as well:

[Fact]
public void Synthetic_national_ids_never_pass_the_checksum()
{
    var faker = new Faker();
    for (int i = 0; i < 10_000; i++)
    {
        string fake = SyntheticData.FakeNationalId(faker);
        Assert.Equal(11, fake.Length);
        Assert.False(NationalIdChecksum.IsValid(fake), fake);
    }
}

[Fact]
public void Ciphertext_does_not_decrypt_under_another_purpose()
{
    var protector = new PiiProtector(Options("k1", "k1"));
    string ciphertext = protector.Encrypt("00000000000", "Orders.BillingNationalId");

    Assert.ThrowsAny<CryptographicException>(() => protector.Decrypt(ciphertext, "Customers.Phone"));
}

dotnet test
Passed!  - Failed:     0, Passed:    11, Skipped:     0, Total:    11, Duration: 356 ms - Bookstore.Privacy.Tests.dll (net10.0)

Production Notes

Key management is the weakest link of this design. Keys should live in a secret store such as Key Vault, the application should reach them with a managed identity, and access should be logged. Envelope encryption, which wraps the data key with a key encryption key, is shown in the same Cryptography series post. Key rotation has two steps: the new key becomes active and new writes use it, then a background job reads rows encrypted with the old key and rewrites them; once the last row has moved to the new key, the old key is removed. Rotating the blind index key is more expensive because every index has to be recomputed; plan for it from the start.

The second point is EF Core integration. Putting encryption in a value converter is tempting, but EF Core builds and caches the model once per context type; if you pass a key to the converter through the constructor, the first context's key stays in every context. In this sample we encrypt through an explicit service (NationalIdVault), which also makes it visible in one place in code who decrypts which field. The third point is logs: with EnableSensitiveDataLogging on, EF Core writes parameter values to the log, and the plain value before encryption can show up there. The redaction setup from the Logging series applies here as well, so that DTOs carrying decrypted values do not end up in the log.

Common Pitfalls

The most expensive pitfall is keeping the key in the same database as the encrypted data: a Keys table or a configuration table on the same server. Whoever steals the backup steals the key too, and encryption becomes just one extra step. The second is thinking masking is a security control: as we saw in the SQL Server Deep Dive series, Dynamic Data Masking does not protect against someone who can write queries, and masking in the application protects nothing if the full value goes to the client. The third is believing you have "searchable encryption" with deterministic encryption or an unkeyed hash; for fields with a small value space this is reversed exactly like the hash example in post 01. The fourth is taking a production copy into staging "just once" and leaving it there for months.

Sources for This Post

Accessed on 15 November 2026.

• Law No. 6698, Art. 12: mevzuat.gov.tr/mevzuatmetin/1.5.6698.pdf
• Regulation on the Erasure, Destruction or Anonymisation of Personal Data, Art. 10: kvkk.gov.tr/Icerik/5441
• GDPR Art. 4(5) and Recital 26: eur-lex.europa.eu/eli/reg/2016/679/oj
• Always Encrypted (Microsoft Learn): learn.microsoft.com/sql/relational-databases/security/encryption/always-encrypted-database-engine

A Small Experiment

★☆☆ Run the protect command twice and see that the ciphertext of the same national ID changes, while the blind index also differs between runs. Why does the index change between runs?

★★☆ Encrypt Customer.Phone with PiiProtector as well, and think about what the inventory test expects when you change the field's class to SensitiveData. If phone lookup is needed, where do you normalise (spaces, country code) before the blind index?

★★★ Write a re-encryption job that finds rows encrypted with the old key (k1) and rewrites them with the active key (k2), resumes where it left off after an interruption, and add a test showing that k1 can be removed safely once the last row has moved.

Protecting data does not remove the need to know when to let it go. In the next post we process the "delete my account" request: erasing personal fields, separating orders kept for the legal period, why soft delete is not enough, deleted customers coming back after a backup restore, and the periodic destruction job.