Why should you care?

Stolen disks or sniffed networks should not expose customer PII in clear text.

See it live — copy this example

Run in SQL Server Management Studio (SSMS) or Azure Data Studio.

-- Force encryption on connections (client connection string)
-- Encrypt=True;TrustServerCertificate=False;HostNameInCertificate=your.server.com;

-- Column-level demo with passphrase (lab only — prefer keys/certificates in prod)
USE DataVerse;
IF COL_LENGTH('dbo.Customers', 'EmailEnc') IS NULL
    ALTER TABLE dbo.Customers ADD EmailEnc VARBINARY(256) NULL;
UPDATE dbo.Customers
SET EmailEnc = ENCRYPTBYPASSPHRASE('LabOnlyPassphrase', Email)
WHERE EmailEnc IS NULL;
SELECT CustomerId, CONVERT(NVARCHAR(256), DECRYPTBYPASSPHRASE('LabOnlyPassphrase', EmailEnc)) AS Email
FROM dbo.Customers
WHERE EmailEnc IS NOT NULL;

What happened?

• Connection strings should enable TLS.
• The passphrase demo shows ENCRYPTBYPASSPHRASE for learning — production uses proper keys, certificates, or Always Encrypted.

Practice next

1. Enable Encrypt=True on a test connection.
2. Run the lab encrypt/decrypt update carefully.
3. Read about TDE for whole-database at-rest encryption.
4. Inspect sys.dm_database_encryption_keys for TDE status.
5. Clear EmailEnc after the experiment.

Remember

Encrypt data in transit and at rest. Use platform key management. Lab passphrase ≠ production design.

TDE on DataVerse

Prod database enables TDE with Azure Key Vault backed keys.

Outcome: Disk snapshots are useless without keys.