在AWS RDS SQL Server中进行审核

最新推荐文章于 2023-01-13 11:29:41 发布

culuo4781

最新推荐文章于 2023-01-13 11:29:41 发布

阅读量583

点赞数

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

原文链接：https://www.sqlshack.com/auditing-in-aws-rds-sql-server/

版权

This article explores the server and database audit in AWS RDS SQL Server.

本文探讨了AWS RDS SQL Server中的服务器和数据库审计。

介绍 (Introduction)

Data security is a critical task for any organization. We should only provide data access to authorized persons. Our main aim is to protect any data while in rest or data in transit. Let’s take the example of a credit card company. It stores the customer’s credit card information in database tables. We should protect the data in the following ways:

数据安全性对于任何组织都是至关重要的任务。我们只应向授权人员提供数据访问权限。我们的主要目的是保护静止或传输中的任何数据。让我们以信用卡公司为例。它将客户的信用卡信息存储在数据库表中。我们应该通过以下方式保护数据：

Infrastructure security such as firewall, data center, hardware security, network security
基础架构安全性，例如防火墙，数据中心，硬件安全性，网络安全性
Prevent access to customer information for unauthorized persons
防止未经授权的人员访问客户信息
Data encryption
数据加密
Data masking
数据屏蔽
Data resilience
数据弹性
Compliance with data security standards such as GDPR, SOX, PCI
符合数据安全标准，例如GDPR，SOX，PCI
Payment, mobile app, website security
付款，移动应用，网站安全
Always encrypted data
始终加密的数据

Once we implement the various security solutions, auditing plays a crucial role. We should be able to investigate any suspicious activities using the audit. SQL Server provides several audit options. You should go through the following articles for this:

一旦我们实施了各种安全解决方案，审核就将发挥至关重要的作用。我们应该能够使用审核调查任何可疑活动。 SQL Server提供了几种审核选项。为此，您应该阅读以下文章：

In the previous articles, we explored various features of AWS RDS SQL Server. In this article, we will explore how we can audit for cloud-based managed RDS instance.

在之前的文章中，我们探讨了AWS RDS SQL Server的各种功能。在本文中，我们将探讨如何审核基于云的托管RDS实例。

先决条件 (Prerequisite)

AWS RDS SQL Server – Launching a new database instance for reference purposes AWS RDS SQL Server –启动新数据库实例以供参考

For this article, I am using the following SQL instance:

对于本文，我使用以下SQL实例：

RDS SQL version: Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) – 14.0.3223.3 (X64

RDS SQL版本： Microsoft SQL Server 2017（RTM-CU16）（KB4508218）– 14.0.3223.3（X64

AWS RDS SQL Server migration using native backups article 使用本机备份的AWS RDS SQL Server迁移文章

AWS RDS SQL Server中SQL Server审核 (SQL Server Audit in AWS RDS SQL Server)

We can use both Server and Database audit specifications in the RDS SQL Server instance. It is a similar audit we create in the on-premise SQL Server as well. We can configure the auditing in all AWS regions except for Asia Pacific (Hong Kong) region as of today:

我们可以在RDS SQL Server实例中同时使用服务器和数据库审核规范。这也是我们在本地SQL Server中创建的类似审核。截止到今天，我们可以在除亚太地区（香港）以外的所有AWS地区配置审计：

Audit type

RDS SQL version

Server audit specification

SQL Server 2012 all editions

Database audit specification

SQL Server 2012 – Enterprise edition

SQL Server 2016 SP1

审核类型

RDS SQL版本

服务器审核规范

SQL Server 2012所有版本

数据库审核规范

SQL Server 2012 –企业版

SQL Server 2016 SP1

We enable audit in RDS instance using Options groups:

我们使用选项组在RDS实例中启用审核：

Navigate to Options groups, and it opens the default along with custom options groups:

导航到“选项”组，它将打开默认选项和自定义选项组：

Click on Create group and specify the following inputs:

单击创建组并指定以下输入：

Option group name
选项组名称
Description (optional)
说明（可选）
Engine: Select the SQL Server edition of your RDS instance such as express, standard, enterprise from the drop-down
引擎：从下拉列表中选择RDS实例SQL Server版本，例如Express，Standard，Enterprise
Major engine version: Select the SQL Server version. I have SQL 2017 RDS instance, so selecting 14.0 as a major instance
主引擎版本：选择SQL Server版本。我有SQL 2017 RDS实例，所以选择14.0作为主要实例

Click on Create and add option as shown below:

单击创建并添加选项，如下所示：

It opens a page for adding audit options in AWS RDS SQL Server. This page contains the following sections.

它会打开一个页面，用于在AWS RDS SQL Server中添加审核选项。此页面包含以下部分。

选项详情 (Option details)

This section shows the options group name we specified earlier. From the drop-down list, select the option SQLSERVER_Audit:

本节显示了我们前面指定的选项组名称。从下拉列表中，选择选项SQLSERVER_Audit ：

S3目的地 (S3 destination)

RDS uploads the audit log files to the S3 bucket in the same region. It only uploads the completed audit files. We will see this in actions in the further section of the article.

RDS将审核日志文件上载到同一区域中的S3存储桶。它仅上载完成的审核文件。我们将在本文的后续部分的操作中看到这一点。

Select the S3 bucket from the drop-down list:

从下拉列表中选择S3存储桶：

IAM角色 (IAM role)

We can select an existing IAM role or create a new role. Let’s create a new role and specify the name sqlaudit for it:

我们可以选择一个现有的IAM角色或创建一个新角色。让我们创建一个新角色，并为其指定名称sqlaudit ：

附加配置–可选 (Additional Configuration – Optional)

It provides two additional configuration options:

它提供了两个附加的配置选项：

Enable Compression: RDS SQL Server compresses the audit log file to the ZIP file and places them into the S3 bucket. It saves the storage in the S3 bucket. By default, this option is enabled 启用压缩： RDS SQL Server将审核日志文件压缩为ZIP文件，并将其放入S3存储桶中。它将存储保存在S3存储桶中。默认情况下，此选项处于启用状态
Enable retention: We can enable and specify a retention window (1 to 840 hours). AWS maintains and stores the audit logs for the configured retention period. By default, this option is disabled that shows the AWS removes the audit logs once they are offloaded to the specified S3 bucket 启用保留时间：我们可以启用并指定保留时间窗口（1到840小时）。 AWS会在配置的保留期内维护和存储审核日志。默认情况下，此选项处于禁用状态，该选项显示一旦将审核日志卸载到指定的S3存储桶，AWS就会删除它们

排程 (Scheduling)

We can select the schedule for adding the option for AWS RDS SQL Server. We can do it immediately (downtime involved) or set it for the next scheduled maintenance window:

我们可以选择添加AWS RDS SQL Server选项的时间表。我们可以立即执行此操作（涉及停机时间），也可以将其设置为下一个计划的维护时段：

Let’s apply the option group immediately and click on the Add option. You get the following confirmation message:

让我们立即应用选项组，然后单击添加选项 。您收到以下确认消息：

Now go back to the RDS dashboard and modify the RDS instance. In Options group, select the option group we created above:

现在返回RDS仪表板并修改RDS实例。在“ 选项”组中 ，选择我们在上面创建的选项组：

Apply this change of option group immediately:

立即应用此选项组更改：

Click on Modify DB instance, and you can see status modifying as shown below:

点击Modify DB instance ，您将看到状态修改，如下所示：

Once the RDS instance is available, we can configure the SQL Server audit:

一旦RDS实例可用，我们就可以配置SQL Server审核：

创建AWS RDS SQL Server的服务器审核规范 (Create server audit specification for AWS RDS SQL Server)

We follow the same audit process for SQL Server on-premise and RDS. Overall, we can visualize the audit process:

对于SQL Server内部部署和RDS，我们遵循相同的审核过程。总体而言，我们可以将审核过程可视化：

Connect to the RDS instance. Right-click on the audit and create a new audit:

连接到RDS实例。右键单击审核并创建一个新的审核：

In this page, we specified the following inputs:

在此页面中，我们指定了以下输入：

Audit Name: It is the name of the SQL Server audit. By default, it gives audit name as Audit-YYYYMMDD-HHMMSS 审核名称：这是SQL Server审核的名称。默认情况下，其审计名称为Audit-YYYYMMDD-HHMMSS
Queue Delay – It is the millisecond’s value that shows SQL Server needs to wait for that much time before processing audits in the file 队列延迟 –毫秒值表示SQL Server在处理文件中的审核之前需要等待那么长时间
On Audit Log Failure: We can specify the behavior in case of audit log failure
- Continue
- Shut-down server
- Fail operation
关于审核日志失败 ：如果审核日志失败，我们可以指定行为
- 继续
- 关机服务器
- 失败操作
Audit destination: We can select the location to save the SQL Server audit. It can be either file, security or application log. In the case of a file, specify the directory to save the log file 审核目标：我们可以选择保存SQL Server审核的位置。它可以是文件，安全性或应用程序日志。如果是文件，请指定目录以保存日志文件
Maximum Rollover Files: It is the maximum number of rollover files. Once the SQL Server finishes writing to this nth number of files, it starts overwriting the previous files 最大翻转文件数：这是最大翻转文件数。一旦SQL Server完成对第n个文件的写入，它将开始覆盖以前的文件
Maximum Files: We can specify either the maximum rollover files or the maximum files. For this demo, let’s specify maximum files 2 最大文件数：我们可以指定最大翻转文件或最大文件数。对于此演示，让我们指定最大文件数2
Maximum File Size: We can specify the maximum size of a single audit file. You can specify a value in MB, GB or TB. In this article, I am specifying a maximum of 2 MB audit file 最大文件大小：我们可以指定单个审核文件的最大大小。您可以MB，GB或TB指定值。在本文中，我最多指定2 MB审核文件

Note: We cannot configure Maximum files and MAX_ROLLOVER_FILES for AWS RDS SQL Server

注意：我们无法为AWS RDS SQL Server配置最大文件数和MAX_ROLLOVER_FILES

Once you do the configuration, it is always recommended to generate a script. We get the following script:

完成配置后，始终建议生成脚本。我们得到以下脚本：

USE [master]
 
GO
 
CREATE SERVER AUDIT [Audit-20200108-190542]
TO FILE 
( FILEPATH = N'C:\sqlshack\Audit\'
  ,MAXSIZE = 2 MB
  ,MAX_FILES = 2
  ,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
  ,ON_FAILURE = CONTINUE
)
 
GO

You get the following error message as an output of this script:

您收到以下错误消息作为此脚本的输出：

Msg 50000, Level 16, State 1, Procedure rds_audit_trigger, Line 175 [Batch Start Line 3]
Unable to create/alter/drop Audit/Audit specification: Max size for audit file can only be set between 2M and 50M
Msg 3609, Level 16, State 2, Line 5
The transaction ended in the trigger. The batch has been aborted.

消息50000，级别16，状态1，过程rds_audit_trigger，第175行[批处理开始第3行]
无法创建/更改/删除审核/审核规范：审核文件的最大大小只能在2M到50M之间设置
讯息3609，第16级，州2，第5行
事务在触发器中结束。该批次已中止。

As specified earlier, we cannot configure the Maximum files and MAX_ROLLOVER_FILES for RDS SQL Server. To resolve this error, let’s modify the script by removing the Maximum files parameter and script executes successfully:

如前所述，我们无法为RDS SQL Server配置最大文件数和MAX_ROLLOVER_FILES。要解决此错误，让我们通过删除“最大文件数”参数来修改脚本，然后脚本成功执行：

CREATE SERVER AUDIT [Audit-20200108-193109]
TO FILE 
( FILEPATH = N'D:\rdsdbdata\SQLAudit'
  ,MAXSIZE = 2 MB
  ,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
  ,ON_FAILURE = CONTINUE)

Now, right-click on Server Audit Specification and create a new server audit.

现在，右键单击“服务器审核规范”，然后创建一个新的服务器审核。

We can do the following configurations:

我们可以进行以下配置：

Name: Specify a server audit specification name 名称：指定服务器审核规范名称
Audit: Select the audit name we created above 审核：选择我们在上面创建的审核名称
Audit Action Type: We can select the audit actions from the drop-down list. You can read more about the Sever audit actions and groups using 审核操作类型：我们可以从下拉列表中选择审核操作。您可以使用Microsoft docs Microsoft文档阅读有关Sever审核操作和组的更多信息。

For this article, I added the following audit actions:

对于本文，我添加了以下审核操作：

FAILED_LOGIN_GROUP
FAILED_LOGIN_GROUP
SERVER_OBJECT_CHANGE_GROUP
SERVER_OBJECT_CHANGE_GROUP

Let’s script out the server audit and execute it:

让我们编写服务器审核脚本并执行它：

USE [master]
 
GO
 
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20200108-193611]
FOR SERVER AUDIT [Audit-20200108-193109]
ADD (FAILED_LOGIN_GROUP),
ADD (SERVER_OBJECT_CHANGE_GROUP)
 
GO

We can see both audit and server audit in SSMS, as shown below:

我们可以在SSMS中看到审核和服务器审核，如下所示：

Right-click on the audit, server audit specification and enable it. Once enabled, it removes the red cross from the audits:

右键单击审核，服务器审核规范并启用它。启用后，它将消除审核中的红叉：

Enabled Audit and Server Audit Specification

To generate some audit activity, let’s try some unsuccessful logins from SSMS to the AWS RDS SQL Server instance. We can do this by specifying an invalid username or password in the SSMS connection window.

为了生成一些审核活动，让我们尝试一些从SSMS到AWS RDS SQL Server实例的不成功登录。为此，我们可以在SSMS连接窗口中指定无效的用户名或密码。

We can access the SQL Server audit using the msdb.dbo.rds_fn_get_audit_file function. We can either select the required columns or use (*) for all columns:

我们可以使用msdb.dbo.rds_fn_get_audit_file函数访问SQL Server审核。我们可以选择所需的列，也可以对所有列使用（*）：

SELECT event_time, 
       server_principal_name, 
       server_instance_name, 
       statement, 
       client_ip, 
       application_name, 
       file_name
FROM msdb.dbo.rds_fn_get_audit_file('D:\rdsdbdata\SQLAudit\*.sqlaudit', DEFAULT, DEFAULT);

In the following screenshot, view the audit logs for login failures:

在以下屏幕快照中，查看登录失败的审核日志：

Once SQL Server finishes writing in the audit log file, it uploads that file to the S3 bucket configured in the options group. We can execute the following command to view audit records in the retention folder:

SQL Server完成写入审核日志文件后，会将其上传到选项组中配置的S3存储桶。我们可以执行以下命令来查看保留文件夹中的审核记录：

SELECT *
FROM msdb.dbo.rds_fn_get_audit_file('D:\rdsdbdata\SQLAudit\transmitted\*.sqlaudit', DEFAULT, DEFAULT);

Similarly, you can create the database audit specification for the RDS SQL instance as well.

同样，您也可以为RDS SQL实例创建数据库审核规范。

结论 (Conclusion)

In this article, we explored the auditing on AWS RDS SQL Server. It is a critical requirement, and you should be aware of this.

在本文中，我们探讨了对AWS RDS SQL Server的审核。这是一个关键要求，您应该意识到这一点。

翻译自: https://www.sqlshack.com/auditing-in-aws-rds-sql-server/

culuo4781

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫