azure - 如何为带有防火墙后存储帐户的 sql server 启用 arm 模板漏洞评估
问题描述
使用 arm 模板启用 sql server 漏洞评估功能时，存储帐户打开防火墙时会引发以下错误。
"error": {
"code": "InvalidStorageAccountCredentials",
"message": "The provided storage account shared access signature or account storage key is not valid."
}
}
模板部分：
{
"type": "Microsoft.Sql/servers/securityAlertPolicies",
"apiVersion": "2017-03-01-preview",
"name": "[concat(variables('sqls01Name'), '/Default')]",
"dependsOn": [
],
"properties": {
"state": "Enabled",
"emailAddresses": "[variables('emailActionGroupAddresses')]",
"emailAccountAdmins": false
}
},
{
"type": "Microsoft.Sql/servers/vulnerabilityAssessments",
"apiVersion": "2018-06-01-preview",
"location": "westeurope",
"name": "[concat(variables('sqls01Name'), '/Default')]",
"dependsOn": [
"[resourceId('Microsoft.Storage/storageAccounts', variables('defenderSa'))]"
],
"properties": {
"storageContainerPath": "[concat('https://',variables('defenderSa'),'.blob.core.windows.net/vulnerability-assessment/')]",
"storageAccountAccessKey": "[listKeys(resourceId('Microsoft.Storage/storageAccounts', variables('defenderSa')), providers('Microsoft.Storage', 'storageAccounts').apiVersions[0]).keys[0].value]",
"recurringScans": {
"isEnabled": true,
"emailSubscriptionAdmins": false,
"emails": "[variables('emailActionGroupAddresses')]"
}
}
},
{
"name": "[variables('defenderSA')]",
"type": "Microsoft.Storage/storageAccounts",
"apiVersion": "2019-06-01",
"location": "westeurope",
"properties": {
"accessTier": "Cool",
"allowBlobPublicAccess": false,
"supportsHttpsTrafficOnly": true,
"networkAcls": {
"bypass": "AzureServices",
"virtualNetworkRules": [{
"id": "[variables('subnetId')]",
"action": "Allow"
}],
"ipRules": [
],
"defaultAction": "Deny"
}
},
"dependsOn": [
],
"sku": {
"name": "Standard_LRS",
"tier": "Standard"
},
"kind": "StorageV2",
"tags": {
}
}
我注意到，当从门户启用该功能时，会显示以下通信：
您选择了位于防火墙后面或虚拟网络中的存储。请注意，使用此存储将为服务器创建托管标识，并将在所选存储上授予“存储 blob 数据参与者”角色。
确实创建了作业并且评估有效，但是当我尝试使用以下代码在 arm 模板中复制它时，它仍然失败。
{
"type": "Microsoft.Storage/storageAccounts/providers/roleAssignments",
"name": "[concat(variables('defenderSA'),'/Microsoft.Authorization/',guid(variables('sqls01Name')))]",
"apiVersion": "2018-09-01-preview",
"dependsOn": [
"[resourceId('Microsoft.Storage/storageAccounts',variables('defenderSA'))]"
],
"properties": {
"roleDefinitionId": "[concat('/subscriptions/', subscription().subscriptionId, '/providers/Microsoft.Authorization/roleDefinitions/', 'ba92f5b4-2d11-453d-a403-e96b0029c9fe')]",
"principalId": "[reference(resourceId('Microsoft.Sql/servers',variables('sqls01Name')),providers('Microsoft.Sql', 'servers').apiVersions[0],'Full').identity.principalId]"
}
}
解决方案
关于问题，请参考以下模板
{
"$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"parameters": {
"clientIp": {
"type": "string",
"defaultValue": "",
"metadata": {
"description": "allow you client to access Azure storage "
}
},
"virtualNetworksName": {
"defaultValue": "testsql09",
"type": "String"
},
"serverName": {
"type": "string",
"defaultValue": "[uniqueString('sql', resourceGroup().id)]",
"metadata": {
"description": "The name of the SQL logical server."
}
},
"location": {
"type": "string",
"defaultValue": "[resourceGroup().location]",
"metadata": {
"description": "Location for all resources."
}
},
"administratorLogin": {
"type": "string",
"defaultValue": "sqladmin",
"metadata": {
"description": "The administrator username of the SQL logical server."
}
},
"administratorLoginPassword": {
"type": "securestring",
"defaultValue": "Password0123!",
"metadata": {
"description": "The administrator password of the SQL logical server."
}
},
"connectionType": {
"defaultValue": "Default",
"allowedValues": [ "Default", "Redirect", "Proxy" ],
"type": "string",
"metadata": {
"description": "SQL logical server connection type."
}
}
},
"variables": {
"serverResourceGroupName": "[resourceGroup().name]",
"subscriptionId": "[subscription().subscriptionId]",
"uniqueStorage": "[uniqueString(variables('subscriptionId'), variables('serverResourceGroupName'), parameters('location'))]",
"storageName": "[tolower(concat('sqlva', variables('uniqueStorage')))]",
"roleAssignmentName": "[guid(resourceId('Microsoft.Storage/storageAccounts', variables('storageName')), variables('storageBlobContributor'), resourceId('Microsoft.Sql/servers', parameters('serverName')))]",
"StorageBlobContributor": "[subscriptionResourceId('Microsoft.Authorization/roleDefinitions', 'ba92f5b4-2d11-453d-a403-e96b0029c9fe')]"
},
"resources": [
{
"type": "Microsoft.Network/virtualNetworks",
"apiVersion": "2020-05-01",
"name": "[parameters('virtualNetworksName')]",
"location": "southeastasia",
"properties": {
"addressSpace": {
"addressPrefixes": [
"10.18.0.0/24"
]
},
"subnets": [
{
"name": "default",
"properties": {
"addressPrefix": "10.18.0.0/24",
"serviceEndpoints": [
{
"service": "Microsoft.Storage"
}
],
"delegations": [],
"privateEndpointNetworkPolicies": "Enabled",
"privateLinkServiceNetworkPolicies": "Enabled"
}
}
],
"virtualNetworkPeerings": [],
"enableDdosProtection": false,
"enableVmProtection": false
}
},
{
"type": "Microsoft.Network/virtualNetworks/subnets",
"apiVersion": "2020-05-01",
"name": "[concat(parameters('virtualNetworksName'), '/default')]",
"dependsOn": [
"[resourceId('Microsoft.Network/virtualNetworks', parameters('virtualNetworksName'))]"
],
"properties": {
"addressPrefix": "10.18.0.0/24",
"serviceEndpoints": [
{
"service": "Microsoft.Storage"
}
],
"delegations": [],
"privateEndpointNetworkPolicies": "Enabled",
"privateLinkServiceNetworkPolicies": "Enabled"
}
},
{
"type": "Microsoft.Sql/servers",
"apiVersion": "2019-06-01-preview",
"name": "[parameters('serverName')]",
"location": "[parameters('location')]",
"identity": {
"type": "SystemAssigned"
},
"properties": {
"administratorLogin": "[parameters('administratorLogin')]",
"administratorLoginPassword": "[parameters('administratorLoginPassword')]",
"version": "12.0"
}
},
{
"type": "Microsoft.Sql/servers/databases",
"apiVersion": "2019-06-01-preview",
"name": "[concat(parameters('serverName'), '/test')]",
"location": "[parameters('location')]",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', parameters('serverName'))]"
],
"sku": {
"name": "Basic",
"tier": "Basic",
"capacity": 5
},
"kind": "v12.0,user",
"properties": {
"collation": "SQL_Latin1_General_CP1_CI_AS",
"maxSizeBytes": 2147483648,
"catalogCollation": "SQL_Latin1_General_CP1_CI_AS",
"zoneRedundant": false,
"readScale": "Disabled",
"storageAccountType": "LRS"
}
},
{
"type": "Microsoft.Sql/servers/securityAlertPolicies",
"apiVersion": "2020-02-02-preview",
"name": "[concat(parameters('serverName'), '/Default')]",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', parameters('serverName'))]"
],
"properties": {
"state": "Enabled",
"emailAccountAdmins": false
}
},
{
"type": "Microsoft.Sql/servers/vulnerabilityAssessments",
"apiVersion": "2018-06-01-preview",
"name": "[concat(parameters('serverName'), '/Default')]",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', parameters('serverName'))]",
"[resourceId('Microsoft.Sql/servers/securityAlertPolicies', parameters('serverName'), 'Default')]",
"[resourceId('Microsoft.Storage/storageAccounts', variables('storageName'))]",
"[extensionResourceId(resourceId('Microsoft.Storage/storageAccounts', variables('storageName')), 'Microsoft.Authorization/roleAssignments', variables('roleAssignmentName'))]"
],
"properties": {
"storageContainerPath": "[concat(reference(resourceId('Microsoft.Storage/storageAccounts', variables('storageName'))).primaryEndpoints.blob, 'vulnerability-assessment')]",
"recurringScans": {
"isEnabled": true,
"emailSubscriptionAdmins": false
}
}
},
{
"type": "Microsoft.Sql/servers/connectionPolicies",
"apiVersion": "2014-04-01",
"name": "[concat(parameters('serverName'), '/Default')]",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', parameters('serverName'))]"
],
"properties": {
"connectionType": "[parameters('connectionType')]"
}
},
{
"type": "Microsoft.Storage/storageAccounts",
"apiVersion": "2019-06-01",
"name": "[variables('storageName')]",
"location": "[parameters('location')]",
"dependsOn": [
"[resourceId('Microsoft.Network/virtualNetworks/subnets', parameters('virtualNetworksName'), 'default')]"
],
"sku": {
"name": "Standard_LRS"
},
"kind": "StorageV2",
"properties": {
"minimumTlsVersion": "TLS1_2",
"allowBlobPublicAccess": true,
"networkAcls": {
"bypass": "AzureServices",
"virtualNetworkRules": [
{
"id": "[resourceId('Microsoft.Network/virtualNetworks/subnets', parameters('virtualNetworksName'), 'default')]",
"action": "Allow",
"state": "Succeeded"
}
],
"ipRules": [
{
"value": "[parameters('clientIp')]",
"action": "Allow"
}
],
"defaultAction": "Deny"
}
}
},
{
"type": "Microsoft.Storage/storageAccounts/providers/roleAssignments",
"apiVersion": "2020-04-01-preview",
"name": "[concat(variables('storageName'), '/Microsoft.Authorization/', variables('roleAssignmentName'))]",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', parameters('serverName'))]",
"[resourceId('Microsoft.Storage/storageAccounts', variables('storageName'))]"
],
"properties": {
"roleDefinitionId": "[variables('StorageBlobContributor')]",
"principalId": "[reference(resourceId('Microsoft.Sql/servers', parameters('serverName')), '2020-02-02-preview', 'Full').identity.principalId]",
"scope": "[resourceId('Microsoft.Storage/storageAccounts', variables('storageName'))]",
"principalType": "ServicePrincipal"
}
}
]
}
推荐阅读
- sqoop - 如果我们将选项文件保存在 hdfs 中，则选项文件功能在 sqoop 中不起作用
- css - 基础 6 中均匀间隔的制表符标题
- sql-server - 如何用序列号集替换 Studio 中列中的随机值？
- macos - 如何在 DMG 安装程序中使用 Chef Omnibus 创建 Mac OS .app 包
- swift - 如何将 Xcode Playground 更改为特定的 Swift 版本？
- sql - 为什么读取器对象中的索引超出范围错误
- html - 为什么我的下拉菜单没有在我的页面上完全展开？
- json - 反序列化“oneOf”结构的 Json 数组
- r - 拆分 data.frame 行并打乱它们的顺序
- azure-devops - 除了访问令牌 (PAT) 之外，还有其他方法可以对 Azure Devops CLI 进行身份验证吗？