New for Devs in SQL Server 2022: Ledger for Blockchain-Backed Security

The new ledger feature in SQL Server 2022 provides blockchain-based security to ensure data isn't tampered with.

The ability to cryptographically guarantee the integrity of data is especially useful for sharing data between partners, allowing an organization to attest to auditors or other business parties that data in a SQL Server 2022 database hasn't been altered.

"So the idea here is, there's cryptography technology based on blockchain that guarantees that you cannot make changes without it being audited, without being visible, like absolutely visibility," explains Leonard Lobel, longtime Microsoft MVP and CTO of Sleek Technologies.

"Even if you're a high-powered admin, DBAs and system administrators as well are not able to bypass this in any way."

Leonard Lobel, Microsoft MVP, CTO at Sleek Technologies

"Even if you're a high-powered admin, DBAs and system administrators as well are not able to bypass this in any way."

Lobel was detailing the ins and outs of the new ledger functionality in a presentation titled "What's New for Developers in SQL Server 2022" at last week's big five-day Visual Studio Live! conference in Las Vegas.

What's more, Lobel will duplicate his presentation in May at the VSLive! conference in Nashville.

Inside the Sessions:

What: What's New for Developers in SQL Server 2022

When: Thursday, May 18, 11 a.m. - 12:15 p.m.

What: Developer Dive into SQL Server 2022

When: Friday, May 19, 8 a.m. - 5 p.m.

Who: Leonard Lobel, Microsoft MVP, CTO at Sleek Technologies

Why: To get up to speed on powerful new features in SQL Server 2022.

Find out more about Visual Studio Live!, taking place May 15-19 in Nashville

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

  • How to boost developer productivity by running SQL Server inside Linux containers with Docker
  • Intelligent Query Processing features, T-SQL enhancements, and modern development platform for machine learning with R and Python
  • Discover the newest security features including ledger tables, Always Encrypted with secure enclaves, and the latest data virtualization capabilities with PolyBase
[Click on image for larger view.] Ledger Table Architecture (source: Microsoft).

The three primary use cases for ledger, according to Microsoft documenation, are:

  • Streamlining audits: Ledger provides the cryptographic proof of data integrity to auditors. This proof can help streamline the auditing process.
  • Multiple-party business processes: Blockchain is a great solution for multiple-party networks where trust is low between parties that participate on the network.
  • Trusted off-chain storage for blockchain: Ledger provides data integrity for off-chain storage of blockchain networks, which helps ensure complete data trust through the entire system.

Lobel explained ledger applies to both entire databases and database tables. For the latter, there are two types of tables: updatable and append-only, which he explained to the packed house 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 ledger database is a database where you say, all the tables in this database, are all ledger tables," Lobel said about the entire database option. "So you can have a database with just some ledger tables that those particular tables will give you these guarantees -- these tamper evident guarantees -- and the rest of the tables won't. Or you can designate it at the database level. And you simply cannot create a table that isn't a ledger table in that database."

All of that 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 master database digest hash, and that's that sense in which it creates a blockchain."

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

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

"You can, like, change history," Lobel said. "We all know how dangerous that could be, right?"

So, to ensure tampering is beyond the reach of even high-level admins and DBAs, full-blown blockchains are distributed.

Many blockchain implementations have high latency and low throughput, Lobel said, "But with ledger, what they managed to do, is they managed to centralize this technology, bring it into the database engine."

Doing that allows for ledger to work without impacting performance. According to documentation: "Ledger and the historical data are managed transparently, offering protection without any application changes. The feature maintains historical data in a relational form to support SQL queries for auditing, forensics, and other purposes. It provides guarantees of cryptographic data integrity while maintaining the power, flexibility, and performance of the SQL database."

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

Other new features of special importance to developers explained by Lobel -- presented with his slides helping to explain the high points -- include:

  • New IQP features:
    [Click on image for larger view.]
  • Azure integration:
    [Click on image for larger view.]
  • T-SQL enhancements:
    [Click on image for larger view.]
  • JSON enhancements:
    [Click on image for larger view.]
  • Granular DDM permissions:
    [Click on image for larger view.]

Lobel's "What's New for Developers in SQL Server 2022" presentation at the VSLive! Nashville conference will be held on Thursday, May 18, from 11 a.m. to 12:15 p.m., while his "Developer Dive into SQL Server 2022" will be held from 8 a.m. to 5 p.m. on the following day.

About the Author

David Ramel is an editor and writer for Converge360.

comments powered by Disqus


  • AI for GitHub Collaboration? Maybe Not So Much

    No doubt GitHub Copilot has been a boon for developers, but AI might not be the best tool for collaboration, according to developers weighing in on a recent social media post from the GitHub team.

  • Visual Studio 2022 Getting VS Code 'Command Palette' Equivalent

    As any Visual Studio Code user knows, the editor's command palette is a powerful tool for getting things done quickly, without having to navigate through menus and dialogs. Now, we learn how an equivalent is coming for Microsoft's flagship Visual Studio IDE, invoked by the same familiar Ctrl+Shift+P keyboard shortcut.

  • .NET 9 Preview 3: 'I've Been Waiting 9 Years for This API!'

    Microsoft's third preview of .NET 9 sees a lot of minor tweaks and fixes with no earth-shaking new functionality, but little things can be important to individual developers.

  • Data Anomaly Detection Using a Neural Autoencoder with C#

    Dr. James McCaffrey of Microsoft Research tackles the process of examining a set of source data to find data items that are different in some way from the majority of the source items.

  • What's New for Python, Java in Visual Studio Code

    Microsoft announced March 2024 updates to its Python and Java extensions for Visual Studio Code, the open source-based, cross-platform code editor that has repeatedly been named the No. 1 tool in major development surveys.

Subscribe on YouTube