Azure SQL Database Ledger Getting Started and Examples


By:   |   Updated: 2021-06-09   |   Comments   |   Related: 1 | 2 | More > Azure


Problem

In the previous tip, Introduction to Azure SQL Database Ledger (Preview), we explored the concept, features, components of the new Azure database functionality. This helps detect any malicious activity in the database and restore it to the original value. It provides the benefit of blockchain technology in the Azure database. In this tip, we will deploy the Azure ledger database and explore its functionalities.

Solution

Azure Ledger is a feature enhancement of the existing Azure SQL Database. Therefore, to deploy it using the Azure portal, navigate to SQL Database in Azure portal.

In the deployment model, select SQL Databases – Single database.

sql deployment option

In the Create SQL Database, select your Azure subscription and resource group. For this tip, I created a new resource group named [azuresqlledger].

create sql database

To deploy the Azure SQL Database Ledger, we require a logical SQL Server. As explained in the previous tip, the current ledger database is available in West Central US. Therefore, I created a logical server and specified the SQL Server administrator account as shown below.

create sql database

In the database details section, specify the database name. You also need to specify the database compute pricing tier. You can browse the pricing tier from the Microsoft docs.

You can choose public or private endpoint for connecting to SQL Server. For a critical production database, you should use a private endpoint. In my demo, I use a public endpoint and Add the client IP address in the firewall rules.

create sql database

In the Security tab, you get an option for Ledger (preview).

create sql database

By default, the ledger is not configured. Therefore, click on Configure ledger and select the following options.

  • Enable for all future tables in this database
  • Digest storage

The digest storage generates the digest and stores them into Azure storage or Azure Confidential Ledger (preview). For this tip, we use the Azure storage option.

configure azure ledger

You can configure a new or choose an existing storage account and a storage container for the digest storage.

configure azure ledger

Click on Apply, and it completes the configuration for the Azure ledger, as shown below.

configure azure ledger

In the additional settings page, choose the database collation (other than default) and maintenance window. The default maintenance windows are 5 pm to 8 am.

create sql database

On the last page, review your Azure database configuration and estimated cost per month.

create sql database

It deploys the Azure SQL Database, and you can connect to it using Azure Data Studio or SQL Server Management Studio (SSMS). As shown below, the ledger database looks similar to a regular Azure database.

ssms databases

Create and use Updatable Ledger Tables

Once we have deployed the Azure SQL Database Ledger, we will create and work with the updatable ledger tables.

  • Create a schema and updatable ledger table
  • Insert data
  • Update data into ledger table
  • Use the ledger view and view the historical data

The following script creates the [Customer] schema and [Account] table. In the CREATE TABLE script, we use the LEDGER=ON clause. It is an optional parameter because we already enabled the ledger feature in the Azure database.

CREATE SCHEMA [Customer]
GO
 
CREATE TABLE [Customer].[Account]
(
    [CustID] INT NOT NULL PRIMARY KEY CLUSTERED,
    [FirstName] VARCHAR (100) NOT NULL,
    [LastName] VARCHAR (100) NOT NULL,
    [Balance] DECIMAL (10,2) NOT NULL
)
WITH 
(
 SYSTEM_VERSIONING = ON,
 LEDGER = ON
);
GO

The updatable ledger table automatically creates a history table and ledger view. The following script returns the ledger, history tables and ledger view name.

SELECT 
ts.[name] + '.' + t.[name] AS [ledger_table_name]
, hs.[name] + '.' + h.[name] AS [history_table_name]
, vs.[name] + '.' + v.[name] AS [ledger_view_name]
FROM sys.tables AS t
JOIN sys.tables AS h ON (h.[object_id] = t.[history_table_id])
JOIN sys.views v ON (v.[object_id] = t.[ledger_view_id])
JOIN sys.schemas ts ON (ts.[schema_id] = t.[schema_id])
JOIN sys.schemas hs ON (hs.[schema_id] = h.[schema_id])
JOIN sys.schemas vs ON (vs.[schema_id] = v.[schema_id])
GO

As shown below, the history table and ledger view share a similar schema as the updated ledger table.

azure ledger query

Let's insert a few customers records in the [Customer].[Account] ledger table.

INSERT INTO [Customer].[Account] VALUES (1, 'Rajendra', 'Gupta', 100)
INSERT INTO [Customer].[Account] VALUES (2, 'Sohan', 'Kumar', 80)
INSERT INTO [Customer].[Account] VALUES (3, 'Akshita', 'Garg', 90)

The updateable ledger table adds the ALWAYS GENERATED automatically. Therefore, we can use the following query and view these columns data.

SELECT * 
      ,[ledger_start_transaction_id],[ledger_end_transaction_id]
      ,[ledger_start_sequence_number],[ledger_end_sequence_number]
FROM [Customer].[Account]
GO

The output shows different transaction IDs (ledger_start_transaction_id) for each row. It is the transaction ID that has the inserted data in the ledger table. If you insert data in a single transaction, all records will have the same transaction id.

Similarly, if you use a single transaction for inserting multiple records, you can use the sequence number (ledger_start_sequence_number) to identify the order for the data insert. We used separate transactions. Therefore, all rows have a value of 0 for the ledger_start_sequence_number column.

query results azure ledger

Now, suppose we update the balance for a customer (customer ID 2). The new balance of customer 2 is $150.

UPDATE [Customer].[Account] SET [Balance] = 150
WHERE [CustID] = 2

The following SQL scripts give us the following data:

  1. View Updated ledger table ALWAYS GENERATED columns data
  2. View data in the history ledger table
  3. View records in the ledger view
SELECT * 
      ,[ledger_start_transaction_id],[ledger_end_transaction_id]
      ,[ledger_start_sequence_number],[ledger_end_sequence_number]
FROM [Customer].[Account]
GO
SELECT * FROM Customer.MSSQL_LedgerHistoryFor_1525580473
GO 
SELECT * FROM Customer.Account_Ledger ORDER BY ledger_transaction_id
GO

We get the following information from the output of the above SQL scripts.

  • The history table (Customer.MSSQL_LedgerHistoryFor_1525580473) has records for the table before updates. For example, we updated the balance for customer id 2 to $150 from the $80. Therefore, the history table has records for a transaction having a balance of $80.
  • The ledger view has an entry for DELETE for the row with $80. It also has an INSERT entry of $150 as a new balance for customer id 2.
query results azure ledger

Similarly, you can delete a record from the [Customer].[Account] table and verify the delete row from the history table and ledger view.

DELETE FROM [Customer].[Account] WHERE custID=3
query results azure ledger

Deploy Append-Only Ledger Tables in Azure SQL Database Ledger

As we explored earlier, the append-only ledger table is suitable only for INSERT records, for example, for a security access control application that records employee login events.

To create the append-only ledger table, we need to specify the following parameters in the CREATE TABLE statement.

  • LEDGER=ON
  • APPEND_ONLY=ON
CREATE SCHEMA [SecurityAccessControl] 
GO
CREATE TABLE [SecurityAccessControl].[LoginEvents]
    (
        [EmpID] INT NOT NULL,
        [AccessDescription] NVARCHAR (MAX) NOT NULL,
        [Timestamp] Datetime2 NOT NULL
    )
    WITH (
          LEDGER = ON (
                       APPEND_ONLY = ON
                       )
         );
INSERT INTO [SecurityAccessControl].[LoginEvents]
VALUES ('1234', 'MainGateEntry', getdate())

Similar to the updated ledger table, the append-only table inserts GENERATED ALWAYS columns in the table. As shown below, it records the transaction id in the ledger_start_transaction_id and sequence number in the ledger_start_sequence_number column.

SELECT *
     ,[ledger_start_transaction_id]
     ,[ledger_start_sequence_number]
FFROM [AccessControl].[KeyCardEvents]
select data

If you try to perform an UPDATE or DELETE transaction for the append-only table, you get the following error message - "Updates are not allowed for the append-only ledger table".

Delete FROM [SecurityAccessControl].[LoginEvents] WHERE empid=1234
delete data

Verify a Ledger Database to Detect Tampering

You can validate the data integrity of the Azure SQL Database ledger to detect any malicious or tampering activity. We have already checked the option – Enable Automatic Digest Storage during the ledger configuration.

In the Azure portal, navigate to the ledger database and click on Security > Ledger. It shows the digest storage configuration that we did earlier.

verify database azure ledger

On the top, you have an option – Verify Database. It gives you a SQL script that verifies the database against the hash value stored in the digest file.

verify database azure ledger

You need to copy this script and run it on the Azure ledger database. It uses the sys.sp_verify_database_ledger_from_digest_storage stored procedure for data validation.

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;
EEND CATCH
verify data azure ledger
Next Steps


Last Updated: 2021-06-09


get scripts

next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips



Comments For This Article





download





Recommended Reading

Introduction to Azure SQL Database Ledger for Data Tampering Protection

Adding Users to Azure SQL Databases

Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime - Part 1

Transfer Files from SharePoint To Blob Storage with Azure Logic Apps

Process Blob Files Automatically using an Azure Function with Blob Trigger














get free sql tips
agree to terms