Verify a ledger table to detect tampering

Applies to: SQL Server 2022 (16.x) Azure SQL Database

In this article, you'll verify the integrity of the data in your ledger tables. If you've enabled the setting Enable automatic digest storage on your Azure SQL 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

Note

Automatic digest storage is only applicable to Azure SQL Database. If you are using SQL Server, switch over to the T-SQL using a manual generated digest tab.

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

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

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

    Note

    The verification script can also be found 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'll 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.

    Screenshot of running ledger verification by using Azure Data Studio.

  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, inspect the location of digest_locations to ensure digests used in verification are retrieved from the locations you expect. You want to make sure that a privileged user hasn't changed 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 was no tampering in your database, the message is:

      Ledger verification successful
      
    • If there was 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.
      

Next steps