Ahmad Yaseen

SQL Server Auditing Interview Questions

October 3, 2019 by

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.

Q1. What is the SQL Server Audit process?

  • 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

Q2. Mention some common and critical events that are considered as a minimum requirement for an audit?

  • 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?

  • Because receiving an excessive number of failed logins could be an indication of an attack on your SQL Server

Q4. Why is it important to audit the login changes in your SQL Server instance?

  • Because this login change could be a fake key that someone will use to hack your SQL Server instance

Q5. Why is it important to audit the user changes in your SQL Server instance?

  • Because this user permission change could be an alarm for an attack on your SQL Server instance

Q6. Why is it important to audit the schema changes in your SQL Server instance?

  • 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?

  • 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

Q8. Could we use the SQL Profiler to audit your SQL Server instance? Is it a recommended tool?

  • 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

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?

  • 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

Q10. How could we use the SQL Server triggers as a SQL Server Audit solution to track the database changes?

  • 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

Q11. Is it recommended to use the Change Data Capture feature to audit the database changes? Why?

  • 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
  • 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
  • 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
  • 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

Q12. Is it recommended to use the Change Tracking feature to audit the database changes? Why?

  • 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
  • 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

Q13. What makes the SQL Audit built-in feature light and easy to use?

  • 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.

  • 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 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 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

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?

  • 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

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?

  • 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

Q17. Is it recommended to use the System-versioned Temporal Table feature as a SQL Server audit solution? Why?

  • 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

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?

  • 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

Q19. Why is it important to audit your auditing system?

  • 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
  • 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?

  • Installation process and requirements
  • Supported SQL Server versions
  • User interface and GUI experience
  • 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
Ahmad Yaseen
Latest posts by Ahmad Yaseen (see all)

Related posts:

  1. SQL Server Auditing Best Practices
  2. C2 auditing – SQL Server Auditing and Common Criteria Compliance using C2 audit mode
  3. Change Data Capture for auditing SQL Server
  4. Auditing by Reading the SQL Server Transaction Log
  5. SQL Server Transaction Log Interview Questions
Auditing, SQL interview questions

About Ahmad Yaseen

Ahmad Yaseen is a Microsoft Big Data engineer with deep knowledge and experience in SQL BI, SQL Server Database Administration and Development fields. He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer. Also, he is contributing with his SQL tips in many blogs. View all posts by Ahmad Yaseen

168 Views