Azure Authentication & Authorization: SQL Auth, Microsoft Entra ID, Managed Identity, and RBAC Explained

Here’s an uncomfortable truth about cloud security incidents: most of them don’t start with some genius hacker breaking sophisticated encryption. They start with something boring — a password that got reused, a connection string with a plaintext credential sitting in a config file, a service account nobody remembers creating that still has admin rights three years after the project it was for got cancelled.

Authentication and authorization are the unglamorous foundation everything else sits on. Get them right, and a stolen laptop or a leaked GitHub repo becomes a non-event. Get them wrong, and it doesn’t matter how good your firewall rules or your encryption are — someone just walks in the front door with a password they found in a spreadsheet.

This article is a deep, practical tour of how Azure — and specifically Azure SQL — handles identity, covering four things that get confused with each other constantly:

• SQL Authentication — the traditional username/password approach every SQL Server DBA already knows
• Microsoft Entra ID Authentication (formerly Azure Active Directory) — identity-based, token-driven authentication
• Managed Identity — how applications and services, not humans, authenticate without ever touching a password
• Azure RBAC (Role-Based Access Control) — who’s allowed to manage the Azure resources themselves, as opposed to who’s allowed inside the database

If you’re studying for DP-300, this exact cluster of topics shows up constantly, so I’ve woven in exam tips throughout rather than bolting them on at the end.

The Big Picture: Two Different Planes

Before diving into each method, one distinction will save you a lot of confusion later: Azure separates the control plane from the data plane.

                     ┌─────────────────────────────┐
                     │        CONTROL PLANE         │
                     │  "Can you manage the Azure    │
                     │   RESOURCE itself?"           │
                     │                               │
                     │   Governed by: Azure RBAC     │
                     │   Examples: create/delete a   │
                     │   SQL server, change firewall │
                     │   rules, scale a database      │
                     └───────────────┬───────────────┘
                                     │
                     ┌───────────────▼───────────────┐
                     │         DATA PLANE             │
                     │  "Can you log in and touch     │
                     │   the DATA inside?"            │
                     │                                │
                     │   Governed by: SQL Auth /       │
                     │   Entra ID Auth / Managed       │
                     │   Identity + T-SQL permissions  │
                     │   Examples: SELECT from a       │
                     │   table, run a stored proc      │
                     └────────────────────────────────┘

RBAC decides whether you can delete the whole SQL server. SQL/Entra ID/Managed Identity authentication decides whether you can log in and SELECT * FROM Orders once the server exists. People conflate these constantly — an Owner-level RBAC role on a SQL server gives you zero rights to query the data inside it unless you’re also granted database-level access. Keep that split in your head and the rest of this article will click into place much faster.

1. SQL Authentication — The Traditional Approach

How it works

This is classic SQL Server behavior, unchanged in spirit since long before Azure existed. You create a login at the server level with a username and password, map it to a user inside a specific database, and grant that user permissions. Azure SQL validates the username and password directly against its own internal store — no external identity provider involved.

When to use it

• Legacy applications that can’t easily be updated to support token-based auth
• Third-party tools that only support username/password connection strings
• Quick dev/test scenarios where you’re not going to bother wiring up Entra ID
• As a break-glass fallback account in case your Entra ID tenant has an outage (a genuinely valid reason to keep at least one SQL login around, even in an Entra-first environment)

Configuration — T-SQL

-- Run against the master database to create a server-level login
CREATE LOGIN app_service_login WITH PASSWORD = 'UseAV3ryStrongP@ssw0rdHere!';

-- Then, connected to the target database, create a user mapped to that login
CREATE USER app_service_user FOR LOGIN app_service_login;

-- Grant only what's needed — never default to db_owner
ALTER ROLE db_datareader ADD MEMBER app_service_user;
ALTER ROLE db_datawriter ADD MEMBER app_service_user;

Configuration — Azure Portal walkthrough

1. Navigate to your Azure SQL Database or logical server in the portal.
2. Under Settings, open Authentication — confirm SQL authentication is enabled (it can be disabled tenant-wide in favor of Entra-only auth, which we’ll cover shortly).
3. Use Query Editor (or SSMS/Azure Data Studio) connected as the server admin to run the CREATE LOGIN / CREATE USER statements above — the portal itself doesn’t have a dedicated “create SQL login” UI; it’s a T-SQL operation.

Security benefits

• Simple, universally understood, works with virtually every tool that speaks SQL Server’s protocol.
• Doesn’t require any dependency on Entra ID being configured or reachable.

Limitations

• Passwords can be weak, reused, shared between team members, or hardcoded into application config — all classic breach vectors.
• No native support for multi-factor authentication.
• Credential rotation is a manual, easy-to-forget process — plenty of “temporary” passwords quietly turn three years old.
• No centralized identity governance — if someone leaves the company, you have to remember every SQL login they might know, rather than disabling one Entra ID account and having access evaporate everywhere at once.

Best practices

• Never use the built-in server admin account for application connections — create scoped logins per application.
• Rotate passwords on a schedule, and store them in Azure Key Vault, not in application config files.
• Grant the minimum role membership needed (db_datareader/db_datawriter instead of db_owner, almost always).
• Consider disabling SQL authentication entirely for production servers once you’ve migrated fully to Entra ID — Azure SQL supports an “Entra ID only” authentication mode for exactly this purpose.

Real-world scenario

A mid-sized retailer has a 12-year-old inventory management application that a vendor abandoned years ago. It only supports SQL Server username/password connection strings — there’s no realistic path to modifying it for token-based auth. The DBA team creates a dedicated, tightly scoped SQL login just for that application, stores the password in Key Vault, rotates it quarterly via an Azure Automation runbook, and restricts the login to db_datareader/db_datawriter on exactly one database. It’s not the ideal modern pattern, but it’s isolated, monitored, and doesn’t share credentials with anything else — which is the realistic goal when you’re stuck supporting legacy software.

2. Microsoft Entra ID Authentication (formerly Azure Active Directory)

How it works

Instead of validating a password directly, Azure SQL trusts Microsoft Entra ID as an external identity provider. A user (or app) authenticates to Entra ID first, receives a signed access token, and presents that token to Azure SQL instead of a password. Azure SQL validates the token’s signature and claims rather than checking credentials itself.

This unlocks everything Entra ID already does well: multi-factor authentication, Conditional Access policies, centralized user lifecycle management, and group-based access — all of which SQL logins simply can’t offer on their own.

When to use it

• Basically the default recommendation for any new Azure SQL deployment today.
• Anywhere you want centralized identity governance — disable one Entra ID account, and access to every database that trusted it disappears immediately.
• Anywhere compliance requires MFA on database access.
• Environments already standardized on Entra ID for Microsoft 365 / other Azure services (which, in practice, is nearly everyone).

Configuration — Azure Portal walkthrough

1. On your SQL server resource (not the database) in the portal, go to Settings → Microsoft Entra ID.
2. Click Set admin, and choose an Entra ID user, group, or service principal to be the Microsoft Entra admin for that server. (Best practice: assign a group, not an individual person, so admin rights survive personnel changes.)
3. Save. This admin can now connect using Entra ID credentials and has the rights to create additional Entra-based database users.
4. Optionally, under Authentication, set the server to Microsoft Entra authentication only to disable SQL logins entirely for that server.

Configuration — T-SQL

Once connected as the Entra admin, you grant access to other Entra identities directly inside the database — no password involved:

-- Grant a specific Entra ID user access to this database
CREATE USER [alex@contoso.com] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [alex@contoso.com];

-- Grant an entire Entra ID security group access — usually the better pattern
CREATE USER [DataAnalystsGroup] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [DataAnalystsGroup];

Granting groups instead of individuals is the single highest-leverage habit here — when someone joins the analytics team, you add them to the Entra ID group once, and their database access follows automatically. When they leave, remove them from the group, and access is gone everywhere at once.

Configuration — Azure CLI

# Set the Microsoft Entra admin for a SQL server
az sql server ad-admin create \
  --resource-group prod-data-rg \
  --server-name prod-sql-server \
  --display-name "SQL-DBA-Admins" \
  --object-id <entra-group-object-id>

Configuration — PowerShell

Set-AzSqlServerActiveDirectoryAdministrator `
  -ResourceGroupName "prod-data-rg" `
  -ServerName "prod-sql-server" `
  -DisplayName "SQL-DBA-Admins" `
  -ObjectId "<entra-group-object-id>"

Security benefits

• Multi-factor authentication support, out of the box.
• Conditional Access — you can require a compliant, managed device before someone can even attempt to connect.
• Centralized lifecycle management — one account disable action revokes access everywhere.
• Full audit trail tied to a real identity, not a shared login name.
• No passwords stored in the database’s own credential store at all.

Limitations

• Requires Entra ID to be reachable — if there’s an Entra ID outage, Entra-only authentication means nobody gets in (this is exactly why a tightly controlled break-glass SQL login is still a defensible idea).
• Slightly more setup complexity than a plain SQL login, especially for teams new to token-based auth.
• Some legacy drivers and tools genuinely don’t support Entra ID token authentication yet.

Best practices

• Assign the Entra admin role to a group, not a person.
• Grant database access via Entra ID groups, mirroring your org chart or team structure.
• Enable Microsoft Entra ID–only authentication on production servers once migration is complete, and keep exactly one tightly monitored break-glass SQL login for emergencies.
• Pair with Conditional Access to require MFA and compliant devices for anyone connecting to sensitive databases.

Real-world scenario

A financial services company has an internal reporting team of fifteen analysts who need read access to a sales data warehouse. Instead of creating fifteen SQL logins, the DBA creates one Entra ID group (“SalesReporting-ReadOnly”), adds all fifteen analysts to it, and grants that group db_datareader in one T-SQL statement. Three months later, when two analysts move to a different department, IT removes them from the Entra ID group as part of the standard offboarding checklist — no DBA involvement needed, and no risk of a forgotten SQL login lingering with access nobody remembers granting.

3. Managed Identity — Authentication Without Passwords, For Applications

How it works

Managed Identity solves a very specific, very common problem: your application (an Azure Function, an App Service, a VM, an Azure Automation runbook) needs to authenticate to Azure SQL — but where does it store its credentials? Historically, the answer was “in a config file or environment variable,” which is exactly the kind of secret sprawl that causes breaches.

Managed Identity removes the secret entirely. Azure automatically creates and manages an identity for the resource, backed by Microsoft Entra ID, and the resource can request a token from Azure’s internal metadata endpoint at runtime — no stored credential anywhere, ever.

There are two flavors:

• System-assigned managed identity — tied to the lifecycle of one specific resource. Delete the App Service, and its identity is deleted too. One-to-one.
• User-assigned managed identity — a standalone identity you create once and attach to multiple resources. Useful when several App Services or VMs should share the same identity and permission set.

   ┌──────────────────┐        1. Request token         ┌─────────────────┐
   │  Azure Function    │ ───────────────────────────▶  │  Microsoft Entra  │
   │  (Managed Identity)│ ◀─────────────────────────── │       ID          │
   └────────┬───────────┘        2. Signed token         └─────────────────┘
            │
            │ 3. Connect using token (no password)
            ▼
   ┌──────────────────┐
   │   Azure SQL DB     │
   └──────────────────┘

When to use it

• Any Azure-hosted application (App Service, Function App, VM, Azure Automation, AKS workload) that needs to talk to Azure SQL, Key Vault, Storage, or other Entra-integrated services.
• Anywhere you’re currently storing a connection string with a password in application settings or a config file — that’s almost always a candidate for migration to managed identity.
• CI/CD pipelines and automation runbooks that need to touch Azure resources without embedding a secret.

Configuration — Azure Portal walkthrough (App Service example)

1. Open your App Service in the portal → Settings → Identity.
2. Under the System assigned tab, toggle Status to On, and save. Azure creates an Entra ID identity for this specific App Service instance.
3. In your Azure SQL Database, connect as the Entra admin and create a user for that identity (the App Service’s name becomes the Entra identity’s display name):

CREATE USER [my-app-service-name] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [my-app-service-name];
ALTER ROLE db_datawriter ADD MEMBER [my-app-service-name];

4. In your application code, use a library like the Azure Identity SDK (DefaultAzureCredential in .NET/Python/JS) to acquire a token automatically — no connection string password needed at all.

Configuration — Azure CLI (creating a user-assigned identity)

# Create a standalone user-assigned managed identity
az identity create \
  --resource-group prod-data-rg \
  --name shared-app-identity

# Attach it to an App Service
az webapp identity assign \
  --resource-group prod-data-rg \
  --name my-web-app \
  --identities /subscriptions/<sub-id>/resourceGroups/prod-data-rg/providers/Microsoft.ManagedIdentity/userAssignedIdentities/shared-app-identity

Configuration — PowerShell

# Enable system-assigned managed identity on a VM
Update-AzVM -ResourceGroupName "prod-data-rg" -VM $vm -IdentityType SystemAssigned

Security benefits

• Zero stored credentials — nothing to leak in a config file, environment variable dump, or accidental GitHub commit.
• Tokens are short-lived and automatically rotated by Azure — no manual rotation process to forget.
• Access can be revoked instantly by removing the identity’s database user or deleting the identity itself.
• Fully auditable through Entra ID sign-in logs, same as any other identity.

Limitations

• Only works for Azure-hosted resources (or on-premises/other-cloud resources connected via Azure Arc) — it’s not a general-purpose solution for authenticating from an arbitrary laptop.
• System-assigned identities are tightly coupled to the resource’s lifecycle, which can occasionally complicate scenarios like blue-green deployments where you want the identity to persist across resource swaps (this is exactly when user-assigned identities earn their keep).
• Debugging “why isn’t my token working” can be less intuitive for teams used to a simple connection string.

Best practices

• Default to managed identity for any Azure-to-Azure service communication — treat a stored password as the exception that needs justifying, not the default.
• Use user-assigned identities when multiple resources need the same permission set, or when identity needs to outlive a specific resource instance.
• Grant the managed identity only the specific database role it needs — the same least-privilege principle applies here as anywhere else.
• Use the Azure Identity SDK’s DefaultAzureCredential (or language equivalent) so the same code works locally (via developer credentials) and in Azure (via managed identity) without code changes.

Real-world scenario

An e-commerce company has an Azure Function that runs nightly to reconcile orders between their SQL database and a payment processor’s API. The original implementation stored the SQL connection string — password included — as an App Setting. During a security review, that’s flagged as a risk: anyone with Reader access to the Function App’s configuration could see the database password in plain text. The team switches to a system-assigned managed identity, creates a scoped Entra ID database user for the Function App with exactly db_datareader and db_datawriter on the one table it needs, and deletes the old connection string entirely. The security review closes the finding, and as a side benefit, nobody has to remember to rotate that password ever again.

4. Azure RBAC (Role-Based Access Control)

How it works

Azure RBAC governs the control plane — who can manage the Azure resource itself: creating a SQL server, changing firewall rules, scaling compute, deleting a database, configuring backups. RBAC roles are assigned at a scope (management group, subscription, resource group, or individual resource) to a security principal (user, group, service principal, or managed identity).

Critically: RBAC does not grant data access. Being an Owner or Contributor on a SQL server does not let you SELECT from a table — that’s governed entirely by the data-plane mechanisms covered above (SQL Auth, Entra ID Auth, Managed Identity + database permissions).

When to use it

• Controlling who can provision, configure, scale, or delete Azure SQL resources.
• Delegating operational tasks (like managing firewall rules or backups) without handing out full subscription Owner rights.
• Separating “who can administer the infrastructure” from “who can query the data” — a distinction auditors specifically look for.

Common built-in roles relevant to SQL

Role	Scope of Control	Typical Assignee
SQL Server Contributor	Manage SQL servers/databases, but cannot access data or manage security policies	Infrastructure/platform team
SQL DB Contributor	Manage individual SQL databases (not servers) — create, scale, configure	Application team leads
SQL Security Manager	Manage security-related settings: auditing, threat detection, firewall rules — without full resource control	Security team
Reader	View resource configuration only, no changes	Auditors, support staff
Owner	Full control, including assigning access to others	Very small number of senior admins

Configuration — Azure Portal walkthrough

1. Navigate to the resource (or resource group / subscription) where you want to grant access → Access control (IAM).
2. Click Add → Add role assignment.
3. Select the role (e.g., SQL DB Contributor).
4. Select the member — ideally an Entra ID group rather than an individual.
5. Review and assign.

Configuration — Azure CLI

az role assignment create \
  --assignee "<entra-group-object-id>" \
  --role "SQL DB Contributor" \
  --scope "/subscriptions/<sub-id>/resourceGroups/prod-data-rg/providers/Microsoft.Sql/servers/prod-sql-server/databases/salesdb"

Configuration — PowerShell

New-AzRoleAssignment `
  -ObjectId "<entra-group-object-id>" `
  -RoleDefinitionName "SQL DB Contributor" `
  -Scope "/subscriptions/<sub-id>/resourceGroups/prod-data-rg/providers/Microsoft.Sql/servers/prod-sql-server/databases/salesdb"

Security benefits

• Fine-grained delegation without handing out subscription-wide Owner access.
• Scoped assignment (resource, resource group, subscription, management group) means you can grant exactly the footprint someone needs.
• Custom roles let you build precisely tailored permission sets when built-in roles are too broad or too narrow.
• Fully integrated with Entra ID groups and Conditional Access for consistent governance.

Limitations

• RBAC alone tells you nothing about who can see the data — teams sometimes mistakenly assume restricting RBAC access is enough to protect sensitive data, and it isn’t.
• Built-in roles can be broader than you’d like; custom role definitions add complexity to maintain.
• RBAC role propagation (especially at higher scopes like management groups) can take a few minutes to reflect — a common source of “why can’t I do this yet” confusion right after an assignment.

Best practices

• Follow least privilege: assign the narrowest built-in role that does the job, scoped to the narrowest resource level that makes sense.
• Assign roles to Entra ID groups, not individuals, for the same lifecycle reasons discussed earlier.
• Use custom RBAC roles when you need something between two built-in roles — e.g., “can manage firewall rules and view metrics, but cannot delete the server.”
• Remember RBAC and data-plane permissions are separate — document both when onboarding a new team member so nothing falls through the cracks.
• Periodically run Access Reviews (an Entra ID governance feature) to catch stale RBAC assignments before they become an audit finding.

Real-world scenario

A healthcare company separates its platform team from its application development teams. The platform team gets SQL Server Contributor at the resource group level — they can provision new databases, configure backups, and manage scaling, but they have no ability to query patient data (they don’t have Entra ID database users created for them). Application developers, by contrast, get no RBAC role at all on the SQL server — they don’t need to manage the infrastructure — but they do get Entra ID database access scoped to db_datareader on a de-identified reporting database. When an external auditor reviews access controls for a compliance certification, this clean separation between “who can manage the box” and “who can see the data” is exactly the story they’re hoping to hear.

Comparison Table: All Four Methods Side by Side

	SQL Authentication	Microsoft Entra ID Auth	Managed Identity	Azure RBAC
Plane	Data plane	Data plane	Data plane	Control plane
Identity type	SQL login (username/password)	Human user or group in Entra ID	Non-human identity for an Azure resource	Any security principal
Credential storage	Password stored/managed by SQL engine	None — token-based	None — fully automatic	N/A (not a login mechanism)
MFA support	No	Yes	N/A (not applicable to app identities)	Inherited via Entra ID sign-in for human assignees
Best for	Legacy apps, break-glass access	Human users, centralized governance	App-to-Azure-service authentication	Managing who can administer the resource
Rotation burden	Manual	None (token-based)	None (fully automatic)	N/A
Typical DP-300 relevance	Understanding legacy patterns, break-glass strategy	Heavily tested — setup, groups, Entra-only mode	Heavily tested — app authentication scenarios	Tested alongside data-plane permission separation

Troubleshooting Tips

• “Login failed for user” on a SQL login — check for typos, confirm the login exists at the server level and a matching user exists in the target database (a login without a mapped database user is a classic gotcha).
• “Cannot open server … requested by the login” — almost always a firewall rule issue; confirm the client IP (or “Allow Azure services” setting) is permitted.
• Entra ID token errors (“AADSTS…”) — these codes are your friend; look them up specifically rather than guessing. Common culprits: token audience mismatch, conditional access blocking the sign-in, or an expired/misconfigured app registration.
• Managed identity “login failed” — confirm you actually ran CREATE USER [identity-name] FROM EXTERNAL PROVIDER inside the target database; enabling the identity in the portal alone does not grant it database access.
• RBAC role “not taking effect” — give it a few minutes for propagation before assuming misconfiguration; also double-check the scope the role was assigned at, since a role on the wrong resource group is a very easy mistake.
• Confusing “access denied” between planes — if someone can log into the SQL server but can’t manage firewall rules (or vice versa), remember you’re likely looking at a data-plane vs. control-plane mismatch, not a bug.

Common Mistakes to Avoid

1. Granting db_owner “just to make the error go away” instead of diagnosing the actual missing permission.
2. Assigning Entra ID database access to individuals instead of groups, creating years of manual cleanup debt.
3. Assuming RBAC access implies data access (or vice versa) — they are completely independent systems.
4. Leaving SQL authentication fully enabled on production servers “temporarily” during an Entra ID migration, indefinitely.
5. Storing managed-identity-eligible credentials in config files anyway, out of habit, instead of actually wiring up DefaultAzureCredential.
6. Forgetting that enabling a managed identity in the portal does nothing on its own — the corresponding CREATE USER ... FROM EXTERNAL PROVIDER step in the database is mandatory and easy to skip.
7. Not keeping at least one tightly controlled break-glass account when moving to Entra-only authentication.

Interview Questions Worth Practicing

1. What’s the difference between Azure RBAC and SQL database permissions, and why can’t you use one to substitute for the other?
2. Walk through how a token-based authentication flow works for an application using managed identity to connect to Azure SQL.
3. When would you choose a system-assigned managed identity over a user-assigned one?
4. What happens to database access if you delete an Entra ID group that was granted db_datareader?
5. How would you migrate a production environment from SQL authentication to Entra ID authentication with minimal downtime risk?
6. What’s the security argument for granting access via Entra ID groups instead of individual accounts?
7. Explain what “Microsoft Entra ID–only authentication” does, and describe a legitimate reason a team might delay enabling it.

DP-300 Exam Tips

• Expect scenario questions that require you to identify which plane a described permission problem belongs to — practice reading the scenario carefully for “can’t connect” (data plane) vs. “can’t scale/configure” (control plane) language.
• Know the exact T-SQL syntax for creating Entra ID and managed identity database users (FROM EXTERNAL PROVIDER) — this shows up directly.
• Be comfortable with both the portal steps and the CLI/PowerShell equivalents for setting an Entra admin and assigning RBAC roles — the exam tests both angles.
• Understand the operational trade-off of enabling Entra-only authentication (stronger security posture vs. dependency on Entra ID availability) — this shows up as a “recommend the best approach” style question.
• Review the built-in SQL-related RBAC roles (SQL Server Contributor, SQL DB Contributor, SQL Security Manager) closely enough to distinguish what each one can and cannot do — the exam likes to test the boundaries.

Frequently Asked Questions

Can I use both SQL authentication and Entra ID authentication on the same server? Yes, by default both are enabled side by side. You can restrict to Entra-only via the server’s authentication settings once you’re ready.

Does a managed identity need a password? No — that’s the entire point. Azure and Entra ID handle token issuance and rotation automatically; there’s never a password to manage.

Can a managed identity be used from outside Azure — like my laptop? No. Managed identity is tied to the Azure resource it’s attached to (or Arc-enabled resources). For local development, you’d typically use your own Entra ID developer credentials via the Azure Identity SDK instead.

If I give someone the Owner RBAC role on a SQL server, can they read the data? Not by default. RBAC Owner lets them manage the resource (including granting themselves an Entra admin role, which would then let them access data) — but Owner alone doesn’t create a database login or grant SELECT rights.

What’s the difference between a custom RBAC role and a database role like db_datareader? A custom RBAC role controls management actions on the Azure resource (control plane); a database role like db_datareader controls what SQL statements someone can run inside the database (data plane). Same “role” vocabulary, completely different systems.

Is Entra ID authentication slower than SQL authentication? There’s a small amount of additional latency for the initial token acquisition, but tokens are cached and reused, so in practice the difference is negligible for real-world application performance.

Decision Matrix: Choosing the Right Method

Your Situation	Recommended Approach
New Azure SQL deployment, human users need access	Microsoft Entra ID Authentication, granted via groups
App or service hosted in Azure needs to connect to SQL	Managed Identity (system-assigned by default; user-assigned if shared across resources)
Legacy application that can’t be modified for token auth	SQL Authentication, tightly scoped, password in Key Vault, rotated regularly
Need an emergency access path if Entra ID has an outage	One break-glass SQL login, monitored closely, used only in genuine emergencies
Deciding who can provision/scale/delete the SQL server itself	Azure RBAC, least-privilege built-in or custom role, assigned to groups
Multiple App Services need identical database access	User-assigned Managed Identity, shared across those resources
Compliance requires MFA on all database access	Microsoft Entra ID Authentication with Conditional Access enforcing MFA
Security review flags stored credentials in app config	Migrate to Managed Identity, remove the stored secret entirely

The pattern to internalize: authenticate humans through Entra ID, authenticate applications through Managed Identity, keep SQL authentication as a narrow, well-guarded exception rather than the default, and always treat Azure RBAC as a separate conversation from database permissions — because it genuinely is one.

Key Takeaways

• Azure separates the control plane (RBAC — can you manage the resource?) from the data plane (SQL Auth / Entra ID / Managed Identity — can you access the data?), and conflating the two is the single most common source of access-control confusion.
• SQL Authentication is the legacy fallback — keep it narrow, monitored, and ideally limited to break-glass scenarios in a mature environment.
• Microsoft Entra ID Authentication should be your default for human access — it brings MFA, Conditional Access, and centralized lifecycle management that passwords simply can’t match.
• Managed Identity should be your default for application-to-Azure-service authentication — it eliminates stored credentials entirely and removes rotation from your team’s to-do list.
• Azure RBAC governs who can administer the resource, not who can see the data inside it — design both layers deliberately, and document how they interact for anyone auditing your environment.