Review: secure environments

Security is the group where Walt scored lowest on the diagnostic, 7 of 13, and it is worth 20 to 25 parts in 100. His study plan gives it the largest share of hours. This review covers the three parts of the group in the study guide: authentication and authorization, security for data at rest and in transit, and compliance controls for sensitive data. MSAZ-281 taught each topic in depth; this lesson collects the decisions the exam asks about and the distinctions that separate right answers from distractors.

Authentication: who can connect

Azure SQL Database and Managed Instance support Microsoft Entra authentication and SQL authentication. Entra authentication brings multifactor authentication, Conditional Access, central account disabling and managed identities for applications. Each logical server or managed instance has one Microsoft Entra administrator, which can be a user or a group; using a group, such as Harrow & Finch's sg-dba-admins, means admins can change without changing the server. Microsoft Entra-only authentication turns SQL authentication off entirely, including the server's SQL administrator login. Questions that say 'ensure that only Entra identities can connect' or 'disable SQL authentication' are about Entra-only authentication.

SQL Server on Azure VMs supports Windows authentication through the domain, SQL authentication, and Microsoft Entra authentication from SQL Server 2022 onward, which needs the instance to be connected through Azure Arc or registered with the SQL IaaS Agent extension. Managed Instance also supports Windows authentication for Entra principals through Kerberos, for applications that cannot change their connection method. A typical exam trap is offering Entra authentication for SQL Server 2019 on a VM; it is not available before 2022.

Applications should use managed identities rather than stored passwords. A system-assigned managed identity belongs to one resource, such as an App Service app, and is deleted with it; a user-assigned managed identity is a separate resource that several services can share. In the database, CREATE USER ... FROM EXTERNAL PROVIDER creates a user for an Entra user, group, service principal or managed identity. In Azure SQL Database these are contained database users, so no login exists in master and the user moves with the database.

Authorization: what they can do

Permissions in SQL Server and Azure SQL follow a hierarchy of securables: server, database, schema, object. A permission granted at a higher scope applies to everything below it, and DENY overrides GRANT at any level, except for members of sysadmin or the database owner. REVOKE removes a GRANT or a DENY; it does not forbid anything. The principle of least privilege means granting the smallest permission at the narrowest scope that lets the task succeed, preferably to a role rather than to individual users. GRANT EXECUTE ON SCHEMA::api to a role, with ownership chaining letting the procedures read their tables, is the classic least-privilege design for an application that only calls stored procedures.

Fixed database roles are convenient and broad: db_datareader can read every table, db_datawriter can change every table, db_owner can do anything in the database. Questions that say 'only the Sales schema' or 'only these three tables' reject the fixed roles. Azure SQL Database has its own server roles in master, with names that start with ##MS_, such as ##MS_ServerStateReader for reading server-state DMVs without broader rights; Managed Instance and SQL Server have the usual fixed server roles and also the ##MS_ roles since SQL Server 2022.

Troubleshooting questions describe a failed connection or a denied query and ask for the cause. Login failed for user errors that mention a state number point to causes such as a wrong password, a disabled login, a missing user in the target database or a wrong default database. 'The server was not found' points to a network or firewall problem, not a permission. A user that exists but cannot read a table usually lacks a grant, has a DENY through a role, or is reading through a view whose ownership chain is broken. An orphaned user, created by restoring a database to another server, has no matching login and is fixed by mapping it to one or by using contained users.

Data at rest and in transit

Transparent data encryption encrypts data and log files at rest and is on by default for new Azure SQL databases. The protector that encrypts the database encryption key can be service-managed or customer-managed. A customer-managed protector is a key in Azure Key Vault or Managed HSM that the customer controls and can rotate or revoke; the vault must have soft delete and purge protection enabled, and the server's identity needs get, wrap key and unwrap key permissions. Revoking access makes the databases inaccessible, which is exactly what some contracts require. TDE protects files and backups; it does not hide data from anyone who can query it.

Encryption in transit uses TLS. Azure SQL Database and Managed Instance let the administrator set a minimum TLS version, and 1.2 is the usual answer when a question asks to reject older clients. Clients should validate the server certificate rather than trust it blindly. Object-level encryption, sometimes called cell-level encryption, uses T-SQL functions such as ENCRYPTBYKEY with symmetric keys protected by certificates in the database; it encrypts chosen values, but the keys live in the database, so a DBA with access can decrypt them.

Always Encrypted encrypts chosen columns on the client side, so the database engine and anyone administering it see only ciphertext. Column master keys are kept outside the database, in Azure Key Vault or a certificate store, and the client driver encrypts and decrypts. Deterministic encryption always produces the same ciphertext for the same value, so it supports equality lookups, joins and grouping, but it reveals which rows share a value. Randomized encryption is stronger and supports no comparisons without an enclave. Always Encrypted with secure enclaves lets the engine run richer operations, such as range comparisons and LIKE, inside a protected memory area. In Azure SQL Database, VBS enclaves are available without attestation, and Intel SGX enclaves on DC-series hardware require Microsoft Azure Attestation and reach end of support on October 31, 2027.

Network access

Server-level firewall rules in Azure SQL Database allow ranges of public IP addresses to reach every database on the logical server, and are stored in master. Database-level rules apply to one database, are stored in that database and move with it in geo-replication; they are created with T-SQL only. The setting 'Allow Azure services and resources to access this server' allows any Azure resource, including other customers', and is a common wrong answer when a question asks for least access. A service endpoint lets traffic from a chosen subnet reach the server over the Azure backbone, but the server still has a public endpoint. A private endpoint gives the server a private IP address in a virtual network; combined with denying public network access, it removes the public path completely. Managed Instance lives in its own subnet and is private by design, with an optional public endpoint on port 3342.

Compliance controls

Data discovery and classification labels columns with an information type and a sensitivity label, and the labels appear in audit records when sensitive columns are queried. Auditing in Azure SQL Database can be set at the server level, which covers all current and future databases, or at the database level; destinations are a storage account, a Log Analytics workspace or Event Hubs. Managed Instance uses SQL Server audit syntax with a server audit and audit specifications, writing to storage or Azure Monitor. Microsoft Defender for SQL adds vulnerability assessment and alerts on threats such as SQL injection and unusual access patterns.

Change tracking records which rows changed, without the old values, for synchronization; change data capture records the changed data itself in change tables, for auditing or feeding other systems. Dynamic data masking hides values in query results for users without the UNMASK permission, with masks such as partial, email or default; it does not encrypt the data and can be worked around by inference, so it is not a security boundary against a determined user. Row-level security filters rows with a predicate function bound to a security policy: a filter predicate hides rows, a block predicate prevents writes that would violate the rule. Ledger makes tables tamper-evident: updatable ledger tables keep history, append-only ledger tables allow only inserts, and database digests stored in immutable storage let auditors verify that nothing was altered.

Reading security questions

Security questions often hide the deciding word in the requirement. 'Prevent' and 'ensure that no one' call for a control that cannot be bypassed, such as Always Encrypted, Entra-only authentication or a DENY, rather than one that only hides or warns, such as masking or an alert. 'Detect' and 'be notified' call for auditing, Defender for SQL or alerts, not prevention. 'Minimize administrative effort' favours built-in, managed options such as service-managed TDE, Entra groups and server-level auditing over per-database or per-user work. 'Least privilege' rules out db_owner, sysadmin and broad fixed roles in nearly every case.

Walt's diagnostic misses in security came mostly from two places: confusing what masking and encryption protect against, and choosing a database-level setting where a server-level one covered future databases. Elena gives him a short drill for both. For each feature in the group, he writes one sentence that starts 'This protects against' and one that starts 'This does not protect against'. Masking, for instance, protects against casual viewing in applications and does not protect against users who can run their own queries. Writing the second sentence is the part that fixes the misses.

Hands-on tasks for security

Every bullet in this group can be practised in a sandbox subscription in a few minutes: set an Entra admin group, turn on Entra-only authentication and watch a SQL login fail, create a user from a managed identity, grant and deny on a schema, turn on server-level auditing to a Log Analytics workspace, add a dynamic data mask, write a row-level security policy, and create a ledger table and verify its digest. Walt schedules these for the January weeks of his plan, two or three tasks a session.