Powershell script to create security groups in Azure Active Directory to manage access to Azure SQL

Question

Below is a script that creates groups in Azure AD and then assigns SQL roles to the groups. The script has a dependency on ODBC 17 and sqlcmd utility version 15 and this makes it a bit hard to use in an Azure devops pipeline. I was wondering if someone knows any alternative powershell cmdlets that I can use run the queries in the script against SQL. I have tried the invoke-sqlcmd but ran into an issue trying to authenticate using an onmicrosoft account. when using the sql admin user that you create with the SQL database I can run the commands to set the permissions on the target database but are not able to run the sql query that creates the groups (FROM EXTERNAL PROVIDER) in the master database. So I am a little stuck. The Idea is the add the script to a deployment pipeline and have it automate the creation of AAD groups that will allow operations teams to manage user access to the database. Please help :-) <# .SYNOPSIS Creates AAD Groups that is used to control access to SQL Server and Database resources

            .DESCRIPTION
                Based on the SQL Server and Database supplied as a input the script will create the required AAD groups required for every Azure SQL Server


            .INPUTS
                sqlServerName: Name of the sql server (IAAS) or the sql server instance (PAAS)
                sqlDatabaseName: Name of the database
                custom_vars_envResourceGroupName : Resource group the sql server resides in
                sqlAdminUser: 
                sqlAdminPassword
                ProjectId
                EnvironmentId
            .OUTPUTS
                Names of the AAD groups that was created

            .NOTES
                Script assumes that it is running in a session that is already connected to Azure RM TODO: Test least privilage
                Script assumes that it is running in a session that is already connected to Azure RM with contributor RBAC rights on the resource group TODO: Test least privilage
                If you are running this manually you need to add the sqlAdmin user into the Server admim group**** TODO: Still need to get the this automated to used the Azure DevOps Service Principle.
                Dont try this with a microsoft account it needs a AAD account to work on the db authenication
                If the qlAdminPassword is passed using clear text dont user "$" or "&" or "/" as special characters in the password.
            .EXAMPLE
            $sqlAdminPasswordnotsecure = "lhfuhwe48234#E"
            $sqlAdminSecurePassword = ConvertTo-SecureString $sqlAdminPasswordnotsecure -AsPlainText -Force
            #.\sqlAADGroupsCreator.ps1 `
                -custom_vars_envResourceGroupName "<Resource Group>" 
                -sqlServerName "<Servername>" 
                -sqlAdminUser "<eg sqlAdminUser@companydirectory.onmicrosoft.com>" 
                -sqlAdminSecurePassword "<complex password>" 
                -sqlDatabaseName "<database name>"
                -ProjectId "<Name of project>" 
                -EnvironmentId "<environment>"
            #>

            #region parameters
            Param(
                [Parameter(Mandatory = $True)][string]$custom_vars_envResourceGroupName,
                [Parameter(Mandatory = $True)][string]$sqlServerName,
                [Parameter(Mandatory = $True)][string]$sqlAdminUser,
                [Parameter(Mandatory = $True)][securestring]$sqlAdminSecurePassword,
                [Parameter(Mandatory = $True)][string]$sqlDatabaseName,
                [Parameter(Mandatory = $True)][string]$ProjectId,
                [Parameter(Mandatory = $True)][string]$EnvironmentId
            )
            $ErrorActionPreference = "Stop"
            #endregion parameters
            #region secureStringConversion
            #Convert secure string back to plain text to be used in SQL connection 
            function Get-PlainText() {
                [CmdletBinding()]
                param
                (
                    [parameter(Mandatory = $true)]
                    [System.Security.SecureString]$SecureString
                )
                BEGIN { }
                PROCESS {
                    $bstr = [Runtime.InteropServices.Marshal]::SecureStringToBSTR($SecureString);

                    try {
                        return [Runtime.InteropServices.Marshal]::PtrToStringBSTR($bstr);
                    }
                    finally {
                        [Runtime.InteropServices.Marshal]::FreeBSTR($bstr);
                    }
                }
                END { }
            }

            #Call The function
            $sqlAdminPassword = get-plaintext -SecureString $sqlAdminSecurePassword 
            #endregion secureStringConversion
            #region manualParameter
            <#Variables that can be used during testing or running manual
            $custom_vars_envResourceGroupName   = "MATA-APP-0-0-COMMON"
            $sqlServerName = "<server name>"
            $sqlAdminUser = "<SqlAdmin@educationtestgovtnz.onmicrosoft.com>"
            $sqlAdminPassword = "<complex password>"
            $sqlDatabaseName = "<database name>"
            $EnvironmentId = "<environment variable"
            $ProjectId ="<application name or abreviation>"
            #>
            #endregion manualParameter
            #region aadGroupsCreation. 
            #Build up database server Url 
            $sqlServerURL = $sqlServerName + ".database.windows.net"
            ###Create AAD Groups based on resource group name
            $sqlAdminGroupName = $sqlReadGroupName = $sqlDbContributorGroupName = $sqlDbReadGroupName = ""
            $sqlAdminGroupName = "AL SQL " + ($ProjectId.ToUpper()) + " " + ($EnvironmentId.ToUpper()) + " Admin"
            $sqlReadGroupName = "AL SQL " + ($ProjectId.ToUpper()) + " " + ($EnvironmentId.ToUpper()) + " Read" 
            $sqlDbContributorGroupName = "AL DB " + ($sqlServerName.ToUpper()) + " " + ($sqlDatabaseName.ToUpper()) + " Contributor" 
            $sqlDbReadGroupName = "AL DB " + ($sqlServerName.ToUpper()) + " " + ($sqlDatabaseName.ToUpper()) + " Read" 


            ###Create the Groups in Azure AD if they dont exist then assign access roles to Resource Group
            #SQL LEVEL ADMIN And DB OWNER#
            #Create new group for Owner
            if ($sqlAdminGroupName -ne ((Get-AzureRmADGroup -SearchString $sqlAdminGroupName).displayname)) {
                #$slqAdminGroupDiscription = "Server Admin and database owner access to "+ $sqlServerName
                New-AzureRmADGroup `
                    -DisplayName $sqlAdminGroupName `
                    -MailNickName "NotSet"
            }

            #Update group membership of sqlAdmin group
            $groupUser = Get-AzureRmADGroupMember -GroupDisplayName $sqlAdminGroupName
            if ($sqlAdminUser -ne ($groupUser.UserPrincipalName)) {
                Add-AzureRmADGroupMember -MemberUserPrincipalName $sqlAdminUser -TargetGroupDisplayName $sqlAdminGroupName
            }
            #DB LEVEL CONTRIBUTOR###
            #Create new group for Contributor
            if ($sqlDbContributorGroupName -ne ((Get-AzureRmADGroup -SearchString $sqlDbContributorGroupName).displayname)) {
                #$sqlContributorGroupDiscription = "Contributor access to "+ $sqlDatabase
                New-AzureRmADGroup `
                    -DisplayName $sqlDbContributorGroupName `
                    -MailNickName "NotSet"
            }

            #SQL LEVEL READER###
            #Create new group for ReadOnly
            if ($sqlReadGroupName -ne ((Get-AzureRmADGroup -SearchString $sqlReadGroupName).displayname)) {
                #$RGReadOnlygroupDiscription = "Read Only access to "+ $sqlDatabase
                New-AzureRmADGroup `
                    -DisplayName $sqlReadGroupName `
                    -MailNickName "NotSet"
            }

            #DB LEVEL READER###
            #Create new group for ReadOnly
            if ($sqlDbReadGroupName -ne ((Get-AzureRmADGroup -SearchString $sqlDbReadGroupName).displayname)) {
                #$RGReadOnlygroupDiscription = "Read Only access to "+ $sqlDatabase
                New-AzureRmADGroup `
                    -DisplayName $sqlDbReadGroupName `
                    -MailNickName "NotSet"
            }
            #endregion aadGroupsCreation.
            #region setDbPermissions
            #Enable Azure AD Administrator on the SQL server (SQL / AAD Integration)
            $sqlAdminGroupNameId = (Get-AzureRmADGroup -DisplayName $sqlAdminGroupName).Id
            Set-AzureRmSqlServerActiveDirectoryAdministrator -ResourceGroupName  $custom_vars_envResourceGroupName  -ServerName $sqlServerName -DisplayName $sqlAdminGroupName -ObjectId $sqlAdminGroupNameID

            #SQL Query to create the AAD groups in SQL master and map the appropriate roles to the AAD Groups
            $createSQLUsersQuery = ""
            $createSQLUsersQuery = @" 
            CREATE USER [$sqlDbContributorGroupName] FROM EXTERNAL PROVIDER;
            CREATE USER [$sqlReadGroupName] FROM EXTERNAL PROVIDER;
            CREATE USER [$sqlDbReadGroupName] FROM EXTERNAL PROVIDER;
            "@
            #Connect to SQL sever and configure the SQL Sever level logins
            Sqlcmd -S $sqlServerURL -d master -U $sqlAdminUser -P $sqlAdminPassword -G -Q $createSQLUsersQuery

            #SQL Query to create the AAD groups in targeted sqldatabase
            $createSQLDBUsersQuery = ""
            $createSQLDBUsersQuery = @"
            CREATE USER [$sqlAdminGroupName] FROM EXTERNAL PROVIDER;
            CREATE USER [$sqlDbContributorGroupName] FROM EXTERNAL PROVIDER;
            CREATE USER [$sqlReadGroupName] FROM EXTERNAL PROVIDER;
            CREATE USER [$sqlDbReadGroupName] FROM EXTERNAL PROVIDER;
            "@
            #Connect to SQL sever and configure the SQL database level logins
            Sqlcmd -S $sqlServerURL -d $sqlDatabaseName -U $sqlAdminUser -P $sqlAdminPassword -G -Q $createSQLDBUsersQuery

            #SQL Query to set the database level permissions in targeted sqldatabase
            $setSQLDBPermissionsQuery = ""
            $setSQLDBPermissionsQuery = @"
            ALTER ROLE db_owner ADD MEMBER [$sqlAdminGroupName]
            ALTER ROLE db_datareader ADD MEMBER [$sqlReadGroupName]
            ALTER ROLE db_datawriter ADD MEMBER [$sqlDbContributorGroupName]
            ALTER ROLE db_datareader ADD MEMBER [$sqlDbContributorGroupName]
            ALTER ROLE db_datareader ADD MEMBER [$sqlDbReadGroupName]
            "@
            #Connect to SQL sever and configure the SQL database level permissions
            Sqlcmd -S $sqlServerURL -d $sqlDatabaseName -U $sqlAdminUser -P $sqlAdminPassword -G -Q $setSQLDBPermissionsQuery
            #endregion setDbPermissions

Answer 1

I think your issue is that you are attempting to run the following T-SQL as the SQL Admin and not the AD Admin that has been configured for your Azure SQL Database instance.

To create an Azure AD-based contained database user (other than the server administrator that owns the database), connect to the database with an Azure AD identity, as a user with at least the ALTER ANY USER permission. Then use the following Transact-SQL syntax:

CREATE USER <Azure_AD_principal_name> FROM EXTERNAL PROVIDER;

Please see this document as it is the primary doc for configuring Azure AD for Azure SQL Database (Single Instance/Elastic Pools), Managed Instance, and Data Warehouse.

As for PowerShell cmdlets, please be aware that AzureRM.sql (supported through 2020) is being deprecated and Az.sql is the new module. More information about the Azure CLI (Link).

For automation scenarios, you need to create credentials from a user name and secure string:

$passwd = ConvertTo-SecureString use a secure password here -AsPlainText -Force $pscredential = New-Object System.Management.Automation.PSCredential('service principal name/id', $passwd) Connect-AzAccount -ServicePrincipal -Credential $pscredential -TenantId $tenantId

$params = @{ 'Query' = 'CREATE USER FROM EXTERNAL PROVIDER' 'ConnectionString' = 'Data Source=MYSERVER;Initial Catalog=MyDatabase;Integrated Security=True' }

Invoke-Sqlcmd @params