Verify a ledger table to detect tampering
APPLIES TO: Azure SQL Database
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.
- Have an active Azure subscription. If you don't have one, create a free account.
- Create a database in SQL Database with ledger enabled.
- Create and use updatable ledger tables or create and use append-only ledger tables.
Run ledger verification for SQL Database
Open the Azure portal, select All resources, and locate the database you want to verify. Select that database in SQL Database.
In Security, select the Ledger option.
In the Ledger pane, select </> Verify database, and select the copy icon in the pre-populated text in the window.
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.
Open Query editor in the left menu.
In the query editor, paste the T-SQL script you copied in step 3, and select Run. Continue to step 8.
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.
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>'
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.