azurerm_sql_database - by default database is geo redundant and fails on azure policy

[vinaynv26]

Hi Team,
We are trying to spin up azure sql server and sql database from Terraform azurerm version 2.22.0
There is azure policy configured not to allow geo redundancy. On creating the database we are getting the error "SQL Database should avoid using GRS backup redundancy" . Would really be helpful if there is an alternative for this. Is there a way through terraform to set the "Backup storage redundancy".
SQLDB_Policy1.txt

PFA the azure policy json and below is the error message.
Error: Error issuing create/update request for SQL Database "dbname" (Resource Group "rg-db-sql", Server "sqlserver001"):
sql.DatabasesClient#CreateOrUpdate: Failure sending request: StatusCode=403 -- Original Error: Code="RequestDisallowedByPolicy" Message="Resource
'dbname' was disallowed by policy. Policy identifiers:

Thanks and Regards,
Vinay N V

[ruandersMSFT]

@vinaynv26

The issue you reference is an Azure Policy enabled on your Azure Subscription, either to the subscription directly or through Management Groups (i.e. policy applied at a higher organizational level). As you have noted, the built in Azure Policy titled "SQL Database should avoid using GRS backup redundancy" is enabled preventing you from deploying an Azure SQL Database where the storageAccountType property is GRS (only LRS or ZRS allowed). GRS is the first (default) value of the storageAccountType property, and since the AzureRM azurerm_sql_database object doesn't provide control of the storageAccountType property yet, the default value of GRS is utilized (which is then disallowed in your environment per Azure Policy).

Next steps should be for the azurerm_sql_database object to add control of the storageAccountType property so that you can specify either LRS or ZRS instead of GRS.

This needs to be implemented and then you would have to upgrade your AzureRM Provider from 2.22.0 to that new supported release to utilized this via Terraform. Because this unspecified value is defaulting to GRS and cannot be defined/controlled via the AzureRM provider yet, consider performing initial deployment of your database via PowerShell, CLI or the Azure Portal and then Import the object into your Terraform State.

[vinaynv26]

@ruandersMSFT : Thank you for the detailed information. Really appreciate. May I know the tentative plan or date or version when the azurerm_sql_database will have the property storageAccountType so we can set via Terraform.

[ruandersMSFT]

Development progression findings:

The StorageAccountType enum is not defined as part of the 2017-03-01-preview azure-sdk-for-go sql enum; therefore, this property cannot be brought to the azurerm_sql_database resource which utilizes 2017-03-01-preview.

The StoreageAccountType enum is defined as part of the v3.0 azure-sdk-for-go sql enum; therefore, the StoreageAccountType argument can be added to the azurerm_mssql_database resource which utilizes v3.0.

[vinaynv26]

@ruandersMSFT noted with thanks. so can please advice in which azurermversion the property StorageAccountType in azurerm_mssql_database is added. Because observe this property is still not there in Terraform docs : https://registry.terraform.io/providers/hashicorp/azurerm/latest/docs/resources/mssql_database

[srinathrangaramanujam]

@ruandersMSFT , any help on this. we are also facing the same issue.

[ruandersMSFT]

@tombuildsstuff What is the process for getting the Azure SDK for Go version updated in the AzureRM Provider? I can help fix this, but I need to have an updated version of the Azure SDK for Go referenced.

[ruandersMSFT]

Awaiting #10042 completion to be able to fix.

[srinathrangaramanujam]

@ruandersMSFT good news az sdk for go has been updated..

[ruandersMSFT]

Looks like they just tagged v2.43.0, thus won't be included until v2.44.0

[ghost]

This has been released in version 2.44.0 of the provider. Please see the Terraform documentation on provider versioning or reach out if you need any assistance upgrading. As an example:

provider "azurerm" {
    version = "~> 2.44.0"
}
# ... other configuration ...

[ghost]

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!