SQL Server Ledger Tables are hackable
Testing its claims, Bad Actors and Counter Measures
Exploring SQL Server 2022 Ledger Tables
SQL Server 2022 introduced Ledger Tables, bringing a new layer of data integrity and security. They come in two varieties:
Append-Only Tables: Immutable tables where new data can be added, but existing data cannot be modified or deleted.
Ledger Tables with History: Standard tables supplemented by an automatically maintained history table that tracks all changes.
You can even create an entire database with WITH LEDGER = ON, making all tables ledger tables by default, complete with their corresponding history tables.
Putting Ledger Tables to the Test
Here’s what I set out to explore:
Deleting Rows: Can we remove data?
Dropping Tables: Is table deletion restricted?
Altering Ledger Tables: What schema changes are allowed?
Bypassing Restrictions: Are there ways to "trick" the system?
Filtered Indexes: How do they behave with ledger tables?
Initial Setup
I created two tables to get started and checked the database using sp_xtables.
(I wrote code to populate with some data and did a test update on staff)
Deleting Rows
Attempting to delete rows? No luck. SQL Server blocked the deletion as expected.
I then targeted the staff table. Interestingly, the row wasn’t deleted—it was moved to the staff_history table. That’s the ledger doing its job.
Now we delete from the history table to really get rid of it.
Again, doing their job as expected.
Dropping Tables
Dropping tables proved tricky. Even after deleting a table, a version of it seemed to linger. When I tried with the other table type for completeness, the result was the same.
Attempting to drop a newly named table? Still blocked.
Altering Ledger Tables
I added two Email columns without issues. The changes reflected in both the main and history tables.
But when I tried to drop a column? Denied. SQL Server maintained its ledger integrity.
Database Deletion
For the final test, I attempted to drop the entire database.
Success! The database deletion worked, and no hidden databases appeared in sys.databases. This seems to be the boundary SQL Server enforces, which makes sense—after all, we always have backups (hopefully).
The Hack: Bypassing Ledger Protections
Now, the big question: Can we hack the data?
Ledger tables are designed to prevent unauthorized changes, but what about direct page manipulation? Using a known SQL Server backdoor, I bypassed the table/index structure and wrote directly to the database pages.
This method is dirty, dangerous, and absolutely discouraged. But here’s what I discovered:
I successfully changed the clearance_level in both the staff and staff_history tables – in two separate modifications.
No record of the modification in the transaction log!
While this backdoor logs to the SQL Server error log, I even managed to strip those entries programmatically.
Key Takeaways: Yes, the Data Can Be Hacked
But only if the attacker has supervisor-level access.
So, how can we make hacking impractical?
Row-Level Checksums: Include a hidden magic value in checksum calculations. However, a determined DBA could trace the SQL, pre-calculate the checksum, and modify it accordingly.
Encryption & Compression: This raises the difficulty significantly. Yet, with time, data can be decrypted, altered, and re-encrypted.
Remote Log Shipping: Replicate logs to an offsite server where the attacker has no access. Implement jobs that verify row-level checksums and aggregate checksums to detect tampering. The catch? Ensure the DBA isn't managing the monitoring system.
Final Thoughts
Ledger tables in SQL Server 2022 offer robust protection, but no system is foolproof. The key is layered security: making unauthorized changes so complex and risky that they become impractical.
Stay vigilant, secure your environments, and always assume that with enough access, anything can be compromised.