This post is a part of the "SC-500: Implementing End-to-End Security Controls for Cloud and AI Workloads" Exam Prep Hub.
This topic falls under these sections:
Secure storage, databases, and networking (25–30%)
   --> Implement security for databases
      --> Implement platform-level security configurations in Azure SQL

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

The SC-500 exam expects you to understand how to secure Azure SQL Database and Azure SQL Managed Instance at the platform level.

Platform-level security focuses on controls that protect the database service and its connections, including:

• Authentication
• Authorization
• Network isolation
• Encryption in transit
• Encryption at rest
• Customer-managed keys
• Dynamic data masking
• Row-level security
• Microsoft Defender for SQL
• Auditing and monitoring

These controls should be implemented using a defense-in-depth approach. No single control protects every layer of a database workload.

1. Understand the Azure SQL Security Model

Azure SQL security can be viewed in several layers:

Azure SQL Database is a platform as a service offering. Microsoft manages many underlying platform responsibilities, such as patching, backups, and infrastructure maintenance, but customers remain responsible for configuring access, network exposure, data protection, and monitoring.

2. Configure Microsoft Entra Authentication

What Is Microsoft Entra Authentication?

Microsoft Entra authentication allows users and applications to connect to Azure SQL using identities managed by Microsoft Entra ID.

Supported identities can include:

• Individual users
• Microsoft Entra groups
• Service principals
• Managed identities
• Applications using Microsoft Entra access tokens

Microsoft Entra authentication provides centralized identity management and can integrate with capabilities such as multifactor authentication, Conditional Access, and identity lifecycle management.

Configure a Microsoft Entra Administrator

Before Microsoft Entra identities can be used to administer an Azure SQL logical server, configure a Microsoft Entra administrator for the server.

The Microsoft Entra administrator can be:

• A Microsoft Entra user
• A Microsoft Entra group

Using a group is often preferable for operational continuity because membership can be managed without changing the SQL server’s configured administrator whenever an individual administrator changes roles.

The Microsoft Entra administrator is configured at the logical-server level. After the administrator is configured, that identity can connect and create database users or assign appropriate database permissions.

Create Microsoft Entra Database Users

A Microsoft Entra user or group can be created inside an Azure SQL database using T-SQL similar to:

CREATE USER [Finance Analysts]
FROM EXTERNAL PROVIDER;

The user can then be added to an appropriate database role or granted specific permissions.

For example:

ALTER ROLE db_datareader
ADD MEMBER [Finance Analysts];

However, built-in roles such as db_datareader may grant more access than necessary. A more secure design is to create custom database roles and grant only the required permissions.

Microsoft Entra-Only Authentication

Microsoft Entra-only authentication disables SQL authentication for the logical server or supported Azure SQL resource.

This can reduce the risks associated with:

• SQL usernames and passwords
• Password reuse
• Password theft
• Password storage in connection strings
• Credential rotation
• Brute-force password attacks

Before enabling Microsoft Entra-only authentication, verify that all applications, scripts, tools, and integration services support Microsoft Entra authentication.

An application that still depends on a SQL login and password may stop connecting after SQL authentication is disabled.

3. Use Managed Identities for Applications

Managed identities are recommended for Azure-hosted applications that need to connect to Azure SQL.

A managed identity allows an Azure resource to authenticate without storing a password, client secret, or connection-string credential in application code.

Common examples include:

• Azure App Service
• Azure Functions
• Azure Virtual Machines
• Azure Kubernetes Service
• Azure Logic Apps
• Azure Automation
• Other Azure services that support managed identities

Typical Configuration Process

1. Enable a system-assigned or user-assigned managed identity on the application.
2. Configure a Microsoft Entra administrator for the SQL logical server.
3. Connect to the target database as an appropriate administrator.
4. Create a database user for the managed identity.
5. Grant the identity only the required database permissions.
6. Configure the application to request and use a Microsoft Entra access token.

Example:

CREATE USER [my-function-app]
FROM EXTERNAL PROVIDER;

Then grant only the permissions required by the application.

System-Assigned versus User-Assigned Managed Identity

A system-assigned identity is useful when the identity should exist only as long as the application exists.

A user-assigned identity is useful when several applications need to share the same identity or when the identity lifecycle should be independent of a particular application.

4. Understand SQL Authentication

SQL authentication uses a SQL login and password rather than Microsoft Entra credentials.

It may still be required for:

• Legacy applications
• Cross-platform applications that do not support Microsoft Entra authentication
• Migration scenarios
• Certain administrative or automation tools

If SQL authentication must be used:

• Use strong, unique passwords.
• Store secrets in a secure secret-management service.
• Avoid embedding credentials in source code.
• Rotate passwords regularly.
• Restrict the login’s permissions.
• Monitor failed authentication attempts.
• Avoid using highly privileged accounts for application connections.

SQL authentication should not be confused with Azure RBAC. Azure RBAC controls Azure resource management operations, while SQL authentication and database permissions control access inside the database.

5. Configure Network Isolation

Network security controls determine which clients can reach Azure SQL.

The primary options include:

• Public endpoint with firewall rules
• Virtual network rules
• Private endpoints
• Disabling public network access

Public Endpoint and Firewall Rules

Azure SQL Database can expose a public endpoint protected by firewall rules.

Firewall rules can be configured at:

• Server level
• Database level

Server-level firewall rules

A server-level firewall rule applies to all databases on the logical server.

This is useful when the same trusted source must access multiple databases.

Database-level firewall rules

A database-level firewall rule applies only to a specific database.

This provides more granular control when different databases require different network access rules.

By default, connections are rejected unless an applicable firewall rule allows them. The most secure configuration is to permit only the required IP addresses or ranges and avoid broad rules.

“Allow Azure Services and Resources to Access This Server”

This setting allows connections from Azure services and resources, including resources that may not belong to the same subscription.

Although convenient, it can create broader network exposure than intended.

Use it only when required and understand that it is not equivalent to allowing only one specific application or subnet.

Private Endpoints

A private endpoint assigns a private IP address from an Azure virtual network to the Azure SQL resource.

With a private endpoint:

• Traffic can remain on private Azure networking.
• The database is accessed through a private IP address.
• Public internet exposure can be reduced.
• Private DNS configuration is required for reliable name resolution.
• Network access can be controlled using virtual network and subnet security controls.

For a strongly isolated design, configure a private endpoint and disable public network access when the workload does not require public connectivity.

Important Exam Distinction

A private endpoint does not automatically guarantee that every client uses it.

You must also consider:

• DNS resolution
• Routing
• Network security rules
• Whether public network access remains enabled
• Whether clients can reach the private endpoint’s virtual network

6. Protect Data in Transit with TLS

Azure SQL encrypts connections in transit using Transport Layer Security.

Encryption in transit protects data as it travels between:

• Applications and Azure SQL
• Administrative tools and Azure SQL
• Integration services and Azure SQL

TLS helps reduce the risk of:

• Network eavesdropping
• Credential interception
• Data interception
• Man-in-the-middle attacks

Client connection strings should require encryption and should not blindly trust the server certificate.

For example, application drivers should be configured to:

• Encrypt the connection
• Validate the server certificate
• Avoid insecure certificate-trust settings

Azure SQL services enforce encrypted connections in transit.

7. Enable Transparent Data Encryption

What Is Transparent Data Encryption?

Transparent Data Encryption, or TDE, encrypts data at rest.

TDE protects:

• Database files
• Transaction log files
• Backup files

TDE is transparent to applications. Applications do not normally need to change their SQL statements or data-access code to use TDE.

New Azure SQL databases are encrypted by default. You should still verify the configuration and understand whether the organization requires customer-managed keys instead of Microsoft-managed keys.

TDE and Customer-Managed Keys

By default, Azure SQL uses Microsoft-managed encryption keys.

For regulated workloads or organizations requiring greater control, configure a customer-managed key in Azure Key Vault.

Customer-managed keys can provide control over:

• Key rotation
• Key access
• Key revocation
• Key auditing
• Key lifecycle management

The customer-managed key protects or wraps the database encryption key. It does not mean that every database operation directly uses the Key Vault key.

Requirements for Customer-Managed TDE

A typical implementation includes:

1. Create or select an Azure Key Vault.
2. Configure appropriate network and access controls for the vault.
3. Create or import a key.
4. Grant the Azure SQL server identity access to the key.
5. Configure the customer-managed key for the logical server or supported database.
6. Monitor key usage and expiration.
7. Plan for key rotation and recovery.

If the SQL service cannot access the configured key, database availability or encryption operations may be affected. Key lifecycle management is therefore a critical operational responsibility.

TDE versus Always Encrypted

TDE protects the database storage layer. Always Encrypted is designed for highly sensitive columns where the database engine should not have access to plaintext values.

8. Understand Dynamic Data Masking

Dynamic Data Masking, or DDM, limits the exposure of sensitive values to users who do not have permission to view the underlying data.

Examples of sensitive values include:

• Credit card numbers
• Telephone numbers
• Email addresses
• Social Security numbers
• Personal identifiers

A masked value may appear similar to:

XXXX-XXXX-XXXX-1234

The exact masking format depends on the configured masking function.

Important Characteristics

Dynamic data masking:

• Does not encrypt the underlying data.
• Does not modify the stored value.
• Is applied when data is returned to a user.
• Helps reduce accidental exposure.
• Is configured at the database level.
• Is not a replacement for database permissions.

Privileged users or users with sufficient permissions may still be able to view the unmasked data.

When to Use Dynamic Data Masking

Use DDM when:

• Support personnel need limited access to production data.
• Developers need to troubleshoot applications without seeing sensitive values.
• Analysts need to see data structure but not full identifiers.
• A database contains sensitive information that should be obscured for some users.

Do not rely on DDM as the only protection for confidential data. Combine it with least-privilege permissions, encryption, auditing, and appropriate application security.

9. Implement Row-Level Security

Row-Level Security, or RLS, restricts which rows a user can access.

RLS is especially useful for:

• Multitenant applications
• Regional data separation
• Department-level access
• Customer-specific data
• Business-unit restrictions

For example, a sales representative may be allowed to see only rows belonging to their assigned region.

How RLS Works

RLS uses a security predicate that determines whether a row can be accessed.

A common design is:

1. Identify the current user or application identity.
2. Compare that identity with a column in the table.
3. Allow or deny access to each row based on the result.

RLS can restrict:

• Reading rows
• Inserting rows
• Updating rows
• Deleting rows

Example Concept

A table might contain:

CustomerId
CustomerName
TenantId

A security predicate can ensure that a user only sees rows where TenantId matches the tenant associated with the current session.

RLS versus Dynamic Data Masking

RLS controls which rows are visible. It does not encrypt the data and should not be treated as an encryption mechanism.

10. Configure Microsoft Defender for SQL

Microsoft Defender for SQL provides threat detection and security assessment capabilities for Azure SQL workloads.

It can help identify suspicious activity such as:

• SQL injection attempts
• Unusual access patterns
• Potential data exfiltration
• Brute-force activity
• Suspicious database behavior
• Potential exploitation attempts

Defender for SQL can also provide vulnerability assessment and security recommendations. Alerts can be investigated through Microsoft Defender for Cloud.

Defender for SQL versus Auditing

These features serve different purposes:

Defender for SQL does not replace firewall rules, identity controls, encryption, or database permissions.

11. Configure SQL Auditing

SQL auditing records database events and sends them to a selected destination.

Supported destinations can include:

• Azure Storage
• Azure Monitor Logs
• Event Hubs

Auditing can help with:

• Regulatory compliance
• Security investigations
• Tracking privileged activity
• Investigating failed access attempts
• Identifying unusual database operations
• Establishing an activity history

Audit logs should be protected from unauthorized modification and retained according to the organization’s compliance and investigation requirements.

Recommended Auditing Practices

• Enable auditing for production databases.
• Send logs to a centralized destination.
• Restrict access to audit logs.
• Configure retention appropriate to business and regulatory requirements.
• Monitor failed logins and privileged operations.
• Correlate audit events with identity and network logs.
• Use Microsoft Sentinel or other monitoring workflows when centralized investigation is required.

12. Apply Least Privilege

Least privilege means granting users and applications only the permissions they require.

Apply least privilege at multiple levels:

Azure resource level

Use Azure RBAC to control who can:

• Create SQL servers
• Modify networking
• Change firewall rules
• Configure auditing
• Configure Defender for SQL
• Change encryption settings

Database level

Use database roles and permissions to control who can:

• Read tables
• Insert data
• Update data
• Delete data
• Execute stored procedures
• Alter database objects

Data level

Use:

• Row-Level Security
• Dynamic Data Masking
• Column-level permissions
• Views
• Stored procedures

Avoid granting broad roles such as db_owner to application identities unless absolutely necessary.

13. Platform-Level Security Implementation Example

Suppose a company hosts a financial application in Azure SQL Database.

The application requirements are:

• The application runs in Azure App Service.
• Only the application and administrators should reach the database.
• Developers must not see full customer payment information.
• Database activity must be audited.
• The organization requires control over encryption keys.

A suitable design would be:

1. Enable a managed identity on the App Service.
2. Create a Microsoft Entra database user for the identity.
3. Grant only the required database permissions.
4. Configure a private endpoint for Azure SQL.
5. Disable public network access if public connectivity is unnecessary.
6. Configure private DNS so the application resolves the SQL server privately.
7. Verify TLS encryption for all connections.
8. Enable TDE and configure a customer-managed key in Azure Key Vault.
9. Apply dynamic data masking to selected sensitive columns.
10. Use RLS if users must see only records associated with their tenant or business unit.
11. Enable SQL auditing and send logs to Azure Monitor Logs or Azure Storage.
12. Enable Microsoft Defender for SQL for threat detection and vulnerability assessment.
13. Use Azure Policy to enforce required security configurations where supported.

This design combines identity, network isolation, encryption, data protection, and monitoring rather than relying on one security feature.

14. Common Exam Traps

Trap 1: Confusing Azure RBAC with database permissions

Azure RBAC controls Azure resource management. It does not automatically grant permission to query tables.

Trap 2: Assuming TDE protects plaintext from administrators

TDE protects data at rest. It does not prevent an authorized database administrator from querying plaintext data.

Trap 3: Confusing DDM with encryption

Dynamic Data Masking hides returned values from some users. It does not encrypt the stored data.

Trap 4: Confusing RLS with DDM

RLS restricts rows. DDM obscures values.

Trap 5: Assuming a private endpoint automatically disables public access

A private endpoint provides private connectivity, but public network access may remain enabled unless explicitly disabled.

Trap 6: Assuming Microsoft Entra authentication automatically grants database access

The identity must also exist in the database and have appropriate permissions.

Trap 7: Granting Storage-style roles to SQL users

Azure SQL database access is not granted by assigning unrelated Azure Storage roles. Use appropriate Azure RBAC roles for management operations and SQL permissions for database operations.

Trap 8: Disabling SQL authentication without checking dependencies

Legacy applications may stop working if they depend on SQL logins and passwords.

Trap 9: Assuming auditing detects and blocks attacks

Auditing records activity. Microsoft Defender for SQL provides threat detection and alerts. Neither replaces preventive access controls.

Trap 10: Assuming customer-managed keys eliminate all security responsibilities

Customer-managed keys increase control, but the organization must manage key permissions, rotation, availability, and recovery.

15. Security Checklist

Use the following checklist when implementing platform-level security for Azure SQL:

• Configure a Microsoft Entra administrator.
• Prefer Microsoft Entra authentication over SQL authentication.
• Use managed identities for Azure-hosted applications.
• Disable SQL authentication when all dependencies support Microsoft Entra authentication.
• Assign database permissions using least privilege.
• Use private endpoints for workloads requiring private connectivity.
• Disable public network access when it is not required.
• Restrict firewall rules to necessary sources.
• Require encrypted connections.
• Verify certificate validation in client applications.
• Confirm that TDE is enabled.
• Use customer-managed keys when required by compliance or organizational policy.
• Protect encryption keys in Azure Key Vault.
• Use Always Encrypted for especially sensitive columns.
• Use Dynamic Data Masking to reduce accidental data exposure.
• Use Row-Level Security for tenant- or row-specific restrictions.
• Enable SQL auditing.
• Enable Microsoft Defender for SQL.
• Review vulnerability assessment recommendations.
• Monitor privileged activity and failed authentication attempts.
• Use Azure Policy to enforce security baselines.
• Test application connectivity after security changes.

Practice Exam Questions

Question 1

An Azure App Service must connect to an Azure SQL database. The security team does not want database passwords or client secrets stored in application settings.

What should you implement?

A. A SQL login with a complex password
B. A public database endpoint with an IP firewall rule
C. A managed identity with a Microsoft Entra database user
D. A database-level firewall rule

Correct answer: C

Explanation: A managed identity allows the App Service to authenticate through Microsoft Entra ID without storing a password or client secret. The identity must also be created as a database user and granted the required permissions.

Question 2

A company wants to ensure that employees can connect to Azure SQL only from a private Azure virtual network. The database must not be reachable through its public endpoint.

Which configuration best meets the requirement?

A. Enable a private endpoint and disable public network access
B. Add the company’s public IP address to the server firewall
C. Enable the “Allow Azure services and resources to access this server” setting
D. Enable SQL authentication and require strong passwords

Correct answer: A

Explanation: A private endpoint provides private connectivity through a virtual network. Disabling public network access ensures that clients cannot continue using the public endpoint. DNS and routing must also be configured correctly.

Question 3

A database contains customer credit card information. Developers need to troubleshoot queries but must not see the complete credit card numbers.

Which feature is most appropriate?

A. Transparent Data Encryption
B. Dynamic Data Masking
C. Private endpoint
D. Microsoft Defender for SQL

Correct answer: B

Explanation: Dynamic Data Masking obscures sensitive values returned to users who do not have permission to view the unmasked data. TDE protects data at rest, while a private endpoint controls network connectivity.

Question 4

A multitenant application stores records for many customers in the same table. Each customer must see only records belonging to its own tenant.

Which feature should be implemented?

A. Transparent Data Encryption
B. Dynamic Data Masking
C. Row-Level Security
D. SQL auditing

Correct answer: C

Explanation: Row-Level Security restricts access to individual rows based on the user, tenant, or session context. Dynamic Data Masking hides portions of values but does not prevent users from seeing rows belonging to other tenants.

Question 5

An organization requires control over the encryption keys used to protect Azure SQL databases. The keys must be rotated and audited by the organization.

What should be configured?

A. Customer-managed keys for Transparent Data Encryption in Azure Key Vault
B. Dynamic Data Masking on all database columns
C. A server-level firewall rule
D. A stored procedure that encrypts query results

Correct answer: A

Explanation: Customer-managed keys for TDE allow the organization to control key lifecycle activities such as rotation, revocation, and auditing. The keys are stored and managed in Azure Key Vault.

Question 6

An administrator assigns a user the Contributor role on an Azure SQL logical server. The user can manage the server resource but cannot query tables in a database.

Why?

A. Azure SQL does not support database permissions
B. Contributor is a management-plane role and does not automatically grant database data access
C. The user must enable public network access
D. The user must use SQL authentication instead of Microsoft Entra authentication

Correct answer: B

Explanation: Azure RBAC management roles control Azure resource operations. Database access requires an appropriate database user, role membership, or explicit SQL permission.

Question 7

A security engineer wants to record database activity for compliance investigations. The organization needs to send the events to a centralized log-analysis platform.

Which feature should be configured?

A. Dynamic Data Masking
B. Row-Level Security
C. Transparent Data Encryption
D. SQL auditing with Azure Monitor Logs

Correct answer: D

Explanation: SQL auditing records database events and can send them to Azure Monitor Logs. Auditing supports compliance, investigation, and analysis of database activity.

Question 8

A company wants to detect SQL injection attempts and unusual database access patterns.

Which service should be enabled?

A. Azure Private Link
B. Azure Key Vault
C. Microsoft Defender for SQL
D. Dynamic Data Masking

Correct answer: C

Explanation: Microsoft Defender for SQL provides threat detection for suspicious database activity, including potential SQL injection and anomalous access patterns. It complements, but does not replace, preventive security controls.

Question 9

An organization has migrated all applications to Microsoft Entra authentication. It wants to prevent applications from using SQL usernames and passwords.

Which configuration should be used?

A. Microsoft Entra-only authentication
B. Dynamic Data Masking
C. Database-level firewall rules
D. SQL auditing

Correct answer: A

Explanation: Microsoft Entra-only authentication disables SQL authentication and requires supported connections to use Microsoft Entra authentication. Applications must be tested before the setting is enabled.

Question 10

A security team wants to protect Azure SQL data stored on disk and in database backups. Applications should not require code changes.

Which feature should be used?

A. Row-Level Security
B. Transparent Data Encryption
C. Microsoft Defender for SQL
D. Microsoft Entra Conditional Access

Correct answer: B

Explanation: Transparent Data Encryption encrypts database files, transaction logs, and backups at rest without normally requiring application changes. It does not restrict which rows users can query or detect suspicious activity.

Final Summary

For the SC-500 exam, remember the following distinctions:

• Microsoft Entra authentication establishes identity.
• Managed identities eliminate the need to store application credentials.
• Database roles and permissions control what users and applications can do inside the database.
• Private endpoints provide private connectivity.
• Firewall rules restrict network sources.
• TLS protects data in transit.
• TDE protects data at rest.
• Customer-managed keys provide additional control over encryption keys.
• Always Encrypted protects selected sensitive values from database administrators.
• Dynamic Data Masking obscures sensitive values.
• Row-Level Security restricts access to rows.
• SQL auditing records database activity.
• Microsoft Defender for SQL detects suspicious database activity and provides security assessment capabilities.
• Azure Policy and least privilege help enforce consistent security across the environment.

The most secure Azure SQL design combines these controls according to the workload’s identity, network, data sensitivity, compliance, and monitoring requirements.

Go to the SC-500 Exam Prep Hub main page