Launch Deal · 50% off 1st month ·

2.3 — Passwordless Access, Auditing and Securing Endpoints

DP-800 Domain 2 · Skill 2.3

What You Need to Know

AI-enabled databases talk to applications, AI model endpoints and APIs (REST, GraphQL, MCP). Every one of those connections needs an identity, least privilege, and an audit trail. Passwords and keys in connection strings are the anti-pattern.

Passwordless database access

Approach How
Microsoft Entra authentication Users and groups sign in with Entra ID (with MFA). Create contained users with CREATE USER [user@contoso.com] FROM EXTERNAL PROVIDER
Managed identity for apps App Service, Functions or Container Apps get a managed identity. Then CREATE USER [my-app] FROM EXTERNAL PROVIDER, and add it to a role. The connection string uses Authentication=Active Directory Managed Identity, with no secret
Microsoft Entra-only authentication Turn off SQL authentication entirely on the server
CI/CD without secrets GitHub Actions or Azure Pipelines use workload identity federation (OIDC) to sign in to Azure, with no stored client secret

Auditing

  • Azure SQL auditing, at server or database level, writes events to Azure Storage, a Log Analytics workspace or Event Hubs. Server-level auditing covers every database.
  • SQL Server Audit on SQL Server: server and database audit specifications, written to files or the event log.
  • Audit successful and failed logins, schema changes, permission changes and access to sensitive tables. Send the logs to Log Analytics or Microsoft Sentinel for alerting.
  • Ledger (lesson 1.2) adds tamper evidence, and auditing records who did what.

Securing AI model endpoints

-- Use the database's managed identity, not an API key, to call Azure OpenAI
CREATE DATABASE SCOPED CREDENTIAL [https://contoso-ai.openai.azure.com/]
    WITH IDENTITY = 'Managed Identity',
         SECRET = '{"resourceid":"https://cognitiveservices.azure.com"}';
  • Give the server's managed identity (for SQL Server 2025, the Azure Arc-enabled identity) the Azure OpenAI role that the documentation requires on that one AI resource (for example Cognitive Services OpenAI Contributor), and nothing broader.
  • External models and sp_invoke_external_rest_endpoint use database scoped credentials. Restrict who can use them with GRANT EXECUTE ON EXTERNAL MODEL and the endpoint permission.
  • Keep any required keys in Azure Key Vault, never in code or prompts.

Securing REST, GraphQL and MCP endpoints

Control Detail
Authentication Data API builder validates Entra ID JWTs (or App Service and Static Web Apps authentication). Anonymous access only for public data
Authorization Roles per entity, with allowed actions (create, read, update, delete, execute). Field-level include and exclude lists, and database policies that filter rows per role
Least-privilege DB identity The API connects with a managed identity that has only the needed grants, never db_owner
Network HTTPS only, private endpoints, and API Management in front for throttling and keys
MCP Expose only the needed entities as tools, read-only where possible, and require approval for writes

Key Concept: Identities, not secrets: Entra and managed identity for users, apps, AI endpoints and pipelines. Least privilege at every hop, and auditing into Log Analytics so you can see what happened.

Exam Traps

  • Storing an Azure OpenAI API key in a database scoped credential when managed identity is available
  • Giving the Data API builder connection db_owner
  • Auditing only one database when a server-level policy should cover them all
  • Leaving SQL authentication on when the requirement is passwordless access only

Check your understanding

Practice Scenario

🔒 This practice scenario is part of Skillmarked Pro

The lessons stay free. Pro unlocks the other 14 DP-800 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.

See plans

Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.

Cancel anytime. Or €199 a year, which saves 17%.

Not sure yet? Try 10 free mock exam questions.

Already Pro? Log in.