How to Use Azure AD Admin Feature with Azure SQL Server

Introduction
As more and more organizations move their applications and data to the cloud, managing identities and access becomes increasingly important. Azure Active Directory (Azure AD) is a cloud-based identity and access management solution that allows you to manage user identities and access to applications and resources.
Azure SQL Server, is a cloud-based relational database service that provides a scalable and highly available database platform for your applications. In this blog post, I will show you how to use the Azure AD admin feature with Azure SQL Server, so you can manage access to your SQL Server resources more efficiently and securely.
Provision an SQL server
For this lab you need your an Azure Account & your own subscription. You also want to install the Azure CLI.
Open your command prompt and sign into Azure using the AZ CLI
az login
az account set -s <subscription-id>
az account show
This ensures you are set to the correct subscription. Next we will create a resource group for our demo:
az group create -n sqlDemo -l westeurope
Next we will provision the SQL server:
az sql server create `
--resource-group sqlDemo `
--name demoSQL<randon-number> `
--location westeurope `
--admin-user sqladmin01 `
--admin-password <your-password>
Next configure server-based firewall to allow us to connect (Note you can visit whatsmyip.org to find your public IP for this lab):
az sql server firewall-rule create `
--resource-group sqlDemo `
--server <same as previous command> `
-n AllowYourIp --start-ip-address <your-IP> --end-ip-address <your-IP>
And finally a database:
az sql db create `
--resource-group sqlDemo `
--server <same as before> `
--name dbdemo123 `
--edition GeneralPurpose `
--compute-model Serverless `
--family Gen5 `
--capacity 2
Use Azure Active Directory admin and contained database users
AAD admin
Azure SQL servers allows us to set a user or a group as an Azure Active Directory Admin, this is great because that means we can configure a security group and have administrators lifecycled managed through access reviews of the security group.
This means I can use SQL server management studio (SSMS) and connect using Azure Active Directory and enforce MFA to logon to the server instead of using an SA account with plain username/password combination.
With this account I can administer access to the databases using contained database users.
Contained database user
A contained database user is a user account that is created and managed within a specific database, rather than in the master database of a SQL Server instance. When you use a contained database user, you do not have to rely on the SQL Server instance for authentication and authorization, which can provide a number of benefits:
- Improved portability: With a contained database user, you can easily move the database to another SQL Server instance, since the user is self-contained within the database. This can be particularly useful if you need to move the database to a different environment, such as from development to production.
- Reduced security risks: Because a contained database user is limited to accessing only the database in which it was created, there is less risk of security breaches that could compromise the entire SQL Server instance.
- Simplified administration: With a contained database user, you do not have to manage user accounts at the SQL Server instance level. Instead, you can create, modify, and delete user accounts directly within the database. This can make it easier to manage user accounts and permissions, especially if you have multiple databases.
- Better performance: When you use a contained database user, SQL Server does not have to query the master database to authenticate and authorize the user. This can result in faster performance, especially in high-traffic environments.
I think best of all: Contained database users can be mapped to Azure Active Directory identities:
CREATE USER [user1@lindbergtech.com] FROM EXTERNAL PROVIDER;
CREATE USER [user2@lindbergtech.com] FROM EXTERNAL PROVIDER;
Run the above command when in the context of the database, not the master.
Activate Azure Active Directory admin function
Activate the function on your new SQL server by going to
- Search for SQL server
- Chose the one you deployed previously
- In the left pane select Azure Active Directory
- Select Set admin
- Search for a user or a group, click select
- Select save
And you're done! Do not forget to clean up your resources when done by deleting the resource-group we created:
az group delete -n sqlDemo
References

About the author
