40、SQL Server审计与扩展事件全面解析

SQL Server审计与扩展事件全面解析

1. SQL Server审计基础操作

在SQL Server中进行审计配置时，首先要选择审计的对象类，这将决定可选择的对象名称类型。比如选择“Database”，对象名称下拉列表就会显示所有可用的数据库，模式和对象的选择也是类似的。确定要审计的对象名称后，再选择要监控的主体名称，点击省略号会显示可用主体列表，从中选择要审计的主体名称，甚至可以选择数据库角色。完成这些选项设置后，点击“OK”创建数据库审计规范。

创建好数据库审计规范后，需要启用它。具体操作是导航到刚创建的数据库审计规范，右键点击并选择“Enable”。同时，也要启用与之关联的审计，导航到该审计，右键点击并选择“Enable”。

使用SQL Server Management Studio查看审计文件时，导航到“Server Security”文件夹下的“Audits”文件夹，右键点击要查看的审计，选择“View Audit Logs”，还可以选择多个审计，便于关联多个审计中的事件发生时间。

2. 审计相关的启动和关闭问题

在设置SQL Server审计前，要知道审计问题可能导致SQL Server实例启动或停止出现故障。
- 启动失败 ：当“ON_Failure = On”或“Shutdown Server on Audit Log Failure”选项被选中时，如果SQL Server无法初始化审计目标或无法写入审计目标，就无法启动。启动时，它会尝试启动所有已启用的审计，若失败，事件查看器会显示类似如下的错误信息：

“The server was stopped because SQL Server Audit 'Audit-20111009-163145' is configured to shut down on failure. To troubleshoot this issue, use the -m flag (Single User Mode) to bypass Audit-generated shutdowns when the server is starting.”

审计目标无法初始化可能有多种原因，如设置“Reserve_Disk_Space”为“on”时，驱动器可能不允许分配所需的全部空间，或者审计文件存储的文件结构发生了变化等。若遇到这种情况，可以使用以下命令从命令行启动SQL Server：

sqlservr.exe –f –s < instance name>

其中，实例名称是可选的，默认值为“MSSQLServer”。“-f”选项会以最小配置模式启动SQL Server，这样就可以禁用或修复有问题的审计目标。

• 强制关闭 ：如果SQL Server因无法写入日志而关闭，会在错误日志中写入“MSG_AUDIT_FORCED_SHUTDOWN”事件。在问题解决之前，SQL Server将无法启动。要绕过此问题，可以使用以下命令从命令行启动SQL Server：

sqlservr.exe -m -s<instance name>

同样，实例名称是可选的。使用“-m”以单用户模式启动SQL Server，这样就可以禁用审计或更改关闭选项以继续操作。

3. 有用的SQL Server审计对象

• sys.dm_server_audit_status ：列出所有SQL Server审计的信息，包含审计的当前状态、状态更改的UTC时间，对于文件审计，还包括文件的位置和大小。
• Fn_get_audit_file ：用于读取审计文件。该函数需要三个参数：路径和文件名（可以包含通配符）、开始读取的初始文件名以及审计文件偏移量（指示函数从哪个点开始读取）。最后两个参数可以指定为默认值或“null”。函数的输出是按操作发生时间排序的审计记录列表。

4. 其他审计方法

SQL Server 2012提供了其他审计方法，可根据业务需求选择：
| 审计方法 | 用途 |
| ---- | ---- |
| DDL Triggers | 跟踪在SQL Server中创建、修改或删除对象的用户 |
| SQL Server Profiler | 识别登录和退出SQL Server的用户、对象的创建、修改和删除、数据库增长、错误和警告的发生、数据库和服务器角色的管理等 |
| Login Auditing | 对用户登录或登录尝试进行不同级别的审计，可选“Failed Logins Only”、“Successful Logins Only”或“Both Failed and Successful Logins” |
| C2 Audit Tracing | 记录访问对象和语句的成功和失败尝试 |

5. 扩展事件接口概述

扩展事件是SQL Server 2008引入的一种极其灵活且开销较低的事件处理系统。默认情况下，SQL Server 2008中有一个名为“system_health”的事件会话在运行，它会跟踪产品支持团队认为有用的信息。扩展事件的基础设施是基于诊断系统不应阻止用户工作负载运行的前提构建的。即使因配置不当给服务器增加了过多开销，服务器也应该有足够的响应能力来禁用该会话。

在SQL Server 2012添加扩展事件用户界面之前，创建事件会话需要依赖DDL语句与会话交互，并搜索动态管理视图（DMV）来确定如何配置会话。配置好会话后，还需要熟悉XML解析才能理解输出结果，因此在SQL Server 2008中，扩展事件的使用并不广泛。

6. 扩展事件的关键术语

• 事件（Events） ：代码中的已知点，例如“sp_statement_starting”事件在存储过程中的语句开始时发生，“sp_statement_completed”事件在存储过程中的语句完成时发生。
• 目标（Targets） ：消耗事件，是事件数据的目的地，如可以将事件数据发送到文件系统或环形缓冲区（SQL Server内部收集服务器信息的方式）。
• 谓词（Predicates） ：用于过滤事件的布尔逻辑，除了过滤，还可以统计事件发生的次数，并在指定次数后才允许事件被消耗。
• 操作（Actions） ：在事件被消耗之前对其执行的活动，主要用于向事件追加数据。事件为提高效率只包含最少信息，因此需要收集的额外信息可通过添加操作来指定，但操作是同步执行的，可能会带来较高开销，需谨慎使用。
• 映射（Maps） ：用于将内部值转换为描述性数据，以提供给事件消费者。可以通过查询“sys.dm_xe_map_values”DMV查看映射。
• 类型（Types） ：扩展事件引擎用于解释事件数据中元素的数据类型。
• 通道（Channels） ：根据事件的功能或关注领域对事件进行分组，事件属于以下四个通道之一：Admin、Analytical、Debug和Operational。
• 包（Packages） ：扩展事件中暴露对象的容器，包含事件、目标、谓词、操作、映射和类型。

7. SQL Trace与扩展事件的关系

从SQL Server 2012开始，SQL Trace已正式被弃用，但它不会马上消失。扩展事件虽然学习曲线较高，但提供了更详细的信息且对服务器的影响更小。

SQL Server 2012添加了新的映射表“sys.trace_xe_event_map”和“sys.trace_xe_action_map”，用于关联SQL Trace中的事件和扩展事件中的对应事件，以及SQL Trace列名和扩展事件操作。

• 关联事件的查询示例：

SELECT * 
FROM sys.trace_xe_event_map txem JOIN  
     sys.trace_events te ON  
   txem.trace_event_id = te.trace_event_id

• 关联列名和操作的查询示例：

SELECT * 
FROM sys.trace_xe_action_map txam JOIN 
     sys.trace_columns tc ON  
   txam.trace_column_id = tc.trace_column_id

此外，有两种方法可以从现有的SQL Trace会话创建扩展事件会话：一是参考Books Online中的“Convert an Existing SQL Trace Script to an Extended Events Session”描述的手动过程；二是参考MSDN扩展事件博客中的“Migrating from SQL Trace to Extended Events”，其中包含一个CLR过程可自动生成输出。

8. 创建事件会话

可以使用“New Session Wizard”、“New Session Dialog”或编写自定义T - SQL命令来创建事件会话。下面以“New Session Dialog”为例，创建一个捕获系统中运行时间超过30秒的所有存储过程的事件会话：
1. 展开SSMS中“Management”节点下的“Extended Events”节点，右键点击“Sessions”文件夹，选择“New Session…”，进入“New Session General”页面。
2. 给会话命名，如“Long Running Procedures”。“Template”下拉列表可使用预定义事件的模板，此例可留空。
3. 在“Schedule”部分，可选择在服务器启动时自动启动会话，也可选择创建后立即启动，选择后者可立即在SSMS中查看捕获的数据。
4. “Causality Tracking”选项可通过为每个触发的事件分配一个由GUID和序列号组成的唯一ID，来关联多个任务之间的工作。
5. 选择“Events”页面，使用以下查询确定需要捕获的事件：

SELECT *  
FROM sys.trace_xe_event_map xem JOIN 
sys.trace_events te on te.trace_event_id = xem.trace_event_id 
WHERE te.name = 'SP:Completed'

结果表明需要捕获“module_end”事件。在“Event library”文本框中过滤事件，选择“module_end”，使用“>”箭头将其移至“Selected events”列表，然后选择“Configure”查看事件配置选项的“Global Fields (Actions)”选项卡。
6. 在“Global Fields (Actions)”选项卡中选择要为每个事件收集的操作或额外信息。
7. 选择“Filter (Predicate)”选项卡，从“Field”下拉列表中选择“duration”，“Operator”选择“greater than”，在“Value”字段中输入“30000000”（持续时间以微秒为单位），以过滤掉运行时间为30秒或更短的存储过程。
8. 选择“Event Fields”选项卡，可选择要捕获的可选数据，如选中“statement”复选框以捕获触发事件的语句文本。
9. 选择“Data Storage”页面，选择事件的目标并配置其属性。可选的目标有：
- etw_classic_sync_target：将扩展事件输出缓冲到Windows事件跟踪（ETW），以便关联SQL Server和操作系统事件。
- event_counter：内存中的目标，跟踪会话运行期间事件发生的次数。
- event_file：将事件输出写入磁盘，默认文件扩展名为“XEL”。
- histogram：内存中的目标，根据数据字段或操作对事件计数进行分组。
- pair_matching：内存中的目标，根据目标配置跟踪没有对应事件的事件。
- ring_buffer：内存中的目标，根据目标配置保存有限的事件数据。在SQL Server 2008中，受“max_memory”限制，默认值为4 MB；在SQL Server 2012中，“max_memory”默认设置为无限制，现在受“max_events_limit”限制，默认值为1000。
此例选择“event_file”目标并配置相应属性。
10. 选择“Advanced”页面，设置事件保留模式、最大调度延迟和最大内存大小等选项。事件保留模式可选择“Single event loss”（默认，在性能影响和事件丢失之间提供良好平衡）、“Multiple event loss”（对系统影响较小，但可能丢失多个事件）或“No event loss”（确保捕获所有事件，但不推荐，因为某些事件可能不支持）。

通过以上步骤，你可以完成一个扩展事件会话的创建，从而更好地监控SQL Server的活动。

SQL Server审计与扩展事件全面解析

9. 事件会话创建流程总结

为了更清晰地展示使用“New Session Dialog”创建事件会话的过程，下面用mermaid流程图进行总结：

graph LR
    A[展开Extended Events节点] --> B[右键点击Sessions文件夹]
    B --> C[选择New Session…]
    C --> D[进入New Session General页面]
    D --> E[命名会话]
    E --> F[选择Template（可留空）]
    F --> G[设置Schedule]
    G --> H[选择是否启用Causality Tracking]
    H --> I[进入Events页面]
    I --> J[查询确定捕获事件]
    J --> K[选择module_end事件]
    K --> L[配置Global Fields (Actions)选项卡]
    L --> M[配置Filter (Predicate)选项卡]
    M --> N[配置Event Fields选项卡]
    N --> O[进入Data Storage页面]
    O --> P[选择目标并配置属性]
    P --> Q[进入Advanced页面]
    Q --> R[设置事件保留模式等选项]

10. 事件会话各页面配置要点

页面名称	配置要点
New Session General	命名会话，选择模板（可选），设置启动时间，考虑是否启用因果跟踪
Events	查询确定要捕获的事件，选择事件并配置全局字段（操作）
Filter (Predicate)	设置过滤条件，如根据持续时间过滤存储过程
Event Fields	选择要捕获的可选数据
Data Storage	选择事件目标并配置属性，目标包括etw_classic_sync_target、event_counter等
Advanced	设置事件保留模式、最大调度延迟和最大内存大小等

11. 事件会话配置注意事项

在创建和配置事件会话时，有一些重要的注意事项需要牢记：
- 操作开销 ：操作（Actions）虽然可以为事件添加额外信息，但由于是同步执行的，可能会带来较高的开销。因此，在选择操作时要谨慎，只选择真正需要的信息。例如，如果不需要数据库名称，就不要选择添加“database_name”操作。
- 事件保留模式 ：不同的事件保留模式对性能和事件捕获有不同的影响。“Single event loss”是默认模式，在大多数情况下能在性能和事件丢失之间取得较好的平衡；“Multiple event loss”对系统影响较小，但可能会丢失多个事件，不利于详细分析；“No event loss”虽然能确保捕获所有事件，但可能会对性能产生较大影响，并且某些事件可能不支持该模式。
- 目标选择 ：不同的目标适用于不同的场景。例如，如果需要关联SQL Server和操作系统事件，可以选择“etw_classic_sync_target”；如果只是想统计事件发生的次数，“event_counter”是一个不错的选择。在选择目标时，要根据实际需求进行合理选择。

12. 审计与扩展事件的综合应用场景

审计和扩展事件在SQL Server的管理和监控中有着广泛的应用场景，以下是一些常见的场景：
- 安全审计 ：通过SQL Server审计功能，可以跟踪用户的登录、权限变更、对象的创建和删除等操作，确保系统的安全性。同时，扩展事件可以用于捕获更详细的事件信息，如存储过程的执行时间、语句的执行情况等，帮助管理员及时发现潜在的安全风险。
- 性能监控 ：利用扩展事件可以监控SQL Server的性能指标，如查询执行时间、锁等待时间等。结合审计信息，可以分析哪些操作对性能影响较大，从而进行针对性的优化。
- 故障排查 ：当SQL Server出现故障时，审计和扩展事件记录的信息可以帮助管理员快速定位问题。例如，通过查看审计日志可以了解是否有异常的登录或操作，通过扩展事件可以分析具体的事件发生情况，找出故障的根源。

13. 总结

SQL Server的审计和扩展事件功能为数据库管理员提供了强大的管理和监控工具。通过合理配置审计和扩展事件会话，可以有效地保障系统的安全性、监控系统的性能，并在出现问题时快速进行故障排查。

在审计方面，要注意审计相关的启动和关闭问题，掌握有用的审计对象，如“sys.dm_server_audit_status”和“Fn_get_audit_file”，同时了解其他审计方法，根据业务需求进行选择。

在扩展事件方面，要理解关键术语，掌握与SQL Trace的关系，学会使用“New Session Dialog”等方式创建事件会话，并注意配置过程中的各项要点和注意事项。

通过综合应用审计和扩展事件功能，可以更好地管理和维护SQL Server，提高系统的可靠性和性能。希望本文介绍的内容能帮助你在实际工作中更好地运用这些功能。