News

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

• By David Ramel
• 03/27/2023

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."

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.

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

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:

  

• Azure integration:

  

• T-SQL enhancements:

  

• JSON enhancements:

  

• Granular DDM permissions:

  

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.