Before we dive in, let’s briefly discuss what Azure AD identities are. Primary types of identities in Azure AD include:
- User identities (used by people)
- Service principals (used by applications or services)
- Managed identities (automated identities to authenticate or access resources.)
These identities store properties, such as password details and permissions, defining their access to resources within Azure.
Creating Users in Azure AD
To establish a new user in Azure AD, follow these steps:
- Sign in to the Azure portal.
- On the left-hand side, select Azure Active Directory.
- In the new panel, under Manage, select Users.
- At the top of the new panel, select + New user.
Here, input the necessary details for the user. Once completed, hit ‘Create’ to complete the process. Azure AD will then create a new user according to the settings specified.
Integrating Azure AD Users with Azure SQL Database
Now that we have created a user in Azure AD, let’s discuss how to map this user to an Azure SQL Database.
To successfully integrate Azure AD user with Azure SQL Database, we need to:
- Enable the Azure AD admin for SQL Server
- Add the Azure AD user to the SQL Database
Enable Azure AD Admin for SQL Server
To make an Azure AD user as an Azure AD admin for SQL Server, follow these steps:
- Navigate to the Azure portal.
- On the left pane, select SQL Servers.
- On the Overview page, under settings, select Active Directory Admin.
- Select Set Admin and you will see a list of All Users. Select the user that you want to make an Azure AD admin for SQL Server.
- Hit the Select button to save the changes.
After enabling an Azure AD admin for SQL Server, the next step is to add the Azure AD users from the SQL Database.
Add Azure AD User to Azure SQL Database
- Connect to the Azure SQL database using SQL Server Management Studio (SSMS). Login with the Azure AD admin credentials that you just set in the previous steps.
- Run the below query, replacing <Azure_AD_User> with the user’s name or user’s email.
CREATE USER [
- To grant this user access to perform specific operations, assign them the necessary roles. For instance, you can add the user to the `db_datareader` role as illustrated below:
ALTER ROLE db_datareader ADD MEMBER [
And that’s it. You have successfully integrated an Azure AD identity with Azure SQL Database.
Remember, adding users and managing access through Azure AD is a crucial skill for the DP-300 Exam, and mastering the process will be instrumental to passing and to managing your Azure environment successfully.
For more advanced options, such as creating service principals, or using managed identities, please refer to Microsoft’s official Azure AD documentation.
Practice Test
True or False: The purpose of creating users from Azure AD identities is to provide a single user identity for resources and apps available within a tenant’s Azure AD.
- Answer: True
Explanation: Creating users from Azure AD identities allows employees to sign into multiple services and resources using the same credentials.
The Azure AD admin for Azure SQL Server can only be a person’s user identity.
- Answer: False
Explanation: Azure AD admin can be a user identity, a group, or a service principal.
Which of the following can be used to provide Azure services with access to an Azure SQL database?
- a) User Identity
- b) Group
- c) Application identity
- d) Service identity
Answer: a, b, c, d
Explanation: All can be utilized to provide access to Azure services to an Azure SQL database.
True or False: RBAC in Azure AD can be used to assign permissions to users, groups, and services to access resources in the Azure SQL database.
- Answer: True
Explanation: RBAC, or Role-Based Access Control, allows you to assign permissions to users, groups, and services.
Is it important to assign the least amount of privileges necessary to a user to perform their work in Azure AD?
- Answer: True
Explanation: This is known as the principle of least privilege, and it is important to follow for security reasons.
Can a Global Admin of the Tenant assign an Azure AD Admin to the Azure SQL server?
- a) Yes
- b) No
Answer: a) Yes
Explanation: A Global Admin of the Tenant can assign Azure AD Admin to the Azure SQL server in the Azure portal.
The Azure AD Admin of an Azure SQL Server can be a Group only.
- Answer: False
Explanation: The Azure AD Admin can be either a user identity, a group, a service principal, managed identity, or even a service object.
Azure RBAC is used to grant permissions to resources that are ___.
- a) At subscription level
- b) At management group level
- c) Stored in Azure Resource Manager
- d) All of the above
Answer: d) All of the above
Explanation: Azure RBAC roles can be assigned at different scopes for different levels of access.
True or False: Azure AD identity can only be assigned to a single user.
- Answer: False
Explanation: Azure AD identity can be assigned to a user, a group, a service principal, or a managed identity for Azure resources.
When should you use Azure AD authentication with Azure SQL?
- a) When you want to centrally manage identities of the database users.
- b) When you want to use integrated authentication from your application.
- c) When your identities are managed by Azure AD.
- d) All of the above.
Answer: d) All of the above.
Explanation: All these scenarios are good reasons to use Azure AD authentication with Azure SQL.
Azure AD supports authentication for a variety of user types including ___.
- a) Guest
- b) Member
- c) External
- d) All of the above
Answer: d) All of the above
Explanation: Azure AD supports authentication for all these user types.
Interview Questions
How can you create a user in Azure SQL Database from an Azure Active Directory identity?
You can create a user from an Azure AD identity using the “CREATE USER [username] FROM EXTERNAL PROVIDER” Transact-SQL command.
What permissions does a user need to be able to create Azure AD users in Azure SQL Database?
A user needs ALTER ANY USER permissions to create Azure AD users in Azure SQL Database.
How can you authenticate a user created from an Azure AD identity in Azure SQL Database?
Authentication of a user created from an Azure AD identity is done through token-based authentication. This requires an access token from Azure AD.
What is the first step to create Azure AD-based contained database users in Azure SQL Database?
The first step is to create a “contained database user” in the database mapped to the Azure AD identity.
Can users in an Azure SQL Database be granted server-level permissions?
No, users in Azure SQL Database can only be granted database-level permissions, not server-level permissions as it’s a PaaS offering.
What are the traditional SQL Server security principals available in Azure SQL Database?
The traditional SQL Server security principals available in Azure SQL Database are: SQL Server logins, database users, roles, and schemas.
How do you assign a role to a Azure SQL Database user?
You can assign a role to a Azure SQL Database user by using the sp_addrolemember stored procedure or the ALTER ROLE [role name] ADD MEMBER [user name] statement.
Can an Azure AD user connect to the master database?
Yes, an Azure AD admin user can connect to the master database. This is required to create other Azure AD users in Azure SQL Databases.
What are the three types of identities you can use in Azure?
The three types of identities are: System-assigned managed identity, User-assigned managed identity, and Service Principal.
What is Universal Authentication in Azure AD?
Universal Authentication is a method of authentication which allows multi-factor authentication, Azure AD Conditional Access, third-party SaaS apps and passwordless authentication.
How does Azure SQL Database handle password expiration and complexity policies?
Azure SQL Database defers to Azure AD for password policies. Azure AD handles password expiration and complexity issues.
What is the purpose of the Azure Active Directory admin role in Azure SQL Database?
The Azure AD admin role is a server-level role with permissions to manage Azure AD users and groups, manage administrator roles, and perform any operation in any database in the server.
How do you add an Azure AD admin user to your SQL Server?
To add an Azure AD admin user, go to the SQL Server’s settings in the Azure portal, find the “Active Directory Admin” section and select “Set admin”. You can then search for your Azure AD user and select it.
Can a user make themselves the Azure AD admin for Azure SQL Database using Transact-SQL?
No, setting the Azure AD admin for Azure SQL Database can only be done in the Azure portal, not via Transact-SQL.
Can a Azure AD group be used as the Azure SQL Database administrator?
Yes, an Azure AD group can be designated as the Azure SQL Database administrator. It is often a good idea to designate a group rather than an individual user as the administrator for resiliency.