Database Logging With Microsoft Dynamics 365

Microsoft Dynamics 365 Finance & Operations provides a database logging feature that can be used to track insert, update, delete, and rename key operation changes made to tables and fields in the application. Database logging is a valuable feature of Microsoft Dynamics 365 used to provide a chronological record of changes that occur within the application. Each record contains details of the date and time stamp, users who performed the change and the values before and after the change, which allows a business to track changes back to the source. Database logging benefits D365 customers by providing businesses control over key risk elements.

Let’s look at a few advantages and challenges of the D365 database logging feature.

Advantages

• Helps meet compliance requirements (e.g., Sarbanes-Oxley).
• Allows auditors to validate transactions more quickly since there is a record of accounting transactions that includes details on the user, time and date and the modified values.
• Reduces fraudulent activity since users are aware their changes can be traced back to their unique ID.
• Provides a record of chronological changes to assist with crime investigation when data corruption occurs.
• Increases record integrity and accuracy with increased transparency into the changes made to records.
• Provides a reliable source since records cannot be modified.

Challenges

• Impacts performance of the application, dependent on the database logging configuration (e.g., increased data storage required to house the data, increased load time for menu items).
• Requires a level of technical knowledge to set up. Microsoft does not provide guidance on the appropriate fields or tables to track given the desired changes to track.
• Tedious database logging setup wizard. Tables are listed under the modules they are referenced by and there is no way to filter or sort the tables displayed. If the module is unknown, configuring a table will require going through each module to locate the appropriate table.
• By default, the technical table names are not listed. There is a chance that the wrong table may be tracked (e.g., there are 5 customer tables, only differentiated by their technical table name).
• If only certain fields on a table are being tracked versus the entire table, only the update event can be tracked. Insert and delete events can only be tracked for an entire table.
• Designed to track individual transactions, not automated transactions that are run in batch jobs.
• Requires proper restrictions. Records can contain sensitive data. Users that have access to the database log will have access to sensitive data. It is important that access to the database log is properly restricted.

In an example use case, a client needed assistance mitigating a risk that they needed to assume due to resourcing constraints. To help mitigate the risk, Protiviti helped the client track meaningful changes made to vendor bank accounts and security role assignments in D365 using database logging. This also provided a detective control that could be leveraged by the client’s audit team.

There are a few key considerations when using the database logging feature:

• Each transaction results in multiple records written to the SQL server transaction log file. As a result, the transaction log file grows rapidly. It is important to maintain the transaction log file because the transaction log file will bring the database back to a consistent state in the case of a system failure. It is recommended to take into consideration increasing the frequency of transaction log file backups.
• Take into consideration the volume and frequency of the changes being tracked to avoid capacity issues. It is recommended to track single transactions that do not change frequently (e.g., configurations, master data, etc.).
• In the database log setup wizard, track specific fields of a table instead of the entire table to help limit the log entries and required storage.

Overall, the database logging feature is a useful tool to help track changes made in the D365 environment. It is important to note that there are third-party applications that integrate with D365 that can help maximize database logging’s capabilities to be leveraged as an audit trail. We invite you to learn about our Microsoft solutions and how we can help your business realize the benefits of database logging.

To learn more about our Microsoft consulting solutions, contact us.