The current official DP-900 skills outline does not list security as a separate item. However, the Microsoft Learn units on Azure SQL, Azure Storage, and Azure Cosmos DB introduce security features such as encryption, Microsoft Defender for SQL, auditing, and Microsoft Entra ID authentication, and practice question banks and older material keep asking about SQL firewall rules, dynamic data masking, and Always Encrypted. Security does not carry much weight on the exam, but this post lays out the basics so those questions do not trip you up.
This post is a supplementary entry to SQL Statement Types in our DP-900 study series. It widens the access-control story that started with DCL (GRANT and REVOKE) there, covering the three states of data, layered defense, the security features of Azure SQL Database, and authentication and encryption in Azure Storage and Cosmos DB, in that order.
---
The Three States of Data and How to Protect Them
Think about how cash is protected. It is guarded one way in the vault, another way in an armored truck, and another way while it is being counted at the register. Data likewise has three states, at rest, in transit, and in use, and each needs different protection.
| State | Meaning | Azure protection | |------|------|------| | At rest | Data stored on disks and in backups | TDE in Azure SQL, Azure Storage service encryption (SSE), Cosmos DB default encryption | | In transit | Data moving across the network | TLS (HTTPS), the secure transfer required setting on storage accounts | | In use | Data being queried or processed | Always Encrypted (including secure enclaves); dynamic data masking to limit exposure |
Most Azure data services have encryption at rest turned on by default. Azure Storage applies 256-bit AES encryption to every storage account and it cannot be disabled, and Cosmos DB encrypts data at rest by default as well.
Exam tip: "Encrypt database files and backups on disk without changing the application" → TDE (at rest). "Protect data moving across the network" → TLS (in transit).
---
Block It Layer by Layer: Defense in Depth
To get into a castle, you cross the moat, have your identity checked at the gate, and can enter only the rooms you are allowed into. If one layer is breached, the next one still holds; this approach is called defense in depth. A request that reaches for data passes through the network, authentication, authorization, and data protection layers in turn.
!Security layers a request passes before reaching data
| Layer | Question it asks | Typical features | |------|------|------| | Network | Where is the connection coming from? | Firewall rules, virtual networks, private endpoints | | Authentication | Who are you? | Microsoft Entra ID, SQL authentication, access keys | | Authorization | What are you allowed to do? | Azure RBAC, GRANT/DENY/REVOKE, SAS, ACLs | | Data protection | How is the data itself protected? | TDE, TLS, Always Encrypted, dynamic data masking | | Monitoring | Has anything unusual happened? | Auditing, Microsoft Defender for SQL and Storage |
Two principles apply across every layer. Least privilege means granting only the permissions a job truly needs. Zero Trust follows three principles, "verify explicitly, use least-privilege access, and assume breach," so that even the internal network is never trusted automatically.
Exam tip: "Give the report author read permission only" → least privilege. "Protect with several layers instead of relying on a single security control" → defense in depth.
---
Azure SQL Database Security
Firewall Rules: Server Level vs Database Level
When you create a new logical server, its server-level firewall blocks every connection to the public endpoint. You can connect only after adding rules for the IP address ranges you want to allow.
| Aspect | Server-level IP firewall rules | Database-level IP firewall rules | |------|------|------| | Scope | Every database on the logical server | One specific database | | How to configure | Azure portal, PowerShell, T-SQL | T-SQL only (sp_set_database_firewall_rule) | | Where stored | The master database | The database itself | | Evaluation order | When no database-level rule matches | Checked first |
Turning on the "Allow Azure services and resources to access this server" setting allows connections from other Azure services and counts as a single server firewall rule. To avoid the public internet entirely, use virtual network rules or private endpoints.
Authentication and Data Protection Features
Azure SQL supports both SQL authentication, where you sign in with a user name and password, and Microsoft Entra authentication, which manages organizational accounts centrally and supports single sign-on. On top of that, the following features protect the data.
| Feature | What it does | What to remember | |------|------|------| | TDE (transparent data encryption) | Encrypts the database, backups, and transaction log at rest in real time | On by default for new Azure SQL databases; no app changes | | Always Encrypted | The client driver encrypts sensitive columns before sending them | Keys are never exposed to the database engine, so even DBAs cannot see plaintext | | Dynamic data masking | Hides sensitive values in query results for unprivileged users | The stored data does not change | | Row-level security | Limits which rows each user can see | For example, sales reps see only customers in their region | | Auditing | Writes database events to an audit log | Compliance and spotting unusual activity | | Microsoft Defender for SQL | Vulnerability assessment plus Advanced Threat Protection | Detects and alerts on SQL injection attacks and anomalous access patterns |
Exam tip: "Call center staff should see only part of an email address" → dynamic data masking. "Even the database administrator must not see national ID numbers in plaintext" → Always Encrypted. "Detect and alert on SQL injection attempts" → Microsoft Defender for SQL. "Allow connections to one database only from a specific office IP, configured with T-SQL" → database-level firewall rule.
---
Azure Storage and Cosmos DB Security
Azure Storage
Data in Azure Storage is automatically encrypted at rest with 256-bit AES, and this encryption cannot be turned off. Microsoft-managed keys are the default, and customer-managed keys can be used for Blob Storage and Azure Files when needed. Access to the data is granted in the following ways.
| Method | Characteristics | Recommendation | |------|------|------| | Microsoft Entra ID + Azure RBAC | Assign roles to users and managed identities; no keys in code | Recommended by Microsoft | | Shared Key (account access key) | Full permissions on the whole account | High exposure risk; avoid where possible | | Shared access signature (SAS) | A delegated token limited in time, permissions, and scope | When you use one, a user delegation SAS is recommended | | ACLs (Data Lake Gen2) | POSIX permissions per file and folder | With the hierarchical namespace |
On the network side, storage firewalls, virtual network rules, and private endpoints narrow the access paths, and the secure transfer required setting allows HTTPS only. Microsoft Defender for Storage detects threats such as unusual access and malicious file uploads.
Azure Cosmos DB
Cosmos DB also encrypts data at rest by default. Data can be accessed with account keys (primary and secondary), but Microsoft recommends applying least privilege with Microsoft Entra ID–based data plane role-based access control and managed identities. If you must keep using keys, rotate them regularly; turning off public network access and using only private endpoints is the recommended setup.
Exam tip: "Share one container read-only with a temporary external partner for seven days" → SAS. "Let an app access storage without keys" → managed identity + Azure RBAC. "Can storage account encryption be turned off?" → No.
---
Practice with Scenarios
Decide on your answer first, then check the columns on the right.
| Situation | Answer | Why | |------|------|------| | Office PCs cannot connect to a newly created Azure SQL logical server | Add a server-level firewall rule for the office IP | All public connections are blocked by default | | Developers querying the production database should see only the last four digits of card numbers | Dynamic data masking | Hides results while stored values stay the same | | Even cloud operators must not see the salary column | Always Encrypted | Keys are never exposed to the database engine | | Encrypt database files and backups without changing the app | TDE | Real-time encryption at rest, on by default | | Each sales rep queries only orders from their own region | Row-level security | Per-user row filtering | | Detect and alert on unusual sign-ins and SQL injection attempts | Microsoft Defender for SQL | Advanced Threat Protection | | Let an outside vendor read a specific blob for just one day | Shared access signature (SAS) | Delegation limited in time and permissions | | An Azure app accesses Cosmos DB without a connection string | Managed identity + data plane RBAC | Keyless, least-privilege authentication |
---
Exam Quick Reference
The key points to review one last time before exam day.
| Topic | What to Remember | |------|------| | Three states of data | At rest (TDE, SSE), in transit (TLS), in use (Always Encrypted) | | Defense in depth | Network → authentication → authorization → data protection; several layers of defense | | Least privilege and Zero Trust | Only the permissions needed; verify explicitly, least privilege, assume breach | | SQL server-level firewall | All databases on the server; portal, PowerShell, T-SQL; stored in master | | SQL database-level firewall | One database; T-SQL only; checked first | | Microsoft Entra authentication | Central organizational accounts, single sign-on; recommended over SQL authentication |