使用SQL Server扩展事件和触发器创建SQL Server审核

最新推荐文章于 2022-09-25 19:34:19 发布

culuo4781

最新推荐文章于 2022-09-25 19:34:19 发布

阅读量492

点赞数

文章标签：数据库大数据 python linux mysql

原文链接：https://www.sqlshack.com/creating-a-sql-server-audit-using-sql-server-extended-events-and-triggers/

版权

This article will provide an overview of manually creating a SQL Server audit using SQL Server Extended events and triggers. It will provide an overview of SQL tracing/profiling as well as extended events. I’ll walk you through how to create a new extended events session and how to use it, in a worked example, to audit failed logins. Finally, we’ll touch on an auditing approach using triggers

本文将概述使用SQL Server Extended事件和触发器手动创建SQL Server审核的过程。它将概述SQL跟踪/分析以及扩展事件。在一个可行的示例中，我将指导您如何创建新的扩展事件会话以及如何使用它来审核失败的登录。最后，我们将介绍使用触发器的审计方法

If you are just joining with this article, not that in the previous article of this series, Implementing a manual SQL Server Audit, we went through the different methods that can be used to audit various types of actions performed at both the SQL Server instance and database levels.

如果您只是与本文一起参加，而不是本系列的上一篇文章《实现手动SQL Server审核》中的内容，那么，我们将介绍了可用于审核在SQL Server实例和SQL Server实例上执行的各种类型操作的不同方法。数据库级别。

SQL跟踪 (SQL Trace)

The SQL Trace feature was introduced the first time in SQL Server 2000, and considered as the best method of SQL Server auditing using different SQL Server actions. At the beginning, you need to define the classes of events that you manage to collect using a set of T-SQL system stored procedures. The defined events can be collected using the SQL Server Profiler tool, which cannot be used in the production environment due to performance purposes, or simply called inside your application to create these event traces manually.

SQL跟踪功能是在SQL Server 2000中首次引入的，被认为是使用不同SQL Server操作进行SQL Server审核的最佳方法。首先，您需要定义使用一组T-SQL系统存储过程来管理要收集的事件的类。可以使用SQL Server Profiler工具收集已定义的事件，由于性能目的，该工具不能在生产环境中使用，也可以在应用程序内部调用以手动创建这些事件跟踪。

These system T-SQL stored procedures include the sp_trace_create that is used to define the SQL Trace, sp_trace_setevent that is used to define the list of event traces to be collected and the columns that is retrieved, and the sp_trace_setstatus that can be used to start, stop and remove the SQL traces. SQL Traces are working temporarily, where the traces will be stopped, and not started automatically, when the SQL Server service is restarted. For more information about the SQL Trace feature, check out the SQL Trace document.

这些系统的T-SQL存储过程包括用于定义事件迹线的列表，以被收集，用于定义SQL跟踪的sp_trace_create，sp_trace_setevent和所检索的列，并且可以用于启动sp_trace_setstatus，停止并删除SQL跟踪。 SQL跟踪暂时工作，重新启动SQL Server服务时，跟踪将停止，并且不会自动启动。有关SQL跟踪功能的更多信息，请查看SQL跟踪文档。

Both the SQL Trace feature and the SQL Server Profiler tool are deprecated and may be removed from future SQL Server versions, as the technology is replaced by the SQL Server Extended Events feature, that we will describe shortly in this article.

不推荐使用SQL跟踪功能和SQL Server Profiler工具，并且可以将其从将来SQL Server版本中删除，因为该技术已被SQL Server扩展事件功能所替代，我们将在本文中简短介绍。

扩展活动 (Extended Events)

SQL Server Extended Events feature was introduced the first time in SQL Server 2008, as a light weight performance monitoring feature. And with the vital enhancements in SQL Server 2012, such as the SQL Server Extended Events graphical user interface that makes it easy to create and configure the Extended Events sessions without the need to go through the underlying architecture of its framework, it is considered as the best replacement for both the SQL Server Profiler and SQL Trace deprecated features.

作为轻量级性能监视功能，SQL Server 2008中首次引入了SQL Server扩展事件功能。借助SQL Server 2012中的重要增强功能，例如SQL Server扩展事件图形用户界面，可以轻松创建和配置扩展事件会话，而无需遍历其框架的基础体系结构，因此被视为SQL Server Profiler和SQL Trace不推荐使用的功能的最佳替代。

SQL Server Extended Events are a highly scalable and configurable events framework that helps in collecting as much useful information as possible from the wide range of available actions, with the least possible SQL Server resources consumption, for troubleshooting and performance tuning purposes. For more information about the SQL Server Extended Events feature, check the Extended Events article.

SQL Server扩展事件是一个高度可扩展和可配置的事件框架，可帮助从各种可用操作中收集尽可能多的有用信息，同时尽可能减少SQL Server资源的消耗，以进行故障排除和性能调整。有关SQL Server扩展事件功能的更多信息，请参见“ 扩展事件”文章。

创建扩展事件会话 (Creating an extended event session)

SQL Server Extended Events can be used also for SQL Server auditing purposes. For example, you can create a SQL Server Extended Events session that audits both the succeeded and failed login processes. To do that, expand the Extended Events option under the Management node, right-click on the Sessions option and choose New Session…, as below:

SQL Server扩展事件也可以用于SQL Server审核。例如，您可以创建一个SQL Server扩展事件会话，以审核成功和失败的登录过程。为此，请在“ 管理”节点下展开“ 扩展事件”选项，右键单击“ 会话”选项，然后选择“ 新建会话 …”，如下所示：

On the displayed New Session window, provide a meaningful name for the new session, which is Audit_Demo in our example, and set the appropriate scheduling settings from the available options, as shown below:

在显示的“新会话”窗口上，为新会话提供一个有意义的名称，在我们的示例中为Audit_Demo，然后从可用选项中设置适当的调度设置，如下所示：

The New Extended Events Session wizards allows you to choose from the available default events templates, similar to the SQL Server Profiler templates, as shown below:

新建扩展事件会话向导使您可以从可用的默认事件模板中进行选择，类似于SQL Server Profiler模板，如下所示：

Or click on the Events tab, to customize your own session and choose the events that you are managed to monitor. In our example here, we will choose the Login event to track the successful login processes and the Error_Reported event to collect the failed logins as follows:

或单击“ 事件”选项卡，以自定义您自己的会话并选择您要监视的事件。在此处的示例中，我们将选择Login事件来跟踪成功的登录过程，并选择Error_Reported事件来收集失败的登录，如下所示：

Double-clicking on the selected event will move you to a new window, on which you can customize the columns that will be recorded and received for that event. For example, we are interested in retrieving specific global information about the successful login process, as shown below:

双击所选事件将带您进入新窗口，在该窗口上您可以自定义将为该事件记录和接收的列。例如，我们对检索有关成功登录过程的特定全局信息感兴趣，如下所示：

审核登录失败 (Auditing for failed logins)

For the failed login processes, we need to filter on the 18456 SQL Server error message, that is returned when a connection attempt is rejected because of an authentication failure that involves a bad password or user name. This can be performed by choosing the Filter tab and specify a filter for the error_number field, to retrieve only the error with number 18456, as shown below:

对于失败的登录过程，我们需要过滤18456 SQL Server错误消息，该错误消息是由于身份验证失败（涉及错误的密码或用户名）而拒绝连接尝试时返回的。这可以通过选择“ 过滤器”选项卡并为error_number字段指定过滤器来执行，以仅检索编号为18456的错误，如下所示：

The location where the SQL Server auditing session result will be written can be specified from the Data Storage tab, by choosing the type of output target and configure its settings as follows:

通过选择输出目标的类型并配置其设置，可以从“数据存储”选项卡中指定将写入SQL Server审核会话结果的位置：

For example, you can choose the target as a SQL Server Extended Events event_file, with XEL extension, then configure its location and properties, as bellow:

例如，您可以选择目标作为具有XEL扩展名SQL Server扩展事件event_file，然后将其位置和属性配置如下：

In the Advanced tab, you can configure the retention and resources settings for the SQL Server Extended Events session. In our example, we will keep the default values, as shown below:

在“高级”选项卡中，可以为SQL Server扩展事件会话配置保留和资源设置。在我们的示例中，我们将保留默认值，如下所示：

Once the SQL Server Extended Events session created, a new empty window will be displayed in the SQL Server Management Studio, in which the caught events will be displayed, as follows:

创建SQL Server扩展事件会话后，将在SQL Server Management Studio中显示一个新的空窗口，其中将显示捕获的事件，如下所示：

If this page is not displayed, right-click on that session and choose the Watch Live Data option, as below:

如果未显示此页面，请右键单击该会话，然后选择“监视实时数据”选项，如下所示：

After performing successful and failed login processes, the events will be collected and displayed by the SQL Server Extended Events session. For example, the successful login process properties, including the user name, the host name, the application used for the login and other useful information will be displayed as shown clearly below:

在执行成功和失败的登录过程后，SQL Server扩展事件会话将收集并显示事件。例如，将成功显示成功登录过程的属性，包括用户名，主机名，用于登录的应用程序和其他有用信息，如下所示：

On the other hand, all useful information about the error message generated when the login attempts to fail, will be caught and displayed in the SQL Server Extended Events session, as shown below:

另一方面，将在SQL Server扩展事件会话中捕获并显示有关登录尝试失败时生成的错误消息的所有有用信息，如下所示：

In addition to writing the logs to the event file for future analysis, as below:

除了将日志写入事件文件以供将来分析外，如下所示：

管理会议 (Managing sessions)

To start or stop the created session, browse for that session under the Extended Event Sessions, and choose the Start Session or Stop Session, as shown below:

要启动或停止创建的会话，请在扩展事件会话下浏览该会话，然后选择开始会话或停止会话，如下所示：

SQL Server Extended Events cannot be used to design a complete database auditing solution. Although it is very useful in auditing the successful and failed logins processes, as shown in the previous example, this feature still limited in terms of auditing the different database DML changes and comparing the values before and after the modification process, that can be easily performed in the SQL Server auditing mechanisms discussed later in this article and the next articles of this series.

SQL Server扩展事件不能用于设计完整的数据库审核解决方案。尽管如上例所示，它在审核成功和失败的登录过程中非常有用，但是在审核不同数据库DML更改以及比较修改过程之前和之后的值方面，此功能仍然受到限制，可以轻松执行本文后面以及本系列后续文章中讨论SQL Server审核机制中的相关内容。

SQL Server触发器 (SQL Server Triggers)

SQL Server triggers are special type of procedures that are automatically fired when an event occurs at the SQL Server database. There are two types of triggers, the DML triggers that are executed as a result of a data modification or insertion operation. The DDL trigger is executed as a response to a data definition operation, such as CREATE, ALTER or DROP statement. The response of the trigger to the different actions can be in the form of another statement that will be executed after the current action, or a statement that will be executed instead of the firing action. For more information about the SQL Server triggers, check the CREATE TRIGGER article.

SQL Server触发器是特殊类型的过程，当SQL Server数据库中发生事件时，将自动触发这些过程。触发器有两种类型， DML触发器是由于数据修改或插入操作而执行的。 DDL触发器作为对数据定义操作（例如CREATE，ALTER或DROP语句）的响应而执行。触发器对不同动作的响应可以采用将在当前动作之后执行的另一条语句的形式，或者将代替触发动作执行的一条语句。有关SQL Server触发器的更多信息，请参见CREATE TRIGGER一文。

SQL Server triggers can be used to track and audit large number of database operations. This is due to the fact that, the triggers are T-SQL scripts that can be customized to build your own SQL Server auditing solution that fits your systems, based on your development skills. You can create at least one trigger on each table that contains critical data to audit the modified or inserted data and compare the data before and after the modification. You can also design a proactive SQL Server auditing system using a trigger that prevents the changes on a specific table and instead of performing that change, it will audit the failed action to a data repository.

SQL Server触发器可用于跟踪和审核大量数据库操作。这是由于以下事实：触发器是T-SQL脚本，可以根据您的开发技能对其进行自定义，以构建适合您系统SQL Server审核解决方案。您可以在包含关键数据的每个表上至少创建一个触发器，以审核修改或插入的数据并比较修改前后的数据。您还可以使用触发器来设计主动SQL Server审核系统，以防止对特定表进行更改，并且将执行失败的操作审核到数据存储库，而不是执行该更改。

Assume that we need to prevent any new insertion to the Employees table and audit these failed operations using SQL Server trigger. We will start with creating the CompanyEmployees table, and fill it with 100 records, using the script below:

假设我们需要防止任何新插入到Employees表中，并使用SQL Server触发器审核这些失败的操作。我们将从创建CompanyEmployees表开始，并使用以下脚本将其填充100条记录：

CREATE TABLE CompanyEmployees
( ID INT IDENTITY (1,1) PRIMARY KEY,
  Emp_Name NVARCHAR(50),
  Emp_BirthDatae Datetime,
  Emp_Salary INT,
  Emp_Address NVARCHAR (MAX)
  )
INSERT INTO CompanyEmployees VALUES ('ALI','1988-08-15',850,'AAAAAABBB')
GO 50
INSERT INTO CompanyEmployees VALUES ('Zaid','1988-06-10',730,'CCCCCDDD')
GO 50

After creating the table, we will create the SQL Server audit repository table, where the employees data will be inserted, instead of the main Employees table, in addition to the name of the user who tried to insert the data and the insertion time. The table can be created using the T-SQL script below:

创建表之后，我们将创建SQL Server审核存储库表，除了试图插入数据的用户名和插入时间之外，还将在其中插入员工数据，而不是主Employees表。可以使用以下T-SQL脚本创建表：

CREATE TABLE Emp_AUDIT_Table
(
  Emp_ID INT ,
  Emp_Name NVARCHAR(50),
  Emp_BirthDatae datetime,
  Emp_Salary INT,
  Emp_Address NVARCHAR (MAX),
  WhoInserted NVARCHAR(128),
  WhenInserted datetime
)

Once the audit table is ready, we will create the INSTEAD OF INSERT trigger to prevent the new insertions, using the CREATE TRIGGER script below:

审核表准备就绪后，我们将使用下面的CREATE TRIGGER脚本创建INSTEAD OF INSERT触发器以防止新插入：

CREATE TRIGGER AuditEmployees ON CompanyEmployees
INSTEAD OF INSERT
AS 
BEGIN
INSERT INTO Emp_AUDIT_Table SELECT i.ID, i.Emp_Name, i.Emp_BirthDatae, i.Emp_Salary,i.Emp_Address, SUSER_SNAME(), getdate()  FROM inserted i
END
GO

If you try to insert a new record to the CompanyEmployees table, it will show you that two rows will be affected, as below:

如果您尝试在CompanyEmployees表中插入一条新记录，它将向您显示两行将受到影响，如下所示：

But internally, the first affected row is the failed inserting process, as no record will be inserted to the main table. And the second affected row is the audit row that will be written to the SQL Server audit table, with full information about the user and the time of insertion, as shown clearly below:

但是在内部，第一个受影响的行是插入过程失败，因为没有记录会插入到主表中。第二个受影响的行是将被写入SQL Server审核表的审核行，其中包含有关用户和插入时间的完整信息，如下所示：

This is a simple example of how we can take benefits from the triggers in auditing the data and schema changes. And it is yours now to build your customized code based on your development skills for SQL Server audit purposes.

这是一个简单的示例，说明我们如何在审计数据和架构更改时从触发器中受益。现在，您将根据自己的开发技能来构建自定义代码，以用于SQL Server审核。

Third party tools like ApexSQL Trigger, can help you rapidly design and automatically maintain a trigger-based, SQL Server audit solution.

ApexSQL Trigger等第三方工具可以帮助您快速设计并自动维护基于触发器SQL Server审核解决方案。

摘要 (Summary)

Building an effective SQL Server auditing system using triggers is difficult and will add more complexity to the database design. In addition, it is not recommended to create the SQL Server triggers in a heavily transaction tables, as it will be executed each time a data insertion or modification process is performed, adding extra time and resources overhead to different SQL Server queries and transactions, and leading to a major performance issues on these tables.

使用触发器来构建有效SQL Server审核系统很困难，并且会增加数据库设计的复杂性。此外，建议不要在事务量很大的表中创建SQL Server触发器，因为它将在每次执行数据插入或修改过程时执行，这会为不同SQL Server查询和事务增加额外的时间和资源开销，并且导致这些表格出现重大性能问题。

In the next article, we will discuss a more effective way to create a SQL Server audit by reading SQL Server Transaction Log records. Stay tuned!

在下一篇文章中，我们将讨论通过读取SQL Server事务日志记录来创建SQL Server审核的更有效方法。敬请关注！