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
