This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Secure, optimize, and deploy database solutions (35–40%)
   --> Implement data security and compliance
      --> Implement secure database access, including passwordless

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

One of the primary responsibilities of a SQL AI Developer is ensuring that applications and users access databases securely. As organizations move toward cloud-native architectures and zero-trust security models, traditional username-and-password authentication is increasingly being replaced by more secure alternatives such as passwordless authentication, Microsoft Entra ID (formerly Azure Active Directory), managed identities, and service principals.

The DP-800 exam expects candidates to understand how to design secure authentication and authorization strategies for SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Microsoft Fabric SQL solutions. Candidates should also understand when to use SQL authentication versus Microsoft Entra authentication, how passwordless authentication works, and how applications securely connect to databases without embedding secrets.

Authentication vs. Authorization

A common exam objective is distinguishing authentication from authorization.

Authentication answers the question:

Who are you?

Authentication verifies the identity of a user or application.

Examples include:

• Microsoft Entra ID login
• SQL login
• Windows Authentication
• Managed Identity
• Service Principal

Authorization answers the question:

What are you allowed to do?

Authorization determines permissions after authentication succeeds.

Examples include:

• SELECT permission
• EXECUTE permission
• Database roles
• Row-Level Security (RLS)
• Object-level permissions

Authentication always occurs before authorization.

Types of Database Authentication

SQL Server supports multiple authentication methods.

SQL Authentication

SQL Authentication uses a SQL login and password stored by SQL Server.

Example:

CREATE LOGIN SalesUser
WITH PASSWORD = 'StrongPassword123!';

Advantages:

• Easy to configure
• Supported by virtually every SQL client
• Independent of Active Directory

Disadvantages:

• Password management required
• Password rotation required
• Secrets must often be stored in applications
• Higher risk of credential theft

Microsoft recommends minimizing the use of SQL authentication whenever possible, particularly in Azure environments.

Windows Authentication

Windows Authentication uses Active Directory credentials.

Advantages:

• Integrated security
• Single sign-on (SSO)
• Centralized identity management
• Kerberos authentication
• Password policies enforced automatically

Common connection string:

Integrated Security=True;

This is the preferred authentication method for on-premises SQL Server environments.

Microsoft Entra Authentication

Microsoft Entra ID is Microsoft’s cloud identity provider and is the preferred authentication mechanism for Azure SQL services.

Benefits include:

• Single Sign-On (SSO)
• Multi-Factor Authentication (MFA)
• Conditional Access
• Centralized identity management
• Passwordless authentication support
• Identity governance
• Integration with Microsoft Fabric

Users authenticate through Microsoft Entra instead of SQL logins.

Example workflow:

User
     │
Microsoft Entra ID
     │
Azure SQL Database

Passwordless Authentication

Passwordless authentication eliminates traditional passwords while maintaining strong identity verification.

Instead of passwords, authentication may use:

• Windows Hello for Business
• Microsoft Authenticator
• FIDO2 Security Keys
• Passkeys
• Biometric authentication
• Managed Identities
• Microsoft Entra tokens

Benefits include:

• Eliminates password theft
• Prevents password reuse
• Reduces phishing attacks
• Removes password rotation requirements
• Improves user experience

Microsoft strongly recommends passwordless authentication whenever possible.

How Passwordless Authentication Works

Instead of sending a password:

Application
      │
Obtains Microsoft Entra access token
      │
Azure SQL Database validates token
      │
Connection established

The database trusts Microsoft Entra rather than validating a stored password.

Managed Identity

Managed Identity is one of the most important DP-800 topics.

A Managed Identity is an identity automatically managed by Azure for Azure resources.

Examples:

• Azure App Service
• Azure Functions
• Azure Virtual Machines
• Azure Container Apps
• Azure Kubernetes Service
• Azure Logic Apps

Instead of storing credentials:

Application
        ↓
Managed Identity
        ↓
Microsoft Entra ID
        ↓
Azure SQL Database

No passwords are stored.

Advantages of Managed Identity

Benefits include:

• No stored passwords
• Automatic credential rotation
• Short-lived access tokens
• Integrated with Microsoft Entra
• Easier compliance
• Reduced security risk

This is Microsoft’s recommended approach for Azure-hosted applications.

Service Principals

A Service Principal represents an application rather than a person.

Common uses include:

• CI/CD pipelines
• Azure DevOps
• GitHub Actions
• Background services
• Automation scripts

Service principals authenticate through Microsoft Entra and can access Azure SQL databases securely.

Access Tokens

Modern Azure SQL authentication uses OAuth access tokens.

Instead of:

Username
Password

Applications obtain:

Microsoft Entra Access Token

The token:

• Has a limited lifetime
• Cannot be reused indefinitely
• Reduces credential theft
• Supports Conditional Access policies

Configuring Microsoft Entra Authentication

Typical steps include:

1. Configure a Microsoft Entra administrator for the SQL server.
2. Create Microsoft Entra users or groups.
3. Create contained database users.
4. Assign database roles.
5. Grant required permissions.

Example:

CREATE USER [Alice@contoso.com]
FROM EXTERNAL PROVIDER;

Grant role:

ALTER ROLE db_datareader
ADD MEMBER [Alice@contoso.com];

No SQL password is required.

Contained Database Users

Contained database users simplify authentication.

Advantages:

• No SQL login required
• Database portability
• Simplified Azure SQL deployments
• Works well with Microsoft Entra identities

Example:

CREATE USER [Developers]
FROM EXTERNAL PROVIDER;

Secure Connection Strings

Avoid storing:

Server=myserver;
User ID=admin;
Password=Password123;

Instead, use Microsoft Entra authentication.

Example (.NET):

Authentication=Active Directory Default;

The application automatically acquires an access token using the available identity.

Connection Security

Authentication should be combined with encrypted network connections.

Best practices include:

• Require TLS encryption
• Validate server certificates
• Encrypt all client-server communication
• Disable legacy protocols

Azure SQL encrypts client connections by default.

Principle of Least Privilege

Applications should receive only the permissions they require.

Example:

Application needs:

• Execute stored procedures

Application does not need:

• ALTER DATABASE
• CONTROL
• db_owner

Using least privilege minimizes security risks.

Passwordless Authentication with Azure Services

Many Azure services automatically support Managed Identity.

Example:

Azure Function
       │
Managed Identity
       │
Microsoft Entra
       │
Azure SQL Database

No secrets are stored in code or configuration files.

Microsoft Fabric Integration

Microsoft Fabric integrates closely with Microsoft Entra ID.

Fabric workloads support:

• Microsoft Entra authentication
• Single Sign-On
• Role-based access
• Passwordless identity
• Unified identity management

DP-800 candidates should understand that Fabric relies heavily on Microsoft Entra identities rather than SQL logins.

Security Best Practices

Microsoft recommends:

• Prefer Microsoft Entra authentication over SQL authentication.
• Use passwordless authentication whenever possible.
• Enable Multi-Factor Authentication (MFA).
• Use Managed Identity for Azure-hosted applications.
• Use Service Principals for automation.
• Avoid embedding credentials in source code.
• Store secrets in Azure Key Vault if passwords or keys are unavoidable.
• Rotate credentials regularly when passwords must be used.
• Use TLS encryption for all database connections.
• Follow the principle of least privilege.
• Audit authentication events regularly.
• Use Conditional Access policies to protect administrative accounts.

Common DP-800 Exam Scenarios

You may be asked to determine:

• Which authentication method is most secure.
• When to use Managed Identity.
• When to use Microsoft Entra authentication.
• How passwordless authentication works.
• When SQL Authentication is appropriate.
• How applications connect without passwords.
• How service principals authenticate.
• How contained database users simplify Azure SQL deployments.
• How to eliminate secrets from connection strings.
• How to secure Azure-hosted AI applications accessing SQL databases.

DP-800 Exam Tips

Remember these key points:

• Microsoft Entra ID is the preferred authentication mechanism for Azure SQL.
• Passwordless authentication reduces phishing and credential theft.
• Managed Identities eliminate stored passwords.
• Service Principals authenticate applications and automation.
• SQL Authentication still exists but is less secure.
• Authentication verifies identity; authorization controls permissions.
• Use least privilege for both users and applications.
• Azure SQL supports OAuth access tokens instead of passwords.
• Fabric uses Microsoft Entra authentication extensively.

Practice Exam Questions

Question 1

Which authentication method is Microsoft’s recommended approach for Azure-hosted applications connecting to Azure SQL Database?

A. Managed Identity

B. SQL Authentication

C. Windows Authentication

D. Shared SQL Administrator account

Correct Answer: A

Explanation:
Managed Identity eliminates the need to store credentials, automatically manages identity, and integrates with Microsoft Entra ID, making it Microsoft’s preferred authentication method for Azure-hosted applications.

Question 2

What is the primary purpose of passwordless authentication?

A. Improve query performance

B. Eliminate traditional passwords while securely verifying identity

C. Replace authorization

D. Encrypt database backups

Correct Answer: B

Explanation:
Passwordless authentication replaces passwords with stronger authentication mechanisms such as biometrics, security keys, Microsoft Authenticator, or access tokens, reducing the risk of credential theft.

Question 3

Which statement correctly distinguishes authentication from authorization?

A. Authentication determines database roles; authorization creates logins.

B. Authentication encrypts data; authorization decrypts it.

C. Authentication verifies identity, while authorization determines what actions are permitted.

D. Authentication assigns object permissions, while authorization validates passwords.

Correct Answer: C

Explanation:
Authentication confirms who a user or application is, whereas authorization determines what resources and operations that authenticated identity may access.

Question 4

A development team wants to eliminate database passwords from application configuration files. Which solution best meets this requirement?

A. Store SQL passwords in source code.

B. Use SQL Authentication with stronger passwords.

C. Share one administrator account among all applications.

D. Use Microsoft Entra authentication with Managed Identity.

Correct Answer: D

Explanation:
Managed Identity allows applications to authenticate without storing passwords or secrets, significantly improving security and simplifying credential management.

Question 5

Which authentication method is commonly used for automated CI/CD pipelines and background services?

A. Windows Authentication

B. Service Principal

C. SQL Authentication

D. Database Owner account

Correct Answer: B

Explanation:
Service Principals represent applications rather than users and are commonly used by automation tools such as Azure DevOps and GitHub Actions.

Question 6

Which feature is automatically provided by Managed Identity?

A. Automatic query tuning

B. Automatic index creation

C. Automatic credential rotation

D. Automatic data encryption

Correct Answer: C

Explanation:
Managed Identity automatically handles credential creation and rotation, eliminating the need for administrators or developers to manage passwords.

Question 7

Which SQL statement creates a Microsoft Entra user in an Azure SQL Database?

A.

CREATE LOGIN Alice WITH PASSWORD='Password123';

B.

CREATE USER Alice WITHOUT LOGIN;

C.

CREATE USER [Alice@contoso.com] FROM EXTERNAL PROVIDER;

D.

CREATE ROLE Alice;

Correct Answer: C

Explanation:
The FROM EXTERNAL PROVIDER clause creates a contained database user that authenticates through Microsoft Entra ID rather than a SQL login.

Question 8

Which security principle recommends granting only the permissions required for a user or application to perform its work?

A. Ownership chaining

B. Principle of least privilege

C. Password complexity

D. Data masking

Correct Answer: B

Explanation:
Least privilege minimizes security risks by limiting permissions to only those necessary for the required tasks.

Question 9

Which authentication mechanism does Azure SQL Database use with Microsoft Entra authentication?

A. Static passwords

B. Kerberos tickets only

C. SQL login hashes

D. OAuth access tokens

Correct Answer: D

Explanation:
Microsoft Entra authentication relies on OAuth access tokens, which are short-lived and securely validated by Azure SQL Database.

Question 10

Why is Microsoft Entra authentication generally preferred over SQL Authentication for Azure SQL Database?

A. It requires longer passwords.

B. It supports centralized identity management, MFA, Conditional Access, and passwordless authentication.

C. It eliminates database roles.

D. It removes the need for database permissions.

Correct Answer: B

Explanation:
Microsoft Entra authentication provides enterprise-grade identity management features, including Single Sign-On, Multi-Factor Authentication, Conditional Access, centralized administration, and support for passwordless authentication, making it more secure than traditional SQL Authentication.

Go to the DP-800 Exam Prep Hub main page