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
