Azure SQL Database Ledger Getting Started and Examples

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.

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

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.

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.

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

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.

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

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

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.

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

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.

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.

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.

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.

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

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]

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

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.

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.

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

Next Steps

• Implement Azure SQL Database Ledger and benefit from the blockchain technology inside SQL Server.
• Go through official Microsoft documentation on Microsoft docs.
• You can also refer to the Maintain Cryptographically Verifiable Data in Azure SQL Database whitepaper for more details.