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_endpointuse database scoped credentials. Restrict who can use them withGRANT EXECUTE ON EXTERNAL MODELand 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.
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.
Sources
- Microsoft Entra authentication for Azure SQL, Microsoft Learn
- Connect using managed identity, Microsoft Learn
- Auditing for Azure SQL Database, Microsoft Learn
- CREATE EXTERNAL MODEL, Microsoft Learn
- Data API builder security overview, Microsoft Learn