Azure SQL 托管实例审核入门Get started with Azure SQL Managed Instance auditing

适用于:是Azure SQL 托管实例 APPLIES TO: yesAzure SQL Managed Instance

Azure SQL 托管实例审核会跟踪数据库事件,并将事件写入 Azure 存储帐户中的审核日志。Azure SQL Managed Instance auditing tracks database events and writes them to an audit log in your Azure storage account. 审核还可:Auditing also:

  • 帮助保持合规性、了解数据库活动,以及深入了解可以指明业务考量因素或疑似安全违规的偏差和异常。Helps you maintain regulatory compliance, understand database activity, and gain insight into discrepancies and anomalies that could indicate business concerns or suspected security violations.
  • 实现并促进遵从合规标准,但不能保证合规性。Enables and facilitates adherence to compliance standards, although it doesn't guarantee compliance. 有关支持标准符合性的 Azure 程序的详细信息,请参阅 Azure 信任中心,可以从中找到符合性认证的最新列表。For more information about Azure programs that support standards compliance, see the Azure Trust Center where you can find the most current list of compliance certifications.

将服务器的审核设置为 Azure 存储Set up auditing for your server to Azure Storage

以下部分介绍了托管实例上的审核配置。The following section describes the configuration of auditing on your managed instance.

  1. 转到 Azure 门户Go to the Azure portal.

  2. 创建一个用于存储审核日志的 Azure 存储容器Create an Azure Storage container where audit logs are stored.

    1. 导航到要在其中存储审核日志的 Azure 存储帐户。Navigate to the Azure storage account where you would like to store your audit logs.

      重要

      • 请使用与托管实例位于同一区域中的存储帐户,以避免跨区域读取/写入。Use a storage account in the same region as the managed instance to avoid cross-region reads/writes.
      • 如果你的存储帐户位于虚拟网络或防火墙后面,请参阅授予从虚拟网络进行访问的权限If your storage account is behind a Virtual Network or a Firewall, please see Grant access from a virtual network.
      • 请注意,如果将保留期从 0(无限期保留)更改为任何其他值,则该保留期将仅应用于在更改保留期值后所写入的日志(即使启用了上述保留期,在保留期值设置为“无限制”期间所写入的日志仍将无限期保留)。If you change retention period from 0 (unlimited retention) to any other value, please note that retention will only apply to logs written after retention value was changed (logs written during the period when retention was set to unlimited are preserved, even after retention is enabled).
    2. 在存储帐户中,转到“概述”,然后单击“Blob”。 In the storage account, go to Overview and click Blobs.

      Azure Blob 小组件

    3. 在顶部菜单中,单击“+ 容器”以创建新容器。In the top menu, click + Container to create a new container.

      创建 Blob 容器图标

    4. 提供一个容器名称,将公共访问级别设置为“专用”,然后单击“确定” 。Provide a container Name, set Public access level to Private, and then click OK.

      创建 Blob 容器配置

    重要

    如果客户希望为服务器级或数据库级审核事件配置不可变的日志存储,则应遵循 Azure 存储提供的说明Customers wishing to configure an immutable log store for their server- or database-level audit events should follow the instructions provided by Azure Storage. (请确保在配置不可变的 blob 存储时,选择了“允许额外追加”。)(Please ensure you have selected Allow additional appends when you configure the immutable blob storage.)

  3. 为审核日志创建容器后,可通过两种方式将其配置为审核日志的目标:使用 T-SQL,或使用 SQL Server Management Studio (SSMS) UIAfter you create the container for the audit logs, there are two ways to configure it as the target for the audit logs: using T-SQL or using the SQL Server Management Studio (SSMS) UI:

    • 使用 T-SQL 为审核日志配置 Blob 存储:Configure blob storage for audit logs using T-SQL:

      1. 在容器列表中,单击新创建的容器,然后单击“容器属性”。In the containers list, click the newly created container and then click Container properties.

        Blob 容器属性按钮

      2. 通过单击复制图标来复制容器 URL并保存该 URL(例如在记事本中)供将来使用。Copy the container URL by clicking the copy icon and save the URL (for example, in Notepad) for future use. 容器 URL 格式应当为 https://<StorageName>.blob.core.chinacloudapi.cn/<ContainerName>The container URL format should be https://<StorageName>.blob.core.chinacloudapi.cn/<ContainerName>

        Blob 容器复制 URL

      3. 生成一个 Azure 存储 SAS 令牌,用于向存储帐户授予托管实例审核访问权限:Generate an Azure Storage SAS token to grant managed instance auditing access rights to the storage account:

        • 导航到在前面的步骤中你在其中创建了容器的 Azure 存储帐户。Navigate to the Azure storage account where you created the container in the previous step.

        • 在“存储设置”菜单中单击“共享访问签名” 。Click on Shared access signature in the Storage Settings menu.

          存储设置菜单中的“共享访问签名”图标

        • 如下所述配置 SAS:Configure the SAS as follows:

          • 允许的服务:BlobAllowed services: Blob

          • 开始日期:为避免与时区相关的问题,请使用昨天的日期Start date: to avoid time zone-related issues, use yesterday's date

          • 结束日期:选择此 SAS 令牌的到期日期End date: choose the date on which this SAS token expires

            备注

            在到期时续订令牌,以避免审核失败。Renew the token upon expiry to avoid audit failures.

          • 单击“生成 SAS”。Click Generate SAS.

            SAS 配置

        • SAS 令牌会显示在底部。The SAS token appears at the bottom. 通过单击复制图标来复制令牌并保存该令牌(例如在记事本中)供将来使用。Copy the token by clicking on the copy icon, and save it (for example, in Notepad) for future use.

          复制 SAS 令牌

          重要

          从令牌的开头删除问号(“?”)字符。Remove the question mark ("?") character from the beginning of the token.

      4. 通过 SQL Server Management Studio 或任何其他支持的工具连接到托管实例。Connect to your managed instance via SQL Server Management Studio or any other supported tool.

      5. 执行以下 T-SQL 语句来使用你在前面的步骤中创建的容器 URL 和 SAS 令牌创建新凭据:Execute the following T-SQL statement to create a new credential using the container URL and SAS token that you created in the previous steps:

        CREATE CREDENTIAL [<container_url>]
        WITH IDENTITY='SHARED ACCESS SIGNATURE',
        SECRET = '<SAS KEY>'
        GO
        
      6. 执行以下 T-SQL 语句来创建新的服务器审核(请选择自己的审核名称,并使用在前面步骤中创建的容器 URL)。Execute the following T-SQL statement to create a new server audit (choose your own audit name, and use the container URL that you created in the previous steps). 如果未指定,则 RETENTION_DAYS 默认为 0(无限期保留):If not specified, the RETENTION_DAYS default is 0 (unlimited retention):

        CREATE SERVER AUDIT [<your_audit_name>]
        TO URL ( PATH ='<container_url>' , RETENTION_DAYS =  integer )
        GO
        

        继续创建服务器审核规范或数据库审核规范Continue by creating a server audit specification or database audit specification.

    • 使用 SQL Server Management Studio 18(预览版)为审核日志配置 Blob 存储:Configure blob storage for audit logs using SQL Server Management Studio 18 (Preview):

      1. 使用 SQL Server Management Studio UI 连接到托管实例。Connect to the managed instance using the SQL Server Management Studio UI.

      2. 展开对象资源管理器的根节点。Expand the root note of Object Explorer.

      3. 展开“安全性”节点,右键单击“审核”节点,然后单击“新建审核” :Expand the Security node, right-click on the Audits node, and click on New Audit:

        展开“安全性”和“审核”节点

      4. 确保“审核目标”中已选择“URL”,然后单击“浏览” :Make sure URL is selected in Audit destination and click on Browse:

        浏览 Azure 存储

      5. (可选)登录到 Azure 帐户:(Optional) Sign in to your Azure account:

        登录 Azure

      6. 从下拉列表中选择订阅、存储帐户和 Blob 容器,或者单击“创建”来创建自己的容器。Select a subscription, storage account, and blob container from the dropdowns, or create your own container by clicking on Create. 完成后,单击“确定”:Once you have finished, click OK:

        选择 Azure 订阅、存储帐户和 Blob 容器

      7. 在“创建审核”对话框中单击“确定” 。Click OK in the Create Audit dialog.

  4. 将 Blob 容器配置为审核日志的目标后,创建并启用服务器审核规范或数据库审核规范(就像对 SQL Server 操作一样):After you configure the blob container as target for the audit logs, create and enable a server audit specification or database audit specification as you would for SQL Server:

  5. 启用在步骤 3 中创建的服务器审核:Enable the server audit that you created in step 3:

    ALTER SERVER AUDIT [<your_audit_name>]
    WITH (STATE=ON);
    GO
    

了解更多信息:For additional information:

将服务器的审核设置为事件中心或 Azure Monitor 日志Set up auditing for your server to Event Hubs or Azure Monitor logs

可将托管实例的审核日志发送到 Azure 事件中心或 Azure Monitor 日志。Audit logs from a managed instance can be sent to Azure Event Hubs or Azure Monitor logs. 本部分介绍如何进行以下配置:This section describes how to configure this:

  1. Azure 门户中导航到托管实例。Navigate in the Azure portal to the managed instance.

  2. 单击“诊断设置”。Click on Diagnostic settings.

  3. 单击“启用诊断”。Click on Turn on diagnostics. 如果已启用诊断,则会显示“+ 添加诊断设置”。If diagnostics is already enabled, +Add diagnostic setting will show instead.

  4. 在日志列表中选择“SQLSecurityAuditEvents”。Select SQLSecurityAuditEvents in the list of logs.

  5. 选择审核事件的目标:事件中心、Azure Monitor 日志或两者。Select a destination for the audit events: Event Hubs, Azure Monitor logs, or both. 为每个目标配置所需的参数(例如,Log Analytics 工作区)。Configure for each target the required parameters (e.g. Log Analytics workspace).

  6. 单击“保存” 。Click Save.

    配置诊断设置

  7. 使用 SQL Server Management Studio (SSMS) 或任何其他支持的客户端连接到托管实例。Connect to the managed instance using SQL Server Management Studio (SSMS) or any other supported client.

  8. 执行下面的 T-SQL 语句,创建服务器审核:Execute the following T-SQL statement to create a server audit:

    CREATE SERVER AUDIT [<your_audit_name>] TO EXTERNAL_MONITOR;
    GO
    
  9. 像对 SQL Server 操作一样创建并启用服务器审核规范或数据库审核规范:Create and enable a server audit specification or database audit specification as you would for SQL Server:

  10. 启用在步骤 8 中创建的服务器审核:Enable the server audit created in step 8:

    ALTER SERVER AUDIT [<your_audit_name>]
    WITH (STATE=ON);
    GO
    

使用审核日志Consume audit logs

使用存储在 Azure 存储中的日志Consume logs stored in Azure Storage

可使用多种方法查看 blob 审核日志。There are several methods you can use to view blob auditing logs.

使用在事件中心中存储的日志Consume logs stored in Event Hubs

若要使用事件中心的审核日志数据,需设置一个流来使用事件并将其写入到目标。To consume audit logs data from Event Hubs, you will need to set up a stream to consume events and write them to a target. 有关详细信息,请参阅 Azure 事件中心文档。For more information, see the Azure Event Hubs documentation.

使用和分析存储在 Azure Monitor 日志中的日志Consume and analyze logs stored in Azure Monitor logs

如果将审核日志写入到 Azure Monitor 日志,则可以在 Log Analytics 工作区中使用它们,可以在其中对审核数据运行高级搜索。If audit logs are written to Azure Monitor logs, they are available in the Log Analytics workspace, where you can run advanced searches on the audit data. 首先,导航到 Log Analytics 工作区。As a starting point, navigate to the Log Analytics workspace. 在“常规”部分下,单击“日志”,然后输入一个简单的查询(例如 search "SQLSecurityAuditEvents")以查看审核日志 。Under the General section, click Logs and enter a simple query, such as: search "SQLSecurityAuditEvents" to view the audit logs.

有了 Azure Monitor 日志,就可以使用集成的搜索和自定义仪表板来轻松分析所有工作负荷和服务器上的数百万记录,获得实时操作见解。Azure Monitor logs gives you real-time operational insights using integrated search and custom dashboards to readily analyze millions of records across all your workloads and servers. 有关 Azure Monitor 日志搜索语言和命令的其他有用信息,请参阅 Azure Monitor 日志搜索参考For additional useful information about Azure Monitor logs search language and commands, see Azure Monitor logs search reference.

备注

本文最近已更新,从使用术语“Log Analytics”改为使用术语“Azure Monitor 日志”。This article was recently updated to use the term Azure Monitor logs instead of Log Analytics. 日志数据仍然存储在 Log Analytics 工作区中,并仍然由同一 Log Analytics 服务收集并分析。Log data is still stored in a Log Analytics workspace and is still collected and analyzed by the same Log Analytics service. 我们正在更新术语,以便更好地反映 Azure Monitor 中日志的角色。We are updating the terminology to better reflect the role of logs in Azure Monitor. 有关详细信息,请参阅 Azure Monitor 术语更改See Azure Monitor terminology changes for details.

Azure SQL 托管实例中的数据库与 SQL Server 中的数据库之间的审核差异Auditing differences between databases in Azure SQL Managed Instance and databases in SQL Server

在审核 Azure SQL 托管实例和 SQL Server 中的数据库方面,主要差异是:The key differences between auditing in databases in Azure SQL Managed Instance and databases in SQL Server are:

  • 使用 Azure SQL 托管实例,审核在服务器级别进行,并在 Azure Blob 存储中存储 .xel 日志文件。With Azure SQL Managed Instance, auditing works at the server level and stores .xel log files in Azure Blob storage.
  • 在 SQL Server 中,审核也在服务器级别执行,但在文件系统/Windows 事件日志中存储事件。In SQL Server, audit works at the server level, but stores events on files system/windows event logs.

托管实例中的 XEvent 审核支持 Azure Blob 存储目标。XEvent auditing in managed instances supports Azure Blob storage targets. 不支持文件和 Windows 日志。File and windows logs are not supported.

Azure Blob 存储审核的主要 CREATE AUDIT 语法差异为:The key differences in the CREATE AUDIT syntax for auditing to Azure Blob storage are:

  • 提供了新语法 TO URL,该语法允许指定用于放置 .xel 文件的 Azure Blob 存储容器的 URL。A new syntax TO URL is provided and enables you to specify the URL of the Azure Blob storage container where the .xel files are placed.
  • 提供了新语法 TO EXTERNAL MONITOR 以启用事件中心和 Azure Monitor 日志这两个目标。A new syntax TO EXTERNAL MONITOR is provided to enable Event Hubs and Azure Monitor log targets.
  • 不支持语法 TO FILE,因为 Azure SQL 托管实例无法访问 Windows 文件共享。The syntax TO FILE is not supported because Azure SQL Managed Instance cannot access Windows file shares.
  • 不支持关闭选项。Shutdown option is not supported.
  • 不支持queue_delay 为 0。queue_delay of 0 is not supported.

后续步骤Next steps