sql server 面试
In this article, we will discuss a number of common and important SQL Server Audit questions that you may be asked during the SQL Server database administrator interview. I advise you to check them out and get ready for the interview.
在本文中，我们将讨论在SQL Server数据库管理员面试过程中可能会问到的一些常见和重要SQL Server审核问题。 我建议您检查一下并准备好面试。
Q1. What is the SQL Server Audit process?
Q1。 什么是SQL Server审核过程？
- SQL Server Audit is the process of tracking and logging the actions that are performed on the SQL Server instance to achieve the main audit goals of securing the company data SQL Server审核是跟踪和记录在SQL Server实例上执行的操作以实现保护公司数据安全的主要审核目标的过程。
Q2. Mention some common and critical events that are considered as a minimum requirement for an audit?
Q2。 是否提到一些常见和重要事件，这些事件被视为审核的最低要求？
- Failed Logins, 登录失败 ， Login Changes, 登录更改 ， Users Changes, 用户更改 ， Schema Changes, and 架构更改和Audit Changes 审核更改
Q3. Why is it important to audit the failed logins in your SQL Server instance?
Q3。 为什么审核SQL Server实例中失败的登录很重要？
- Because receiving an excessive number of failed logins could be an indication of an attack on your SQL Server 因为收到过多的失败登录信息可能表明您SQL Server受到攻击
Q4. Why is it important to audit the login changes in your SQL Server instance?
Q4。 为什么审核SQL Server实例中的登录更改很重要？
- Because this login change could be a fake key that someone will use to hack your SQL Server instance 因为此登录更改可能是伪造的密钥，所以有人会用它来入侵您SQL Server实例
Q5. Why is it important to audit the user changes in your SQL Server instance?
Q5。 为什么审核SQL Server实例中的用户更改很重要？
- Because this user permission change could be an alarm for an attack on your SQL Server instance 因为此用户权限更改可能是SQL Server实例受到攻击的警报
Q6. Why is it important to audit the schema changes in your SQL Server instance?
Q6。 为什么审核SQL Server实例中的架构更改很重要？
- It is recommended to track all database schema changes in order to catch any illegal schema changes 建议跟踪所有数据库架构更改，以捕获任何非法的架构更改
Q7. What are the limitations of the C2 audit and why it is not recommended?
Q7。 C2审核的局限性是什么？为什么不建议这样做？
- The C2 Auditing feature has two limitations. The first one is that there is no option to configure and specify what we need to audit, where it will audit all actions at the SQL Server instance and database levels. The second limitation for C2 Auditing is that there is no option to configure the path where the generated SQL Server Audit TRC files will be saved, where it will automatically save these files into the default DATA folder of the installed instance. These two limitations may lead to an issue of generating large trace files that will fill the disk where the database data files saved and stop writing data into the databases C2审核功能有两个限制。 第一个是没有选项来配置和指定我们需要审核的内容，它将在SQL Server实例和数据库级别审核所有操作。 C2审核的第二个限制是没有选项来配置将生成SQL Server Audit TRC文件保存到的路径，它将自动将这些文件保存到已安装实例的默认DATA文件夹中。 这两个限制可能导致生成大型跟踪文件的问题，该跟踪文件将填满保存数据库数据文件的磁盘并停止将数据写入数据库
Q8. Could we use the SQL Profiler to audit your SQL Server instance? Is it a recommended tool?
Q8。 我们可以使用SQL事件探查器来审计您SQL Server实例吗？ 是推荐工具吗？
- SQL Server Audit using the SQL Profiler could be a great option when the events are selected properly, and properties are filtered correctly so that it will collect the requested information only. In this way, the size of the generated trace file will be minimized which makes it easier to be analyzed. Due to the fact that the SQL Profiler tool will be eventually deprecated by Microsoft, it cannot be considered as a long-term auditing solution 如果正确选择了事件，并且正确过滤了属性，以便仅收集请求的信息，则使用SQL事件探查器SQL Server审核可能是一个不错的选择。 这样，将使生成的跟踪文件的大小最小化，从而使其更易于分析。 由于SQL Profiler工具最终将被Microsoft弃用，因此不能将其视为长期审核解决方案。
Q9. Explain why we cannot take advantage of the SQL Server Extended Events feature as a SQL Server audit solution, although it is a lightweight, highly scalable and configurable events framework?
Q9。 解释为什么尽管它是一个轻量级，高度可伸缩和可配置的事件框架，但为什么不能利用SQL Server扩展事件功能作为SQL Server审核解决方案呢？
- This is because the Extended Events feature is limited in terms of auditing the different types of database DML changes. In addition, no option to compare the values before and after the modification process using this feature 这是因为扩展事件功能在审核数据库DML更改的不同类型方面受到限制。 此外，在使用此功能的修改过程之前和之后，没有选项可以比较这些值
- Creating a SQL Server audit using SQL Server Extended Events and Triggers使用SQL Server扩展事件和触发器创建SQL Server审核。
Q10. How could we use the SQL Server triggers as a SQL Server Audit solution to track the database changes?
Q10。 我们如何使用SQL Server触发器作为SQL Server审核解决方案来跟踪数据库更改？
- SQL Server triggers can be customized to build a SQL Server auditing solution that fits your company requirements. For example, triggers can be created on all tables that contain critical data to track and log the modified or inserted data, with the ability to compare the data before and after the modification. You can also create an INSTEAD OF trigger to prevent the changes on a specific table and log the failed action to a data repository instead of performing that change 可以自定义SQL Server触发器，以构建适合您公司要求SQL Server审核解决方案。 例如，可以在包含关键数据的所有表上创建触发器，以跟踪和记录修改或插入的数据，并能够比较修改前后的数据。 您还可以创建INSTEAD OF触发器以防止对特定表进行更改，并将失败的操作记录到数据存储库中，而不是执行该更改
- Creating a SQL Server audit using SQL Server Extended Events and Triggers使用SQL Server扩展事件和触发器创建SQL Server审核。
Q11. Is it recommended to use the Change Data Capture feature to audit the database changes? Why?
Q11。 是否建议使用“更改数据捕获”功能来审核数据库更改？ 为什么？
- First of all, the SQL Server database audit using CDC provides no option to track the SELECT statement. In addition, CDC feature requires significant maintenance and administration effort including an automatic process to archive the CDC tables, due to the fact that, SQL Server will keep the tracking data in the changing table for a configurable number of days only, and will be stored in the same or different data file within the tracked database 首先，使用CDCSQL Server数据库审核没有提供跟踪SELECT语句的选项。 此外，由于以下事实，CDC功能需要大量维护和管理工作，包括自动过程来存档CDC表，原因是SQL Server仅将跟踪数据在更改表中保留可配置的天数，并将其存储。在跟踪数据库中相同或不同的数据文件中
- Having the tracked data stored on the same database, creating an auditing report that shows all DML changes on all databases requires good development skills and big programming effort to call each function for each table from all databases and consolidate the data together 将跟踪的数据存储在同一数据库中，创建一个审计报告以显示所有数据库上所有DML的更改都需要良好的开发技能和大量的编程工作，才能从所有数据库中为每个表调用每个函数并将数据整合在一起
- Another reason for not considering the CDC as a recommended SQL Server audit solution is that it will not handle the DDL changes on the CDC enabled tables automatically, which requires an extra effort to reflect this DDL change to the CDC tracking tables 不将CDC视为推荐SQL Server审核解决方案的另一个原因是，它不会自动处理已启用CDC的表上的DDL更改，这需要付出额外的努力才能将DDL更改反映到CDC跟踪表中
- Also, the CDC capture jobs will not work when the SQL Server Agent service is not running. In this case, the database log file will grow rapidly, as the log truncation will not advance, until all the pending changes are logged in the CDC tracking tables 另外，当SQL Server代理服务未运行时，CDC捕获作业将不起作用。 在这种情况下，数据库日志文件将Swift增长，因为日志截断不会继续进行，直到所有未决的更改都记录在CDC跟踪表中为止
Q12. Is it recommended to use the Change Tracking feature to audit the database changes? Why?
Q12。 是否建议使用“更改跟踪”功能来审核数据库更改？ 为什么？
- CT feature is not recommended to be used as a SQL Server audit solution. First of all, CT records no information about the inserted or deleted data, and no option to compare the data before and after the data modification process, without retaining the version history 不建议将CT功能用作SQL Server审核解决方案。 首先，CT不记录有关已插入或删除的数据的信息，并且在保留版本历史记录的情况下，没有在数据修改过程之前和之后比较数据的选项。
- If the tables to be tracked have no Primary Key constraints defined on it, CT is not a tracking option here. In addition, CT requires extra coding effort to retrieve useful information by joining the internal tables of SQL Change Tracking with the tracked source table, based on the Primary Key value of the changed row 如果要跟踪的表上没有定义主键约束，则CT在这里不是跟踪选项。 另外，CT需要额外的编码工作，以根据更改后的行的主键值将SQL Change Tracking的内部表与被跟踪的源表连接起来，以检索有用的信息。
Q13. What makes the SQL Audit built-in feature light and easy to use?
Q13。 是什么使SQL Audit内置功能轻巧易用？
- It is built using the Extended Events feature 它是使用扩展事件功能构建的
Q14. List the three main components in the SQL Server Audit feature and the differences between them.
Q14。 列出SQL Server审核功能中的三个主要组件以及它们之间的区别。
- SQL Server Audit in which you can define the path to store the audit information, the auditing synchronization mode, the audit file rollover mechanism, and the action to be performed in case of audit failure SQL Server审核 ，您可以在其中定义存储审核信息的路径，审核同步模式，审核文件翻转机制以及在审核失败的情况下要执行的操作
- SQL Server Audit Specifications that are used to track and log the changes performed at the SQL Server instance level and raised by the Extended Events feature SQL Server审核规范 ，用于跟踪和记录在SQL Server实例级别执行并由扩展事件功能引起的更改
- SQL Database Audit Specification that is used to track and log different types of actions, performed at the database level and raised by the Extended Events feature SQL数据库审核规范 ，用于跟踪和记录不同类型的操作，这些操作在数据库级别执行并由扩展事件功能引发
Q15. Will the SQL Server Audit feature work when the database is detached from the current SQL instance and attached to a new instance? Why?
Q15。 当数据库从当前SQL实例分离并附加到新实例时，SQL Server审核功能是否可以工作？ 为什么？
- No, it will not work. In the current instance, the audit specification is connected to a specific SQL Server Audit that controls the audit storage mechanism. When the database, with a Database Audit Specification configured on it, is detached from the current instance and attached to a new instance with no SQL Server Audit, has SQL Server Audit with a different GUID, or this new SQL Server instance does not support the SQL Server Audit feature, the Audit Specification will not work and not record will be logged. To fix this issue, you need to connect the Audit Specification to an existing SQL Server Audit, or simply create a new one and connect that Database Audit Specification to it 不，它不起作用。 在当前实例中，审核规范连接到控制审核存储机制的特定SQL Server审核。 当将其上配置了数据库审核规范的数据库与当前实例分离并附加到没有SQL Server审核的新实例，具有其他GUIDSQL Server审核或此新SQL Server实例不支持SQL Server审核功能，审核规范将不起作用，并且不会记录任何记录。 若要解决此问题，您需要将审核规范连接到现有SQL Server审核，或仅创建一个新的并将该数据库审核规范连接到它
Q16. What should we consider when configuring the SQL Server Audit feature on a database participating in SQL Mirroring or Always on Availability Group site?
Q16。 在参与SQL镜像的数据库或“始终在可用性组”站点上的数据库上配置SQL Server审核功能时，应该考虑什么？
- In this case, you need to create the same SQL Server Audit, with the same GUID as the principal or primary server, in the mirrored or secondary replicas. In addition, you should grant permission to the SQL Server service account on the folder where the audit logs will be stored. Otherwise, the audit specification will not work in case of failover 在这种情况下，您需要在镜像副本或辅助副本中创建与主体服务器或主服务器相同的GUID，并具有相同的GUID。 此外，您应授予将存储审核日志的文件夹上SQL Server服务帐户的权限。 否则，在故障转移的情况下审核规范将不起作用
Q17. Is it recommended to use the System-versioned Temporal Table feature as a SQL Server audit solution? Why?
Q17。 是否建议使用系统版本的临时表功能作为SQL Server审核解决方案？ 为什么？
- Firstly, the System-versioned Temporal Table feature records no information about the type of the performed changes. In addition, both the source data and the historical data will be kept in the same database, making this option less secure SQL audit solution. Also, the System-versioned Temporal Table feature provides no option to audit the DDL or Server level changes 首先，系统版本的时态表功能不记录有关已执行更改的类型的信息。 此外，源数据和历史数据都将保存在同一数据库中，从而使该选项的安全性降低了SQL审计解决方案。 此外，系统版本的临时表功能不提供审计DDL或服务器级别更改的选项
- check Performing a SQL Server Audit using System-Versioned Temporal Tables请选中使用系统版本的临时表执行SQL Server审核。
Q18. Why is it very important to define the SQL auditing scope at the beginning of the audit process design and why we need to narrow down that scope?
Q18。 为什么在审计流程设计的开始就定义SQL审计范围非常重要，为什么我们需要缩小范围呢？
- The process of defining the scope of the SQL audit project is very important step as it helps in specifying what is required to be audited, who we should audit and for how long, which is very important to take decision in the next auditing steps within the project and prevent the excessive consumption of the SQL Server resources that leads to performance degradation issue. In addition, narrowing down the amount of collected data will help in making it easier to review it 定义SQL审核项目范围的过程非常重要，因为它有助于指定需要审核的内容，我们应审核的对象以及审核的时间，这对于在Windows的后续审核步骤中做出决定非常重要。项目，并防止过度使用SQL Server资源而导致性能下降的问题。 此外，缩小收集到的数据量将有助于使其更易于查看
Q19. Why is it important to audit your auditing system?
Q19。 为什么审核您的审核系统很重要？
- Auditing the changes that are performed on the SQL Server audit solution helps in catching any unauthorized user who is trying to disable the auditing process to perform illegal activities. In this way, you will make sure that no action is performed with being audited. In addition, this helps also in meeting the auditor’s requirements regarding the audit solution logs’ integrity by providing evidence for covering all eventualities 审核在SQL Server审核解决方案上执行的更改有助于捕获试图阻止审核过程执行非法活动的任何未授权用户。 这样，您将确保不执行任何审计操作。 此外，这还通过提供涵盖所有突发事件的证据来帮助满足审核员对审核解决方案日志完整性的要求。
- Another simple use for this audit is to remind the database administrator to reenable the audit in case he disabled it for maintenance purposes and missed enabling it again 此审核的另一种简单用法是提醒数据库管理员重新启用审核，以防他出于维护目的而禁用它，而错过了再次启用它的情况。
Q20. What aspects that drive your decision in choosing the best SQL Server audit 3rd party tool?
Q20。 是什么推动你选择最佳SQL Server审计第三方工具的决定方面？
- Installation process and requirements 安装过程和要求
- Supported SQL Server versions 支持SQL Server版本
- User interface and GUI experience 用户界面和GUI体验
- Available configuration options 可用的配置选项
- Server level and database level audited actions 服务器级别和数据库级别的审核操作
- Options to store and archive the logs 用于存储和存档日志的选项
- Options to check the audit log’s integrity 检查审核日志完整性的选项
- Options for tool and critical action alerts 工具和关键动作警报的选项
- Available auditing reports 可用的审核报告
翻译自: https://www.sqlshack.com/sql-server-auditing-interview-questions/
sql server 面试
302
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
