Title 21 Code of Federal Regulations Part 11 (in the rest of the text it will be referred to as Title 21 CFR Part 11) is part of the Code of Federal Regulations established by the United States Food and Drug Administration (FDA) as a set of regulations on electronic records and electronic signatures (ERES). The CFR Part 11 specifically defines the standards that have to be imposed in order to consider electronic records and electronic signatures as trustworthy, reliable, and equivalent to paper records
The initial Title 21 CFR Part 11 regulation was released in August 1997 with the purpose of ensuring the accuracy as well as the trustworthiness of information and data and to in some way encourage the use of electronic records over paper records – FDA Title 21 CFR Part 11: Electronic Records; Electronic Signatures; Final Rule (1997)
Due to complaints by industry on vesting significant resources on specifications that added no real value, and in addition to the fact that the regulations caused confusion across different industries a guidance document was released FDA Guidance for Industry Part 11, Electronic Records: Electronic Signatures – Scope and Application (2003) to ameliorate some of these issues
But considering that this was just the guideline and not the law and in some areas, the 2003 guidance contradicted the requirements imposed in the Final Rule from 1997, in 2007, FGDA released the additional guidance for industries in order to supplement the previous guidelines and to additionally define the scope of Title 21 CFR Part 11 and when this regulation applies
Title 21 CFR Part 11 consists of 36 pages, but only 3 pages constitute the actual rules, while the rest are different preambles and FDA’s comments on different complaints from industry. The total of 19 requirements constitute the Title 21 CFR Part 11, and only some of them are specific to Title 21 CFR Part 11 while others are more generic and are actually the part of other FDA regulations
In order to make this article more precise, it will use the latest narrow scope guidance, and according to them this regulation applies when:
- The record is required by all other Title 21 CFR Part regulations
- The electronic records are used to demonstrate compliance with all other Title 21 CFR Part regulations
According to this, the Title 21 CFR Part 11 should be applied when:
- Electronic records are used instead of equivalent paper records
- Paper based records are present, but the company relies on the electronic records when performing activities required by any FDA regulations
- Electronic records submitted to the FDA in electronic form, that are under all other Title 21 CFR Part regulations
- Electronic signatures are issued with intention of full equality to the handwritten signatures, which includes initials and all other signings required by all other Title 21 CFR Part regulations
The Title 21 CFR Part 11 is divided into a three subparts and ApexSQL Audit addresses the Title 21 CFR Part 11 subpart B section § 11.10 requirement which is directly related to the SQL Server. The § 11.10 requirement of the Part 11 segment outlines controls that must be in place for so called closed systems, which is the environment where access to a system is fully controlled by the persons in charge for the content of all electronic records hosted in the system. SQL Server is a typical example of a closed system
The Subpart B–Electronic Records § 11.10 Controls for closed systems section is divided into 11 specifics requirements and for 7 of those ApexSQL Audit can be used to help cover SQL Server compliance with Part 11
The following Title 21 CFR Part 11 requirements supported by ApexSQL Audit will be covered in this part:
§ 11.10 (a) – When applied to SQL Server, this requires implementation of policies and procedures in relation to SQL Server for detecting, preventing, correcting and safeguarding electronic data as well as ensuring that such policies and procedures will not severely affect the performance of SQL Server and thus the performance of related company activities
§ 11.10 (b) – Requires that implemented SQL Server auditing and SQL Server compliance solution can ensure systematic inspection of data collected in audit logs, access to reports on these data in human readable form and tracking of data changes. The reports must be in form that can be submitted to the agency or copied by the agency
§ 11.10 (c) – Implementing continuous SQL Server auditing of all activities related to electronic data covered by the Title 21 CFR Part 11, to protect electronic data from improper altering or deleting, with ability for recovering any improperly altered or deleted electronic record
ApexSQL Audit
ApexSQL has made the Title 21 CFR Part 11 compliance checklist for ApexSQL Audit document, with the intention to help and to present precisely the areas of Title 21 CFR Part 11 requirements where ApexSQL Audit can be used to help with implementation of SQL Server compliance. The intent of this document is to provide information of the Title 21 CFR Part 11 requirements that have to be addressed using ApexSQL Audit which includes the detailed ApexSQL Audit configuration guidance for fulfilling the each of supported requirements. Recommended SQL Server auditing and reporting settings of ApexSQL Audit, to ensure SQL Server compliance with the individual Title 21 CFR Part 11 compliance requirements will be described
§ 11.10 (a) – Validation of systems to ensure accuracy, reliability, consistent intended performance, and the ability to discern invalid or altered records 1
When ApexSQL Audit is implemented as the part of Title 21 CFR Part 11 regulation solution, it can help ensure the establishment of SQL Server compliance supervision processes and procedures when Title 21 CFR Part 11 compliance is applied. ApexSQL Audit is a tool that is designed to audit all activities related to SQL Server which includes any data change related activity such as adding, altering or deleting the electronic records as well as access to any electronic data. ApexSQL Audit saves all data collected during the SQL Server auditing process into the single, tamper evident central repository database. All audited data transmitted during the auditing process between the audited instance and the central instance are SHA256 encrypted, to prevent potential data exposure to unauthorized persons such as data sniffing for example. The central repository database and the audited data archives that stores the audited data are tamper evident to allow tracking of any malicious data change
In addition, the real-time alerting engine that can be configured to meet any alerting requirements imposed on the user, which also includes an additional ability for creating user defined custom alerts based on the T-SQL scripts
§ 11.10 (b) – The ability to generate accurate and complete copies of records in both human readable and electronic form suitable for inspection, review, and copying by the agency 1
This is the Title 21 CFR Part 11 requirement that is associated with ApexSQL Audit’s capability of generating human readable reports in both human readable and electronic form. ApexSQL Audit can help to ensure SQL Server auditing and collecting of any event that occurs in SQL Server, which in conjunction with the wide-ranging reporting abilities should ensure displaying of all needed information related and required by Title 21 CFR Part 11 compliance. ApexSQL Audit can present audited data in the consolidated form which can be fully customized by the user when needed, in order to meet any special reporting requirements
ApexSQL Audit creates reports on all audited SQL Server events, and reports include all the information needed to make sure that each individual event can be properly qualified and identified. The below image illustrates the information columns that can be displayed for the audited SQL Server events.
ApexSQL Audit has two reporting types –common reports and reports. This reporting system allows companies to meet all Title 21 CFR Part 11 related requirements which includes any individual specifically designed reports. More information on ApexSQL reporting can be found in the ApexSQL Audit Feature highlight: Custom reports article
§ 11.10 (c) – Protection of records to enable their accurate and ready retrieval throughout the records retention period 1
ApexSQL Audit is designed to audit SQL Server DML activity in two ways, as well as the ability to set up the real-time alerts for any specific Insert, update and delete. It can track and inform on any DML activity that have occurred which includes the collecting of the T-SQL statement that was executed behind the audited event, but also to audit and collect the before and after data values changes. For setting up and defining the auditing of SQL Server events, ApexSQL Audit has two filter types – the Simple and Advanced filter. While both are suited for precise SQL Server auditing, the advanced filter is particularly capable and specifically designed to ensure unprecedented auditing precision. More details about the advanced auditing filter can be found in Feature highlight: Advanced auditing filter article
So covering of this requirement will be in two tiers – SQL Server auditing of DML events that occurs over electronic records data and SQL Server auditing of before-after for electronic data values change
SQL Server auditing of DML events
To achieve this requirement using the Simple filter
-
Choose the SQL Server instance on the left side of the screen that has to be tracked for electronic data changes and select Add Database.
-
Choose the database that will be audited for electronic data changes. Expand the DML filter on the Operations section and select the Insert, update and delete operations
Perform the same selection for each SQL Server database where electronic data changes auditing is required. After the auditing filter is set in the same manner for every database where electronic data auditing is required, tracking electronic data changes for stated databases is ensured
To achieve the same using the Advanced auditing filter
-
Choose the SQL Server instance in the left side of the screen and select the Advanced filter in the upper ribbon of the filter settings
Thanks to the fact that advanced filter is based on logical expressions , two conditions are enough to achieve the equal filtering criteria to the above described simple filter settings
-
Press here to add condition will add new condition template. Click on the condition field and choose Database name from the list
Clicking on the <empty> will open a dialog to add the desired databases
Adding these databases in filter condition will be interpreted as: audit all SQL Server events that occur on the selected SQL Server for any database listed in the condition.
Now, the one more filter condition is needed to determine for which database operations the auditing will be established. In this particular case where SQL Server auditing of electronic data changes is required, the DML operations Insert, update and delete have to be used in that filter condition
Press the
to add new condition, and choose Database operations as condition. Now, the click on the data field will expand the DML operations list where Delete, Insert and Update operations have to be selected
And the final look of the condition should be like this
Regardless of what filter type is used, ApexSQL Audit’s real-time alerting will ensure that person in charge for Title 21 CFR Part 11 implementation will be informed in a timely manner of any unforeseen electronic data changes. Alerts should be created for the database tables that contain sensitive electronic data which are not intended to be changed or which are not prone to frequent changes. Establishing alerts is particularly important for electronic data which manipulation could be the potentially significant issue and where any change must be checked as soon as possible. To set the alerts:
First choose the Alerts tab, and then click New in the Manage tab
Choose Auditing alert from the first page of the wizard
In the New alert wizard, the first Alert name and notification options dialog allows specifying name for the alert, text for the subject and the body text. These fields are fully customizable and appropriate variables can be added by simple click on the variable name from the list.
The Limit the number of reports for this alert to one per minute (for each server) option should be checked to prevent an inordinate amount of reports. This can be useful in situations when an events number spike is possible and/or expected. Where the electronic data is rarely/not frequently changed, and the importance of electronic data is high, this option should be left unchecked
Next is the Server deployment dialog where the required SQL Server instance must be selected by selecting the appropriate checkbox
A note that steps described in the above section are generally the common steps for this application operations and actually the self-explainable. They will not be addressed in this article again, but only the precise filtering condition required for appropriate SQL Server auditing and/or alerting
For specifying the alert conditions, the advanced filter is used and here is an example of the filter that will raise alerts for Insert, update and delete events when they occur in tables specified in the alert condition
In the Actions dialog of the alert wizard the Send this alert report via email checkbox is recommended to be checked and appropriate person in charge email address entered, to ensure that the user will be notified immediately upon the alert is triggered.
Clicking on the link Click here to configure an account for sending e-mail allows configuring of the mail account
The final Alert summary dialog, shows all the relevant information about the alert that will be created when press on OK. These steps are also common ones, and will not be addressed again in the rest of the article
After required alerts are configured, ApexSQL Audit will be set to audit and notify the person in charge on each and every unauthorized, accidental and/or malicious electronic data modifications and thus reducing or even eliminating the risk of violation of the data integrity, allowing an immediate response when such events occur
Before and after auditing of data values
For fully preserving data integrity, Title 21 CFR Part 11 guidelines recommend the tracking of electronic data changes history. Such tracking ApexSQL Audit can be achieve fully using the implemented Before-after feature. The Before-after functionality of ApexSQL Audit is based on CLR triggers that automatically collect table data changes and store the collected data values in a central repository database. All data values are encrypted from the moment of capturing and they are stored encrypted in the central repository database. The data values change history can be seen only using the ApexSQL Audit reports, while accessing the data outside of application will allow the user to see only obfuscated unreadable information
For complying with Title 21 CFR Part 11, besides being able to see who changed the data, it is also important to track whether the change of electronic data was done properly. Logging each electronic data value changed, guarantees that mistakes, errors and malicious changes can be noticed via reviewing the chain of changed values. Such review should confirm the legitimacy of each electronic data change or to distinguish the problems and then initiate the procedure for fixing the electronic data.
Tracking of old and new values is an important type of SQL Server auditing, though before after auditing of SQL Server should be done with caution and the person in charge for implementation must comprehend that before and after audits should be implemented selectively for the sensitive electronic data only. Inappropriate implementation can increase the size of the central repository database significantly and thus the audit data store, and in some cases it can have negative impact on performance of audited SQL Server
|
Quick tip: Before and after auditing of tables with high activity and large amount of data changes isn’t recommended and potentially it could impose substantial overhead on audited SQL Server and occupy the large amount of the storage space |
To setup the Before-after auditing, select the SQL Server instance and Before-after tab in the main window. Press Add database to choose databases that will be audited from Add database dialog and press OK. Adventure Works 2014 will be used here just for the purpose of the article
Now when the databases are added, select the database and then Add tables to choose tables that will be set for SQL Server auditing
To define the audition of the added table, select the operation that should be audited only for the columns that contain electronic data of interest for Title 21 CFR Part 11 SQL Server compliance
After selecting what should be audited, pressing Apply in the notification bar will create appropriate CLR triggers and before and after auditing of the selected table(s)/column(s) will start
To report on audited data changes, select the Reports in the left pane and select the DML history report. To see the Before-after changes, make sure that the Before-after checkbox of the Event source section is selected (If only Before-after data value change history should be displayed, then uncheck the Trace checkbox)
Press Preview to see the report
Presenting collected before and after data using the built-in report offers extensive options for filtering of collected data, but also allowing generated reports to be exported into a PDF, Word, Excel or CSV file formats
Footnote:
- As defined by the Code of Federal Regulations Title 21
February 25, 2016
