SQL Server审核功能–发现和体系结构

介绍 (Introduction)

Intended audience

目标听众

This document is intended for database administrators who plan to develop, deploy, assess or implement auditing solutions in Microsoft SQL Server on Microsoft Windows platform.

本文档适用于计划在Microsoft Windows平台上的Microsoft SQL Server中开发，部署，评估或实施审核解决方案的数据库管理员。

Context

语境

Security has become a very critical mission for almost every IT professional. In Europe, for instance, the European Commission has published a regulation called « General Data Protection Regulation » a.k.a. GDPR that allows personal data circulation (in Europe) but under certain conditions: every company must guarantee that its data are safe or at least it did its best to make it secure.

对于几乎所有IT专业人员而言，安全性已成为一项非常关键的任务。 例如，在欧洲，欧洲委员会发布了一项名为《通用数据保护条例》的法规，又称GDPR，该法规允许（在欧洲）流通个人数据，但在某些情况下：每家公司必须保证其数据安全或至少做到了最好使其安全。

This regulation makes auditing a requirement as we should be able to detect security breaches and prove at any time that you made the best effort to guarantee confidentiality, integrity and availability of the system and log any activities of interest. For instance, we need to know that someone (who?) has modified the structure of a table at 1 AM even if it’s planned maintenance. This regulation not only applies to European-based company, but also any company around the world that processes data related European citizens.

该规定使审计成为一项要求，因为我们应该能够检测到安全漏洞，并随时证明您已尽最大努力保证系统的机密性，完整性和可用性并记录任何感兴趣的活动。 例如，我们需要知道有人（谁？）在凌晨1点修改了表的结构，即使该表已计划维护。 该法规不仅适用于总部位于欧洲的公司，而且适用于处理与数据相关的欧洲公民的全球任何公司。

There are multiple ways to audit activity in SQL Server. They use different features and techniques and they all have their advantages and disadvantages. Based on those particularities and what we need to audit, we can come to think that it could be “better” to choose one from another. Among them, there are SQL Server Audits which are built on top of Extended Events.

有多种方法可以审计SQL Server中的活动。 它们使用不同的功能和技术，各有优缺点。 基于这些特殊性以及我们需要审核的内容，我们可以认为，从另一个中选择是“更好”的选择。 其中包括基于扩展事件构建的SQL Server审核 。

But we won’t insist on each of those features as they are already very well introduced by Minette Steynberg in her article entitled “Creating a successful auditing strategy for your SQL Server databases“.

但是，我们不会坚持每个功能，因为Minette Steynberg在其标题为“ 为SQL Server数据库创建成功的审核策略 ”的文章中已经很好地介绍了这些功能。

Actually, in the following sections, we will first take a look at the SQL Server Audit using SQL Server Management Studio (SSMS). This discovery will lead us to notice important components in audit architecture. Finally, we will see what can be done with audits and what built-in tools (dmv, dmf) are at our disposal.

实际上，在以下各节中，我们将首先了解使用SQL Server Management Studio（SSMS）进行SQL Server审核。 这一发现将使我们注意到审计体系结构中的重要组成部分。 最后，我们将看到审计可以做什么以及哪些内置工具（dmv，dmf）可供我们使用。

SQL Server审核的发现 (A discovery of SQL Server Audit)

To provide a good overview of SQL Server Audit, let’s assume that we’ve never read anything about this feature and we are going to use SQL Server Management Studio (SSMS) to get an insight on them. In the following subsections, we will try to set up an audit for DBCC commands.

为了提供对SQL Server审核的良好概述，假定我们从未阅读过有关此功能的任何内容，并且将使用SQL Server Management Studio（SSMS）来对它们进行深入了解。 在以下小节中，我们将尝试为DBCC命令设置审核。

Creating audits using SQL Server Management Studio (SSMS)

使用SQL Server Management Studio（SSMS）创建审核

Creating a SQL Server Audit is pretty simple using SSMS. To do so, we must connect to a SQL Server instance (using SSMS) and go down the tree view to “Security/Audits”.

使用SSMS创建SQL Server审核非常简单。 为此，我们必须连接到SQL Server实例（使用SSMS），并将树形视图下移至“安全性/审计”。

Then right-click on “Audits” and choose “New Audit…”

然后右键单击“审计”，然后选择“新审计...”

And you can fill the form, then click “OK”.

您可以填写表格，然后单击“确定”。

Note
The File path must exist otherwise you get the following error message

注意
文件路径必须存在，否则会出现以下错误消息

As you can see in the figure below, the audit is actually created in a disabled state.

如下图所示，审核实际上是在禁用状态下创建的。

Here are the actions we can do on this audit when we right-click on it:

右键单击审核时，可以采取以下措施：

There are basically two actions that can be useful here: “Enable Audit” and “View Audit Logs”, which is only valuable if the audit is enabled.

基本上有两个可用的操作：“启用审核”和“查看审核日志”，这仅在启用审核后才有用。

But, so far, we haven’t told the audit what user action or feature events we want to audit… Actually, if we enable the audit, we will only get a list of events related to this audit. Here is what is stored in audit log if we enable then disable the audit at this stage: (the audit log is actually truncated because it’s too large to display on a single page)

但是，到目前为止，我们还没有告诉审计我们要审计哪些用户操作或功能事件……实际上，如果启用审计，我们将仅获得与此审计有关的事件列表。 如果启用此功能，则在此阶段禁用审核，这是存储在审核日志中的内容：（审核日志实际上被截断，因为它太大而无法在单个页面上显示）

So, we can say that an audit, audits events on itself.

因此，可以说审核是对自身事件的审核。

Creating Server Audit Specifications

创建服务器审核规范

To tell SQL Server the audit “Audit-Demo-SSMS” has to store for instance, all calls to DBCC, we must create an additional SQL Server object called “Server Audit Specification”:

为了告诉SQL Server审核“ Audit-Demo-SSMS”必须存储例如对DBCC的所有调用，我们必须创建一个名为“ Server Audit Specification”的附加SQL Server对象：

We can right-click on this “folder” and add a new Server Audit Specification. We must link it to the “Audit-Demo-SSMS” audi