Skip to content

ledger-verify-database.md

Latest commit

 

History

History
155 lines (118 loc) · 8.21 KB

ledger-verify-database.md

File metadata and controls

155 lines (118 loc) · 8.21 KB
title Verify a Ledger Table to Detect Tampering
description This article discusses how to verify if a table was tampered with.
author VanMSFT
ms.author vanto
ms.reviewer mathoma, randolphwest
ms.date 01/26/2026
ms.service sql
ms.subservice security
ms.topic how-to
ms.custom
sfi-image-nochange
monikerRange =azuresqldb-current || >=sql-server-ver16 || >=sql-server-linux-ver16 || =azuresqldb-mi-current

Verify a ledger table to detect tampering

[!INCLUDE SQL Server 2022 Azure SQL Database Azure SQL Managed Instance]

In this article, you verify the integrity of the data in your ledger tables. If you configure the Automatic digest storage on your database, follow the T-SQL using automatic digest storage section. Otherwise, follow the T-SQL using a manual generated digest section.

Prerequisites

Run ledger verification for the database

T-SQL using automatic digest storage

  1. Connect to your database by using SQL Server Management Studio.

  2. Create a new query with the following T-SQL statement:

    DECLARE @digest_locations AS NVARCHAR (MAX) = (SELECT *
                                               FROM sys.database_ledger_digest_locations
                                               FOR JSON AUTO, INCLUDE_NULL_VALUES);
SELECT @digest_locations AS digest_locations;
BEGIN TRY
    EXECUTE sys.sp_verify_database_ledger_from_digest_storage @digest_locations;
    SELECT 'Ledger verification succeeded.' AS Result;
END TRY
BEGIN CATCH
    THROW;
END CATCH

    [!NOTE]
    You can also find the verification script in the Azure portal. Open the Azure portal and locate the database you want to verify. In Security, select the Ledger option. In the Ledger pane, select Verify database.

  3. Execute the query. You see that digest_locations returns the current location of where your database digests are stored and any previous locations. Result returns the success or failure of ledger verification.

    :::image type="content" source="media/ledger/verification_script_exectution.png" alt-text="Screenshot of running ledger verification by using Visual Studio Code." lightbox="media/ledger/verification_script_exectution.png":::

  4. Open the digest_locations result set to view the locations of your digests. The following example shows two digest storage locations for this database:

    • path indicates the location of the digests.

    • last_digest_block_id indicates the block ID of the last digest stored in the path location.

    • is_current indicates whether the location in path is the current (true) or previous (false) one.

      [
 {
     "path": "https:\/\/digest1.blob.core.windows.net\/sqldbledgerdigests\/janderstestportal2server\/jandersnewdb\/2021-05-20T04:39:47.6570000",
     "last_digest_block_id": 10016,
     "is_current": true
 },
 {
     "path": "https:\/\/jandersneweracl.confidential-ledger.azure.com\/sqldbledgerdigests\/janderstestportal2server\/jandersnewdb\/2021-05-20T04:39:47.6570000",
     "last_digest_block_id": 1704,
     "is_current": false
 }
]

    [!IMPORTANT]
    When you run ledger verification, check the location of digest_locations to make sure digests used in verification are retrieved from the locations you expect. Make sure that a privileged user doesn't change locations of the digest storage to an unprotected storage location, such as Azure Storage, without a configured and locked immutability policy.

  5. Verification returns the following message in the Results window.

    • If there's no tampering in your database, the message is:

      Ledger verification successful

    • If there's tampering in your database, the following error appears in the Messages window:

      Failed to execute query. Error: The hash of block xxxx in the database ledger doesn't match the hash provided in the digest for this block.

T-SQL using a manual generated digest

  1. Connect to your database by using SQL Server Management Studio.

  2. Create a new query with the following T-SQL statement:

    EXECUTE sp_generate_database_ledger_digest ;

  3. Execute the query. The results contain the latest database digest and represent the hash of the database at the current point in time. Copy the contents of the results to use in the next step.

    :::image type="content" source="media/ledger/ledger-retrieve-digest.png" alt-text="Screenshot that shows retrieving digest results by using Visual Studio Code." lightbox="media/ledger/ledger-retrieve-digest.png":::

  4. Create a new query with the following T-SQL statement. Replace <YOUR DATABASE DIGEST> with the digest you copied in the previous step.

    EXECUTE sp_verify_database_ledger N'
<YOUR DATABASE DIGEST>
';

  5. Execute the query. The Messages window contains the following success message.

    :::image type="content" source="media/ledger/ledger-verify-message.png" alt-text="Screenshot that shows the message after running T-SQL query for ledger verification by using Visual Studio Code." lightbox="media/ledger/ledger-verify-message.png":::

    [!TIP]
    Running ledger verification with the latest digest only verifies the database from the time the digest was generated until the time the verification runs. To verify that the historical data in your database wasn't tampered with, run verification by using multiple database digest files. Start with the point in time for which you want to verify the database. An example of a verification passing multiple digests would look similar to the following query.

    EXECUTE sp_verify_database_ledger N'
[
    {
        "database_name":  "ledgerdb",
        "block_id":  0,
        "hash":  "0xDC160697D823C51377F97020796486A59047EBDBF77C3E8F94EEE0FFF7B38A6A",
        "last_transaction_commit_time":  "2020-11-12T18:01:56.6200000",
        "digest_time":  "2020-11-12T18:39:27.7385724"
    },
    {
        "database_name":  "ledgerdb",
        "block_id":  1,
        "hash":  "0xE5BE97FDFFA4A16ADF7301C8B2BEBC4BAE5895CD76785D699B815ED2653D9EF8",
        "last_transaction_commit_time":  "2020-11-12T18:39:35.6633333",
        "digest_time":  "2020-11-12T18:43:30.4701575"
    }
]';

Note

In this example, you call the sp_generate_database_ledger_digest stored procedure to generate the digest and use it immediately for verification. However, when a customer uses a custom trusted storage, they can save the digest in the trusted storage for a later verification.

Related content