本文共 11885 字,大约阅读时间需要 39 分钟。
This article explores the server and database audit in AWS RDS SQL Server.
本文探讨了AWS RDS SQL Server中的服务器和数据库审计。
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:
数据安全性对于任何组织都是至关重要的任务。 我们只应向授权人员提供数据访问权限。 我们的主要目的是保护静止或传输中的任何数据。 让我们以信用卡公司为例。 它将客户的信用卡信息存储在数据库表中。 我们应该通过以下方式保护数据:
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实例。
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
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:
单击创建组并指定以下输入:
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中添加审核选项。 此页面包含以下部分。
This section shows the options group name we specified earlier. From the drop-down list, select the option SQLSERVER_Audit:
本节显示了我们前面指定的选项组名称。 从下拉列表中,选择选项SQLSERVER_Audit :
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存储桶:
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 :
It provides two additional configuration options:
它提供了两个附加的配置选项:
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审核:
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:
在此页面中,我们指定了以下输入:
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:
我们可以进行以下配置:
For this article, I added the following audit actions:
对于本文,我添加了以下审核操作:
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:
右键单击审核,服务器审核规范并启用它。 启用后,它将消除审核中的红叉:
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_nameFROM 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实例创建数据库审核规范。
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的审核。 这是一个关键要求,您应该意识到这一点。
翻译自:
转载地址:http://pzswd.baihongyu.com/