Azure-quickstart-templates: sql-auditing-server-policy-to-blob-storage Fails on Initial Deployment

[bazauto]

201-sql-auditing-server-policy-to-blob-storage

Issue Details

The initial deployment of this template when using the isStorageBehindVnet parameter set to true fails. Deploying once the SQL server and storage are configured with the role by the template the first time works.

It appears the role assignment or identity creation doesn't complete before returning to the ARM template processing so it isn't ready when the auditingSettings section is evaluated.

As a workaround I've split the auditingSettings section into its own template and deploy that separately later on in our release pipeline to ensure that the SQL server, the Identity and role assignment have completed.

Is this expected behaviour? Are there any workarounds to keep all this in a single template?

I'm using the blob store version of the audit setup to allow it to be written to an immutable blob container.

Repro steps

1. Deploy the template with isStorageBehindVnet set to true
2. The deployment will fail
3. Re-Deploy with same parameters into the same Resource Group and it will succeed

[MCKLMT]

As mentioned in the template are you owner on the subscription or resource group? https://github.com/Azure/azure-quickstart-templates/blob/master/201-sql-auditing-server-policy-to-blob-storage/azuredeploy.json#L42
I can successfully deploy the template in my environment.

[bazauto]

Thank you for your response.

Yes, I've double checked and I'm the owner of both the subscription and resource group.

Are you setting the isStorageBehindVnet parameter to true? If I set this to false it deploys successfully.
It only fails when the role assignment to the managed identity of SQL is created on the storage account.

So it..

• Creates the storage account
• Creates the SQL server
• Creates the role assignment for the system generated identity of the SQL server
• Then fails when trying to enable the auditing

The error is "Insufficient read or write permissions on storage account 'sqlaudit7suhl2qougixq'. Add permissions to the server Identity to the storage account."

Re-deploying the template with the same parameters to the same resource group that contains the previously created resources completes successfully.

I've repeated this test quite a few times in case it was a strange timing issue. But I get the same results every time.

[MCKLMT]

I have set the parameter to true and I don't encounter the issue. I will try later but it's definitely a permission issue.

[molinamelendezj]

I'm having similar issue. @bazauto you said you set isStorageBehindVnet false and got it to work. but I get same issue regardless when true, did you change anything else from that template to get it to work when its false?

@MCKLMT I agree it is a permission issue. could you provide the details and permission of what they should be for both when isStorageBehindVnet false and when isStorageBehindVnet true. in my case an azure pipeline service principle should have the right permissions to get this to work but I don't know what those permissions should be.

[MCKLMT]

If _isStorageBehindVnet_ is set to True, the ServicePrincipal should be defined as Contributor on the Storage Account.

[bazauto]

@molinamelendezj I changed only the isStorageBehindVnet parameter.

@MCKLMT When you say the ServicePrincipal should be defined as Contributor. Which ServicePrincipal are you referring to? Is this the System Managed one of the SQL Server resource. That permission is added by the template. Or the principal used by pipeline.

I've not tried the example directly in a pipeline, only once integrated it into my own templates. I went back to testing the example manually once I couldn't get my own templates to work smoothly.

In my case the pipeline SPN has Contributor on the subscription and when I test manual deployment my account is a classic administrator of the subscription which I believe gives me "Service Administrator" over all resources in it. I've checked and these permissions are inherited by the Storage Account.

[MCKLMT]

Yes, this is the System Identity of the SQL resource https://github.com/Azure/azure-quickstart-templates/blob/master/201-sql-auditing-server-policy-to-blob-storage/azuredeploy.json#L78
The template will assign the role of Contributor to the identity when the template is deployed.