How to Use Azure AD Admin Feature with Azure SQL Server

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:

  1. 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.
  2. 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.
  3. 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.
  4. 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

Create a single database - Azure SQL Database
Create a single database in Azure SQL Database using the Azure portal, PowerShell, or the Azure CLI.

About the author

About me
If you have landed on my page you will have already understood my passion for tech, but obviously there is more to life than that. Here I will try and outline a few of my other hobbies. Strength training I am a person who loves to move around and challenge