This chapter covers the comprehensive security features of Azure SQL Database, a critical topic for the AZ-500 exam. You will learn about network security, authentication, authorization, encryption, auditing, and threat protection for Azure SQL Database. Approximately 15-20% of the exam questions touch on database security, making this a high-yield area. Mastery of these concepts is essential for the 'Compute Security' domain.
Jump to a section
Think of an Azure SQL Database as a high-security vault in a bank. The vault itself is the database, and the bank building is the Azure SQL Database logical server. To access the vault, you need multiple keys and permissions. First, you must pass through the bank's front door (firewall rules) — only specific IP addresses or Azure services are allowed entry. Once inside, you need a valid employee badge (SQL authentication or Azure AD authentication) to enter the vault room. But even with a badge, you can only open certain drawers based on your role (RBAC roles like SQL DB Contributor or built-in database roles like db_datareader). The vault also has an alarm system (auditing) that records every time a drawer is opened or closed. Additionally, some documents inside are encrypted with a special cipher (Transparent Data Encryption) so that even if someone steals the vault, they cannot read the documents without the decryption key. Finally, the bank has a security guard who monitors all access and can block suspicious activity (Advanced Threat Protection). This layered security ensures that only authorized individuals can access specific data, and all actions are logged and monitored.
Overview of Azure SQL Database Security
Azure SQL Database is a fully managed Platform-as-a-Service (PaaS) database engine that handles most database management functions like upgrading, patching, backups, and monitoring without user involvement. Security is a shared responsibility: Microsoft secures the physical infrastructure, operating system, and database engine, while you are responsible for securing your data, access controls, and compliance. Azure SQL Database provides a layered security model encompassing network security, identity and access management, encryption, and monitoring.
Network Security: Firewall Rules and Virtual Network Service Endpoints
Azure SQL Database uses server-level and database-level firewall rules to control inbound traffic. By default, all external access is blocked. Firewall rules can be based on source IP address ranges or Azure Virtual Network service endpoints.
Server-level IP firewall rules: These apply to all databases on the logical server. You can configure them via the Azure portal, PowerShell, Azure CLI, or REST API. Default: no rules, so all access is denied. You must explicitly allow IP ranges.
Database-level IP firewall rules: These apply to a specific database and are stored in the master database. They can be created and managed by database users with control over the master database.
Virtual Network service endpoints and firewall rules: This feature restricts access to traffic from a specific virtual network subnet. When enabled, Azure SQL Database only accepts connections from the specified subnet. This is more secure than IP-based rules because it uses the source virtual network identity rather than a public IP.
Configuration example using Azure CLI to create a server-level firewall rule:
az sql server firewall-rule create --resource-group myResourceGroup --server mySqlServer --name AllowMyIP --start-ip-address 192.168.1.1 --end-ip-address 192.168.1.10Authentication: SQL Authentication vs. Azure AD Authentication
Azure SQL Database supports two authentication methods:
SQL Authentication: Uses a username and password stored in the database. This is the legacy method. The server admin account is created when provisioning the server. Additional SQL logins can be created in the master database.
Azure Active Directory (Azure AD) Authentication: Uses identities managed by Azure AD. This is the recommended method for centralized identity management and multi-factor authentication. When Azure AD authentication is enabled, you can create Azure AD users and groups as database principals.
To enable Azure AD authentication, you must set an Azure AD admin for the SQL server. This can be a user or a group. Once set, any Azure AD user or group can be granted access to the database.
Azure CLI to set Azure AD admin:
az sql server ad-admin create --resource-group myResourceGroup --server mySqlServer --display-name AdminName --object-id <object-id>Authorization: RBAC and Database Roles
Authorization controls what authenticated users can do. Azure SQL Database uses two layers:
Azure Role-Based Access Control (RBAC): Controls management-plane operations (e.g., creating databases, configuring firewall rules). Built-in roles include:
- SQL DB Contributor: Full access to manage SQL databases but cannot manage server-level security policies. - SQL Security Manager: Can manage firewall rules and security policies. - Reader: Read-only access to resources. - Database-level roles: Control data-plane operations (e.g., reading, writing, creating tables). Built-in database roles include: - db_owner: Full control over the database. - db_datareader: Read all data. - db_datawriter: Insert, update, delete data. - db_ddladmin: Execute DDL commands. - db_securityadmin: Manage permissions.
You can also create custom roles. Use CREATE ROLE and GRANT statements.
Example: Granting a user read access:
CREATE USER [user@domain.com] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [user@domain.com];Encryption: Transparent Data Encryption (TDE) and Always Encrypted
Transparent Data Encryption (TDE): Encrypts the database at rest, including backups and transaction log files. It uses a database encryption key (DEK) which is protected by a server certificate (service-managed) or an asymmetric key stored in Azure Key Vault (customer-managed). TDE is enabled by default for new databases since 2017. To verify:
SELECT name, is_encrypted FROM sys.databases;Always Encrypted: A client-side encryption technology that ensures sensitive data (e.g., credit card numbers) is encrypted before being sent to the database. The database never sees the plaintext. The encryption keys are stored in Azure Key Vault or Windows Certificate Store. This protects data from database administrators with high privileges. Always Encrypted supports deterministic encryption (for equality lookups) and randomized encryption (for higher security).
Auditing and Threat Detection
Auditing: Tracks database events and writes them to an audit log in Azure Storage, Log Analytics, or Event Hubs. You can audit server-level and database-level events. Default audit policy includes actions like database authentication successes/failures, DDL statements, and DML statements. You can customize with custom audit action groups.
Configuration via Azure CLI:
az sql server audit-policy update --resource-group myResourceGroup --server mySqlServer --state Enabled --blob-storage-target-state Enabled --storage-account-resource-id /subscriptions/.../storageAccounts/mystorageaccountAdvanced Threat Protection (ATP): Detects anomalous activities indicating potential security threats, such as SQL injection, brute force attacks, and unusual access patterns. It provides security alerts with details and recommended actions. ATP is part of Azure Defender for SQL.
Data Masking and Row-Level Security
Dynamic Data Masking: Obfuscates sensitive data in query results to non-privileged users. For example, masking a credit card number as XXXX-XXXX-XXXX-1234. You define masking rules per column. Privileged users (e.g., db_owner) see unmasked data.
Row-Level Security (RLS): Restricts rows based on user identity or context. You create a security policy with a predicate function that filters rows. For example, a customer can only see their own orders.
Interaction with Related Technologies
Azure SQL Database integrates with: - Azure Key Vault: For storing encryption keys (TDE with customer-managed keys, Always Encrypted). - Azure Policy: To enforce compliance rules (e.g., require TDE enabled). - Azure Security Center: Provides security recommendations and threat detection. - Azure Monitor: For metrics, logs, and alerts.
Exam-Relevant Values and Defaults
Default firewall: all connections blocked.
TDE: enabled by default for new databases after 2017.
Azure AD authentication: must be explicitly enabled.
Auditing: not enabled by default.
Advanced Threat Protection: part of Azure Defender for SQL, which is not free.
Dynamic Data Masking: not enabled by default.
Row-Level Security: not enabled by default.
Configure server-level firewall rule
Use Azure portal, CLI, or PowerShell to add a firewall rule that allows your client IP address range. The rule is stored in the master database. Without this, all connections are blocked. The rule specifies a name, start IP, and end IP. For example, to allow your office network, set start and end IP accordingly. For Azure services, you can enable 'Allow Azure services and resources to access this server' which adds a rule allowing all Azure IPs. This is less secure and should be avoided if possible.
Enable Azure AD authentication
Set an Azure AD admin for the SQL server. This can be a user or a group. Once set, Azure AD authentication is enabled. You can then create contained database users mapped to Azure AD identities. For example, `CREATE USER [user@domain.com] FROM EXTERNAL PROVIDER;`. This step is critical for centralized identity management and multi-factor authentication.
Assign appropriate RBAC roles
Use Azure RBAC to grant management-plane permissions. For example, assign the SQL Security Manager role to a user who needs to manage firewall rules. This role allows them to view and modify firewall rules but not access data. Use the Azure portal or CLI: `az role assignment create --assignee <user> --role "SQL Security Manager" --scope <server-id>`.
Grant database-level permissions
Connect to the database and create users if not already done. Then grant roles like db_datareader or db_datawriter. For least privilege, grant only necessary permissions. Example: `ALTER ROLE db_datareader ADD MEMBER [user@domain.com];`. For granular permissions, use `GRANT SELECT ON schema::Sales TO [user];`.
Enable auditing and threat detection
Configure auditing to log events to a storage account, Log Analytics workspace, or Event Hub. Also enable Advanced Threat Protection (part of Azure Defender for SQL) to detect threats. These are configured at the server level. Auditing helps with compliance and forensics. Threat detection provides alerts for suspicious activities like SQL injection attempts.
Enterprise Scenario 1: Financial Services Application
A large bank uses Azure SQL Database to store customer transaction data. They must comply with PCI DSS and GDPR. Their security configuration includes: - Network: Virtual Network service endpoints to restrict access to only their application VMs. No public IP access. - Authentication: Azure AD authentication with multi-factor authentication for all users. - Authorization: Custom database roles for different departments (e.g., tellers have read/write to transactions, managers have read-only to reports). - Encryption: TDE enabled (service-managed) for at-rest encryption. Always Encrypted for credit card numbers and social security numbers. - Auditing: All database events are audited to a Log Analytics workspace. Alerts are set for failed logins and DDL changes. - Threat Detection: Azure Defender for SQL is enabled, generating alerts for SQL injection and brute force attacks.
What goes wrong when misconfigured: If the firewall rule is too permissive (e.g., allowing all Azure services), a malicious service could attempt to connect. If Azure AD authentication is not enforced, weak SQL passwords could be compromised. If auditing is disabled, compliance violations go undetected.
Enterprise Scenario 2: Healthcare SaaS Application
A healthcare SaaS provider hosts patient records in Azure SQL Database. They need to comply with HIPAA. Their configuration: - Network: Only allow connections from their application's virtual network using service endpoints. Additionally, they use a private endpoint for Azure SQL Database to ensure traffic never traverses the internet. - Authentication: Azure AD authentication with conditional access policies requiring compliant devices. - Authorization: Row-Level Security to ensure doctors only see their own patients' data. The security policy uses a predicate function that checks the user's Azure AD group membership. - Encryption: TDE with customer-managed keys in Azure Key Vault. Keys are rotated every 90 days. - Auditing: All access to PHI (Protected Health Information) is audited. They use custom audit action groups to capture SELECT statements on sensitive tables. - Threat Detection: Azure Defender for SQL is enabled, and alerts are integrated with their SIEM.
Performance considerations: Enabling auditing and threat detection adds minimal overhead. Always Encrypted can impact query performance because encryption/decryption happens on the client side. For high-volume transactions, consider using deterministic encryption for equality searches to allow indexing.
Common Misconfiguration Pitfall
A common mistake is enabling 'Allow Azure services and resources to access this server' for convenience. This opens access to all Azure services, increasing the attack surface. Instead, use service endpoints or private endpoints. Another pitfall is not enabling TDE on existing databases (it is enabled by default for new ones but not for older ones). Always verify TDE status.
AZ-500 Exam Focus on Azure SQL Database Security
This topic falls under Domain 2: Compute Security, Objective 2.5: Implement security for Azure SQL Database. The exam tests your ability to configure and manage:
Firewall rules (server-level vs. database-level)
Virtual Network service endpoints and private endpoints
Authentication (SQL vs. Azure AD)
Authorization (RBAC and database roles)
Encryption (TDE, Always Encrypted, and key management)
Auditing and threat detection
Common Wrong Answers and Why Candidates Choose Them
Choosing 'Allow Azure services' as the best practice for network access: Candidates think it's easy and sufficient, but it allows all Azure IPs, which is not secure. The correct answer is to use Virtual Network service endpoints or private endpoints.
Selecting SQL authentication as more secure because it's 'in-house': Azure AD authentication is actually more secure because it supports MFA and centralized management. The exam expects Azure AD as the recommended method.
Thinking TDE is not enabled by default: For databases created after 2017, TDE is enabled by default. Candidates may assume it's off and need to enable it.
Confusing Dynamic Data Masking with Always Encrypted: Dynamic Data Masking obfuscates data in results but data is stored in plaintext. Always Encrypts encrypts data before it reaches the database. The exam tests this distinction.
Specific Numbers, Values, and Terms
Default firewall: no rules, all blocked.
TDE: enabled by default for new databases (since 2017).
Azure AD authentication: must be enabled by setting an Azure AD admin.
Auditing: can be stored in Azure Storage, Log Analytics, or Event Hubs.
Advanced Threat Protection: part of Azure Defender for SQL (paid tier).
Dynamic Data Masking: can mask data for non-privileged users.
Row-Level Security: uses a security policy with a predicate function.
Edge Cases and Exceptions
Database-level firewall rules: Can only be created by users with control over the master database. If the server admin is not available, database-level rules cannot be managed.
Azure AD authentication with guest users: Guest users (B2B) can be added as Azure AD users and granted database access, but they must be in the Azure AD tenant.
TDE with customer-managed keys: Requires Azure Key Vault and specific permissions. If the key is deleted or disabled, the database becomes inaccessible.
How to Eliminate Wrong Answers
For network questions: If the answer allows 'all Azure services', it's likely wrong unless the scenario explicitly requires it.
For authentication: If the answer uses SQL authentication without a specific reason, it's wrong. Azure AD is the recommended default.
For encryption: If the question asks about 'encrypting data before it leaves the client', think Always Encrypted, not TDE.
For auditing: If the question asks about 'detecting threats', think Advanced Threat Protection, not just auditing.
Azure SQL Database firewall blocks all traffic by default; you must explicitly allow IP ranges or use VNet service endpoints.
Azure AD authentication is the recommended method and supports MFA; SQL authentication is legacy.
TDE is enabled by default for databases created after 2017; Always Encrypted requires client-side configuration.
Auditing and Advanced Threat Protection must be enabled separately; they are not on by default.
Dynamic Data Masking obfuscates data in results but does not encrypt; Always Encrypted encrypts before storage.
Use Virtual Network service endpoints or private endpoints instead of 'Allow Azure services' for secure network access.
Row-Level Security uses a security policy with a predicate function to filter rows based on user context.
These come up on the exam all the time. Here's how to tell them apart.
Server-level firewall rules
Apply to all databases on the logical server
Managed by server admin or Azure RBAC roles
Stored in master database
Configured via Azure portal, CLI, or PowerShell
Cannot be created by database users without server-level permissions
Database-level firewall rules
Apply only to a specific database
Can be managed by database users with control over master database
Stored in master database
Configured via T-SQL (e.g., sp_set_database_firewall_rule)
Useful for granting access to specific databases without server-level permissions
Transparent Data Encryption (TDE)
Encrypts data at rest (database files, backups, logs)
Transparent to applications; no code changes needed
Uses database encryption key (DEK) protected by service-managed or customer-managed key
Enabled by default for new databases
Does not protect data from database administrators
Always Encrypted
Encrypts sensitive data columns before they reach the database
Requires application changes (client-side encryption)
Uses column encryption keys stored in Azure Key Vault or Windows Certificate Store
Not enabled by default; must be configured per column
Protects data from database administrators and high-privilege users
Mistake
Azure SQL Database firewall rules are set only at the server level.
Correct
Firewall rules can be set at both server and database levels. Database-level rules are stored in the master database and apply to a specific database. They can be managed by database users with appropriate permissions.
Mistake
Transparent Data Encryption (TDE) must be manually enabled for all databases.
Correct
TDE is enabled by default for all new Azure SQL databases created after 2017. For older databases, you may need to enable it manually.
Mistake
Dynamic Data Masking is a security feature that encrypts data at rest.
Correct
Dynamic Data Masking only obfuscates data in query results for non-privileged users; the data is stored in plaintext. Always Encrypted is the feature that encrypts data before it reaches the database.
Mistake
Azure AD authentication is less secure than SQL authentication.
Correct
Azure AD authentication is more secure because it supports multi-factor authentication, conditional access, and centralized identity management. SQL authentication uses static passwords that can be compromised.
Mistake
Enabling 'Allow Azure services and resources to access this server' is a secure practice.
Correct
This setting allows traffic from any Azure service, including services outside your subscription. It is a security risk and should be avoided. Use Virtual Network service endpoints or private endpoints instead.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
Server-level firewall rules apply to all databases on the logical SQL server and are managed by the server admin or Azure RBAC roles. Database-level firewall rules apply to a specific database and can be managed by database users with control over the master database. Database-level rules are useful for granting access to specific databases without granting server-level permissions.
To enable Azure AD authentication, you must set an Azure AD admin for the SQL server. This can be a user or a group. Once set, you can create contained database users mapped to Azure AD identities using T-SQL: `CREATE USER [user@domain.com] FROM EXTERNAL PROVIDER;`. Then grant appropriate database roles.
Yes, for all new Azure SQL databases created after 2017, TDE is enabled by default. For older databases, you may need to enable it manually. You can check using `SELECT name, is_encrypted FROM sys.databases;`.
Dynamic Data Masking obfuscates sensitive data in query results to non-privileged users, but the data is stored in plaintext. Always Encrypted encrypts sensitive data on the client side before it is sent to the database, so the database never sees the plaintext. Always Encrypted provides stronger protection against privileged users.
You can enable auditing at the server or database level. Auditing writes events to a destination: Azure Storage, Log Analytics workspace, or Event Hubs. Configure via Azure portal, CLI, or PowerShell. Example CLI: `az sql server audit-policy update --resource-group <rg> --server <server> --state Enabled --blob-storage-target-state Enabled --storage-account-resource-id <id>`.
Advanced Threat Protection (ATP) is part of Azure Defender for SQL. It detects anomalous activities such as SQL injection, brute force attacks, and unusual access patterns. It provides security alerts with details and recommended actions. ATP must be enabled separately and is a paid feature.
Yes, guest users (B2B collaboration users) can be added to your Azure AD tenant and then granted access to Azure SQL Database. They must be created as database users using `CREATE USER [guestuser@externaldomain.com] FROM EXTERNAL PROVIDER;` and granted appropriate permissions.
You've just covered Azure SQL Database Security — now see how well it sticks with free AZ-500 practice questions. Full explanations included, no account needed.
Done with this chapter?