Redigera

Dela via


Verify a ledger table to detect tampering

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

In this article, you'll verify the integrity of the data in your ledger tables. If you've configured 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

  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.