Ledger for Blockchain-Backed Security — Visual Studio Magazine

News

New for developers in SQL Server 2022: Ledger for blockchain-backed security

The new ledger feature in SQL Server 2022 provides blockchain-based security to ensure that data is not tampered with.

The ability to cryptographically guarantee the integrity of data is particularly useful for sharing data between partners, allowing an organization to certify to auditors or other business partners that data in a SQL Server 2022 database has not been altered.

“So the idea here is that there’s cryptography technology based on the blockchain that guarantees you can’t make changes without it being audited, without being visible, like absolute visibility,” explains Leonard Lobel, longtime Microsoft MVP and CTO of Sleek Technologies.

“Even if you are a skilled administrator, there is no way around this for DBAs and sysadmins.”

Lobel detailed the new ledger functionality in a presentation titled “What’s New for Developers in SQL Server 2022” at last week’s major five-day Visual Studio Live! conference in Las Vegas.

Also, Lobel will duplicate his presentation in May at VSLive! conference in Nashville.

And developers who really want to get into the nitty gritty of the ledger and other SQL Server 2022 features can attend his nine-hour Developer Dive into SQL Server 2022, where they can learn:

• How to increase developer productivity by running SQL Server in Linux containers with Docker
• Intelligent Query Processing features, T-SQL enhancements and modern machine learning development platform with R and Python
• Discover the latest security features including ledger tables, Always encrypted with secure enclaves and the latest data virtualization capabilities with PolyBase

The three primary use cases for ledgers, according to Microsoft documentation, are:

• Streamlining audits: Ledger provides the cryptographic proof of data integrity to auditors. This evidence can help to streamline the audit process.
• Business processes with multiple parties: Blockchain is a great solution for multi-party networks where trust is low between parties participating in the network.
• Reliable off-chain storage for blockchain: Ledger provides data integrity for off-chain storage of blockchain networks, helping to ensure complete data trust throughout the system.

Lobel’s explained ledger applies to both entire databases and database tables. For the latter, there are two types of tables: updatable and append-only, as he explained to the packed room audience via hands-on demos.

Updatable ledger tables track all changes via a history table, while append-only ledger tables are typically used for event-based scenarios, where events are meant to be kept intact, with no UPDATEs or DELETES allowed.

“A general ledger database is a database where you say all the tables in this database are all general ledger tables,” Lobel said of the full database option. “So you can have a database with only some general ledger tables that those particular tables will give you these guarantees — these tamper-proof guarantees — and the rest of the tables won’t. Or you can set it at the database level. And you simply can’t make a table that is not a general ledger table in that database.”

All of this is made possible by the database digest, a single hash, which for every transaction and every change to the database in any way, creates and generates a transaction that rehashes the database.

“It rolls the hash of the current transaction into the previous main database digest hash, in the sense that it creates a blockchain.”

Typically, SQL Server’s auditing functionality is used to detect whether a user has tampered with data, along with temporal functions, which maintain the history of tables.

However, as Lobel noted, the auditing and timing features can be turned off by a DBA.

“You can, sort of, change history,” Lobel said. “We all know how dangerous that can be, right?”

So to ensure that tampering is beyond the reach of even high-level administrators and DBAs, full-fledged blockchains are deployed.

A lot of blockchain implementations have high latency and low throughput, Lobel said, “But with Ledger, what they were able to do is they were able to centralize this technology, bring it into the database engine.”

Doing so allows the ledger to function without impacting performance. According to documentation: “The ledger and the historical data are managed transparently, providing protection without any application changes. The function maintains historical data in a relational form to support SQL queries for auditing, investigation and other purposes. It provides guarantees of cryptographic data integrity while the power , the flexibility and performance of the SQL database is maintained.”

Lobel used hands-on demos to dig into the details of the ledger, which was just one focus of his 75-minute presentation.

Other new features of particular importance to developers explained by Lobel – presented with his slides that help explain the highlights – include:

Lobel’s “What’s New for Developers in SQL Server 2022” presentation at VSLive! The Nashville conference will be held on Thursday, May 18 from 11:00 am to 12:15 pm, while his “Developer Dive into SQL Server 2022” will be held from 8:00 am to 5:00 pm the following day.