SQL Server Transaction Logs: What Law Enforcement Investigators Need to Know

Each database modification is a standalone event

At any time someone modifies the database, this is considered a unique standalone event that triggers the creation of a log. Be advised this is a fully automated process and the database creates it without you having to do anything else. Upon triggering the event, the logs are written in sequential order.

Each database modification is a standalone event that triggers a sequential sequence of log creation.

What can you deduce from SQL server transaction logs?

If you know what to look for, SQL server transaction logs can be a treasure trove of data for database forensics experts to investigate. This includes:

• Details regarding every database transaction
• Log sequence numbers (LSN)
• Reserved space for rollbacks
• Modification operations
• Before data
• After data
• Partial data
• Metadata

However, they DO NOT store T-SQL statements or exact queries, so be advised they have certain limitations.

Note that a single database may have multiple transaction logs

However, it’s not recommended practice. Moreover, the database won’t have a better performance as a result. Still, it’s something a database forensics expert may want to check during the investigation. After all, people may have multiple reasons for trying out different database configurations.

Opting to have multiple transaction logs actually does make sense when the disk drive is about to run out of space to write data or if the original one is coming close to full capacity. Ideally, such problems should be addressed before it gets to this point, but that’s how it often turns out in practice.

Opting to have multiple transaction logs can get you out of trouble if you are nearing full storage capacity.

Can you disable SQL server transaction logs?

Note that the very design of SQL databases requires the presence of SQL server transaction logs, so disabling them is not an option.

The closest thing you can do would be to set the SQL recovery model to simple mode, the benefit of which is reducing the severity of transaction log bloating.

The 4 cornerstones of ACID compliance

Another reason why you can’t just turn them off is due to ACID compliance. This stands for:

• Atomicity: atoms, as defined by physicists as the smallest particle of an element, can actually be broken down into pieces that are even smaller. How does this relate to databases? Simple: the concept refers to the integrity of the database as a whole. When put into the context of an eCommerce store, it’s impossible to pay for a product unless it’s in your shopping cart.

• Consistency: this goes hand in hand with data validation. In case the data doesn’t pass these validation checks, it won’t be allowed to be written into the database. In such an event, the database is rolled back to a previous state that’s valid and compliant with the rules.

• Isolation: databases are powerful IT constructs, fully capable of handling multiple transactions being written to them all at once. But this is a bit of an oversimplification. Technically, if two customers buy something from an online store at exactly the same second, fractions of a second will be the ones to decide which query will be processed first. In this case, isolation is what ensures that the store won’t sell an item that’s out of stock. If there is only 1 remaining in the store’s inventory, whoever clicked first will be the one to receive it.

• Durability: another way to describe this would be by using the term ‘reliability’. Even if the database doesn’t remain online 100% of the time, the goal is to minimize the impact on the user experience as much as possible. If a website relies on a database to function properly, it’s important to retain at least some of its functionality even in the event of a system failure, power outage, or other similar catastrophes.

SQL databases are built the way they are to ensure regulatory compliance.

What are the three SQL recovery models?

To manage bloated transaction in sql server and keep them from growing out of hand, an administrator may choose to tweak the SQL server settings. This will have a profound effect on how the SQL transaction logs are manifested.

Below, we’ll explain what each of these SQL recovery models means:

1. Simple SQL recovery model

When the disk space is running out or the SQL transaction logs are getting too bloated, a database admin may opt to enable the simple SQL recovery model. When enabling this option, transaction backups will be disabled, which significantly increases the risk of data loss. As soon as it’s enabled, this will automatically trigger the truncation process.

By turning on the simple SQL recovery model you incur the risk of data loss.

2. Bulk-logged SQL recovery model

Unlike the previous option we mentioned, the bulk-logged SQL recovery model does support backups. Keep in mind that, while it’s turned on, automated transaction log truncation is out of the question and you will need to make manual transaction log backups on a regular basis.

The benefit of enabling this mode is that you get to save some hard drive space in the process – the logging will be minimal.

3. Full SQL recovery model

This mode supports backups and there is no risk of data loss, at least under usual circumstances. While this mode is enabled, automated transaction log truncation is disabled. To make the necessary space for further log entries to be written, you will need to make backups on a regular basis.

Since the full SQL recovery model means logging everything that’s supported, expect the log file to become bloated rather quickly.

SQL servers: what to know about transaction log maintenance

Every SQL server administrator needs to realize the importance of SQL server  transaction log maintenance. This is especially true for SQL databases that process a substantial amount of entries each day, which is typically what happens on high-traffic websites.

To measure the transaction log space, enter the following command:

DBCC SQLPREF

This will reveal important metrics that will give you an idea about the status of the database:

• Database name
• Log size in MB
• Percentage of space used
• Status

The importance of SQL server transaction log backups

Keep in mind that regular backups are the only way to ensure the SQL database stays in optimal condition. Once you make the backups necessary, you will be free to use the previously occupied space once again.

Making backups is crucial for database disaster recovery. As we’ve discussed above, the simple SQL recovery model is the only one that doesn’t support them and using it introduces the risk of data loss.

The importance of SQL Server transaction log backups cannot be overstated. In the event of a disaster, valuable data can be recovered this way.

Common tactics hackers use to compromise databases

Since we’ve already published a complete article on common tactics hackers use to compromise databases, here is just a quick recap:

• Brute forcing: this is a method that involves making automated scripted login attempts, hoping that one of them will match the actual password used to protect the database. The shorter and simpler the password, the less time it takes to break it.

• SQL injection: this method involves taking advantage of bad programming practices to attack a database through some kind of a front-end interface that isn’t protected against this kind of attack. As it happens, front-end interfaces tend to be the weakest point of SQL database security.

• Packet sniffing: every network is like a web of data that passes from point A to point B. However, if proper network security measures are not put into place, someone could potentially be eavesdropping on the traffic that passes through (non-secure public Wi-FI spots are particularly risky in this regard).

• Privilege escalation: this is another way to refer to an unauthorized individual abusing higher-than-necessary access privileges to maliciously modify or delete data that’s stored in a database. This can happen either in person or through the internet.

• Exploiting software vulnerabilities: failing to apply updates as they get released can result in getting hacked by a malicious actor who aims to exploit these software vulnerabilities. Too many webmasters delay maintenance and updates due to wanting to keep the database online 24/7 (it must be taken offline for updates to be applied, even if just for a couple of seconds).

• Stolen backup tapes: since backup tapes are rarely secured by any kind of encryption, they are ripe for the taking by anyone who manages to sneak into premises and physically steal them.

The importance of having a reliable digital forensics database investigation tool

Trying to investigate a database without the proper database forensics tools is like trying to dig a moat with a spoon. Why not make your job easier by utilizing a professional solution such as DBF by SalvationDATA? It will help you with your forensics database investigation in many ways, including:

• Easily discover traces of criminal activity or signs of fraud.

• Since this is a potent SQL recovery tool, it will let you read inaccessible or corrupted data and restore it without requiring and special technical knowledge.

• Do a simple keyword search to streamline the investigation and find exactly what you’re looking for within seconds.

• Bypass the database password and encryption that stands between you and your ability to discover the truth behind what happened.

• Automatically generate a detailed digital forensics expert report that measures up to the strictest demands and is fully accepted in court.

• A cost-effective way to conduct any digital forensics database investigation that effectively reduces your labor costs. Since DBF also offers a  free trial, you can even get started for free!

With DBF, you’ll be able to solve more cases involving databases and thus enjoy more industry recognition.

Conclusion

Knowing the basics of SQL server transaction logs is paramount to leading a successful forensics database investigation.

Don’t forget that, without the proper tools, you can quickly start to feel like a fish without water, finding yourself unable to do your job properly. So make it easy on yourself and check out SalvationDATA’s digital forensic product catalog to see what we’ve prepared for you.