SQL Server meet Blockchain!
The invention of digital storage has revolutionized our world even more than digital computing. While the first generation of digital computers were used almost exclusively for number-crunching, today computers are far more likely to be used for information management. Without the digital storage capabilities provided by today’s database management systems, our “Big Data” world could not exist.
Information technology has changed our lives — but not always for the better. Advances in database technology allow us to process a far greater Velocity, Volume, and Variety of data than ever before. These are the famous “three Vs” of Big Data. However, there’s a missing V in this formulation — Veracity. Because digital information is so easy to copy and manipulate it’s also easy to fabricate or falsify. Once written, digital information can be overwritten without a trace. The result is the proliferation of fake news, forged and tampered documents, and a general reduction in trust within society.
There’s a missing “V” in the Big Data equation — Veracity
The problem of trust exists within database services as well. Typically, the contents of a database like SQL Server are ultimately determined by application code or a DBA. If a row in the database has a creation date of two years ago, you generally have no practical way of proving that the row wasn’t really created or modified yesterday.
Blockchain offers a solution to the digital trust dilemma. Once written, public blockchain records cannot be overwritten. For the first time, we have a digital medium that is truly immutable and whose timestamp is absolutely trustworthy.
Blockchain offers a solution to the digital trust dilemma.
Unfortunately, a blockchain is not a database. It doesn’t provide the storage capabilities, functionality or throughput that we need to build modern applications. Therefore, if we want to use blockchain to add trust to modern applications we have to find a way to link blockchain to existing database systems.
ProvenDB for SQL Server
We built ProvenDB to bridge the gap between blockchain and database systems. ProvenDB integrates a MongoDB-compatible database service with public blockchain capabilities; providing the best of both worlds — at least if you are happy with the MongoDB document database model.
However, if you want to integrate an SQL Server database with a public blockchain you’ve been out of luck — until now! We are very happy to announce ProvenDB for SQL Server, which allows you to anchor your SQL Server data to the public blockchain of your choice. The resulting blockchain proofs allow you to prove the origin date of your SQL Server data and to prove that the data has not been tampered with.
How it works
When you have data in a SQL Server database that you want to anchor to the databases, you invoke the fanchor stored procedure. This causes cryptographic signatures of the data to be created. These signatures can be “signed” by your company’s cryptographic key (possibly the same key that guarantees the identity of your website). These signatures are aggregated and anchored to a public Blockchain such as Bitcoin, Hedera, or Ethereum.
Once anchored to the public Blockchain, the signatures form an impeccable and irrefutable proof of the integrity and origin time of the SQL Server rows. The Blockchain record — which cannot be altered by any known technology — proves the overall integrity and timestamp of items in the database, eliminating any possibility of undetected tampering or backdating.
Getting Started
Download provendb-sqlserver from the ProvenDB downloads page at https://www.provendb.com/downloads. Run the installation script to install the provendb-sqlserver binaries on your desktop.
PS C:\tools\bin> & ([ScriptBlock]::Create((New-Object Net.WebClient).DownloadString('https://raw.githubusercontent.com/SouthbankSoftware/provendb-sqlserver/main/installation/install.ps1')))
Installing from `provendb-sqlserver-windows.zip` to `C:\tools\bin`...
Next thing is to install our metadata tables into your target SQL Server database:
PS C:\tools\bin> provendb-sqlserver install `
>> --dbaPassword=xxxxxxx--dbaUserName=SA `
>> --sqlConnect='Server=guy13;1433;Encrypt=false;Trusted_Connection=True;TrustServerCertificate=True' `
>> --provendbPassword=xxxxxxx `
>> --provendbUser=provendb --dropExisting `
>> --createDemoAccount --config=provendb.yaml
17:15:27.439 INFO Connecting to SQL Server
17:15:27.641 INFO Dropping user and objects for provendbdemo
17:15:27.817 WARN Cannot drop the user 'provendbdemo', because it does not exist or you do not have permission. while executing DROP USER provendbdemo
17:15:27.833 INFO Dropping user and objects for provendb
17:15:28.016 INFO Creating user and schema
17:15:28.676 INFO Creating tables and procedures
17:15:28.823 INFO Creating user and schema
17:15:30.803 INFO Wrote new config to provendb.yaml
17:15:30.803 INFO This configuration uses a trial key - get your key at https://app.provendb.com/app/dashboard/api-keys
17:15:30.803 INFO Install completeRunning the monitor
The provendb monitor must be running to process provenDB stored procedure commands. Let’s start it with default settings (it will use the provendb.yaml file we created in the previous step to work out how to connect to the database:
PS C:\tools\bin> provendb-sqlserver monitor
17:18:40.043 INFO Connecting to SQL Server
17:18:40.285 INFO Monitoring with 120 s interval.
17:18:40.285 INFO Looking for new requests in the provendbRequests table
17:18:40.405 INFO Waiting for 120 secondsCreating proofs
The fanchorrequest stored procedure allows us to request that data in the database be anchored to the blockchain. Below, we request that data in the CONTRACTSTABLE with contractIDs between 0 and 100 be anchored:
PS: C:\tools\bin> sqlcmd.sh -U provendb -P DBEnvy2016
1> /* Setup some useful variables */
2> XVARTYPEWIDTH 256
3> 256
4> go
Msg 102, Level 15, State 1, Server 01192e6a19b8, Line 3
Incorrect syntax near '256'.
1>
1> :setvar SQLCMDMAXVARTYPEWIDTH 256
2> :setvar SQLCMDMAXFIXEDTYPEWIDTH 256
3> use provendb
4>
5> EXEC [dbo].[fanchorrequest] 'provendbdemo.dbo.contractstable' , 'contractData,metaData', 'contractId BETWEEN 0 and 100' , 'CONTRACTID'
6> go
Changed database context to 'provendb'.
(1 rows affected)
----------------------------------------
1
The procedure returns the requestId: in this case “1” since this is our first request. See https://provendb.readme.io/docs/sqlfanchorrequest for details of the stored procedure arguments. In this case, we’ve specified CONTRACTID as the primary key and contractData and metaData as the columns to be anchored.
If we look at the output from the monitor, we’ll see this:
17:24:41.362 INFO Processing request {"table":"provendbdemo.dbo.contractstable","columns":"contractData,metaData","where":"contractId BETWEEN 0 and 100","keyColumn":"CONTRACTID"}
17:24:41.457 INFO --> Anchoring data to HEDERA
17:24:41.457 INFO 100 keys
17:25:19.113 INFO Anchored to https://testnet.dragonglass.me/hedera/search?q=69aa8cee75791de41fba50865855fdc946bd32bc33dddbf559c5530ccbbb26b3ee1103ac559e44e590c636800a18f853
17:25:19.113 INFO Saving proof df3f85292c2cbc2bcad8830c9f33d0745282c8dceb6aceb013ff1b13c9315f5a:tFAtzIVLV-EulSheQEXH6 to db
17:25:19.321 INFO Proof df3f85292c2cbc2bcad8830c9f33d0745282c8dceb6aceb013ff1b13c9315f5a:tFAtzIVLV-EulSheQEXH6 created and stored to DB
17:25:19.327 INFO Request 1 succeededOur data was hashed and a Merkle tree “signature” of the data was placed on an immutable blockchain.
Validating data
We can use the fvalidateRequestId procedure to validate that the data has not been tampered with. Let’s do that for the proof we just created:
1> EXEC [dbo].[fvalidaterequestid] 1
2> go(1 rows affected)
----------------------------------------
2(1 rows affected)
This creates a validate request with ID 2. The monitor output reports on the validation:
17:33:20.415 INFO Processing request {"proofId":"df3f85292c2cbc2bcad8830c9f33d0745282c8dceb6aceb013ff1b13c9315f5a:tFAtzIVLV-EulSheQEXH6"}
17:33:20.456 INFO PASS: data hash matches proof hash
17:33:20.466 INFO Wrote proof to df3f85292c2cbc2bcad8830c9f33d0745282c8dceb6aceb013ff1b13c9315f5a:tFAtzIVLV-EulSheQEXH6.provendb
17:33:20.466 INFO PASS: Validated all data hashes for df3f85292c2cbc2bcad8830c9f33d0745282c8dceb6aceb013ff1b13c9315f5a:tFAtzIVLV-EulSheQEXH6
17:33:20.466 INFO Validating Chainpoint proof
17:33:21.652 INFO HEDERA transaction 69aa8cee75791de41fba50865855fdc946bd32bc33dddbf559c5530ccbbb26b3ee1103ac559e44e590c636800a18f853 has hash value df3f85292c2cbc2bcad8830c9f33d0745282c8dceb6aceb013ff1b13c9315f5a
17:33:21.652 INFO PASS: blockchain hash matches proof hash
17:33:21.652 INFO PASS: Proof validated with hash df3f85292c2cbc2bcad8830c9f33d0745282c8dceb6aceb013ff1b13c9315f5a on https://testnet.dragonglass.me/hedera/search?q=69aa8cee75791de41fba50865855fdc946bd32bc33dddbf559c5530ccbbb26b3ee1103ac559e44e590c636800a18f853We can also look in the provendbrequests table to get information about the validation request:
1> SELECT * from provendbrequests where id=2
2> go
id requestType requestJSON status statusDate messages proofId
-------------------- ------------ ------------------------------------------------------------------------------------------------------
2 VALIDATE {"proofId":"df3f85292c2cbc2bcad8830c9f33d0745282c8dceb6aceb013ff1b13c9315f5a:tFAtzIVLV-EulSheQEXH6"} SUCCESS 2021-12-17 ["PASS: Validated all data hashes for df3f85292c2cbc2bcad8830c9f33d0745282c8dceb6aceb013ff1b13c9315f5a:tFAtzIVLV-EulSheQEXH6","PASS: Proof validated with hash df3f85292c2cbc2bcad8830c9f33d0745282c8dceb6aceb013ff1b13c9315f5a on https://testnet.dragonglass.m df3f85292c2cbc2bcad8830c9f33d0745282c8dceb6aceb013ff1b13c9315f5a:tFAtzIVLV-EulSheQEXH6What if the data is manipulated?
The whole point of all this is to try and prevent and detect data tampering. Let’s see what happens if someone tries to manipulate the data.
Here I change the metadata for one of the contracts:
1> UPDATE provendbdemo.dbo.contractstable SET METADATA='{"info":"'+CAST(getDate() AS VARCHAR(100))+'"}'
2> WHERE CONTRACTID=11;
3> goNow if I revalidate my proof:
1> EXEC [dbo].[fvalidaterequestid] 1
2> goProvenDB reports that the proof is invalidated, and reports on the rows that no longer match the blockchain proof:
17:43:22.960 INFO Processing request {"proofId":"df3f85292c2cbc2bcad8830c9f33d0745282c8dceb6aceb013ff1b13c9315f5a:tFAtzIVLV-EulSheQEXH6"}
17:43:22.992 ERROR FAIL: proof hash does not match data hash proof: df3f85292c2cbc2bcad8830c9f33d0745282c8dceb6aceb013ff1b13c9315f5a, data: 0d34e6948439e969671a52f799852f1c336c3633638e4e6bfa20b1442fc8ac88
17:43:22.992 ERROR Hash mismatch on key 11
17:43:22.992 ERROR FAIL: Mismatch in data hashes for df3f85292c2cbc2bcad8830c9f33d0745282c8dceb6aceb013ff1b13c9315f5a:tFAtzIVLV-EulSheQEXH6 - see log
17:43:23.008 ERROR Request 3 failed: ["FAIL: Mismatch in data hashes for df3f85292c2cbc2bcad8830c9f33d0745282c8dceb6aceb013ff1b13c9315f5a:tFAtzIVLV-EulSheQEXH6 - see log",{"badKeys":["11"]}]There’s simply no way to change the blockchain signature and consequently no way to get away with any data tampering!
Give it a try!
ProvenDB for SQL Server is available now for early adopters. Check out the documentation and download the binaries from our downloads page. We’re super keen to find out what you think!
ProvenDB uses Blockchain technology to provide the world’s first entirely trustworthy database. Immutable versions of database state are anchored to the Blockchain, delivering an unparalleled level of data integrity. ProvenDB allows developers to build high-performance applications that include cryptographic proof of data integrity and provenance without having to understand Blockchain programming complexities. Setup a free account today at ProvenDB.com.
