Verify a ledger table to detect tampering

APPLIES TO: Azure SQL Database

Note

Azure SQL Database ledger is currently in public preview.

In this article, you'll verify the integrity of the data in your Azure SQL Database ledger tables. If you selected Enable automatic digest storage when you created your database in SQL Database, follow the Azure portal instructions to automatically generate the Transact-SQL (T-SQL) script needed to verify the database ledger in the query editor. Otherwise, follow the T-SQL instructions by using SQL Server Management Studio or Azure Data Studio.

Prerequisites

Run ledger verification for SQL Database

  1. Open the Azure portal, select All resources, and locate the database you want to verify. Select that database in SQL Database.

    Screenshot that shows the Azure portal with the All resources tab selected.

  2. In Security, select the Ledger option.

    Screenshot that shows the Azure portal with the Security Ledger tab selected.

  3. In the Ledger pane, select </> Verify database, and select the copy icon in the pre-populated text in the window.

    Azure portal verify database button

    Important

    If you haven't configured automatic digest storage for your database digests and are instead manually managing digests, don't copy this script. Continue to step 6.

  4. Open Query editor in the left menu.

    Screenshot that shows the Azure portal Query editor menu option.

  5. In the query editor, paste the T-SQL script you copied in step 3, and select Run. Continue to step 8.

    Screenshot that shows the Azure portal Run query editor to verify the database.

  6. If you're using manual digest storage, enter the following T-SQL into the query editor to retrieve your latest database digest. Copy the digest from the results returned for the next step.

    EXECUTE sp_generate_database_ledger_digest
    
  7. In the query editor, paste the following T-SQL, replacing <database_digest> with the digest you copied in step 6, and select Run.

    EXECUTE sp_verify_database_ledger N'<database_digest>'
    
  8. Verification returns the following messages 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 does not match the hash provided in the digest for this block.
      

Next steps