Part 1 of this series shows how ApexSQL Audit should be set up for implementing the Administrative safeguards section of the HIPAA compliance (CFR 45 Part 164, Subpart C – Security Standards for the Protection of Electronic Protected Health Information developed to undertake protection of EPHI). In Part 2, the ApexSQL setup for fulfilling the remaining parts of the Administrative safeguards are discussed.
ApexSQL Audit settings that help in fulfilling the HIPAA’s Technical safeguards and Policies and procedures and documentation requirements sections will be demonstrated in this final part of this article
The HIPAA requirements from Administrative safeguards section that will be covered include:
The HIPAA Technical safeguards section supported by ApexSQL Audit:
§164.312(a)(2)(i) – Requires assigning a unique SQL Server Login name for identifying and tracking user identity
§164.312(b) – Requires implementing continuous auditing of SQL Server with mechanisms that log and report any activity in SQL Server contain or use EPHI
§164.312(c) – Implementing continuous auditing of all SQL Server activities related to EPHI, to protect EPHI from improper altering or deleting
The Policies and procedures and documentation requirements section of HIPAA supported by ApexSQL Audit
§164.316(b)(1)(ii) – Maintain a written or electronic form record of who, what and where auditing of SQL Server
§164.316(b)(2)(i), §164.316(b)(2)(ii), §164.528(a) – Ensure the appropriate audit log archival for 6 years from the date of creation and ensure that archived audited data are available on request
§164.312(a)(2)(i) Assign a unique name and/or number for identifying and tracking user identity
This HIPAA requirement imposes the obligation that every user that connects to SQL Server must connect with unique credentials assigned. This means that for each user who will have the right to access the SQL Server that stores the EPHI data, a unique SQL Server login must be created. In practice that mean that in case 5 users are granted access to the SQL Server with EPHI data, five unique logins must be created and ApexSQL Audit in its report must list five events for Create login . Whenever a new user is assigned a unique login, this must be reported and documented. Having a custom report for the Create login event is recommended as it will allow quick and easy and full insight in users and whether the §164.312(a)(2)(i) HIPAA section is covered properly
In order to prevent any misuse of the privileges and to ensure security, users are allowed to access the instance with EPHI data only from the computers assigned to them. This means that accessing the instance by the user from other computers must be logged and carefully reviewed. The ApexSQL Audit alerting engine is designed to allow real-time alerting on any such unapproved access that can help the person in charge to react immediately and to apply appropriate measures.
To be informed on such user non-aligned behavior an alert should be created that will be triggered on any such login or failed login attempt, with the following filter criteria
This particular example shows a separate alert for the individual user. If opted to cover all users that must be tracked via individual alerts, an alert must be created for each user of interest.
The advanced filter used in the ApexSQL Audit alert engine allows for a single alert for multiple users to be created which will be triggered when any of users listed in alert try to access the EPHI data instance from computer that is not assigned to him
Additional functionality to identify the issue faster includes customizing the alert information that will be displayed when the alert is raised. It is recommended to adg the Client host info in the alert body in the following way
A variable “tag” e.g. %TransactionId% can be added easily without typing by clicking on appropriate tag name
In such case, when an alert is raised, the responsible officer will have immediate information about the user who is trying to login and from which exact machine it is being attempted
§164.312(b) Standard: Audit controls, §164.312(c) Integrity & Mechanism to authenticate electronic protected health information
These sections basically require the implementation of policies and procedures that will ensure that EPHI stored in SQL Server will not be altered or deleted in an unauthorized manner. The basic requirements for fulfilling such requirement is Before-After auditing which is capable of ensuring full data change audit trails
For HIPAA it is not only important to see who changed the data, but also whether the change is properly done. By logging the each DML change of the EPHI data ensures that mistakes or malicious changes can be detected by reviewing the chain of changed values to confirm the validity of each change or to detect the issue and start the procedure for correcting the EPHI data.
Captured data will be stored in two additionally created tables within the database where the audited table resides:
- The AUDIT_LOG_DATA table stores the actual values of data that was changed – the old data value before a change and new data value after the change has occurred
- The AUDIT_LOG_TRANSACTIONS table keeps the actual transaction information like: login name, application name, table name and date/time of the committed transaction
ApexSQL Audit Before-After feature is DML trigger based auditing. Tracking old and new values are an important type of audit, however this type of auditing should be done carefully and realize that these audits should be done selectively for the sensitive EPHI data only, as it can significantly increase the size of audited data store, and in some cases negatively impact performance
Note: Auditing of high activity tables is not recommended and might impose significant overhead on SQL Server and occupy the large amount of the storage space
The Before-After feature is initiated by pressing the Before-After button in the main menu
It will open the main window and the new Before-After can be started by pressing the New button
This will start the new project where the first step is connecting to the SQL Server and selecting the database where the table(s) should be audited for data changes
AdventureWorks2014 database will be used for the purpose of this article.
If auditing is to be created for the first time for selected database, the auditing “architecture”, a collection of SQL Server objects e.g. tables that provide the necessary overhead for auditing, need to be created
Manage architecture dialog will be displayed Showing the basic objects that form the back end infastructure of before-and-after auditing.
To install the architecture and proceed with defining the Before-After auditing, press the Install button and confirm with the Yes button. The script execution success can be reviewed in the Script execution results window that will appear after
If no errors are displayed, the auditing architecture is successfully installed. On pressing the Close button, the main Before-After data auditing window will be opened
Here the user can select what tables to audit, what columns for each table and which operation to audit per table. ApexSQL Audit allows auditing of data changes on the column level, and thus allows a level of granularity helpful for HIPAA auditing
Once the tables, columns and operation types e.g. Insert are selected, on pressing the Create button in the main menu, theprocess of creating the appropriate DML triggers on selected tables will be started. The Script dialog will be opened where the user can preview the trigger creation script.
By pressing the Execute button in the main menu, script will be executed and appropriate DML triggers created. Immediately upon the script is executed, the info window will appear with information that script is executed successfully or it will indicate if some errors occurred during the execution
On closing the dialogs, the main grid will be reloaded and the check sign should be displayed in the “Audited” column in the grid for the table that is selected for auditing
Also in the bottom right, a Triggers pane will show active triggers for the selected table
The moment triggers are created, the auditing process is started. To report on audited data changes, the there are two types of reports – Standard and Aggregate
The Aggregate report is designed to provide users with quick insight whether any data changes occurred and what kind of changes
Here it can be seen that 5 update and one insert operations occurred. In case that these are unexpected operations, the Standard report should be run where all details for each operation will be displayed as well as the old and new data values
The Standard report is the one that will be used primarily, as this is where the actual before and after data changes can be tracked
Presenting captured data via the built-in reporting module offers broad options for filtering of captured data and also generated reports can be exported into a CSV, TXT, XLS, DOC or PDF file formats
§164.316(b)(1)(ii) Documentation, §164.316(b)(2)(i) Time limit , §164.316(b)(2)(ii) Availability , §164.528(a) Right to an accounting of disclosures of protected health information
ApexSQL Audit can perform central repository database archiving including the ability to verify integrity and detect the tampering of the archived databases. The archived database are both time limited and it is on user to decide when and what archived databases will be purged. ApexSQL Audit allows each database to be added for reporting directly from the user interface in just a few clicks of the mouse if the archived database is on any available SQL Server
To archive the database, press the Archive button in the main menu
In the Archiving dialog, press the Archive button and wait until the archiving process accomplishes
To verify audit integrity of the archive and detect potential tampering, press Verify integrity in the main menu, and in the Verify audit integrity dialog, select the archive from the drop down menu
Start the integrity verification. In case of any tampering with the data in the selected archive is detected, the details will be listed in the Suspected tampering pane
September 22, 2015