You may get error message similar to the one below when you are trying to update vulnerability assessment baseline using PowerShell command.
"statusCode": "BadRequest",
"serviceRequestId": null,
"statusMessage": "{\"error\":{\"code\":\"VulnerabilityAssessmentStorageAuthenticationFailed\",\"message\":\"Could not authenticate to storage account 'storageaccount.blob.core.windows.net' .\"}}",
"eventCategory": "Administrative",
Set-AzSqlDatabaseVulnerabilityAssessmentRuleBaseline `
-ResourceGroupName "ResourceGroup01" `
-ServerName "Server01" `
-DatabaseName "Database01" `
-RuleId "VA2108" `
-BaselineResult @('Principal1', 'db_ddladmin', 'SQL_USER', 'None') , @( 'Principal2', 'db_ddladmin', 'SQL_USER', 'None')
This error indicates authentication issue connecting from Azure SQL Server to the Storage blob where that vulnerability assessment saves baseline and vulnerability assessment scan results.
To understand how this authentication works between Azure SQL Server and Storage blob account. please take into consideration the following prerequisites:
The SQL Vulnerability Assessment service needs permission to the storage account to save baseline and scan results. There are three methods:
- Use Storage Account key: Azure creates the SAS key and saves it (though we don't save the account key)
- Use Storage SAS key: The SAS key must have: Write | List | Read | Delete permissions
- Use SQL Server managed identity: The SQL Server must have a managed identity. The storage account must have a role assignment for the SQL Managed Identity as Storage Blob Data Contributor. When you apply the settings, the VA fields storageContainerSasKey and storageAccountAccessKey must be empty. When storage is behind a firewall or virtual network, then the SQL managed identity is required.
When you use the Azure portal to save SQL VA settings, Azure checks if you have permission to assign a new role assignment for the managed identity as Storage Blob Data Contributor on the storage. If permissions are assigned, Azure uses SQL Server managed identity, otherwise Azure uses the key method.
For the VA service save the scan results/baseline and read the baseline, it must have permission to access to the storage account.
There are two methods:
- Using Storage Account key / SAS Key
- Using SQL Server managed identity (Recommended)
- The SQL server must have a managed identity.
- The storage account must have role assignment for the SQL server identity as Storage Blob Data Contributor.
- When we have a storage behind firewall / VNET – VA only works with managed identity method
Note: Assign the Storage blob Data Contributor role to AAD Group might not working probably and you need to assign the role to the Azure SQL Server managed identity directly.
