SQL provisioning on Azure with Bicep fails with timeout error - The resource write operation failed to complete successfully

Question

I have written IaC using bicep to provision a SQL server and a database using the code below.

deploy_sql.bicep

targetScope = 'subscription'


module createSQL './modules/sql/create-sql.bicep' = {
  name: 'CreateProvisionSQL'
  params:{
    server_name: 'sql-db-app-dev'
    admin_username: 'sqladministrator'
    admin_password: 'xxxxxxxxxxxx'
    db_name: 'my_db'
  }
  scope: resourceGroup('my-resource-group')
  dependsOn: [createRG]
}

var myoutput = createSQL.outputs.MyConnectionString

./modules/sql/create-sql.bicep

resource sqlServer 'Microsoft.Sql/servers@2021-11-01' = {
  name: 'sql-db-app-dev'
  location: resourceGroup().location
  // kind: 'v12.0'
  properties: {
    administratorLogin: 'administratorLogin'
    administratorLoginPassword: 'xxxxxxxxxxxx'
    administrators: {
      administratorType: 'ActiveDirectory'
      principalType: 'Group'
      login: 'myGroup'
      sid: 'xxxxxxxxxxxxxx'
      tenantId: tenant().tenantId
      azureADOnlyAuthentication: false
  }
}
}

resource serverName_sqlDBName 'Microsoft.Sql/servers/databases@2020-08-01-preview' = {
  parent: sqlServer
  name: 'sample_db'
  location: resourceGroup().location
  sku: {
    name: 'standard'
    tier: 'standard'
    capacity: 10
  }
  properties: {
    collation: 'SQL_Latin1_General_CP1_CS_AS'
  }
}
output MyConnectionString string = '${sqlServer.name}${environment().suffixes.sqlServerHostname},1433;'

For the deployment, I am deploying using the command below.

az deployment group create --name deploy_sql --template-file .\create-sql.bicep --resource-group my-resource-group --output jsonc

What happens is that the deployment runs for about 30 minutes which is rather unusual, then it will eventually timeout with the error below.

One thing to note is that I am constantly deleting the resource and recreating it again by deleting the resource group and the deployment in Azure.

{"status":"Failed","error":{"code":"DeploymentFailed","message":"At least one resource deployment operation failed. Please list deployment operations for details. Please see https://aka.ms/arm-deployment-operations for usage details.","details":[{"code":"Conflict","message":"{\r\n \"status\": \"Failed\",\r\n \"error\": {\r\n \"code\": \"ResourceDeploymentFailure\",\r\n \"message\": \"The resource write operation failed to complete successfully, because it reached terminal provisioning state 'Failed'.\",\r\n \"details\": [\r\n {\r\n \"code\": \"DeploymentFailed\",\r\n \"message\": \"At least one resource deployment operation failed. Please list deployment operations for details. Please see https://aka.ms/arm-deployment-operations for usage details.\",\r\n \"details\": [\r\n {\r\n \"code\": \"Conflict\",\r\n \"message\": \"{\\r\\n \\\"status\\\": \\\"Failed\\\",\\r\\n \\\"error\\\": {\\r\\n \\\"code\\\": \\\"ResourceDeploymentFailure\\\",\\r\\n \\\"message\\\": \\\"The resource write operation failed to complete successfully, because it reached terminal provisioning state 'Failed'.\\\",\\r\\n \\\"details\\\": [\\r\\n {\\r\\n \\\"code\\\": \\\"OperationTimedOut\\\",\\r\\n \\\"message\\\": \\\"The operation timed out and automatically rolled back. Please retry the operation.\\\"\\r\\n }\\r\\n ]\\r\\n }\\r\\n}\"\r\n }\r\n ]\r\n }\r\n ]\r\n }\r\n}"}]}}

Answer 1

Hi
MrFlinstone •,

Welcome to Microsoft Q&A forum and thanks for using Azure Services.

As I understand, you are unable to provision Azure SQL Database using Bicep.

Please check the documentation here which states in detail about creation of the same:

https://learn.microsoft.com/en-us/azure/azure-sql/database/single-database-create-bicep-quickstart?view=azuresql&tabs=CLI

Use commands as below in Azure CLI:

az group create --name exampleRG --location eastus

az deployment group create --resource-group exampleRG --template-file main.bicep --parameters administratorLogin=<admin-login>

Note:

Replace <admin-login> with the administrator username of the SQL logical server. You'll be prompted to enter administratorLoginPassword.

Issue might exist in the Microsoft.Sql/servers/administrators@2021-05-01-preview API.

Refer similar thread: https://learn.microsoft.com/en-us/answers/questions/723847/azure-sql-database-deployment-fails-with-bicep-set

Please retry after a day and see if this works as resources have been created multiple times which might cause provisioning issue.

Let us know your result so that we can further investigate.

Thank you.