Azure SQL 数据库和 Azure Synapse Analytics 的审核Auditing for Azure SQL Database and Azure Synapse Analytics

适用于:是Azure SQL 数据库是Azure Synapse Analytics (SQL DW)APPLIES TO: yesAzure SQL Database yesAzure Synapse Analytics (SQL DW)

审核 Azure SQL 数据库Azure Synapse Analytics 可跟踪数据库事件,并将这些事件写入 Azure 存储帐户、Log Analytics 工作区或事件中心内的审核日志。Auditing for Azure SQL Database and Azure Synapse Analytics tracks database events and writes them to an audit log in your Azure storage account, Log Analytics workspace, or Event Hubs.

审核还可: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 信任中心,可以从中找到 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 Azure SQL compliance certifications.

备注

有关 Azure SQL 托管实例审核的信息,请参阅以下文章:SQL 托管实例审核入门For information on Azure SQL Managed Instance auditing, see the following article, Get started with SQL Managed Instance 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.

重要

Azure SQL 数据库审核已针对可用性和性能进行优化。Azure SQL Database auditing is optimized for availability and performance. 在活动量极高的情况下,Azure SQL 数据库或 Azure Synapse 允许操作继续进行,可能不会记录某些已审核的事件。During very high activity Azure SQL Database or Azure Synapse allows operations to proceed and may not record some audited events.

审核限制Auditing limitations

  • 目前不支持高级存储 。Premium storage is currently not supported.
  • Azure Data Lake Storage Gen2 存储帐户分层命名空间目前不受支持Hierarchical namespace for Azure Data Lake Storage Gen2 storage account is currently not supported.
  • 不支持在已暂停的 Azure Synapse 上启用审核。Enabling auditing on a paused Azure Synapse is not supported. 若要启用审核,请恢复运行 Azure Synapse。To enable auditing, resume Azure Synapse.

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

可为特定数据库定义审核策略,也可将审核策略定义为 Azure(托管 SQL 数据库或 Azure Synapse)中的默认服务器策略:An auditing policy can be defined for a specific database or as a default server policy in Azure (which hosts SQL Database or Azure Synapse):

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

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

  • 在数据库上启用审核以及在服务器上启用审核都不会替代或更改服务器审核的任何设置。Enabling auditing on the database, in addition to enabling it on the server, does not override or change any of the settings of the server 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.

    备注

    应避免同时启用服务器审核和数据库 blob 审核,除非:You should avoid enabling both server auditing and database blob auditing together, unless:

    • 需要对特定数据库使用不同的存储帐户、保持期或 Log Analytics 工作区。You want to use a different storage account, retention period or Log Analytics Workspace 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.

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

为服务器设置审核Set up auditing for your server

默认审核策略包括所有操作和下列操作组集合,将用于审核针对数据库执行的所有查询和存储过程以及成功和失败的登录: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 SQL 数据库和 Azure Synapse 审核在审核记录中存储字符字段的 4000 个字符的数据。Azure SQL Database and Azure Synapse 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 门户配置审核。The following section describes the configuration of auditing using the Azure portal.

备注

不能对已暂停的 Synapse SQL 池启用审核。Enabling auditing on a paused Synapse SQL pool is not possible. 若要启用审核,请取消暂停 Synapse SQL 池。To enable auditing, un-pause the Synapse SQL pool.

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

  2. 导航到“SQL 数据库”或“SQL Server”窗格中“安全性”标题下的“审核” 。Navigate to Auditing under the Security heading in your SQL database or SQL server 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. 你有多个选项,用于配置将在其中写入审核日志的位置。You have multiple options for configuring where audit logs will be written. 可将日志写入 Azure 存储帐户、写入 Log Analytics 工作区(供 Azure Monitor 日志(预览版)使用)或写入事件中心(通过事件中心(预览版)使用)。You can write logs to an Azure storage account, to a Log Analytics workspace for consumption by Azure Monitor logs (preview), or to event hub for consumption using event hub (preview). 可以将这些选项随意组合起来进行配置,审核日志会写入到每一个之中。You can configure any combination of these options, and audit logs will be written to each.

    存储选项

对存储目标的审核Audit to storage destination

若要配置将审核日志写入存储帐户的操作,请选择“存储”,打开“存储详细信息”。 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. Then click OK. 早于保留期的日志会被删除。Logs older than the retention period are deleted.

  • 保持期的默认值为 0(无限制保留)。The default value for retention period is 0 (unlimited retention). 在配置用于审核的存储帐户时,可以通过在“存储设置”中移动“保留期(天数)”滑块来更改此值。You can change this value by moving the Retention (Days) slider in Storage settings when configuring the storage account for auditing.

    • 如果将保留期从 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).

    存储帐户

备注Remarks

  • 审核日志将写入到 Azure 订阅的 Azure Blob 存储中的追加 BlobAudit logs are written to Append Blobs in an Azure Blob storage on your Azure subscription
  • 若要为服务器或数据库级审核事件配置不可变的日志存储,请遵循 Azure 存储提供的说明To configure an immutable log store for the server or database-level audit events, follow the instructions provided by Azure Storage. 确保在配置不可变的 blob 存储时,选择了“允许额外追加”。Make sure you have selected Allow additional appends when you configure the immutable blob storage.
  • 可以将审核日志写入到 VNet 或防火墙后面的 Azure 存储帐户。You can write audit logs to a an Azure Storage account behind a VNet or firewall. 有关具体说明,请参阅将审核写入 VNet 和防火墙后面的存储帐户For specific instructions see, Write audit to a storage account behind VNet and firewall.
  • 配置审核设置后,可打开新威胁检测功能,并配置电子邮件用于接收安全警报。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.
  • 有关日志格式、存储文件夹的层次结构和命名约定的详细信息,请参阅 Blob 审核日志格式参考For details about the log format, hierarchy of the storage folder and naming conventions, see the Blob Audit Log Format Reference.
  • 使用 AAD 身份验证时,失败的登录记录将不会出现在 SQL 审核日志中。When using AAD Authentication, failed logins records will not appear in the SQL audit log. 若要查看失败的登录审核记录,需要访问 Azure Active Directory 门户,该门户记录这些事件的详细信息。To view failed login audit records, you need to visit the Azure Active Directory portal, which logs details of these events.
  • 只读副本的审核会自动启用。Auditing on Read-Only Replicas is automatically enabled. 有关存储文件夹的层次结构、命名约定和日志格式的详细信息,请参阅 SQL 数据库审核日志格式For further details about the hierarchy of the storage folders, naming conventions, and log format, see the SQL Database Audit Log Format.

对 Log Analytics 目标的审核Audit to Log Analytics destination

若要配置将审核日志写入 Log Analytics 工作区的操作,请选择“Log Analytics (预览版)”,并打开“Log Analytics 详细信息”。 To configure writing audit logs to a Log Analytics workspace, select Log Analytics (Preview) and open Log Analytics details. 选择或创建要将日志写入到其中的 Log Analytics 工作区,然后单击“确定”。Select or create the Log Analytics workspace where logs will be written and then click OK.

LogAnalyticsworkspace

有关 Azure Monitor 日志工作区的更多详细信息,请参阅设计 Azure Monitor 日志部署For more details about Azure Monitor Logs Workspaces see Designing your Azure Monitor Logs deployment

对事件中心目标的审核Audit to Event Hub destination

若要配置将审核日志写入事件中心的操作,请选择“事件中心(预览版)”,打开“事件中心详细信息”。 To configure writing audit logs to an event hub, select Event Hub (Preview) and open Event Hub details. 选择要将日志写入到的事件中心,然后单击“确定”。Select the event hub where logs will be written and then click OK. 请确保事件中心与数据库和服务器位于同一区域。Be sure that the event hub is in the same region as your database and server.

Eventhub

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

如果选择将审核日志写入 Azure Monitor 日志:If you chose to write audit logs to Azure Monitor logs:

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

    查看审核日志

  • 然后,可以通过两种方式查看日志:Then, you have two ways to view the logs:

    单击“审核记录”页面顶部的“Log Analytics”会在 Log Analytics 工作区中打开“日志”视图,在其中可以自定义时间范围和搜索查询。 Clicking on Log Analytics at the top of the Audit records page will open the Logs view in Log Analytics workspace, where you can customize the time range and the search query.

    在 Log Analytics 工作区中打开

    单击“审核记录”页面顶部的“查看仪表板”会打开一个显示审核日志信息的仪表板,在其中可以深化到“安全见解”、“访问敏感数据”,等等。 Clicking View dashboard at the top of the Audit records page will open a dashboard displaying audit logs info, where you can drill down into Security Insights, Access to Sensitive Data and more. 此仪表板旨在帮助你获取数据的安全见解。This dashboard is designed to help you gain security insights for your data. 还可以自定义时间范围和搜索查询。You can also customize the time range and search query. 查看 Log Analytics 仪表板View Log Analytics Dashboard

    Log Analytics 仪表板

    Log Analytics 安全见解

  • 也可从 Log Analytics 边栏选项卡访问审核日志。Alternatively, you can also access the audit logs from Log Analytics blade. 打开 Log Analytics 工作区,然后在“常规”部分单击“日志”。 Open your Log Analytics workspace and under General section, click Logs. 一开始可以使用简单的查询(例如:搜索“SQLSecurityAuditEvents”)来查看审核日志。You can start with a simple query, such as: search "SQLSecurityAuditEvents" to view the audit logs. 在这里,还可以使用 Azure Monitor 日志来对审核日志数据运行高级搜索。From here, you can also use Azure Monitor logs to run advanced searches on your audit log data. 在 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.

如果已选择将审核日志写入到事件中心,请执行以下操作:If you chose to write audit logs to Event Hub:

  • 若要使用事件中心的审核日志数据,需设置一个流来使用事件并将其写入到目标。To consume audit logs data from Event Hub, you will need to set up a stream to consume events and write them to a target. 有关详细信息,请参阅 Azure 事件中心文档For more information, see Azure Event Hubs Documentation.
  • 事件中心内的审核日志在 Apache Avro 事件的主体中捕获,并使用带有 UTF-8 编码的 JSON 格式进行存储。Audit logs in Event Hub are captured in the body of Apache Avro events and stored using JSON formatting with UTF-8 encoding. 若要读取审核日志,可以使用 Avro 工具或处理此格式的类似工具。To read the audit logs, you can use Avro Tools or similar tools that process this format.

如果选择将审核日志写入到 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. 有关存储文件夹的层次结构、命名约定和日志格式的详细信息,请参阅 SQL 数据库审核日志格式For further details about the hierarchy of the storage folders, naming conventions, and log format, see the SQL Database Audit Log Format.

  • 使用 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.

      重要

      在数据库级审核中,辅助数据库的存储设置与主数据库相同,因而会导致生成跨区域流量。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).

管理 Azure SQL 数据库审核Manage Azure SQL Database auditing

使用 Azure PowerShellUsing 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 APIUsing REST API

REST APIREST API:

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

使用 Azure 资源管理器模板Using Azure Resource Manager templates

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

备注

链接的示例在外部公共存储库上并且“按现样”提供,不提供任何担保,并非在任何 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.