SQL 数据库审核入门Get started with SQL database auditing

审核 Azure SQL 数据库SQL 数据仓库会跟踪数据库事件,并将这些事件写入 Azure 存储帐户中的审核日志。Auditing for Azure SQL Database and SQL Data Warehouse 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 信任中心,可以从中找到 SQL 数据库符合性认证的最新列表。For more information about Azure programs that support standards compliance, see the Azure Trust Center where you can find the most current list of SQL Database compliance certifications.

Note

本主题适用于 Azure SQL 服务器,同时也适用于在 Azure SQL 服务器中创建的 SQL 数据库和 SQL 数据仓库数据库。This topic applies to Azure SQL server, and to both SQL Database and SQL Data Warehouse databases that are created on the Azure SQL server. 为简单起见,在提到 SQL 数据库和 SQL 数据仓库时,本文统称 SQL 数据库。For simplicity, SQL Database is used when referring to both SQL Database and SQL Data Warehouse.

Note

本文最近已更新,从使用术语“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 数据库审核概述Azure SQL database auditing overview

可使用 SQL 数据库审核来:You can use SQL database auditing to:

  • 保留 选定事件的审核痕迹。Retain an audit trail of selected events. 可以定义要审核的数据库操作的类别。You can define categories of database actions to be audited.
  • 报告 数据库活动。Report on database activity. 可以使用预配置的报告和仪表板快速开始使用活动和事件报告。You can use pre-configured reports and a dashboard to get started quickly with activity and event reporting.
  • 分析 报告。Analyze reports. 可以查找可疑事件、异常活动和趋势。You can find suspicious events, unusual activity, and trends.

Important

审核日志会写入到 Azure 订阅的 Azure Blob 存储中的追加 BlobAudit logs are written to Append Blobs in Azure Blob storage on your Azure subscription.

  • 支持所有存储类型(v1、v2、blob)。All storage kinds (v1, v2, blob) are supported.
  • 支持所有存储复制配置。All storage replication configurations are supported.
  • 目前不支持高级存储。Premium storage is currently not supported.
  • 目前不支持 VNet 中的存储。Storage in VNet is currently not supported.
  • 目前不支持“应用有防火墙的存储”。Storage behind a Firewall is currently not supported

定义服务器级和数据库级审核策略Define server-level vs. database-level auditing policy

可为特定数据库定义审核策略,也可将审核策略定义为默认服务器策略:An auditing policy can be defined for a specific database or as a default server policy:

  • 服务器策略适用于服务器上的所有现有数据库和新建数据库。A server policy applies to all existing and newly created databases on the server.

  • 如果启用服务器 blob 审核,它将一直应用于数据库。If server blob auditing is enabled, it always applies to the database. 将不考虑数据库审核设置审核数据库。The database will be audited, regardless of the database auditing settings.

  • 除在服务器上启用 blob 审核外,在数据库或数据仓库上启用 blob 审核也不会替代或更改服务器 blob 审核的任何设置。Enabling blob auditing on the database or data warehouse, in addition to enabling it on the server, does not override or change any of the settings of the server blob auditing. 这两种审核会并存。Both audits will exist side by side. 换言之,会并行对数据库执行两次审核;一次按服务器策略审核,一次按数据库策略审核。In other words, the database is audited twice in parallel; once by the server policy and once by the database policy.

    Note

    除非有以下需要,否则应该避免同时启用服务器 Blob 审核和数据库 Blob 审核:You should avoid enabling both server blob auditing and database blob auditing together, unless:

    • 需要对特定数据库使用不同的存储帐户或保留期。You want to use a different storage account or retention period for a specific database.
    • 对于与服务器上其他数据库不同的特定数据库,应审核事件类型或类别。You want to audit event types or categories for a specific database that differ from the rest of the databases on the server. 例如,可能拥有仅需要针对特定数据库进行审核的表插入。For example, you might have table inserts that need to be audited only for a specific database.

    否则,建议仅启用服务器级 blob 审核,并对所有数据库禁用数据库级审核。Otherwise, we recommended that you enable only server-level blob auditing and leave the database-level auditing disabled for all databases.

为数据库设置审核Set up auditing for your database

以下部分介绍如何使用 Azure 门户配置审核。The following section describes the configuration of auditing using the Azure portal.

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

  2. 导航到“SQL 数据库/服务器”窗格中“安全性”标题下的“审核”。Navigate to Auditing under the Security heading in your SQL database/server pane.

    导航窗格Navigation pane

  3. 如果想设置服务器审核策略,可以选择数据库审核页中的“查看服务器设置”链接。If you prefer to set up a server auditing policy, you can select the View server settings link on the database auditing page. 然后,可查看或修改服务器审核设置。You can then view or modify the server auditing settings. 服务器审核策略应用于此服务器上所有现有和新建数据库。Server auditing policies apply to all existing and newly created databases on this server.

    导航窗格

  4. 如果希望在数据库级别启用审核,请将“审核”切换到“启用”。If you prefer to enable auditing on the database level, switch Auditing to ON.

    如果启用了服务器审核,数据库配置的审核将与服务器审核并存。If server auditing is enabled, the database-configured audit will exist side-by-side with the server audit.

    导航窗格

  5. 若要配置将审核日志写入存储帐户的操作,请选择“存储”,打开“存储详细信息”。To configure writing audit logs to a storage account, select Storage and open Storage details. 依次选择要用于保存日志的 Azure 存储帐户以及保持期。Select the Azure storage account where logs will be saved, and then select the retention period. 将删除旧日志。The old logs will be deleted. Then click OK.

    存储帐户

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

  7. 若要自定义已审核的事件,可通过 PowerShell cmdletREST API 执行此操作。If you want to customize the audited events, you can do this via PowerShell cmdlets or the REST API.

  8. 配置审核设置后,可打开新威胁检测功能,并配置电子邮件用于接收安全警报。After you've configured your auditing settings, you can turn on the new threat detection feature and configure emails to receive security alerts. 使用威胁检测时,会接收针对异常数据库活动(可能表示潜在的安全威胁)发出的前瞻性警报。When you use threat detection, you receive proactive alerts on anomalous database activities that can indicate potential security threats. 有关详细信息,请参阅威胁检测入门For more information, see Getting started with threat detection.

Important

在 Azure SQL 数据仓库或在具有 Azure SQL 数据仓库的服务器上启用审核将导致数据仓库恢复,即使在以前被暂停的情况下也是如此。Enabling auditing on an Azure SQL Data Warehouse, or on a server that has an Azure SQL Data Warehouse on it, will result in the Data Warehouse being resumed, even in the case where it was previously paused. 请确保在启用审核后再次暂停数据仓库Please make sure to pause the Data Warehouse again after enabling auditing.'

分析审核日志和报告Analyze audit logs and reports

如果选择将审核日志写入到 Azure 存储帐户,可以使用多种方法来查看日志:If you chose to write audit logs to an Azure storage account, there are several methods you can use to view the logs:

  • 审核日志会在安装期间选择的帐户中进行聚合。Audit logs are aggregated in the account you chose during setup. 可使用 Azure 存储资源管理器等工具浏览审核日志。You can explore audit logs by using a tool such as Azure Storage Explorer. 在 Azure 存储中,审核日志以 Blob 文件集合的形式保存在名为 sqldbauditlogs 的容器中。In Azure storage, auditing logs are saved as a collection of blob files within a container named sqldbauditlogs. 有关存储文件夹层次、命名约定和日志格式的详细信息,请参阅 Blob 审核日志格式参考For further details about the hierarchy of the storage folder, naming conventions, and log format, see the Blob Audit Log Format Reference.

  • 使用 Azure 门户Use the Azure portal. 打开相关数据库。Open the relevant database. 在数据库的“审核”页的顶部,单击“查看审核日志”。At the top of the database's Auditing page, click View audit logs.

    导航窗格

    此时会打开“审核记录”,可在其中查看日志。Audit records opens, from which you'll be able to view the logs.

    • 可单击“审核记录”页顶部的“筛选”,查看特定的日期。You can view specific dates by clicking Filter at the top of the Audit records page.

    • 可以通过切换“审核源”在服务器审核策略和数据库审核策略创建的审核记录之间进行切换。You can switch between audit records that were created by the server audit policy and the database audit policy by toggling Audit Source.

    • 通过选中“仅显示 SQL 注入的审核记录”复选框,可以仅查看与 SQL 注入相关的审核记录。You can view only SQL injection related audit records by checking Show only audit records for SQL injections checkbox.

      导航窗格

  • 使用系统函数 sys.fn_get_audit_file (T-SQL) 以表格格式返回审核日志数据。Use the system function sys.fn_get_audit_file (T-SQL) to return the audit log data in tabular format. 有关使用此函数的详细信息,请参阅 sys.fn_get_audit_fileFor more information on using this function, see sys.fn_get_audit_file.

  • 使用 SQL Server Management Studio 中的“合并审核文件”选项(从 SSMS 17 开始):Use Merge Audit Files in SQL Server Management Studio (starting with SSMS 17):

    1. 在 SSMS 菜单中,选择“文件” > “打开” > “合并审核文件”。From the SSMS menu, select File > Open > Merge Audit Files.

      导航窗格

    2. 此时会打开“添加审核文件”对话框。The Add Audit Files dialog box opens. 通过“添加”选项,选择是合并本地磁盘中的审核文件还是从 Azure 存储中导入。Select one of the Add options to choose whether to merge audit files from a local disk or import them from Azure Storage. 需要提供 Azure 存储详细信息和帐户密钥。You are required to provide your Azure Storage details and account key.

    3. 添加要合并的所有文件后,单击“确定”完成合并操作。After all files to merge have been added, click OK to complete the merge operation.

    4. 合并的文件会在 SSMS 中打开,可在其中进行查看和分析,以及将其作为 XEL 或 CSV 文件导出或导出到表中。The merged file opens in SSMS, where you can view and analyze it, as well as export it to an XEL or CSV file, or to a table.

  • 使用 Power BI。Use Power BI. 可在 Power BI 中查看和分析审核日志数据。You can view and analyze audit log data in Power BI. 如需详细信息并访问可下载的模板,请参阅在 Power BI 中分析审核日志数据For more information and to access a downloadable template, see Analyze audit log data in Power BI.

  • 通过门户或使用 Azure 存储资源管理器等工具从 Azure 存储 blob 容器下载日志文件。Download log files from your Azure Storage blob container via the portal or by using a tool such as Azure Storage Explorer.

    • 在本地下载日志文件后,可双击打开文件,然后在 SSMS 中查看和分析日志。After you have downloaded a log file locally, double-click the file to open, view, and analyze the logs in SSMS.
    • 也可通过 Azure 存储资源管理器同时下载多个文件。You can also download multiple files simultaneously via Azure Storage Explorer. 为此,请右键单击特定子文件夹,然后选择“另存为”,以便在本地文件夹中进行保存。To do so, right-click a specific subfolder and select Save as to save in a local folder.
  • 其他方法:Additional methods:

    • 下载多个文件或包含日志文件的子文件夹后,可以按照前述 SSMS 合并审核文件说明在本地合并它们。After downloading several files or a subfolder that contains log files, you can merge them locally as described in the SSMS Merge Audit Files instructions described previously.

    • 以编程方式查看 blob 审核日志:View blob auditing logs programmatically:

生产做法Production practices

审核异地复制的数据库Auditing geo-replicated databases

通过异地复制数据库,在主数据库上启用审核时,辅助数据库将有相同的审核策略。With geo-replicated databases, when you enable auditing on the primary database the secondary database will have an identical auditing policy. 还可以在独立于主数据库的“辅助服务器”上启用审核,从而在辅助数据库上设置审核。It is also possible to set up auditing on the secondary database by enabling auditing on the secondary server, independently from the primary database.

  • 服务器级(推荐):同时在主服务器和辅助服务器上启用审核 - 基于各自的服务器级策略,将分别对主数据库和辅助数据库进行审核。Server-level (recommended): Turn on auditing on both the primary server as well as the secondary server - the primary and secondary databases will each be audited independently based on their respective server-level policy.
  • 数据库级:辅助数据库的数据库级审核只能从主数据库审核设置进行配置。Database-level: Database-level auditing for secondary databases can only be configured from Primary database auditing settings.
    • 必须在主数据库本身上启用审核,而不是在服务器上启用。Auditing must be enabled on the primary database itself, not the server.

    • 在主数据库上启用审核后,也会在辅助数据库上启用审核。After auditing is enabled on the primary database, it will also become enabled on the secondary database.

      Important

      在数据库级审核中,辅助数据库的存储设置与主数据库相同,因而会导致生成跨区域流量。With database-level auditing, the storage settings for the secondary database will be identical to those of the primary database, causing cross-regional traffic. 建议仅启用服务器级审核,并对所有数据库禁用数据库级审核。We recommend that you enable only server-level auditing, and leave the database-level auditing disabled for all databases.

重新生成存储密钥Storage key regeneration

在生产环境中,可能会定期刷新存储密钥。In production, you are likely to refresh your storage keys periodically. 如果向 Azure 存储写入审核日志,则需在刷新密钥时重新保存审核策略。When writing audit logs to Azure storage, you need to resave your auditing policy when refreshing your keys. 过程如下:The process is as follows:

  1. 打开“存储详细信息”。Open Storage Details. 在“存储访问密钥”框中,选择“辅助”并单击“确定”。In the Storage Access Key box, select Secondary, and click OK. 然后单击“审核配置”页顶部的“保存”。Then click Save at the top of the auditing configuration page.

    导航窗格

  2. 转到存储配置页,重新生成主访问密钥。Go to the storage configuration page and regenerate the primary access key.

    导航窗格

  3. 返回“审核配置”页,将“存储访问密钥”从“辅助”切换为“主要”,然后单击“确定”。Go back to the auditing configuration page, switch the storage access key from secondary to primary, and then click OK. 然后单击“审核配置”页顶部的“保存”。Then click Save at the top of the auditing configuration page.

  4. 返回“存储配置”页并重新生成辅助访问密钥(为下一个密钥刷新周期做好准备)。Go back to the storage configuration page and regenerate the secondary access key (in preparation for the next key's refresh cycle).

其他信息Additional Information

  • 有关日志格式、存储文件夹的层次结构和命名约定的详细信息,请参阅 Blob 审核日志格式参考For details about the log format, hierarchy of the storage folder and naming conventions, see the Blob Audit Log Format Reference.

    Important

    Azure SQL 数据库审核在审核记录中存储字符字段的 4000 个字符的数据。Azure SQL Database Audit stores 4000 characters of data for character fields in an audit record. 当可审核操作返回的语句data_sensitivity_information 值包含超过 4000 个的字符时,超出前 4000 个字符的任何数据将被截去不进行审核When the statement or the data_sensitivity_information values returned from an auditable action contain more than 4000 characters, any data beyond the first 4000 characters will be truncated and not audited.

  • 审核日志会写入到 Azure 订阅的 Azure Blob 存储中的追加 Blob。Audit logs are written to Append Blobs in an Azure Blob storage on your Azure subscription:

    • 追加 Blob 目前不支持高级存储。Premium Storage is currently not supported by Append Blobs.
    • 目前不支持 VNet 中的存储。Storage in VNet is currently not supported.
  • 默认审核策略包括所有操作和下列操作组集合,将用于审核针对数据库执行的所有查询和存储过程以及成功和失败的登录:The default auditing policy includes all actions and the following set of action groups, which will audit all the queries and stored procedures executed against the database, as well as successful and failed logins:

    BATCH_COMPLETED_GROUPBATCH_COMPLETED_GROUP
    SUCCESSFUL_DATABASE_AUTHENTICATION_GROUPSUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
    FAILED_DATABASE_AUTHENTICATION_GROUPFAILED_DATABASE_AUTHENTICATION_GROUP

    可以按照使用 Azure PowerShell 管理 SQL 数据库审核部分中所述,使用 PowerShell 配置不同类型的操作和操作组的审核。You can configure auditing for different types of actions and action groups using PowerShell, as described in the Manage SQL database auditing using Azure PowerShell section.

使用 Azure PowerShell 管理 SQL 数据库审核Manage SQL database auditing using Azure PowerShell

PowerShell cmdlet(包括支持使用 WHERE 子句进行其他筛选)PowerShell cmdlets (including WHERE clause support for additional filtering):

有关脚本示例,请参阅使用 PowerShell 配置审核和威胁检测For a script example, see Configure auditing and threat detection using PowerShell.

使用 REST API 管理 SQL 数据库审核Manage SQL database auditing using REST API

REST APIREST API:

支持使用 WHERE 子句执行附加筛选的扩展策略:Extended policy with WHERE clause support for additional filtering:

使用 ARM 模板管理 SQL 数据库审核Manage SQL database auditing using ARM templates

可以使用 Azure 资源管理器模板管理 Azure SQL 数据库审核,如以下示例中所示:You can manage Azure SQL database auditing using Azure Resource Manager templates, as shown in these examples:

Note

链接的示例在外部公共存储库上并且“按现样”提供,不提供任何担保,并非在任何 Azure 支持计划/服务下都受支持。The linked samples are on an external public repository and are provided 'as is', without warranty, and are not supported under any Azure support program/service.