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


  • GitHub Copilot for Azure Gets Preview Glitches

    This reporter, recently accepted to preview GitHub Copilot for Azure, has thus far found the tool to be, well, glitchy.

  • New .NET 9 Templates for Blazor Hybrid, .NET MAUI

    Microsoft's fifth preview of .NET 9 nods at AI development while also introducing new templates for some of the more popular project types, including Blazor Hybrid and .NET MAUI.

  • What's Next for ASP.NET Core and Blazor

    Since its inception as an intriguing experiment in leveraging WebAssembly to enable dynamic web development with C#, Blazor has evolved into a mature, fully featured framework. Integral to the ASP.NET Core ecosystem, Blazor offers developers a unique combination of server-side rendering and rich client-side interactivity.

  • Nearest Centroid Classification for Numeric Data Using C#

    Here's a complete end-to-end demo of what Dr. James McCaffrey of Microsoft Research says is arguably the simplest possible classification technique.

  • .NET MAUI in VS Code Goes GA

    Visual Studio Code's .NET MAUI workload, which evolves the former Xamarin.Forms mobile-centric framework by adding support for creating desktop applications, has reached general availability.

Subscribe on YouTube