Terraform-provider-azurerm: mssql_server: breaking change in the azure api

[TPPWC]

Community Note

• Please vote on this issue by adding a 👍 reaction to the original issue to help the community and maintainers prioritize this request
• Please do not leave "+1" or "me too" comments, they generate extra noise for issue followers and do not help prioritize the request
• If you are interested in working on this issue or have submitted a pull request, please leave a comment

Terraform (and AzureRM Provider) Version

azurerm = 2.31.1

Affected Resource(s)

• azurerm_mssql_server

Terraform Configuration Files

resource "azurerm_mssql_server" "sqlsrv" {
  name                         = "sql${var.instance}${lower(var.app_shortname)}${lower(var.app_env)}${var.postfixid}"
  resource_group_name          = var.resource_group_name
  location                     = var.location
  version                      = "12.0"
  administrator_login          = random_string.sql-admin.result
  administrator_login_password = random_string.sql-admin-password.result
}

Debug Output

Panic Output

Expected Behavior

No extended policy is set in the resource block, so it should not be recognized at all.

Actual Behavior

Error regarding extended policy:

Error: issuing create/update request for SQL Server "sqlx1txxlxbdevxx312" Blob Auditing Policies(Resource Group "rgxxxx"): sql.ExtendedServerBlobAuditingPoliciesClient#CreateOrUpdate: Failure sending request: StatusCode=400 -- Original Error: Code="DataSecurityInvalidUserSuppliedParameter" Message="Invalid parameter 'storageEndpoint'. Value should be a blob storage endpoint (e.g. https://MyAccount.blob.core.windows.net)."

Steps to Reproduce

Just deploy a sql server with sql database using the azurerm_mssql provider

1. terraform apply

Important Factoids

References

• #0000

[JiriKovar]

We have the same problem ever since midnight CEST.
What we know:

• It started without any changes or commits to our IaC repo or CI/CD pipelines
• It affects West EU
• It does NOT affect East US (yet)

Based on these information it appears to be a problem in Azure API & we've opened an issue with MS Support about this.

[TPPWC]

Ok, thx for the update!

[JiriKovar]

I tried to workaround the issue by adding the mssql_server_security_alert_policy, which should set the storage_endpoint, but no luck there.

[mama-wk]

Same here, terraform deployments are broken atm. Yesterday it worked with azurerm v2.30.0, today not anymore, also not with v2.32.0.
I tried to add azurerm_mssql_server_extended_auditing_policy but with no luck either. Sample code here:

provider "azurerm" {
  features {}
  environment      = "public"
}
resource "random_password" "password" {
  length            = 16
  min_numeric       = 1
  min_special       = 1
  min_upper         = 1
  special           = true
  override_special  = "!.-_%@[]"
}

resource "azurerm_resource_group" "rg" {
  name     = "sql-terraform-test-environment"
  location = "West Europe"
}

resource "azurerm_storage_account" "store" {
  name                     = "apptestsqlstorage"
  resource_group_name      = azurerm_resource_group.rg.name
  location                 = azurerm_resource_group.rg.location
  account_kind             = "BlobStorage"
  account_tier             = "Standard"
  account_replication_type = "LRS"
}

resource "azurerm_mssql_server" "sql" {
  name                         = "sql-server"
  resource_group_name          = azurerm_resource_group.rg.name
  location                     = azurerm_resource_group.rg.location
  version                      = "12.0"
  administrator_login          = "admin123!"
  administrator_login_password = random_password.password.result

  tags = {
    environment = "test"
  }
}

resource "azurerm_mssql_server_extended_auditing_policy" "pol" {
  server_id                               = azurerm_mssql_server.sql.id
  storage_endpoint                        = azurerm_storage_account.store.primary_blob_endpoint
  storage_account_access_key_is_secondary = false
  retention_in_days                       = 7
}

This results in the following error:

Error: issuing create/update request for SQL Server "sql-server" Blob Auditing Policies(Resource Group "sql-terraform-test-environment"): sql.ExtendedServerBlobAuditingPoliciesClient#CreateOrUpdate: Failure sending request: StatusCode=400 -- Original Error: Code="DataSecurityInvalidUserSuppliedParameter" Message="Invalid parameter 'storageEndpoint'. Value should be a blob storage endpoint (e.g. https://MyAccount.blob.core.windows.net)."

[JiriKovar]

I had a call with MS Support, they are engaging the conversation with the API team.

However the interesting thing I noticed is that the REST API endpoint for SQL Server create/update that I believe Terraform is using under the hood, does not have any 'storageEndpoint' property. However the REST API endpoint for Server Security Alert Policies does. Can someone check whether terraform isn't using this endpoint for some unknown reason while creating the 'azurerm_sql_server' resource without 'extended_auditing_policy' specified? Because that would explain a lot.

[JiriKovar]

My final educated guess is that azurerm_sql_server resource calls the Azure API in a deprecated way and a breaking change removing the compatibility has been made and released to the West EU datacenter.

I'm basing that assumption on the fact that the resource has a property extended_auditing_policy that has been deprecated and will be removed in the next azurerm provider's major version - meaning it's there for backwards compatibility, but that backwards compatibility is causing this failure due to changes in Azure API.

[ddarwent]

I'm seeing the same. The SQL Server actually gets created, I edited the state file to remove the "status": "tainted", line from the "azurerm_mssql_server" resource. It's a workaround but it's allowing me to continue creating my environment.

[TPPWC]

Well this won't work for us, since we are using TF Enterprise and it's not that easy to edit the state file :(

[tombuildsstuff]

👋 hey folks

Having taken a look into this unfortunately this is a breaking change/bug in the Azure API - I've opened https://github.com/Azure/azure-rest-api-specs/issues/11271 to track this.

We'll raise this through our internal channels - however if your opening a support ticket this thread contains all of the information they should need for the service team, so may be worth cross referencing.

Thanks!

[satano]

I'm seeing the same. The SQL Server actually gets created, I edited the state file to remove the "status": "tainted", line from the "azurerm_mssql_server" resource. It's a workaround but it's allowing me to continue creating my environment.

Thanks @ddarwent this helped us. Just needed to make it twice, because after SQL Server itself was OK, the same error happened again on the databases (azurerm_sql_database).

[marianbendik]

@satano How did you please proceed? Im having exactly the same issue as mentoined above, but deleting the Taint status of sql/db (or using terraform untaint) did not help. I am still getting error message from the API, and deployment fails.

[satano]

@marianbendik We have Terraform state stored in container in Azure storage account. So I just edit the state file. Editing directly in browser is not very comfort, so I just copy the file content into editor, search for "status": "tainted" and delete that line. Copy changed content back to file in Azure and save it. That's all. When we run our pipeline (it runs terraform apply), it works. But as I wrote, if fails with the same error, but not for SQL server, but for the SQL databases now. Then I just repeat the workaround - edit the file again, delete all the "status": "tainted" lines (we have 1 server, but several databases) and save it back. Another pipeline run is OK and our infrastructure is created.

[maciejgwizdala]

I just had the same issue. terraform untaint on Azure SQL DB resource did the trick.

[ElkRom]

Who knows, when fix will be deployed?

[guillelb]

Any news?. It doesn't work on WestEurope and azurerm v2.32.0

Error issuing create/update request for SQL Server "xxx-sqlserver" Blob Auditing Policies(Resource Group "xxx"): sql.ExtendedServerBlobAuditingPoliciesClient#CreateOrUpdate: Failure sending request: StatusCode=400 -- Original Error: Code="DataSecurityInvalidUserSuppliedParameter" Message="Invalid parameter 'storageEndpoint'. Value should be a blob storage endpoint

[tombuildsstuff]

👋

We've raised this issue both via a High Priority support ticket and an ICM Ticket internally within Microsoft but are still waiting for the Service Team to respond here unfortunately - we've also confirmed this is present in multiple regions, so unfortunately this requires the Service Team's attention to fix this.

Thanks!

[tombuildsstuff]

Latest update from our side: https://github.com/Azure/azure-rest-api-specs/issues/11271#issuecomment-712308813

[Makae]

I needed to "terraform untaint" at least twice for the deployment to work.

[dlm69]

My workaround is to remove the azurerm_mssql_database_extended_auditing_policy or azurerm_mssql_server_extended_auditing_policy and replacing with the old (soon to be depracated) extended_auditing_policy block within azurerm_mssql_database or azurerm_mssql_server. It works for now.

[jason-johnson]

@dlm69 Would you mind sharing what you put in that policy? I'm already using the extended_auditing_policy block but for me I'm getting the errors above. The only thing in my extended_auditing_policy block is storage_account_access_key and storage_endpoint.

[marianbendik]

@dlm69 Thank you, this worked for me.

@jason-johnson Below i share working configuration with the deprecated policy block.

resource "azurerm_sql_server" "sqlserver" {
  name                         = sqlserver
  resource_group_name          = var.rg
  location                     = var.location
  version                      = "12.0"
  administrator_login          = var.sql_admin
  administrator_login_password = random_password.sql.result

    extended_auditing_policy {
    storage_endpoint                        = azurerm_storage_account.sql.primary_blob_endpoint
    storage_account_access_key              = azurerm_storage_account.sql.primary_access_key
    storage_account_access_key_is_secondary = true
    retention_in_days                       = 6
  }
}

resource "azurerm_sql_database" "proddb" {
  name                         = sqldb
  resource_group_name          = var.rg
  location                     = var.location
  server_name         = azurerm_sql_server.proddb.name
      extended_auditing_policy {
    storage_endpoint                        = azurerm_storage_account.sql.primary_blob_endpoint
    storage_account_access_key              = azurerm_storage_account.sql.primary_access_key
    storage_account_access_key_is_secondary = true
    retention_in_days                       = 6
  }
}

[jason-johnson]

@marianbendik Thanks. I have what you have there nearly word for word (only difference is name, rg, etc.) but I still get the same error as the bug report. What version of terraform/azurerm are you using? Could that be making a difference?

[marianbendik]

@jason-johnson

❯ tf version
Terraform v0.13.4
+ provider registry.terraform.io/hashicorp/azurerm v2.32.0

[dlm69]

@jason-johnson - I am using the same as @marianbendik - However I have also tested it with other azurerm provider versions inc. 2.31.1, 2.31.0, 2.30.0.

[jason-johnson]

Ok, thanks for the help. I bumped the version (we were on 0.13.0) but I also noticed that we didn't have the extended_auditing_policy block for the database declaration, only the server, so I put that in and now everything is working.

[Timovzl]

[...] we didn't have the extended_auditing_policy block for the database declaration, only the server, so I put that in and now everything is working.

@jason-johnson Doesn't that mean that you went from having _no_ extended auditing policy to actually _having_ one, i.e. an unintended change just to get the deployment working again? Or is there a way to specify an extended_auditing_policy block that still results in having _no_ extended auditing policy?

[dlm69]

@Timovzl - We went from having no extended auditing to having to include that block for some resources. I just put the retention
value down to 1 but I haven't yet tried to put in a non existant storage endpoint value to see if that's all that is needed as a workaround. Give it a go.

[tombuildsstuff]

@Timovzl at this time the only workaround would be to specify an Extended Auditing Policy as suggested above.

Whilst the Service Team are working on reverting the bug on their end - I've been informed that this regression will first roll out to all regions, before the hotfix is then rolled out - rather than being reverted. Unfortunately that's the path they've opted to take here, so that means existing versions of the Provider will be broken until the API fix is rolled out.

From Terraform's perspective, I'm testing a branch at the moment which adds a workaround for this issue - which we're intending to roll out in tomorrow's release. It's unfortunate the Service Team are rolling this forward rather than reverting and rolling out the hotfix - which would automatically fix all clients, unfortunately the Service Team have opted to take a different path here, and so shipping a fix in tomorrow's release of the Azure Provider is the next best thing we can do here.

Thanks!

[Timovzl]

@tombuildsstuff Thanks for the clarification. The quick fix on Terraform's end is much appreciated! I'll be trying out the new version when it becomes available.

[tombuildsstuff]

👋

I've just opened https://github.com/terraform-providers/terraform-provider-azurerm/pull/8975 which introduces a workaround for this issue, which'll go out in tomorrow's release (v2.33.0) - we're running the regression tests at the moment, however based on what I can see (from the older sql, rather than mssql resources which are still running) this should be fine 👍

Apologies for the delay here, we were hoping the Service Team would revert the breaking change (and fix all users) - but this PR should workaround this issue for users who can upgrade to the latest version of the Azure Provider.

Thanks!

[kamruldevops]

thanks @tombuildsstuff it was really helpful.
We are still using v2.2.0 :( and sql
We ended up creating those missing components using API manually so that terraform can bypass the resource creation or update :( ... thus we can still survive for another 1-2 weeks

[JiriKovar]

FYI MS deployed the breaking change to the East US DC today.

[tombuildsstuff]

👋🏻

We've just released v2.33 of the Azure Provider, which includes a workaround for this issue. You can upgrade to v2.33 of the Azure Provider by updating the version number in your Terraform Configuration.

For Terraform 0.13+ that's:

terraform {
    required_providers {
        azurerm = {
            source = "hashicorp/azurerm"
            version = "= 2.33.0"
        }
    }
}

or on Terraform 0.12:

provider "azurerm" {
  version = "=2.33.0"
  features {}
}

At which point running terraform init -upgrade should download the latest version of the Azure Provider.

From a technical level this workaround sets the field isAzureMonitorTargetEnabled to true - since this is a workaround, this field is hard-coded rather than user-configurable at this point in time (albeit we can look to expose this in future if required). Longer-term once the bugfix deployment of the Azure API has been rolled out to all regions, older versions of the Azure Provider should working again - and we'll look to revert this workaround (likely towards the end of the year).

Whilst it's unfortunate that the Service Team have opted to roll the breaking change out to all regions, before deploying the bug fix - as opposed to rolling back (which means that it could be another 1-2 weeks until older versions of the Azure Provider work) - based on the Acceptance Tests, we believe this should fix this issue for users who can upgrade to the latest version of the Azure Provider.

Thanks!

[jason-johnson]

@Timovzl

@jason-johnson Doesn't that mean that you went from having _no_ extended auditing policy to actually _having_ one, i.e. an unintended change just to get the deployment working again? Or is there a way to specify an extended_auditing_policy block that still results in having _no_ extended auditing policy?

Indeed. I only had the extended auditing policy for the server itself, not the databases. Adding the block for the databases seems to have fixed the issue.

[Timovzl]

We've just released v2.33 of the Azure Provider, which includes a workaround for this issue.

Works for me. Thanks!

[tombuildsstuff]

👋

Having spoken with Azure Support, it appears that the fix for this in the Azure API has been rolled out to the West Europe region - as such older versions of the Azure Provider should now be available to use in that region. Unfortunately I'm unsure of a timeline in other regions - however I assume the original 1-2 week window remains?

Thanks!

[JiriKovar]

I can confirm that the change in Azure API was released to West Europe and it works with azurerm provider 2.32.0 , but it was not yet rolled out to East US for example.

[bramvdklinkenberg]

still having this issue in east us.

[brandonmbanks]

@tombuildsstuff I'm still experiencing this issue even with azurerm 2.33.0 and running terraform init -upgrade

provider "azurerm" {
  version = ">=2.33.0"
  features {}
}

Code="DataSecurityInvalidUserSuppliedParameter" Message="Invalid parameter 'storageEndpoint'. Value should be a blob storage endpoint

Resources are in eastus2. Is this expected?

[randywallace]

yep, east us is still busted.

[roshanp85]

Do we know, if we have a possible ETA, targeted for eastus region ?

[tombuildsstuff]

@roshanp85 the last two releases of the Azure Provider have included a fix for this, so you can upgrade and this'll be available in EastUS: https://github.com/terraform-providers/terraform-provider-azurerm/blob/master/CHANGELOG.md#2330-october-22-2020

[vi7us]

It looks like issue is back. I'm having same error in westeurope, should this be reopened @tombuildsstuff? I'm using azurerm_mssql_database resource. It looks like azurerm_sql_database works.

[ThorstenHans]

Yes, I can also confirm that the issue is back for westeurope in combination with azurerm_mssql_database

currently using azurerm in 2.35.0

[SemMulder]

I can confirm that I was also still experiencing issues as of two days ago.

[vi7us]

To reproduce bug, i encounter a bit strange behavior: When component is created for first time it works. But when deployment is run again (no update or change) it fails. I can provide more details if needed.

[virtualbubble]

I am experiencing this issue in North and West Europe with the following versions of Terraform core and the provider.

• Terraform v0.13.5
• provider registry.terraform.io/hashicorp/azurerm v2.33.0

[tombuildsstuff]

@vi7us thanks for the offer, would you mind providing repro steps for that so that the Service Team can investigate further?

[vi7us]

@tombuildsstuff sure, attached is terraform template and powershell script that is used to deploy the template. If you need any further clarification, let me know. Thank you for checking!
tf_sql_logging_issue.zip

[hashibot[bot]]

I'm going to lock this issue because it has been closed for _30 days_ ⏳. This helps our maintainers find and focus on the active issues.

If you feel this issue should be reopened, we encourage creating a new issue linking back to this one for added context. If you feel I made an error 🤖 🙉 , please reach out to my human friends 👉 [email protected]. Thanks!

[tombuildsstuff]

Unlocking this issue so that the Service Team can post an update

[dprateek1991]

Same issues here. It seems to impacting entire Terraform Resource for MSSQL Server and Database. I'm getting errors while setting Audit Policy when Storage Account is behind a VNet. All permissions and things are done right, still getting errors.

Azure Region - West Europe
Terraform Provider - version = ">=2.33.0"

I'm getting multiple errors like -

1. Add SQL Firewall Rule

resource "azurerm_sql_firewall_rule" "sql_server_firewall_rule_azure_services" {
name = "Allow Azure Access"
resource_group_name = var.resource_group_name
server_name = azurerm_mssql_server.sql_server.name
start_ip_address = "0.0.0.0"
end_ip_address = "0.0.0.0"
depends_on = [azurerm_mssql_server.sql_server]
}

Error: Error creating SQL Firewall Rule Allow Azure Access (Resource Group devsolzone, Server dev-solzone-sqlserver-munfsinb): sql.FirewallRulesClient#CreateOrUpdate: Failure responding to request: StatusCode=400 -- Original Error: json: cannot unmarshal array into Go struct field serviceError2.details of type map[string]interface {}

2. Audit Policy errors for both SQL Server and SQL DB

Error: waiting for creation of MsSql Server "dev-solzone-sqlserver-munfsinb" Extended Auditing Policy (Resource Group "devsolzone"): Code="BlobAuditingInsufficientStorageAccountPermissions" Message="Insufficient read or write permissions on storage account 'devsolzonesqlsamunfsinb'. "

on ../../../modules/azure-sql-server/main.tf line 84, in resource "azurerm_mssql_server_extended_auditing_policy" "server_audit_policy":
84: resource "azurerm_mssql_server_extended_auditing_policy" "server_audit_policy" {

Error: creating MsSql Database "dev-solzone-sqldb-munfsinb" Extended Auditing Policy (Sql Server "dev-solzone-sqlserver-munfsinb" / Resource Group "devsolzone"): sql.ExtendedDatabaseBlobAuditingPoliciesClient#CreateOrUpdate: Failure responding to request: StatusCode=400 -- Original Error: autorest/azure: Service returned an error. Status=400 Code="BlobAuditingInsufficientStorageAccountPermissions" Message="Insufficient read or write permissions on storage account 'devsolzonesqlsamunfsinb'. "

on ../../../modules/azure-sql-server/main.tf line 125, in resource "azurerm_mssql_database_extended_auditing_policy" "db_audit_policy":
125: resource "azurerm_mssql_database_extended_auditing_policy" "db_audit_policy" {

[tombuildsstuff]

@dprateek1991 those errors are unrelated to this issue - those errors are saying either the MSSQL server (via it's Managed Identity) or the Service Principal being used doesn't have permissions to the storage account: Insufficient read or write permissions on storage account 'devsolzonesqlsamunfsinb'.

[dprateek1991]

@tombuildsstuff But that's the thing, I do have this permission enabled. I wonder whether this as well works or not. With VNet enabled Storage Account I can't seem to use Audit Policy for SQL Server and Database

resource "azurerm_role_assignment" "server_audit_owner" {
scope = azurerm_storage_account.sql_storage_account.id
role_definition_name = "Storage Blob Data Owner"
principal_id = azurerm_mssql_server.sql_server.identity.0.principal_id
}

[brandonmbanks]

@tombuildsstuff Can this issue be reopened?

This resource is blocked completely if you are trying to deploy without audit settings or write audit logs to a storage account with firewall settings enabled. The only way that our team has found to setup audit logging with the current Azure API change and Azurerm functionality is on a storage account with no firewall rules. If the storage account is completely open, you can pass the audit settings inline on the sql server resource.

extended_auditing_policy {
  storage_endpoint           = azurerm_storage_account.example.primary_blob_endpoint
  storage_account_access_key = azurerm_storage_account.example.primary_access_key
}

Using the inline settings, we get BlobAuditingInsufficientStorageAccountPermissions when the storage account has firewall enabled.

These MSFT docs outline what is required if the storage account has a firewall enabled.

• Enabled "Allow trusted Microsoft services to access this storage account"
• The SQL Server managed identity needs "Storage Blob Data Contributor" RBAC on the storage account

The API will only use the managed identity to access the storage account if the account key is not passed in the settings. This is not allowed using the inline settings

The key is optional in the new azurerm_mssql_server_extended_auditing_policy resource. This would allow the SQL Server identity to access the storage account.

This is where the Azure API issue https://github.com/Azure/azure-rest-api-specs/issues/11271 becomes a problem and forces the inline settings to be passed. If the inline settings are not passed we get the same error as the original post:

Error: issuing create/update request for SQL Server "sqlx1txxlxbdevxx312" Blob Auditing Policies(Resource Group "rgxxxx"): sql.ExtendedServerBlobAuditingPoliciesClient#CreateOrUpdate: Failure sending request: StatusCode=400 -- Original Error: Code="DataSecurityInvalidUserSuppliedParameter" Message="Invalid parameter 'storageEndpoint'. Value should be a blob storage endpoint (e.g. https://MyAccount.blob.core.windows.net)."