Breaking change in the SQL Extended Auditing Settings API

[tombuildsstuff]

👋🏼

We've had several reports opened about a breaking change in the SQL Extended Auditing Settings API - after investigation we can confirm the same thing.

When creating a new SQL Server, using the following Payload:

PUT /subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/tom-dev-resources/providers/Microsoft.Sql/servers/tom-dev-mssql?api-version=2019-06-01-preview

x-ms-correlation-request-id: 5d12dd49-ece8-a6e3-36d1-34c1f9bc8854

{
    "location": "westeurope",
    "properties": {
        "administratorLogin": "XXX",
        "administratorLoginPassword": "XXX",
        "minimalTlsVersion": "1.2",
        "publicNetworkAccess": "Enabled",
        "version": "12.0"
    },
    "tags": {
        "environment": "production"
    }
}

.. and subsequently posting to ensure the Extended Auditing Policy is disabled, via this Request:

PUT /subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/tom-dev-resources/providers/Microsoft.Sql/servers/tom-dev-mssql/extendedAuditingSettings/default?api-version=2017-03-01-preview

x-ms-correlation-request-id: 5d12dd49-ece8-a6e3-36d1-34c1f9bc8854

{
    "properties": {
        "state": "Disabled"
    }
}

We get the following Response:

x-ms-request-id: 9f5070be-d3f0-471a-9647-f6161d46a4f5
x-ms-correlation-request-id: 5d12dd49-ece8-a6e3-36d1-34c1f9bc8854
x-ms-routing-request-id: GERMANYNORTH:20201019T065335Z:e524a764-7455-44e7-a2ee-ba86c2cdbfb1

{
    "error": {
        "code": "DataSecurityInvalidUserSuppliedParameter",
        "message": "Invalid parameter 'storageEndpoint'. Value should be a blob storage endpoint (e.g. https://MyAccount.blob.core.windows.net)."
    }
}

It appears on the 15th/16th October this API started requiring that the storageEndpoint field has validation regardless of if it's specified or not (but is Optional) - meaning it's currently not possible to disable this functionality.

Since this is a breaking change to the API - can this be reverted so that it's possible to disable this functionality in addition to enabling it?

On a separate note - this API is a Preview version from 2017 - why is this still in Preview 3 years later, surely there should have been a stable API release during that time?

Thanks!

[dpalat]

Is there any update on this?

[tombuildsstuff]

👋

We've had an update from the Service Team (via Azure Support) that it's possible to add an extra field to the payload to workaround this (isAzureMonitorTargetEnabled=true), but that doesn't work for existing users.. and so is a workaround not a fix.

As such from what I can tell this regression needs to be rolled forward/backwards since this has broken all existing clients - unfortunately there's no ETA for that but it is being investigated - I'll post another update as we have one.. hopefully someone from the Service Team can chime in here before then.

Thanks!

[tombuildsstuff]

@nathannfan @vishrutshah @jaredmoo @yaakoviyun @bcham @changlong-liu @00Kai0 @akning-ms

Could we get a "Service Attention" on this API Regression so that the Service Team can see it directly?

[ghost]

Thanks for the feedback! We are routing this to the appropriate team for follow-up. cc @azureSQLGitHub.

[tombuildsstuff]

thanks @ArcturusZhang :)

[TPPWC]

Is there an update for this? We also raised an ARR ticket with MS.

[tombuildsstuff]

@TPPWC based on the last update I've seen - there's a fix for this being worked on by the Service Team - however it's currently scheduled for the next release train (which I'm informed takes 1-2 weeks).

We've pushed back on that, requesting this is fixed/rolled-back sooner (as this is a regression, not a new feature that's not being used) - but this is ultimately on the Service Team to rollback / prioritise the hotfix.

From Terraform's perspective, we could look to add this new field to the payload in this weeks release of the Azure Provider, that's not going to work for existing users who may not be able to upgrade due to other Azure API issues; as such this ultimately requires that the Service Team roll back their breaking API change here.

As such, whilst we'll look to ship this fix in this weeks release of the Azure Provider if necessary - we're pushing for the breaking API change to be reverted so that this works for all users before then - we'll post another update here when we have it, however we'll ask the Service Team to post an update here with more information too.

[TPPWC]

Hmm ok, that means that we have to tell our customers that they are not able to deploy or update any sql server or database for the next 1-2- weeks? I am not sure if they like to hear that.....

[Timovzl]

We've had an update from the Service Team (via Azure Support) that it's possible to add an extra field to the payload to workaround this (isAzureMonitorTargetEnabled=true), but that doesn't work for existing users.. and so is a workaround not a fix.

@tombuildsstuff Can you explain what you mean by an existing user here?

When setting up a brand new environment, can we use this to somehow work around the issue? It's not quite clear to me, but if there is a workaround, I'd like to try it.

[tombuildsstuff]

@tombuildsstuff Can you explain what you mean by an existing user here?

This was meant to refer to existing installs/versions of the provider, rather than users here - apologies for the ambiguity.

Hmm ok, that means that we have to tell our customers that they are not able to deploy or update any sql server or database for the next 1-2- weeks? I am not sure if they like to hear that.....

Indeed, as mentioned above we're pushing for the Service Team to revert/roll a fix out asap, but it's unfortunately outside of our control. We can look to ship a workaround in this weeks release if the API hasn't been reverted by then - but that will only work for the users who can upgrade (and some are unable to upgrade due to other API issues) - which is why we're pushing the team to fix/revert this asap instead.

[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.

With regards to this issue, since this is a bug in the Azure API, we'll leave this issue open until the bug fix deployment has been rolled out - and have requested that the Service Team post an update when that's done so that users are aware.

Thanks!

[Badajoz95]

This is causing a problem through the Azure Portal UI and PowerShell. Repro is set Azure SQL Server Auditing to a Log Analytics Workspace and then attempt to disable it. You get the same error message in both.

[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!

[LukeCarrier]

Thanks for minimising the disruption and saving us some troubleshooting, @tombuildsstuff!

[johnjbateman]

This issue is affecting East US region. Any work on when this will be rolled out?

[OptuMatt]

Any update? This is still causing problems.

[brandonmbanks]

@ArcturusZhang Could we get an update on this? Still seeing this issue.

[ArcturusZhang]

Hey @azureSQLGitHub could some one from the service team take a look at this issue?

[tombuildsstuff]

At the same time could we get an update on this one: https://github.com/terraform-providers/terraform-provider-azurerm/issues/8915

[DavidTrigano]

Hi @tombuildsstuff,

I am the Product Manager of Azure SQL Auditing. Thanks for the feedback and for taking the time to help us improve our products.

The issue mentioned in this incident was discovered a few months ago and fixed 3 weeks after, currently mitigated and deployed worldwide, this issue is now fixed. Based on our telemetry, customers who are still facing the above error message may have a client-side error.

We are going to close this issue, but feel free to open a new one if you have additional requests.

@ArcturusZhang you can go ahead and close this issue.

[ArcturusZhang]

Closed as requested by @DavidTrigano

[OptuMatt]

I have an issue with the following in Terraform 0.13.4 with Azure Providers 2.40 and 2.41…

resource "azurerm_mssql_server" "sqlserver" { name = var.sql_server_name resource_group_name = var.resource_group_name location = var.azure_location version = var.sql_server_version administrator_login = var.sql_admin_name administrator_login_password = var.sql_admin_password

azuread_administrator { login_username = var.sql_ad_admin_name object_id = var.sql_ad_admin_id tenant_id = var.arm_tenant_id }

}

The above successfully creates the SQL Server and will subsequently update it if there is no auditing on the server. As soon as auditing is enabled on the server, in another terraform configuration managed by a different department, the above causes the following error…

Error: issuing create/update request for SQL Server "" Blob Auditing Policies(Resource Group ""): 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)." on ......\modules\azure_sql_DBaaS_server_infrastructure\main.tf line 1, in resource "azurerm_mssql_server" "sqlserver": 1: resource "azurerm_mssql_server" "sqlserver" {

There are only 2 ways I’ve been able to get this to run successfully after auditing is enabled.

1. Remove auditing in the Azure Portal before each deployment.
2. Add a second audit log destination using an extended auditing policy block added to the above resource in my terraform configuration.

Other things I’ve tried with no success.

1. Add a lifecycle block to ignore changes to the server’s extended auditing policy. lifecycle {ignore_changes = [extended_auditing_policy]} a. Returns the same error.
2. Edit the state file, since it has the following… "extended_auditing_policy": [{"retention_in_days": 0, "storage_account_access_key": "", "storage_account_access_key_is_secondary": false, "storage_endpoint": ""}] a. Remove the details of the extended auditing policy from the state file. "extended_auditing_policy": [] i. Simply adds the details and returns the same error. b. Remove the extended auditing policy from the state file entirely. i. Simply adds the details and returns the same error.

Ideally, I’d like to be able to add the lifecycle block to ignore changes to the SQL Server’s Extended Auditing Policy. While adding a second audit log destination using an extended auditing policy block did prevent the error, this block will be deprecated soon AND this secondary logging is not needed.

[DavidTrigano]

Hello @OptuMatt,

Thanks for the feedback. Can you please open an Azure incident so our engineers can work with you on this matter? To create a Azure support request, please follow the below instructions: https://docs.microsoft.com/en-us/azure/azure-portal/supportability/how-to-create-azure-support-request

Many thanks, D.

[OptuMatt]

I have Azure Support Request, 121010724001695, open for this issue. Their recommendation is to “manage the resources only with Terraform and one configuration”, which is not possible for me, and in my opinion not a reasonable suggestion. I, like many others, believe that monolithic things like this should be avoided when possible.