Last week I spent quite some time troubleshooting and fixing the above mentioned error. To prevent this from happening in the future, I decided to document the findings and a possible solution here.
The initial situation
Let’s start with the initial situation. There is a Azure DevOps YAML pipeline that runs in a first stage terraform init and terraform plan and in a second stage (after approval of the generated plan) terraform init and terraform apply.
- task: TerraformCLI@1
displayName: Terraform plan
inputs:
command: plan
commandOptions: -var-file="./vars/${{parameters.backendName}}.${{parameters.environmentCode}}.tfvars" -replace="null_resource.sql_db_initialization" -replace azuread_application_password.aadapppwd -replace azuread_application_password.graphapiapppwd
environmentServiceName: ${{parameters.azureSubscription}}
workingDirectory: "$(System.DefaultWorkingDirectory)/deploy/iac"
publishPlanResults: "IaC-plan"
- task: TerraformCLI@1
displayName: Terraform apply
inputs:
command: apply
commandOptions: -auto-approve -var-file="./vars/${{parameters.backendName}}.${{parameters.environmentCode}}.tfvars" -replace="null_resource.sql_db_initialization" -replace azuread_application_password.aadapppwd -replace azuread_application_password.graphapiapppwd
environmentServiceName: ${{parameters.azureSubscription}}
workingDirectory: "$(System.DefaultWorkingDirectory)/deploy/iac"
runAzLogin: true
As you can see there is a null_resource with name sql_db_initialization which gets replaced on every execution. Besides a Azure MSSQL server, a MSSQL database and some Entra ID security groups (permission groups), the Infrastructure as Code (IaC) config also defines this null resource with a local-exec provisioner that executes a database initialization script.
resource "null_resource" "sql_db_initialization" {
provisioner "local-exec" {
command = ".\\Initialize-SqlDatabase.ps1 -ServerInstance \"tcp:${azurerm_mssql_server.sql.name}.database.windows.net,1433\" -Database ${azurerm_mssql_database.sqldb.name} -ReadGroupName ${format("pm-%s-%s-read", local.sql_srv_name, local.sql_db_name)} -ReadWriteGroupName ${format("pm-%s-%s-readwrite", local.sql_srv_name, local.sql_db_name)} -InsightsSchemaOperateGroupName ${format("pm-%s-%s-insights-schema-operate", local.sql_srv_name, local.sql_db_name)}"
interpreter = ["pwsh", "-Command"]
}
}
And below the database initialization script.
Initialize-SqlDatabase.ps1
param (
[string] $ServerInstance,
[string] $Database,
[string] $ReadGroupName,
[string] $ReadWriteGroupName,
[string] $InsightsSchemaOperateGroupName
)
Import-Module SqlServer
# get token from current az cli context
$access_token = (az account get-access-token --resource=https://database.windows.net --query accessToken --output tsv)
# query to grant permissions, etc.
$sql = "
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name='$ReadGroupName')
BEGIN
CREATE USER [$ReadGroupName] FROM EXTERNAL PROVIDER;
END
ALTER ROLE db_datareader ADD MEMBER [$ReadGroupName]
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name='$ReadWriteGroupName')
BEGIN
CREATE USER [$ReadWriteGroupName] FROM EXTERNAL PROVIDER;
END
ALTER ROLE db_datareader ADD MEMBER [$ReadWriteGroupName]
ALTER ROLE db_datawriter ADD MEMBER [$ReadWriteGroupName]
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name='$InsightsSchemaOperateGroupName')
BEGIN
CREATE USER [$InsightsSchemaOperateGroupName] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = insights
END
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name='insightsSchemaOperator' and type = 'R')
BEGIN
CREATE ROLE insightsSchemaOperator
END
GRANT SELECT ON SCHEMA::insights TO insightsSchemaOperator
GRANT INSERT ON SCHEMA::insights TO insightsSchemaOperator
GRANT ALTER ON SCHEMA::insights TO insightsSchemaOperator
ALTER ROLE insightsSchemaOperator ADD MEMBER [$InsightsSchemaOperateGroupName]
"
Invoke-SqlCmd -ServerInstance $ServerInstance -Database $Database -AccessToken $access_token -Query $sql
Versions
- Terraform:
1.10.4 hashicorp/null:3.2.3hashicorp/azurerm:3.117.0
The problem
Rather by chance, I detected the following error in the pipeline logs of terraform apply step – especially because the pipeline step didn’t fail.
null_resource.sql_db_initialization (local-exec): Invoke-Sqlcmd: D:\a\1\s\deploy\iac\Initialize-SqlDatabase.ps1:43 null_resource.sql_db_initialization (local-exec): Line | null_resource.sql_db_initialization (local-exec): 43 | Invoke-SqlCmd -ServerInstance $ServerInstance -Database $Database -Ac … null_resource.sql_db_initialization (local-exec): | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ null_resource.sql_db_initialization (local-exec): | Server identity does not have Azure Active Directory Readers permission. Please follow the steps here : null_resource.sql_db_initialization (local-exec): | https://docs.microsoft.com/azure/azure-sql/database/authentication-aad-service-principal Msg 37353, Level 16, null_resource.sql_db_initialization (local-exec): | State 1, Procedure , Line 27. null_resource.sql_db_initialization: Creation complete after 22s [id=1059739393600906732]
The insights
I did some research on this error on the Internet and came across the following sources.
IMPORTANT NOTE: all clients access the Azure SQL resource by using Microsoft Entra authentication!
- Enable service principals to create Microsoft Entra users
- Configure and manage Microsoft Entra authentication with Azure SQL
- Configure and manage Microsoft Entra authentication with Azure SQL – Microsoft Graph permissions
- Managed identities in Microsoft Entra for Azure SQL – Permissions
As stated in the article behind the first link in the list, the Azure MSSQL managed identity (user or system assigned managed identity) requires explicit permissions for creating logins.
When using applications to access Azure SQL, creating Microsoft Entra users and logins requires permissions that aren’t assigned to service principals or managed identities by default: the ability to read users, groups, and applications in a tenant from Microsoft Graph.
I wondered why I hadn’t recognized this error earlier, so I looked up when it first occurred. It started to occur when the script Initialize-SqlDatabase.ps1 was extended by the following statement.
CREATE USER [$InsightsSchemaOperateGroupName] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = insights
But why did all the CREATE USER statements before this one get applied successfully? I also found a plausible answer to this question. Because I applied the IaC config the first time (with the initial version of Initialize-SqlDatabase.ps1) manually with my user account (not an application) having Global Administrator activated through PIM.
The solution
The question arose how to fix this problem by following the principle of least privilege. As usual there were multiple iterations required until a working solution was found.
In a first step I tried to enable the system assigned managed identity of the Azure MSSQL Server using terraform.
resource "azurerm_mssql_server" "sql" {
...
identity {
type = "SystemAssigned"
}
}
Additionally, I assigned the following Microsoft Graph permissions to the system assigned managed identity.
data "azuread_application_published_app_ids" "well_known" {}
resource "azuread_service_principal" "msgraph" {
client_id = data.azuread_application_published_app_ids.well_known.result.MicrosoftGraph
use_existing = true
}
resource "azuread_app_role_assignment" "user-read-all" {
app_role_id = azuread_service_principal.msgraph.app_role_ids["User.Read.All"]
principal_object_id = azurerm_mssql_server.sql.identity[0].principal_id
resource_object_id = azuread_service_principal.msgraph.object_id
}
resource "azuread_app_role_assignment" "groupmember-read-all" {
app_role_id = azuread_service_principal.msgraph.app_role_ids["GroupMember.Read.All"]
principal_object_id = azurerm_mssql_server.sql.identity[0].principal_id
resource_object_id = azuread_service_principal.msgraph.object_id
}
resource "azuread_app_role_assignment" "application-read-all" {
app_role_id = azuread_service_principal.msgraph.app_role_ids["Application.Read.All"]
principal_object_id = azurerm_mssql_server.sql.identity[0].principal_id
resource_object_id = azuread_service_principal.msgraph.object_id
}
After these adjustments/changes, terraform plan failed with the following error.
╷ │ Error: Missing required argument │ │ with azuread_app_role_assignment.user-read-all, │ on main-sql.tf line 161, in resource "azuread_app_role_assignment" "user-read-all": │ 161: principal_object_id = azurerm_mssql_server.sql.identity[0].principal_id │ │ The argument "principal_object_id" is required, but no definition was │ found. ╵
Again some research on the Internet … according to this GitHub issue enabling the system assigned managed identity of Azure MSSQL Server using terraform does not yet work as expected. To not lose more time, I set the status of System assigned managed identity of Azure MSSQL server to On manually through the Azure portal.
After that the terraform apply step in the pipeline failed with another error.
╷
│ Error: Could not create app role assignment
│
│ with azuread_app_role_assignment.user-read-all,
│ on main-sql.tf line 159, in resource "azuread_app_role_assignment" "user-read-all":
│ 159: resource "azuread_app_role_assignment" "user-read-all" {
│
│ AppRoleAssignedToClient.BaseClient.Post(): unexpected status 403 with OData
│ error: Authorization_RequestDenied: Insufficient privileges to complete the
│ operation.
╵
To enable terraform to assign app roles, the Az DevOps service principal (application registration used by the corresponding Az DevOps service connection) requires Microsoft Entra built-in role Privileged Roles Administrator. It feels wrong to grant the Az DevOps service principal such a highly privileged role. On the other hand, if infrastructure management is done through Azure DevOps pipelines, high privileges are required. To mitigate the risk, this highly privileged role could also be assigned just temporarily.
Even after assigning this Microsoft Entra built-in role, the terraform apply step in the pipeline still failed with the Server identity does not have Azure Active Directory Readers permission error…
In my case I create a contained database user for a Microsoft Entra group. Therefore, the system assigned managed identity requires the Group.Read.All permission additionally to the ones listed here.
I finally came up with the following solution.
## Assign Microsoft Graph permissions to system assigned managed identity of Azure MSSQL Server to allow service principal lookup (required by deploy\iac\Initialize-SqlDatabase.ps1)
## For more details see https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-service-principal?view=azuresql#enable-service-principals-to-create-microsoft-entra-users
## If you experience the following error, set status of system assigned managed identity of Azure MSSQL Server manually to 'on' in the Azure portal
## │ Error: Missing required argument
## │
## │ with azuread_app_role_assignment.user-read-all,
## │ on main-sql.tf line 170, in resource "azuread_app_role_assignment" "user-read-all":
## │ 170: principal_object_id = azurerm_mssql_server.sql.identity[0].principal_id
## │
## │ The argument "principal_object_id" is required, but no definition was
## │ found.
data "azuread_application_published_app_ids" "well_known" {}
resource "azuread_service_principal" "msgraph" {
client_id = data.azuread_application_published_app_ids.well_known.result.MicrosoftGraph
use_existing = true
}
resource "azuread_app_role_assignment" "user-read-all" {
app_role_id = azuread_service_principal.msgraph.app_role_ids["User.Read.All"]
principal_object_id = azurerm_mssql_server.sql.identity[0].principal_id
resource_object_id = azuread_service_principal.msgraph.object_id
}
resource "azuread_app_role_assignment" "group-read-all" {
app_role_id = azuread_service_principal.msgraph.app_role_ids["Group.Read.All"]
principal_object_id = azurerm_mssql_server.sql.identity[0].principal_id
resource_object_id = azuread_service_principal.msgraph.object_id
}
resource "azuread_app_role_assignment" "groupmember-read-all" {
app_role_id = azuread_service_principal.msgraph.app_role_ids["GroupMember.Read.All"]
principal_object_id = azurerm_mssql_server.sql.identity[0].principal_id
resource_object_id = azuread_service_principal.msgraph.object_id
}
resource "azuread_app_role_assignment" "application-read-all" {
app_role_id = azuread_service_principal.msgraph.app_role_ids["Application.Read.All"]
principal_object_id = azurerm_mssql_server.sql.identity[0].principal_id
resource_object_id = azuread_service_principal.msgraph.object_id
}
Leave a Reply