Unable to build msql virtual machine from terraform, using azurerm_mssql_virtual_machine. Errorcode: CRPNotAllowedOperation

Question

I'm running azurerm_mssql_virtual_machine to build a SQL Server virtual machine from a custom imag. (Image configured with SQL Server 2016 prepare image).

This is the code that I am running:

  resource "azurerm_mssql_virtual_machine" "mssql_vm" {
  provider            = azurerm.spoke-subscription
  virtual_machine_id  = azurerm_windows_virtual_machine.sql_server.id
  sql_license_type                 = "PAYG"
  sql_connectivity_port            = "49535"
  sql_connectivity_update_username = var.sql_login
  sql_connectivity_update_password = var.sql_password
  sql_instance {
    collation = "Latin1_General_CI_AS"
  }
  assessment {
    enabled = true
    run_immediately = true
  }
  storage_configuration {
  disk_type             = "${var.disk_type}"
  storage_workload_type = "OLTP"
    data_settings {
      default_file_path = "F:\\DATA"
      luns              = [1]
    }
    log_settings {
      default_file_path = "G:\\LOGS"
      luns              = [2]                 
    }
    temp_db_settings {
      default_file_path = "K:\\TEMPDB"
      luns              = [3]                
    }
   }
   lifecycle {
    ignore_changes = [
      tags,
     #assessment[0].schedule
    ]
  }
   tags = {
      "application owner" = var.application_owner_tag
      "environment"       = var.environment_tag
      "department"        = var.department_tag
      "technicalcontact"  = var.technicalcontact_tag
      "application"       = var.application_tag
      "service"           = "SQL server"
    }
 }

I get this error:

performing CreateOrUpdate: sqlvirtualmachines.SqlVirtualMachinesClient#CreateOrUpdate: Failure sending request: StatusCode=0 --
Original Error: Code="CRPNotAllowedOperation" Message="Operation cannot be completed due to the following error: VM Extension with publisher 'Microsoft.SqlServer.Management' and type 'SqlIaaSAgent' does not support setting enableAutomaticUpgrade property to true on this subscription.

Steps I've taken to try and resolve:

1. Re-register SQL Server virtual machines to the Azure subscription
2. Turned off automatic upgrade on azurerm_windows_virtual_machine

Answer 1

I tried to reproduce the same in my environment:

Code:

resource "azurerm_mssql_virtual_machine" "example" {
  virtual_machine_id               = azurerm_windows_virtual_machine.example.id
  sql_license_type                 = "PAYG"
  r_services_enabled               = true
  sql_connectivity_port            = 1433
  sql_connectivity_type            = "PRIVATE"
  sql_connectivity_update_password = "xxx"
  sql_connectivity_update_username = "sqllogin"

  auto_patching {
    day_of_week                            = "Sunday"
    maintenance_window_duration_in_minutes = 60
    maintenance_window_starting_hour       = 2
  }
  
}


resource "azurerm_virtual_network" "example" {
  name                = "kavexample-network"
  address_space       = ["10.0.0.0/16"]
  location            = data.azurerm_resource_group.example.location
  resource_group_name = data.azurerm_resource_group.example.name
}

resource "azurerm_subnet" "example" {
  name                 = "internal"
  resource_group_name  = data.azurerm_resource_group.example.name
  virtual_network_name = azurerm_virtual_network.example.name
  address_prefixes     = ["10.0.2.0/24"]
}

resource "azurerm_network_interface" "example" {
  name                = "kavya-example-nic"
  location            = data.azurerm_resource_group.example.location
  resource_group_name = data.azurerm_resource_group.example.name

  ip_configuration {
    name                          = "internal"
    subnet_id                     = azurerm_subnet.example.id
    private_ip_address_allocation = "Dynamic"
  }
}

resource "azurerm_windows_virtual_machine" "example" {
  name                = "kavyaexamplemc"
  resource_group_name = data.azurerm_resource_group.example.name
  location            = data.azurerm_resource_group.example.location
  size                = "Standard_F2"
  admin_username      = "xxx"
  admin_password      = "xx"
  enable_automatic_updates   = true
  patch_mode = "Manual"
  hotpatching_enabled = true
  network_interface_ids = [
    azurerm_network_interface.example.id,
  ]

  os_disk {
    caching              = "ReadWrite"
    storage_account_type = "Standard_LRS"
  }

  source_image_reference {
    publisher = "MicrosoftWindowsServer"
    offer     = "WindowsServer"
    sku       = "2016-Datacenter"
    version   = "latest"
  }
}

Received same error:

│ Error: waiting for creation of Sql Virtual Machine (Sql Virtual Machine Name "kavyaexamplemc" / Resource Group "v-sakavya-Mindtree"): Code="CRPNotAllowedOperation" Message="Operation cannot be completed due to the following error: VM Extension with publisher 'Microsoft.SqlServer.Management' and type 'SqlIaaSAgent' does not support setting enableAutomaticUpgrade property to true on this subscription."

Even tried changing, but was still receving the same error again and again.

enable_automatic_updates   = false
      patch_mode = "Manual"
      hotpatching_enabled = false

Try deleting the vm resource completely and create a new one with changed settings .

Try using below code:

I tried setting enable_automatic_upgrades = false , azurerm_virtual_machine has this property .Make use of that. Also ,

Code:

resource "azurerm_virtual_network" "main" {
  name                = "kavyasarvnetwork"
  address_space       = ["10.0.0.0/16"]
  location            = data.azurerm_resource_group.example.location
  resource_group_name = data.azurerm_resource_group.example.name
}

resource "azurerm_subnet" "internal" {
  name                 = "internal"
  resource_group_name  = data.azurerm_resource_group.example.name
  virtual_network_name = azurerm_virtual_network.main.name
  address_prefixes     = ["10.0.2.0/24"]
}

resource "azurerm_network_interface" "main" {
  name                = "kavyasarnic"
  location            = data.azurerm_resource_group.example.location
  resource_group_name = data.azurerm_resource_group.example.name

  ip_configuration {
    name                          = "testconfiguration1"
    subnet_id                     = azurerm_subnet.internal.id
    private_ip_address_allocation = "Dynamic"
  }
}

resource "azurerm_virtual_machine" "example" {
  name                  = "kavyasarvm"
  location              = data.azurerm_resource_group.example.location
  resource_group_name   = data.azurerm_resource_group.example.name
  network_interface_ids = [azurerm_network_interface.main.id]
  vm_size               = "Standard_DS1_v2"
 
   storage_os_disk {
    name              = "kavyasar-OSDisk"
    caching           = "ReadOnly"
    create_option     = "FromImage"
    managed_disk_type = "Premium_LRS"
    os_type = "Windows"
  }

    storage_image_reference {
    publisher = "MicrosoftSQLServer"
    offer     = "SQL2017-WS2016"
    sku       = "SQLDEV"
    version   = "latest"
  }
  os_profile {
    computer_name  = "hostname"
    admin_username = "testadmin"
    admin_password = "Password1234!"
  }

  os_profile_windows_config {
    timezone                  = "Pacific Standard Time"
    provision_vm_agent        = true
    enable_automatic_upgrades = false
  }
  
  tags = {
    environment = "staging"
  }
}

resource "azurerm_mssql_virtual_machine" "example" {

  virtual_machine_id               = azurerm_virtual_machine.example.id
  sql_license_type                 = "PAYG"
  r_services_enabled               = true
  sql_connectivity_port            = 1433
  sql_connectivity_type            = "PRIVATE"
  sql_connectivity_update_password = "Password1234!"
  sql_connectivity_update_username = "sqllogin"
}

This seems to be the cause due to limitations: What is the SQL Server IaaS Agent extension? (Windows) - SQL Server on Azure VMs | Microsoft Learn

The SQL IaaS Agent extension only supports:

SQL Server VMs deployed through the Azure Resource Manager. SQL Server VMs deployed through the classic model are not supported.

SQL Server VMs deployed to the public or Azure Government cloud. Deployments to other private or government clouds are not supported.

Reference : azurerm_mssql_virtual_machine | Resources | hashicorp/azurerm | Terraform Registry