Create users from Azure AD identities

In Azure SQL, you can create users from Azure AD identities by leveraging Azure Active Directory authentication. This feature enables you to authenticate users and manage their access to Azure SQL databases using Azure AD. In this article, we will walk through the step-by-step process of creating users with Azure AD identities.

Step 1: Set up Azure AD authentication for your Azure SQL server

To enable Azure AD authentication for your Azure SQL server, follow these steps:

1. Go to the Azure portal and navigate to your Azure SQL server.
2. Under the Security section, click on Azure Active Directory.
3. In the Azure Active Directory blade, click on the Set admin button.
4. In the Admin blade, select the Azure AD tenant that you want to use and click on Save.

Step 2: Create an Azure AD group

To create an Azure AD group, follow these steps:

1. Go to the Azure portal and navigate to your Azure AD tenant.
2. Under the Manage section, click on Groups.
3. Click on the New group button.
4. In the New blade, provide a name, group type, and description for the group.
5. Click on Create to create the group.

Step 3: Assign users to the Azure AD group

To assign users to the Azure AD group, follow these steps:

1. Go to the Azure portal and navigate to your Azure AD tenant.
2. Under the Manage section, click on Users.
3. Select the user you want to assign to the group.
4. In the User blade, click on Groups.
5. In the Group memberships blade, click on Add member.
6. Search for the previously created group and select it.
7. Click on Select to add the user to the group.

Step 4: Create a user in Azure SQL

To create a user in Azure SQL using Azure AD authentication, follow these steps:

1. Go to the Azure portal and navigate to your Azure SQL server.
2. Under the Security section, click on Active Directory admin.
3. In the Active Directory admin blade, click on Set admin to add an admin user.
4. Search for the Azure AD group you created earlier and select it.
5. Click on Select to add the group as an admin user.

Step 5: Grant access to the user

To grant access to the user, follow these steps:

1. Connect to your Azure SQL server using SQL Server Management Studio or any other database management tool.
2. Run the following query to grant the user access to a specific database:

CREATE USER [] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [];
ALTER ROLE db_datawriter ADD MEMBER [];

Replace with the name of the Azure AD user you want to grant access to.

Congratulations! You have successfully created a user from an Azure AD identity in Azure SQL. The user can now authenticate using their Azure AD credentials and access the specified database.

When using Azure AD authentication for users, you don’t need to manage passwords. The authentication process is securely handled through Azure AD, simplifying the management of user access to your Azure SQL databases.