# Managing Identity and Access for Azure SQL

> Chapter 7 of the Courseiva DP-300 curriculum — https://courseiva.com/learn/dp-300/manage-identity-access-azure-sql

**Official objective:** 3.1 — Configure authentication and authorization for Azure SQL

## Introduction

How do you stop the wrong person from accessing your database while making sure the right person can do their job? That is exactly the problem that identity and access management solves, and it is a core topic on the DP-300 exam because every real database must keep data safe from unauthorised eyes and accidental damage.

## The Nightclub Door Policy Analogy

A nightclub's front door is a checkpoint that enforces two separate rules: who is allowed in (identity) and what they can do once inside (access). The club owner does not personally check every guest; instead, they hire a bouncer and set rules that the bouncer follows automatically. This setup mirrors exactly how Azure SQL manages its security.

The bouncer checks each guest's ID against a list of approved names. In Azure SQL, this identity check uses a login — a digital ID that proves who you are. The login can be created inside the SQL server itself (SQL authentication) or linked to a corporate directory (Microsoft Entra ID authentication). Just as a club might accept driver's licences or passports, Azure SQL accepts these two forms of identity.

Once inside, the bouncer’s job changes. Now they enforce what each guest can do — the VIP lounge? The dance floor? The bar? This is authorisation, and in Azure SQL it is managed by permissions and roles. A user might be a 'db_owner' (full access, like a club manager who can go anywhere and touch anything) or a 'db_datareader' (only allowed to read data, like a guest who can only look at the decorations but not touch them). The bouncer never guesses or makes exceptions — he follows the owner’s exact, written policy. This guarantees that only the right people get in, and once they are in, they can only do what their role allows.

## Core explanation

When you create an Azure SQL database, it does not live on your personal computer. It lives in Microsoft’s cloud, on a server that you do not physically control. This means you need a way to prove who is asking to connect and what they are allowed to do once they connect. That is the entire job of identity and access management.

First, let us define identity. An identity is a digital representation of a person, application, or service that needs to connect to the database. In the world of Azure SQL, identities come in two main flavours: SQL logins and Microsoft Entra ID principals. A SQL login is created inside the SQL server itself. Think of it as a username and password that SQL Server stores and checks. When a human or an application presents this username and password, SQL Server checks its own internal list to see if the details match. If they do, the identity is verified — that is authentication.

Microsoft Entra ID, on the other hand, is Microsoft’s cloud-based directory service. It stores identities for an entire organisation. If your company uses Microsoft 365 for email, you already have an identity in Microsoft Entra ID. When you set up Azure SQL to use Microsoft Entra ID authentication, the database does not check a local password. Instead, it asks Microsoft Entra ID to verify the user. This is more secure because Microsoft Entra ID supports features like multi-factor authentication — where you need your password plus a code from your phone — and conditional access policies that can block logins from strange locations or devices.

Once the identity is authenticated, the database needs to decide what that identity is allowed to do. That is authorisation. Authorisation in Azure SQL is managed through permissions and roles. A permission is a single right — for example, the right to read data from a table, or the right to delete a table. A role is a named collection of permissions. Instead of giving every user individual permissions, which becomes messy very quickly, you put users into roles. There are built-in roles like db_owner, db_datareader, and db_datawriter. There are also server-level roles like sysadmin and dbcreator.

Let us walk through a concrete example. Suppose your company has a database that stores customer orders. You have three people who need access: Alice, who manages the database; Bob, who enters new orders; and Charlie, who runs reports but should never change any data. You would create a Microsoft Entra ID user for each person (or use their existing company email identity). Then, in the database, you would add Alice to the db_owner role so she can change schema and fix issues. You would add Bob to db_datawriter, which lets him insert, update, and delete data. You would add Charlie to db_datareader, which only lets him read data. Now, when Bob accidentally tries to delete the entire Orders table, the database will refuse because his role does not include that permission.

Another critical concept is the difference between logins and users. A login is a server-level identity — it lets you connect to the SQL Server instance. A user is a database-level identity — it is mapped to a login and defines what you can do inside a specific database. So you might have one login (the same username and password) that is mapped to a user in the Sales database and a different user in the HR database, each with completely different roles. This separation means you can control access independently for every database, even if the person uses the same credentials to connect.

Why does all of this matter for the DP-300 exam? The exam will ask you to choose the correct authentication method for a given scenario, to assign the right role to a user, and to understand how to migrate from SQL authentication to Microsoft Entra ID authentication. You will need to know the exact syntax for creating logins and users, and you will need to know that Microsoft Entra ID authentication is the recommended modern approach because it centralises identity management and adds layers of security like multi-factor authentication.

## Real-world context

An IT professional tasked with managing a company’s Azure SQL databases will spend a significant portion of their day managing who has access to what. Here is a realistic scenario.

Your company, FreshBake, runs an online bakery ordering platform. The database holds customer names, addresses, credit card details, and order history. The company has five employees: Sam (the CTO), Priya (a developer), Lina (a customer support agent who needs to view order details), Jake (a data analyst who runs sales reports), and an automated application called OrderAPI that places new orders from the website. Your job is to set up access so that everyone can do their job without any risk of data leaking or being corrupted.

You begin by deciding on the authentication method. Because your company already uses Microsoft 365 for email, you choose Microsoft Entra ID authentication for all human users. This means Sam, Priya, Lina, and Jake will log into the database using their existing work email and password, plus a multi-factor authentication prompt on their phones. For the OrderAPI application, you cannot use a human identity, so you create a managed identity — a special identity that Azure manages automatically — for the application to use.

Next, you create logins. In the master database of your Azure SQL server, you run a command to create a Microsoft Entra ID login for Sam, Priya, Lina, and Jake. You also configure the server to allow Microsoft Entra ID authentication. Then, for the OrderAPI application, you create a contained database user that maps directly to the managed identity, so it can connect without needing a server-level login.

Now comes authorisation. In the database that holds order data, you create users for each of these identities. For Sam, you add him to the db_owner role because he needs full control for maintenance. For Priya, you add her to the db_ddladmin role so she can create and alter tables, but not delete data. For Lina, you add her to db_datareader so she can look up orders for customers. For Jake, you also add him to db_datareader, but you also grant him explicit permission to execute a specific stored procedure that generates monthly sales summaries. For the OrderAPI application, you add it to db_datawriter and db_datareader so it can both read and write order records.

Finally, you monitor access. You enable auditing in Azure SQL, which logs every login attempt and every query. When Lina calls you saying she cannot see a new order, you check the audit logs and discover she is trying to query a table she does not have permission to access. You adjust her role to fix the issue. A few weeks later, a security audit reveals that Jake was running suspiciously large exports of customer data. Because you used Microsoft Entra ID, you can immediately revoke Jake’s access from the Azure portal — you do not need to change anything in the database itself. The entire security model is working as intended, and the company passes its compliance review.

## Exam focus

The DP-300 exam tests your understanding of identity and access for Azure SQL in several specific ways. You should expect multiple-choice questions where you must choose between SQL authentication and Microsoft Entra ID authentication for a given scenario. The clear pattern is this: if the question mentions multi-factor authentication, conditional access, or centralised user management, the answer is almost always Microsoft Entra ID authentication. If the question involves legacy applications that cannot use modern authentication or isolated environments without internet access, the answer might be SQL authentication.

Another common trap is the difference between a server-level login and a database-level user. The exam loves to present a scenario where a user connects to the server but cannot see any databases. The answer is often that the login exists but no user has been created in the target database, or the user has not been mapped to the login. You need to remember that creating a login alone does not grant access to any database — you must then create a user in the specific database and assign roles.

The exam also tests built-in database roles heavily. Memorise the following:
- db_owner — full control over the database
- db_datareader — read all data
- db_datawriter — insert, update, delete all data
- db_ddladmin — run DDL (create, alter, drop) commands
- db_securityadmin — manage permissions and roles inside the database

Expect questions that ask you to assign a role to a user for a specific job function. For example: 'A support agent needs to view customer data but must never change it. Which role should you assign?' The answer is db_datareader.

Another frequent topic is contained databases. A contained database is a database that stores its own user information, rather than relying on server-level logins. This makes the database easier to move to another server. The exam will ask when you should use contained database users versus server-level logins. The answer typically relates to portability — if you plan to move the database to a different server frequently, contained users are better.

Watch out for questions that mention 'Azure SQL Managed Instance' versus 'Azure SQL Database'. The authentication and authorisation concepts are the same, but Managed Instance supports more SQL Server features, including Windows authentication. The exam will test whether you know which features are available in each service tier.

Finally, you must know how to use T-SQL commands for identity management. You may be asked to identify the correct syntax for creating a login, creating a user, or granting a permission. For example:
- CREATE LOGIN MyLogin WITH PASSWORD = 'Str0ng!Pass'
- CREATE USER MyUser FOR LOGIN MyLogin
- ALTER ROLE db_datareader ADD MEMBER MyUser

If you can write these commands from memory and understand when to use each one, you will be well prepared for this section of the exam.

## Step by step

1. **Choose authentication method** — Decide whether to use SQL authentication or Microsoft Entra ID authentication. For most modern environments, choose Microsoft Entra ID because it supports multi-factor authentication and centralised user management.
2. **Create server-level login (if using SQL authentication)** — Use the CREATE LOGIN T-SQL command to create a username and password that allows connection to the SQL Server instance. This does not grant access to any database yet.
3. **Create database-level user** — In the target database, use CREATE USER to map a user to an existing login (or to a Microsoft Entra ID identity). This step creates a database identity that can be assigned permissions.
4. **Assign roles or permissions** — Use ALTER ROLE to add the user to a built-in role like db_datareader or db_datawriter, or use GRANT to give specific permissions. This step defines exactly what the user can do inside the database.
5. **Test and audit access** — Ask the user to connect and perform their tasks. Enable auditing to log all login attempts and queries. Review logs regularly to catch unauthorised access attempts or permission gaps.

## Comparisons

### SQL Authentication vs Microsoft Entra ID Authentication

**SQL Authentication:**
- Username and password stored inside SQL Server
- No multi-factor or conditional access support
- Requires manual password management and rotation

**Microsoft Entra ID Authentication:**
- Identity verified by Microsoft Entra ID cloud service
- Supports multi-factor authentication and conditional access
- Centralised password policies and automated security features

### Server-Level Login vs Database-Level User

**Server-Level Login:**
- Created at the server level in the master database
- Grants ability to connect to the SQL Server instance
- Single login can map to multiple users in different databases

**Database-Level User:**
- Created inside a specific user database
- Grants access only to that specific database
- Must be created separately for each database

### db_datareader Role vs db_datawriter Role

**db_datareader Role:**
- Allows SELECT (read) on all tables and views
- Cannot insert, update, or delete data
- Used for reporting or read-only access scenarios

**db_datawriter Role:**
- Allows INSERT, UPDATE, DELETE on all tables
- Cannot read data unless combined with db_datareader
- Used for applications or users that need to write data

## Diagram

_Flowchart showing the process from user connection through authentication to authorisation in Azure SQL._

```mermaid
flowchart TD
    A[User or Application] --> B{Authentication Method}
    B -->|SQL Auth| C[SQL Login Check]
    B -->|Entra ID Auth| D[Microsoft Entra ID Check]
    C --> E[Server Connection Established]
    D --> E
    E --> F{Database User Exists?}
    F -->|No| G[Access Denied]
    F -->|Yes| H[Authorisation Check]
    H --> I[Assign Roles and Permissions]
    I --> J[Access Granted to Data]
```

## Common misconceptions

- **Misconception:** Once I create a SQL login, that person automatically has access to all databases on the server. **Reality:** A SQL login only grants access to connect to the server. To access a specific database, you must create a user for that login inside each database and assign permissions. (People confuse the server-level login with database-level access because in many simple setups, the administrator creates a login and a user in the same step.)
- **Misconception:** Microsoft Entra ID authentication is only for human users, not for applications. **Reality:** Applications can use managed identities or service principals in Microsoft Entra ID to authenticate without human interaction. (Because Microsoft Entra ID is commonly associated with employee login pages, people overlook its application identity features.)
- **Misconception:** If I give a user the db_datareader role, they can also update data because reading includes writing. **Reality:** db_datareader only grants SELECT permission. A separate role, db_datawriter, is required for INSERT, UPDATE, and DELETE operations. (The word 'reader' sounds passive, but beginners often assume read access implies the ability to change data, which it does not.)
- **Misconception:** SQL authentication is more secure than Microsoft Entra ID authentication because the passwords are stored inside the database. **Reality:** Microsoft Entra ID authentication is more secure because it supports multi-factor authentication, conditional access policies, and centralised password management. (Beginners mistakenly think local storage is safer than cloud storage, not realising that Microsoft Entra ID uses enterprise-grade security and encryption.)
- **Misconception:** Contained database users are the same as server-level logins, just named differently. **Reality:** Contained database users are stored entirely inside the database and do not require a server-level login. This makes the database portable to other servers. (The similar purpose of granting database access leads people to conflate two fundamentally different implementation methods.)

## Key takeaways

- Authentication is the process of verifying who you are, and authorisation is the process of determining what you are allowed to do after authentication succeeds.
- Azure SQL supports two authentication methods: SQL authentication (username and password stored in SQL Server) and Microsoft Entra ID authentication (identity verified by Microsoft's cloud directory).
- A server-level login lets you connect to the server, but you must create a database-level user mapped to that login before you can access any specific database.
- Built-in database roles like db_owner, db_datareader, and db_datawriter simplify permission management by grouping common sets of permissions into named roles.
- Contained database users store identity information inside the database itself, making the database portable across servers without needing to recreate logins.
- Microsoft Entra ID authentication is the recommended approach for production environments because it supports multi-factor authentication and centralised user management.

## FAQ

**What is the difference between a login and a user in Azure SQL?**

A login is a server-level identity that lets you connect to the SQL Server instance. A user is a database-level identity that is mapped to a login and defines what you can do inside a specific database.

**Can I use Microsoft Entra ID authentication for an application that runs on a virtual machine?**

Yes, you can use a managed identity assigned to the virtual machine, or a service principal, to authenticate without storing any credentials in the application code.

**How do I create a contained database user?**

Use CREATE USER with the WITH PASSWORD = 'password' clause inside a contained database. This creates a user that does not require a server-level login.

**What is the db_owner role allowed to do?**

Members of the db_owner role have full control over the database, including the ability to modify schema, add users, and delete data.

**Why would I use Microsoft Entra ID authentication instead of SQL authentication?**

Microsoft Entra ID authentication provides features like multi-factor authentication, conditional access policies, and centralised password management, making it more secure and easier to manage for organisations already using Microsoft 365.

**Can a user with db_datareader modify data?**

No, db_datareader only grants SELECT permission. To modify data, the user needs db_datawriter or explicit INSERT, UPDATE, DELETE permissions.

## Check your understanding

1. **You need to create a user who can connect to the server, manage security within a database, but not alter the database schema. Which database role should you assign?**

   Answer: db_securityadmin. This role allows the user to manage permissions and roles inside the database without being able to change tables or data.

2. **A company wants to move its Azure SQL Database to a different server quarterly. Which user type is most appropriate to simplify this process?**

   Answer: Contained database users, because they store identity information inside the database and do not require server-level logins, making the database portable.

3. **What T-SQL command creates a new user in the SalesDB database for an existing SQL login called SalesLogin?**

   Answer: USE SalesDB; CREATE USER SalesUser FOR LOGIN SalesLogin;

4. **A developer complains they can connect to the Azure SQL server but cannot see any databases. What is the most likely cause?**

   Answer: No database user has been created for the developer's login in the target database, or the user has not been granted any permissions or roles.

5. **Which authentication method should you choose if your organisation requires multi-factor authentication for all database access?**

   Answer: Microsoft Entra ID authentication, because it supports multi-factor authentication natively, while SQL authentication does not.

---

Interactive version with quiz and diagrams: https://courseiva.com/learn/dp-300/manage-identity-access-azure-sql