Ledger - Automatic digest upload for SQL Server without Azure connectivity

[Guest PieterVanhove]

Ledger is a new feature that offers the power of blockchain in Azure SQL Database and SQL Server 2022. We’re making the data in the SQL database verifiable using the same cryptographic patterns seen in blockchain technology, while keeping the flexibility and performance of a traditional database. Ledger databases are centrally managed databases that can also cryptographically attest to other parties, such as auditors or other business parties. Ledger ensures your data can be trusted and hasn't been tampered with.

 

 

 

How it works

 

 

Each transaction that the database executes is cryptographically hashed (SHA-256). Transactions are cryptographically linked together, like a blockchain. Cryptographically hashed database digests represent the state of the database. They can be periodically generated and stored outside the database in a tamper-proof storage location, such as an Azure immutable Blob storage or Azure Confidential Ledger (only for Azure SQL Database). Database digests are later used to verify the integrity of the database by comparing the value of the hash in the digest against the calculated hashes in the database.

 

 

 

How to configure automatic digest upload for SQL Server instances without Azure connectivity?

 

 

While working with customers on ledger for the Early Adoption Program of SQL Server 2022, many of them had concerns about the automatic digest upload for on-premises SQL Server instances. Microsoft currently only supports Azure immutable blob storage for ledger, but many customers don't open internet connection or connection to Azure for their SQL Server due to internal security policies. The customers needed to write their own custom solution to manually generate the digests and store them securely on their on-premises Write Once Read Many (WORM) storage devices. To help our customers, we have created 2 scripts that could be used as a starting point and can be modified according to the customers’ needs.

 

 

 

Scripts

 

Generate manual digests with SQL Agent Job

 

 

This job is going to execute the sp_generate_database_ledger_digest stored procedure on a regular basis. The schedule is currently set to 30 seconds, just like the automatic digest upload. The digests are always added to the same file. The stored procedure is executed in a PowerShell job step. Since PowerShell passes results as streams of objects, cmdlets can differentiate between data and messages, and only put data on the output pipeline. Execute the script below on your SQL Server 2022 instance to create the SQL Agent Job.

 

Remark: Make sure you change:

 

• <--YourDatabase--> into the database name that you want to generate digests for e.g., ConstosoHR
  
• <--YourDigestFile--> into the file path where you want to store your digest file e.g., C:\Ledger\Digests.txt. This should be a location on the WORM device.
  

 

 

 

USE [msdb]

GO

 

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

 

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

END

 

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Generate Manual Digest',

@enabled=0,

@notify_level_eventlog=0,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N'No description available.',

@category_name=N'[uncategorized (Local)]',

@owner_login_name=N'', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, _name=N'Generate Manual Digest',

_id=1,

@cmdexec_success_code=0,

_success_action=1,

_success_step_id=0,

_fail_action=2,

_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'PowerShell',

@command=N'Set-Location "SQLSERVER:\SQL\.\$(ESCAPE_NONE(SRVR))\Databases\<--YourDatabase-->"

Invoke-Sqlcmd "SET NOCOUNT ON; EXEC sp_generate_database_ledger_digest" -Verbose | % { $_[0] } | out-file ''<--YourDigestFile-->'' -Append',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every 30 seconds',

@enabled=1,

@freq_type=4,

@freq_interval=1,

@freq_subday_type=2,

@freq_subday_interval=30,

@freq_relative_interval=0,

@freq_recurrence_factor=0,

@active_start_date=20220414,

@active_end_date=99991231,

@active_start_time=0,

@active_end_time=235959,

@schedule_uid=N'0d1ace0f-c54d-4021-afbe-7e4817fac283'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, _name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

 

 

 

 

Run manual verification

 

 

The stored procedure below fetches the digest file from a file location. This can be set with the parameter @FileLocation. This should be a location on the WORM device that you have used to store the digest file on. It reads all digests that were generated, creates a JSON array of the digests, and passes that to the stored procedure sp_verify_database_ledger to run the database verification. This new stored procedure could also be scheduled by a SQL Agent Job to run the verification on a regular basis.

 

 

 

 

 

CREATE PROCEDURE sp_verify_manual_digest

@FileLocation NVARCHAR(256)

AS

BEGIN

SET NOCOUNT ON

CREATE TABLE #ManualDigests(

Content NVARCHAR(1000)

)

 

Declare @Statement NVARCHAR(max)

SET @Statement='

BULK INSERT #ManualDigests

FROM ''' + @FileLocation + '''

WITH

(

DATAFILETYPE = ''widechar'',

ROWTERMINATOR =''\n''

);'

EXECUTE SP_EXECUTESQL @Statement

DECLARE @Digest NVARCHAR(MAX)

SELECT @Digest='[' + STRING_AGG(Content, ',') + ']' FROM #ManualDigests

EXECUTE sp_verify_database_ledger @Digest

DROP TABLE #ManualDigests

END

GO

 

Continue reading...