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

• Have an active Azure subscription if you're using Azure SQL Database or Azure SQL Managed Instance. If you don't have one, create a free account.
• Create and use updatable ledger tables or create and use append-only ledger tables.
• SQL Server Management Studio or Azure Data Studio.
• The database option ALLOW_SNAPSHOT_ISOLATION has to be enabled on the database before you can run the verification stored procedures.

Run ledger verification for the database

T-SQL using automatic digest storage

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:

   SQL

   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.

   

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.

     JSON

     [
      {
          "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:

     Output

     Ledger verification successful
     

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

     Output

     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 or Azure Data Studio.

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

   SQL

   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 be used in the next step.

   

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

   SQL

   EXECUTE sp_verify_database_ledger N'
   <YOUR DATABASE DIGEST>
   ';
   

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

   

   Tip

   Running ledger verification with the latest digest will only verify the database from the time the digest was generated until the time the verification was run. 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.

   SQL

   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, we call the sp_generate_database_ledger_digest stored procedure to generate the digest and use it immediately for verification. However, when a customer is using a custom trusted storage, they could save the digest in the trusted storage for a later verification.

Related content

• Ledger overview
• sys.database_ledger_digest_locations
• sp_verify_database_ledger_from_digest_storage
• sp_verify_database_ledger
• sp_generate_database_ledger_digest