如何打开ARM模板中Azure SQL数据库的审计和威胁检测？

Question

自2015年11月以来，Azure SQL数据库威胁检测功能已在“通用预览”中出现。

https://azure.microsoft.com/en-us/blog/threat-detection-public-preview/

但是，我无法了解如何在ARM模板中打开此特性及其依赖项(Azure SQL数据库审核)，无论是在Azure Quickstart模板中还是在Azure资源管理器模式GitHubs链接中。

azure-快速启动-模板

azure-资源管理器-架构

感谢任何知道的人都能回答这个问题。非常感谢。

Answer 1

以下是两个示例模板：

首先，启用整个SQL服务器的审核和威胁检测。

{
    "$schema": "http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {
        "serverName": {
            "type": "string",
            "metadata": {
                "description": "The name of the new database server to create."
            }
        },
        "serverLocation": {
            "type": "string",
            "metadata": {
                "description": "The location of the database server."
            }
        },
        "administratorLogin": {
            "type": "string",
            "metadata": {
                "description": "The account name to use for the database server administrator."
            }
        },
        "administratorLoginPassword": {
            "type": "securestring",
            "metadata": {
                "description": "The password to use for the database server administrator."
            }
        },
        "databaseName": {
            "type": "string",
            "metadata": {
                "description": "The name of the new database to create."
            }
        },
        "collation": {
            "type": "string",
            "defaultValue": "SQL_Latin1_General_CP1_CI_AS",
            "metadata": {
                "description": "The database collation for governing the proper use of characters."
            }
        },
        "edition": {
            "type": "string",
            "defaultValue": "Standard",
            "metadata": {
                "description": "The type of database to create. The available options are: Web, Business, Basic, Standard, and Premium."
            }
        },
        "maxSizeBytes": {
            "type": "string",
            "defaultValue": "1073741824",
            "metadata": {
                "description": "The maximum size, in bytes, for the database"
            }
        },
        "requestedServiceObjectiveName": {
            "type": "string",
            "defaultValue": "S0",
            "metadata": {
                "description": "The name corresponding to the performance level for edition. The available options are: Shared, Basic, S0, S1, S2, S3, P1, P2, and P3."
            }
        },
        "eventTypesToAudit": {
            "type": "string",
            "defaultValue":"All",
            "metadata": {
                "description": "The event type to audit."
            }
        }
    },
    "resources": [
        {
            "name": "[parameters('serverName')]",
            "type": "Microsoft.Sql/servers",
            "location": "[parameters('serverLocation')]",
            "apiVersion": "2014-04-01-preview",
            "properties": {
                "administratorLogin": "[parameters('administratorLogin')]",
                "administratorLoginPassword": "[parameters('administratorLoginPassword')]",
                "version": "12.0"
            },
            "resources": [
                {
                    "name": "[parameters('databaseName')]",
                    "type": "databases",
                    "location": "[parameters('serverLocation')]",
                    "apiVersion": "2014-04-01-preview",
                    "dependsOn": [
                        "[concat('Microsoft.Sql/servers/', parameters('serverName'))]"
                    ],
                    "properties": {
                        "edition": "[parameters('edition')]",
                        "collation": "[parameters('collation')]",
                        "maxSizeBytes": "[parameters('maxSizeBytes')]",
                        "requestedServiceObjectiveName": "[parameters('requestedServiceObjectiveName')]"
                    }
                },
                {
                    "apiVersion": "2014-04-01-preview",
                    "dependsOn": [
                        "[concat('Microsoft.Sql/servers/', parameters('serverName'))]"
                    ],
                    "location": "[parameters('serverLocation')]",
                    "name": "AllowAllWindowsAzureIps",
                    "properties": {
                        "endIpAddress": "0.0.0.0",
                        "startIpAddress": "0.0.0.0"
                    },
                    "type": "firewallrules"
                },
                {
                    "apiVersion": "2014-04-01-preview",
                    "type": "auditingPolicies",
                    "name": "Default",
                    "dependsOn": [
                        "[concat('Microsoft.Sql/servers/', parameters('serverName'))]",
                        "[concat('Microsoft.Sql/servers/', parameters('serverName'), '/databases/',parameters('databaseName'))]"
                    ],
                    "properties": {
                        "auditingState": "Enabled",
                        "storageAccountName": "<your-storage-account-name>",
                        "storageAccountKey": "<your-storage-account-key>",
                        "storageAccountResourceGroupName": "<your-storage-account-resource-group-name>",
                        "storageAccountSubscriptionId": "<your-storage-account-subscriptionid>",
                        "eventTypesToAudit": "parameters('eventTypesToAudit')"
                    }
                },
                {
                    "apiVersion": "2015-05-01-preview",
                    "type": "securityAlertPolicies",
                    "name": "Default",
                    "dependsOn": [
                        "[concat('Microsoft.Sql/servers/', parameters('serverName'))]",
                        "[concat('Microsoft.Sql/servers/', parameters('serverName'), '/databases/',parameters('databaseName'))]",
                        "[concat('Microsoft.Sql/servers/', parameters('serverName'), '/auditingPolicies/Default')]"
                    ],
                    "properties": {
                        "state": "Enabled",
                        "disabledAlerts": "",
                        "emailAddresses": "abcd@efgh.com",
                        "emailAccountAdmins": "true"
                    }
                }
            ]
        }
    ]
}

第二，只对特定数据库启用审核和威胁检测。

{
    "$schema": "http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {
        "serverName": {
            "type": "string",
            "metadata": {
                "description": "The name of the new database server to create."
            }
        },
        "serverLocation": {
            "type": "string",
            "metadata": {
                "description": "The location of the database server."
            }
        },
        "administratorLogin": {
            "type": "string",
            "metadata": {
                "description": "The account name to use for the database server administrator."
            }
        },
        "administratorLoginPassword": {
            "type": "securestring",
            "metadata": {
                "description": "The password to use for the database server administrator."
            }
        },
        "databaseName": {
            "type": "string",
            "metadata": {
                "description": "The name of the new database to create."
            }
        },
        "collation": {
            "type": "string",
            "defaultValue": "SQL_Latin1_General_CP1_CI_AS",
            "metadata": {
                "description": "The database collation for governing the proper use of characters."
            }
        },
        "edition": {
            "type": "string",
            "defaultValue": "Standard",
            "metadata": {
                "description": "The type of database to create. The available options are: Web, Business, Basic, Standard, and Premium."
            }
        },
        "maxSizeBytes": {
            "type": "string",
            "defaultValue": "1073741824",
            "metadata": {
                "description": "The maximum size, in bytes, for the database"
            }
        },
        "requestedServiceObjectiveName": {
            "type": "string",
            "defaultValue": "S0",
            "metadata": {
                "description": "The name corresponding to the performance level for edition. The available options are: Shared, Basic, S0, S1, S2, S3, P1, P2, and P3."
            }
        },
        "eventTypesToAudit": {
            "type": "string",
            "defaultValue":"All",
            "metadata": {
                "description": "The event type to audit."
            }
        }
    },
    "resources": [
        {
            "name": "[parameters('serverName')]",
            "type": "Microsoft.Sql/servers",
            "location": "[parameters('serverLocation')]",
            "apiVersion": "2014-04-01-preview",
            "properties": {
                "administratorLogin": "[parameters('administratorLogin')]",
                "administratorLoginPassword": "[parameters('administratorLoginPassword')]",
                "version": "12.0"
            },
            "resources": [
                {
                    "name": "[parameters('databaseName')]",
                    "type": "databases",
                    "location": "[parameters('serverLocation')]",
                    "apiVersion": "2014-04-01-preview",
                    "dependsOn": [
                        "[concat('Microsoft.Sql/servers/', parameters('serverName'))]"
                    ],
                    "properties": {
                        "edition": "[parameters('edition')]",
                        "collation": "[parameters('collation')]",
                        "maxSizeBytes": "[parameters('maxSizeBytes')]",
                        "requestedServiceObjectiveName": "[parameters('requestedServiceObjectiveName')]"
                    },
                    "resources":[
                        {
                            "apiVersion": "2014-04-01-preview",
                            "type": "auditingPolicies",
                            "name": "Default",
                            "dependsOn": [
                                "[concat('Microsoft.Sql/servers/', parameters('serverName'), '/databases/',parameters('databaseName'))]"
                            ],
                            "properties": {
                                "auditingState": "Enabled",
                                "storageAccountName": "<your-storage-account-name>",
                                "storageAccountKey": "<your-storage-account-key>",
                                "storageAccountResourceGroupName": "<your-storage-account-resource-group-name>",
                                "storageAccountSubscriptionId": "<your-storage-account-subscriptionid>",
                                "eventTypesToAudit": "parameters('eventTypesToAudit')"
                            }
                        },
                        {
                            "apiVersion": "2015-05-01-preview",
                            "type": "securityAlertPolicies",
                            "name": "Default",
                            "dependsOn": [
                                "[concat('Microsoft.Sql/servers/', parameters('serverName'), '/databases/',parameters('databaseName'))]",
                                "[concat('Microsoft.Sql/servers/', parameters('serverName'), '/databases/',parameters('databaseName'), '/auditingPolicies/Default')]"
                            ],
                            "properties": {
                                "state": "Enabled",
                                "disabledAlerts": "",
                                "emailAddresses": "abcd@efgh.com",
                                "emailAccountAdmins": "true"
                            }
                        }
                    ]
                },
                {
                    "apiVersion": "2014-04-01-preview",
                    "dependsOn": [
                        "[concat('Microsoft.Sql/servers/', parameters('serverName'))]"
                    ],
                    "location": "[parameters('serverLocation')]",
                    "name": "AllowAllWindowsAzureIps",
                    "properties": {
                        "endIpAddress": "0.0.0.0",
                        "startIpAddress": "0.0.0.0"
                    },
                    "type": "firewallrules"
                }
            ]
        }
    ]
}

注意:请不要忘记替换存储帐户的信息。

实际上，Yoav已经在博客的评论中回答了你的问题。而且，我已经测试了答案，并做了一些改进。

Answer 2

上个星期发生了变化，需要对securityAlertPolicies部分再增加两个参数：

"storageEndpoint": "https://<storage account name>.blob.core.windows.net/",
"storageAccountAccessKey": "<storage account key>"

这样，服务也可以将生成的警报写入您的存储帐户。

Answer 3

来自Jack的答案是接近的，但是(此时)您需要auditingSettings来指向blob存储，因为安全警报不适用于表存储。因此，添加以下auditingSettings和securityAlertPolicies作为Microsoft.Sql/servers资源的子资源。

    {
        "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
        "contentVersion": "1.0.0.0",
        "parameters": {},
        "variables": {},
        "resources": [
            {
                "name": "[parameters('sqlserverName')]",
                "type": "Microsoft.Sql/servers",
                "location": "[resourceGroup().location]",
                "apiVersion": "2014-04-01-preview",
                "properties": {},
                "resources": [
                    {
                        "apiVersion": "2015-05-01-preview",
                        "type": "auditingSettings",
                        "name": "Default",
                        "dependsOn": [
                            "[parameters('sqlserverName')]",
                            "[concat('Microsoft.Storage/storageAccounts/', parameters('storageAccountName'))]"
                        ],
                        "properties": {
                            "State": "Enabled",
                            "storageEndpoint": "[concat('https://', parameters('storageAccountName'), '.blob.core.windows.net/')]",
                            "storageAccountAccessKey": "[listKeys(resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccountName')), providers('Microsoft.Storage', 'storageAccounts').apiVersions[0]).keys[0].value]",
                            "storageAccountSubscriptionId": "[subscription().subscriptionId]",
                            "eventTypesToAudit": "All"
                        }
                    },
                    {
                        "apiVersion": "2015-05-01-preview",
                        "type": "securityAlertPolicies",
                        "name": "DefaultSecurityAlert",
                        "dependsOn": [
                            "[parameters('sqlserverName')]",
                            "[concat('Microsoft.Storage/storageAccounts/', parameters('storageAccountName'))]",
                            "[concat('Microsoft.Sql/servers/', parameters('sqlserverName'), '/auditingSettings/Default')]"
                        ],
                        "properties": {
                            "state": "Enabled",
                            "disabledAlerts": "",
                            "emailAddresses": "[parameters('securityAlertPolicyEmails')]",
                            "emailAccountAdmins": "Enabled",
                            "retentionDays": "10",
                            "storageEndpoint": "[concat('https://', parameters('storageAccountName'), '.blob.core.windows.net/')]",
                            "storageAccountAccessKey": "[listKeys(resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccountName')), providers('Microsoft.Storage', 'storageAccounts').apiVersions[0]).keys[0].value]"
                        }
                    }
                ]
            }
        ]
    }

资料来源：

blob存储审核配置来自这里：https://blogs.msdn.microsoft.com/azuresqldbsupport/2017/01/11/arm-template-turning-on-blob-auditing/

威胁检测资源配置来自这里(请注意，本例中的存储审核配置不适用于我)：https://blogs.msdn.microsoft.com/azuresqldbsupport/2017/01/11/arm-template-to-deploy-server-with-auditing-and-threat-detection-turned-on/