Share via

How can I fully automate creating AAD users at an Azure SQL Database with bicep deployment scripts?

limond 6 Reputation points
2022-03-03T07:59:40.85+00:00

Hello,

I would like to automate the AAD user creation at a Azure SQL Database at infrastructure creation time using bicep.
I currently create an SQL server (Microsoft.Sql/servers), assign a UMI as administrator and use the same UMI to run a powershell deployment script (Microsoft.Resources/deploymentScripts) which is passed a custom user object from bicep to run "Invoke-Sqlcmd" to create AAD users by running "CREATE USER [$($_.name)] FROM EXTERNAL PROVIDER;". The Invoke-Sqlcmd is passed an access token from "(Get-AzAccessToken -ResourceUrl https://database.windows.net).Token"

The user is not created, instead an error is shown that the server's identity must have the "Directory Readers" role in AAD which makes sense.

My solution which I am not comfortable with is:

  1. Create a group "DirectoryReaders" in AAD, give it the role "Directory Readers" (requires a P1 license)
  2. Create a resource group "SQL-Deploymentmanager" untouched by the bicep deployment, create a UMI "SQL-Deploymentmanager" in the portal.
  3. Make the UMI "SQL-Deploymentmanager" owner and member of the "DirectoryReaders" group
  4. Create a bicep deploymentscript running "az ad group add" with the identity of UMI "SQL-Deploymentmanager" and add the identity of the SQL server to the group "DirectoryReaders"
  5. After that the user provisioning script can run without problems.

I dislike this approach as it requires Azure resources and AAD configuration before a bicep deployment can take place.

I would very much appreciate any help.

Azure SQL Database
Microsoft Entra ID
Microsoft Entra ID
A Microsoft Entra identity service that provides identity management and access control capabilities. Replaces Azure Active Directory.
20,634 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. AaronHughes 391 Reputation points
    2022-03-09T07:39:06.93+00:00

    I would suggest that you are mixing INFRA and Solution deployment here.

    Users at the DB level can be deployed using the SQL Project - you could stand up a group and use this to facilitate access to the DB for the specific group of users or you can store the user creation scripts as part of the users as part of the sql project

    https://www.mssqltips.com/sqlservertip/6557/azure-devops-ci-cd-using-github-repo-and-visual-studio-azure-sql-database-project/

    If you really want to push the users at point of INFRA deployment then you could use the PS commands within your Bicep CICD
    https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-service-principal-tutorial

    1 person found this answer helpful.
    0 comments
  2. Oury Ba-MSFT 18,021 Reputation points Microsoft Employee
    2022-03-07T18:56:04.537+00:00

    Hi @limond Thank you for posting your question on Microsoft Q&A forums.
    From my understanding you are trying to automate creating AAD users in Azure SQL using Bicep deployment.

    Unfortunately, there are some manual steps needed. Permissions can be lowered when using UMI.

    One UMI can be created and assigned to multiple servers in same tenant, to reduce manual intervention. (We recommend creating 1 UMI for each subscription if possible.)

    Instead of Directory.Reader permission, these can be used.
    • User.Read.All - allows access to Azure AD user information
    • GroupMember.Read.All – allows access to Azure AD group information
    • Application.Read.ALL – allows access to Azure AD service principal (applications) information

    User-assigned managed identity in Azure AD for Azure SQL - Azure SQL Database & Azure SQL Managed Instance | Microsoft Learn

    Regards,
    Oury