Homomorphic Encryption Algorithm in SQL Server

Problem

Homomorphic encryption is a cryptographic algorithm that lets computations be performed directly on encrypted data without needing to decrypt it. This enables secure outsourcing of computations on sensitive data while preserving privacy.

Is it possible to have homomorphic encryption in SQL Server?

Solution

Fully homomorphic encryption (FHE) lets organizations securely process, analyze, and share encrypted data in untrusted environments without ever decrypting it. Enabling true zero-trust computing, supporting AI, machine learning, and big data analytics while maintaining privacy and compliance in applications such as financial predictive modeling, healthcare research, and consumer behavior insights.

Terms and definitions

Let’s define some key terms before we dive into our first example:

Public Key Pair

Public Key Pair is a cryptographic key, paired with a private key, that can be shared openly and is used to verify digital signatures created with the corresponding private key.

Private Key Pair

Private Key Pair is a secret cryptographic key, unique to its owner, used to create digital signatures that can be verified with the corresponding public key.

Paillier Cryptosystem

Paillier Cryptosystem is a probabilistic asymmetric algorithm for public key cryptography.

True Zero-Trust Computing

True Zero-Trust Computing means that no system, user, or environment is ever inherently trusted, where every interaction must be verified, and sensitive data is never exposed in unsecure form.

Average Blood Pressure

Average blood pressure means the overall pressure in your arteries during a heartbeat cycle. Since your heart spends more time resting (diastolic) than pumping (systolic), doctors calculate the Average BP = (2 * Diastolic + Systolic) / 3.

Example

FHE enables computation on encrypted data, but is currently slower, heavier, and harder to use than traditional methods, but though research and with hardware acceleration are rapidly closing the gap.

Besides the fact that SQL Server has the bigint data type, this data type has a limitation of 64 bits. When working with encryption it is normal to work with numbers like 2^1024 which has 308 decimal digits, which is far beyond the bigint capacity.

One option is to work with Python, but due to some incompatibilities I faced, I decide to create a CLR solution for this tip.

Visual Studio

Open Visual Studio:

  1. Get started choosing Create a new project
  2. Choose the template of Class Library (.Net framework) – Visual Basic
  3. I will call the Project SqlMathClr and click Create
  4. Rename the Class1.vb file to SqlMath.vb
  5. Replace the content of SqlMath.vb with the code below
  6. Build the solution
  7. Record the path of the created SqlMathClr.dll, in my case “E:\VS Projects\SqlMathClr\SqlMathClr\bin\Release”

SqlMath.vb code:

Imports System.Data.SqlTypes
Imports System.Numerics
Imports Microsoft.SqlServer.Server
 
Public Class SqlMath
    Private Shared ReadOnly rnd As New Random()
 
    <SqlFunction(IsDeterministic:=True, IsPrecise:=True)>
    Public Shared Function BigAdd(num1 As SqlString, num2 As SqlString) As SqlString
        Dim a As BigInteger = BigInteger.Parse(num1.Value)
        Dim b As BigInteger = BigInteger.Parse(num2.Value)
        Return New SqlString(BigInteger.Add(a, b).ToString())
    End Function
 
    <SqlFunction(IsDeterministic:=True, IsPrecise:=True)>
    Public Shared Function BigDivide(num1 As SqlString, num2 As SqlString) As SqlString
        Dim a As BigInteger = BigInteger.Parse(num1.Value)
        Dim b As BigInteger = BigInteger.Parse(num2.Value)
        Return New SqlString(BigInteger.Divide(a, b).ToString())
    End Function
 
    <SqlFunction(IsDeterministic:=True, IsPrecise:=True)>
    Public Shared Function BigGCD(num1 As SqlString, num2 As SqlString) As SqlString
        Dim a As BigInteger = BigInteger.Parse(num1.Value)
        Dim b As BigInteger = BigInteger.Parse(num2.Value)
        Return New SqlString(BigInteger.GreatestCommonDivisor(a, b).ToString())
    End Function
 
    <SqlFunction(IsDeterministic:=True, IsPrecise:=True)>
    Public Shared Function BigLCM(num1 As SqlString, num2 As SqlString) As SqlString
        Dim a As BigInteger = BigInteger.Parse(num1.Value)
        Dim b As BigInteger = BigInteger.Parse(num2.Value)
        If a.IsZero OrElse b.IsZero Then
            Return New SqlString("0")
        End If
        Dim gcd As BigInteger = BigInteger.GreatestCommonDivisor(a, b)
        Dim lcm As BigInteger = BigInteger.Abs(a * b / gcd)
        Return New SqlString(lcm.ToString())
    End Function
 
    <SqlFunction(IsDeterministic:=True, IsPrecise:=True)>
    Public Shared Function BigMod(num As SqlString, modNum As SqlString) As SqlString
        Dim n As BigInteger = BigInteger.Parse(num.Value)
        Dim m As BigInteger = BigInteger.Parse(modNum.Value)
        Return New SqlString(BigInteger.Remainder(n, m).ToString())
    End Function
 
    <SqlFunction(IsDeterministic:=True, IsPrecise:=True)>
    Public Shared Function BigModPow(baseNum As SqlString, exponent As SqlString, modNum As SqlString) As SqlString
        Dim b As BigInteger = BigInteger.Parse(baseNum.Value)
        Dim e As BigInteger = BigInteger.Parse(exponent.Value)
        Dim m As BigInteger = BigInteger.Parse(modNum.Value)
        Return New SqlString(BigInteger.ModPow(b, e, m).ToString())
    End Function
 
    <SqlFunction(IsDeterministic:=True, IsPrecise:=True)>
    Public Shared Function BigMultiply(num1 As SqlString, num2 As SqlString) As SqlString
        Dim a As BigInteger = BigInteger.Parse(num1.Value)
        Dim b As BigInteger = BigInteger.Parse(num2.Value)
        Return New SqlString(BigInteger.Multiply(a, b).ToString())
    End Function
 
    <SqlFunction(IsDeterministic:=True, IsPrecise:=True)>
    Public Shared Function BigPow(baseNum As SqlString, exponent As SqlInt32) As SqlString
        Dim baseBI As BigInteger = BigInteger.Parse(baseNum.Value)
        Dim result As BigInteger = BigInteger.Pow(baseBI, exponent.Value)
        Return New SqlString(result.ToString())
    End Function
 
    <SqlFunction(IsDeterministic:=True, IsPrecise:=True)>
    Public Shared Function BigSubtract(num1 As SqlString, num2 As SqlString) As SqlString
        Dim a As BigInteger = BigInteger.Parse(num1.Value)
        Dim b As BigInteger = BigInteger.Parse(num2.Value)
        Return New SqlString(BigInteger.Subtract(a, b).ToString())
    End Function
 
    <SqlFunction(IsDeterministic:=False, IsPrecise:=True)>
    Public Shared Function GenerateCoprime(num1 As SqlString) As SqlString
        Dim a As BigInteger = BigInteger.Parse(num1.Value)
        Dim candidate As BigInteger
 
        If a <= 1 Then
            Throw New ArgumentException("Number must be greater than 1")
        End If
 
        Do
            candidate = RandomBigInteger(a)
        Loop While BigInteger.GreatestCommonDivisor(candidate, a) <> 1
 
        Return New SqlString(candidate.ToString())
    End Function
 
    <SqlFunction()>
    Public Shared Function HDecrypt(pkn As SqlString, pkg As SqlString, skLambda As SqlString, skmu As SqlString, num As SqlString) As SqlString
        Dim n As BigInteger = BigInteger.Parse(pkn.Value)
        Dim n2 As BigInteger = BigInteger.Multiply(n, n)
        Dim g As BigInteger = BigInteger.Parse(pkg.Value)
        Dim c As BigInteger = BigInteger.Parse(num.Value)
        Dim s As BigInteger = BigInteger.Parse(skLambda.Value)
        Dim m As BigInteger = BigInteger.Parse(skmu.Value)
        Dim u As BigInteger = BigInteger.ModPow(c, s, n2)
        Dim L As BigInteger = BigInteger.Divide((u - 1), n)
        Dim x As BigInteger = (L * m) Mod n
 
        Return x.ToString
    End Function
 
    <SqlFunction()>
    Public Shared Function HEncrypt(pkn As SqlString, pkg As SqlString, num As SqlString) As SqlString
        Dim n As BigInteger = BigInteger.Parse(pkn.Value)
        Dim n2 As BigInteger = BigInteger.Multiply(n, n)
        Dim g As BigInteger = BigInteger.Parse(pkg.Value)
        Dim m As BigInteger = BigInteger.Parse(num.Value)
        Dim r As BigInteger = BigInteger.Parse(GenerateCoprime(pkn))
        Dim x As BigInteger = (BigInteger.ModPow(g, m, n2) * BigInteger.ModPow(r, n, n2)) Mod n2
        Return x.ToString
    End Function
 
    <SqlFunction()>
    Public Shared Function ModInverse(num1 As SqlString, num2 As SqlString) As SqlString
        Dim a As BigInteger = BigInteger.Parse(num1.Value)
        Dim b As BigInteger = BigInteger.Parse(num2.Value)
        Return ModInverseInternal(a, b).ToString()
    End Function
 
    Private Shared Function ModInverseInternal(a As BigInteger, m As BigInteger) As BigInteger
        Dim t As BigInteger = 0
        Dim newT As BigInteger = 1
        Dim r As BigInteger = m
        Dim newR As BigInteger = a
 
        While newR <> 0
            Dim q As BigInteger = BigInteger.Divide(r, newR)
            Dim tmpT As BigInteger = t
            t = newT
            newT = tmpT - q * newT
            Dim tmpR As BigInteger = r
            r = newR
            newR = tmpR - q * newR
        End While
 
        If r > 1 Then
            Throw New ArgumentException("a is not invertible modulo m")
        End If
        If t < 0 Then
            t += m
        End If
        Return t
    End Function
 
    Private Shared Function RandomBigInteger(max As BigInteger) As BigInteger
        Dim bytes(max.ToByteArray().Length - 1) As Byte
        Dim result As BigInteger
        Do
            rnd.NextBytes(bytes)
            bytes(bytes.Length - 1) = 0
            result = New BigInteger(bytes)
        Loop While result <= 1 OrElse result >= max
        Return result
    End Function
 
End Class

If you review the code, you will see that I am returning all of the values as string due to overflow problems with the bigint data type in SQL Server.

SQL Server CLR

The first thing that you need to do it to enable CLR:

-- MSSQLTips (TSQL)
 
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO

It is necessary for security reasons to add the dll as trusted assembly. Change the path to your destination.

-- MSSQLTips (TSQL)
 
DECLARE @hash varbinary(64) = 
    (SELECT HASHBYTES('SHA2_512', BulkColumn)
        FROM OPENROWSET(BULK 'E:\VS Projects\SqlMathClr\SqlMathClr\bin\Release\SqlMathClr.dll', SINGLE_BLOB) AS AssemblyBlob);
 
EXEC sp_add_trusted_assembly 
    @assembly_hash = @hash,
    @description = N'SQL Math CLR assembly';
GO

Now it is necessary to register the assembly, creating it. Change the path to your destination.

-- MSSQLTips (TSQL)
 
CREATE ASSEMBLY [AsmMath]
    FROM 'E:\VS Projects\SqlMathClr\SqlMathClr\bin\Release\SqlMathClr.dll'
    WITH PERMISSION_SET = SAFE;
GO

In case of you are updating the already created AsmMath, execute it again and change the path to your destination.

-- MSSQLTips (TSQL)
 
ALTER ASSEMBLY [AsmMath]
    FROM 'E:\VS Projects\SqlMathClr\SqlMathClr\bin\Release\SqlMathClr.dll'
    WITH PERMISSION_SET = SAFE;
GO

I will create a schema to group all CLR functions.

-- MSSQLTips (TSQL)
 
CREATE SCHEMA AsmMath;
GO

Now we are going to add all functions to the schema.

-- MSSQLTips (TSQL)
 
CREATE FUNCTION AsmMath.BigAdd(@a nvarchar(MAX), @b nvarchar(MAX))
RETURNS nvarchar(MAX)
AS EXTERNAL NAME [AsmMath].[SqlMathClr.SqlMath].BigAdd;
GO
 
CREATE FUNCTION AsmMath.BigDivide(@a nvarchar(MAX), @b nvarchar(MAX))
RETURNS nvarchar(MAX)
AS EXTERNAL NAME [AsmMath].[SqlMathClr.SqlMath].BigDivide;
GO
 
CREATE FUNCTION AsmMath.BigGCD(@a nvarchar(MAX), @b nvarchar(MAX))
RETURNS nvarchar(MAX)
AS EXTERNAL NAME [AsmMath].[SqlMathClr.SqlMath].BigGCD;
GO
 
CREATE FUNCTION AsmMath.BigLCM(@a nvarchar(MAX), @b nvarchar(MAX))
RETURNS nvarchar(MAX)
AS EXTERNAL NAME [AsmMath].[SqlMathClr.SqlMath].BigLCM;
GO
 
CREATE FUNCTION AsmMath.BigMod(@a nvarchar(MAX), @m nvarchar(MAX))
RETURNS nvarchar(MAX)
AS EXTERNAL NAME [AsmMath].[SqlMathClr.SqlMath].BigMod;
GO
 
CREATE FUNCTION AsmMath.BigModInverse(@a NVARCHAR(MAX), @m NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME [AsmMath].[SqlMathClr.SqlMath].ModInverse;
GO
 
CREATE FUNCTION AsmMath.BigModPow(@a nvarchar(MAX), @exp nvarchar(MAX), @m nvarchar(MAX))
RETURNS nvarchar(MAX)
AS EXTERNAL NAME [AsmMath].[SqlMathClr.SqlMath].BigModPow;
GO
 
CREATE FUNCTION AsmMath.BigMultiply(@a nvarchar(MAX), @b nvarchar(MAX))
RETURNS nvarchar(MAX)
AS EXTERNAL NAME [AsmMath].[SqlMathClr.SqlMath].BigMultiply;
GO
 
CREATE FUNCTION AsmMath.BigPow(@a nvarchar(MAX), @exp INT)
RETURNS nvarchar(MAX)
AS EXTERNAL NAME [AsmMath].[SqlMathClr.SqlMath].BigPow;
GO
 
CREATE FUNCTION AsmMath.BigSubtract(@a nvarchar(MAX), @b nvarchar(MAX))
RETURNS nvarchar(MAX)
AS EXTERNAL NAME [AsmMath].[SqlMathClr.SqlMath].BigSubtract;
GO
 
CREATE FUNCTION AsmMath.GenCoprime(@a NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME [AsmMath].[SqlMathClr.SqlMath].GenCoprime;
GO
 
CREATE FUNCTION AsmMath.HEncrypt(@pkn NVARCHAR(MAX), @pkg NVARCHAR(MAX), @num NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME [AsmMath].[SqlMathClr.SqlMath].HEncrypt;
GO
 
CREATE FUNCTION AsmMath.HDecrypt(@pkn NVARCHAR(MAX), @pkg NVARCHAR(MAX), @sklambda NVARCHAR(MAX), @skmu NVARCHAR(MAX), @num NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME [AsmMath].[SqlMathClr.SqlMath].HDecrypt;
GO

If you want to verify the assembler run this code:

-- MSSQLTips (TSQL)
 
SELECT       [name]
            ,[clr_name]
            ,[create_date]
            ,[modify_date]
    FROM     sys.assemblies
    WHERE    [name] = 'AsmMath';
GO

In case you need to remove the CLR, run this code:

-- MSSQLTips (TSQL)
 
DROP FUNCTION IF EXISTS AsmMath.BigAdd;
DROP FUNCTION IF EXISTS AsmMath.BigDivide;
DROP FUNCTION IF EXISTS AsmMath.BigGCD;
DROP FUNCTION IF EXISTS AsmMath.BigLCM;
DROP FUNCTION IF EXISTS AsmMath.BigMod;
DROP FUNCTION IF EXISTS AsmMath.BigModInverse;
DROP FUNCTION IF EXISTS AsmMath.BigModPow;
DROP FUNCTION IF EXISTS AsmMath.BigMultiply;
DROP FUNCTION IF EXISTS AsmMath.BigPow;
DROP FUNCTION IF EXISTS AsmMath.BigSubtract;
DROP FUNCTION IF EXISTS AsmMath.GenCoprime;
DROP FUNCTION IF EXISTS AsmMath.HDecrypt;
DROP FUNCTION IF EXISTS AsmMath.HEncrypt;
 
DROP ASSEMBLY IF EXISTS AsmMath;
GO

SQL Server function

The first function is to create a prime number:

-- =============================================
-- Author:         SCP - MSSQLTips
-- Create date:    20250822
-- Description:    Generate a large prime number
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[ufnNumberPrimeNew]
                (@Seed bigint)
RETURNS bigint
AS
BEGIN
    DECLARE @max bigint = 999999999999999;
    DECLARE @prime bigint;
 
    SET @prime = @Seed;
 
    WHILE @prime <= @max BEGIN
        IF [dbo].[ufnNumberPrimeChk] (@prime) = 1 
            BREAK;
 
        SET @prime = @prime + 1;
    END
 
    RETURN @prime;
END
GO

The second function is to check if the number is prime:

-- =============================================
-- Author:         SCP - MSSQLTips
-- Create date:    20250822
-- Description:    Check if a number is prime
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[ufnNumberPrimeChk] 
                (@n bigint)
RETURNS bit
AS
BEGIN
    IF @n < 2 OR @n % 2 = 0
        RETURN 0;
 
    IF @n IN (2,3)
        RETURN 1;
 
    DECLARE @i bigint = 2;
 
    WHILE @i * @i <= @n BEGIN
        IF @n % @i = 0
            RETURN 0;
 
        SET @i += 1;
    END
 
    RETURN 1;
END
GO

SQL Server Table

Patients Blood Pressure

CREATE TABLE [dbo].[PatientsBloodPressure](
    [Id] [int] NOT NULL,
    [DummySystolic] [decimal](10, 1) NULL,
    [DummyDiastolic] [decimal](10, 1) NULL,
    [DummyAvg] [decimal](10, 1) NULL,
    [BPEncrypted] [nvarchar](max) NULL,
 CONSTRAINT [PK_PatientBloodPressure] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Examples

Testing the assembly’s:

-- MSSQLTips (TSQL)
 
SELECT AsmMath.BigPow('2', 1024) AS TwoPower1024;
SELECT AsmMath.BigAdd('12345678901234567890', '98765432109876543210') AS BigAdd;
SELECT AsmMath.BigMultiply('12345678901234567890', '98765432109876543210') AS BigProduct;
SELECT AsmMath.BigGCD('12345678901234567890', '98765432109876543210') AS GCD;
SELECT AsmMath.BigLCM('12345678901234567890', '98765432109876543210') AS LCM;
SELECT AsmMath.BigModPow('987654321', '12345', '1000000007') AS ModExp;
SELECT AsmMath.GenCoprime(1091555129684048179) AS Coprime;
GO

Resulting in:

CLR functions example

Blood pressure study

In healthcare, AES is ideal for encrypting patient records stored internally, allowing trusted staff to decrypt and process data as needed. Fully homomorphic encryption (FHE), on the other hand, is used when sensitive patient data must remain confidential while enabling external parties to perform analytics or machine learning on it without ever accessing the raw data, ensuring privacy compliance while still extracting valuable insights.

A certain hospital wants to collaborate with a third-party research organization in a study to calculate the average blood pressure of its patients, but privacy laws prevent sharing raw patient information.

To ensure confidentiality, the hospital first generates a public–private key pair. The public key is shared with patients, while the private key remains securely with the hospital manager.

Each patient is provided with an IoT blood pressure device. The device records the measurement and sends the result to the hospital through an API, encrypted with the hospital’s public key.

The hospital then collects all encrypted values and, using homomorphic encryption techniques, can compute the average blood pressure across all patients without ever decrypting individual measurements. This guarantees that no patient’s personal data is exposed at any stage of the study.

Generating the public and private keys

The first step is to generate two different prime numbers and then follow the process to obtain the keys:

-- MSSQLTips (TSQL)
 
DECLARE @p bigint = [dbo].[ufnNumberPrimeNew] (FLOOR(RAND(CHECKSUM(NEWID())) * 2147483647));
DECLARE @q bigint = @p;
 
WHILE @p = @q
    SET @q = [dbo].[ufnNumberPrimeNew] (FLOOR(RAND(CHECKSUM(NEWID())) * 2147483647));
 
DECLARE @n bigint = [AsmMath].[BigMultiply] (@p,@q);
DECLARE @n2 nvarchar(MAX) = [AsmMath].[BigMultiply] (@n,@n);
 
DECLARE @lambda bigint = [AsmMath].[BigLCM] (@p - 1,@q - 1);
DECLARE @g bigint = @n + 1;
 
 
DECLARE @u nvarchar(MAX) = [AsmMath].[BigModPow] (@g,@lambda,@n2);
DECLARE @l nvarchar(MAX) = [AsmMath].[BigDivide] ([AsmMath].[BigSubtract] (@u,1), @n); 
 
DECLARE @mu bigint = [AsmMath].[BigModInverse] (@l, @n);
 
SELECT 'Public Key',@n pkn, @g pkg;
SELECT 'Private Key',@lambda sklambda, @mu skmu;
GO

Resulting in:

Public and Private keys shared

For this example, we assume you are the hospital manager and will handle the private key protection. A dummy column will store unencrypted records to enable average calculations and also for us to visualize the real data. If you want you can change the values below to your previous generated ones.

-- MSSQLTips (TSQL)
 
-- PUBLIC KEYS ==================================
DECLARE @pkn bigint = '953070681873550127';
DECLARE @pkg bigint = '953070681873550128';
 
-- PRIVATE KEYS =================================
DECLARE @sklambda bigint = '476535339907528560';
DECLARE @skmu bigint = '180581948158477459';

Just to explain how it works lets sum the value of two encrypted numbers m1 and m2

-- MSSQLTips (TSQL)
 
-- PUBLIC KEYS ==================================
DECLARE @pkn bigint = '953070681873550127';
DECLARE @pkg bigint = '953070681873550128';
 
-- PRIVATE KEYS =================================
DECLARE @sklambda bigint = '476535339907528560';
DECLARE @skmu bigint = '180581948158477459';
 
DECLARE @m1 bigint = 10;
DECLARE @m2 bigint = 20;
DECLARE @n2 nvarchar(MAX) = [AsmMath].[BigMultiply] (@pkn,@pkn);
 
-- Encryption of m1 and m2
DECLARE @c1 nvarchar(MAX) = [AsmMath].[HEncrypt] (@pkn,@pkg,@m1);
DECLARE @c2 nvarchar(MAX) = [AsmMath].[HEncrypt] (@pkn,@pkg,@m2);
 
-- Sum of the encrypted values c1 and c2
DECLARE @cSum nvarchar(MAX) = [AsmMath].[BigMod] ([AsmMath].[BigMultiply](@c1,@c2),@n2);
 
DECLARE @u nvarchar(MAX) = [AsmMath].[BigModPow] (@cSum,@sklambda,@n2);
DECLARE @l nvarchar(MAX)= [AsmMath].[BigDivide] ([AsmMath].[BigSubtract] (@u,1), @pkn); 
 
SELECT     [AsmMath].[HDecrypt] (@pkn,@pkg,@sklambda,@skmu,@c1) m1
        ,@c1 m1Encrypted
        ,[AsmMath].[HDecrypt] (@pkn,@pkg,@sklambda,@skmu,@c2) m2
        ,@c2 m2Encrypted
        ,[AsmMath].[BigMod] ([AsmMath].[BigMultiply] (@l,@skmu),@pkn) AS Total
        ,@cSum TotalEncrypted;
GO

Resulting in the expected sum of values encrypted where I show the decrypted values to confirm.

Example of encrypted sum 10 + 20

Back to our hospital example at the patient side, each patient has its IOT to measure the Blood Pressure and send it throughout an API to the hospital, I will simulate 10 patients that individually send us data. The patient side only need the public key to work. Since the SQL bigint data type has limitations, I will save the generated number as a string and also uses a decimal place constant to scale the number to a integer before encryption.

-- PUBLIC KEYS ==================================
DECLARE @pkn bigint = '953070681873550127';
DECLARE @pkg bigint = '953070681873550128';
 
DECLARE @BP 
    TABLE    (id int IDENTITY
            ,DummySystolic decimal(10,1)
            ,DummyDiastolic decimal(10,1)
            ,DummyAvg int
            ,BPEncrypted nvarchar(MAX));
 
DECLARE  @i int = 0
        ,@Patients int = 10
        ,@DecimalPlaces int = 1
        ,@Systolic decimal(10,1)
        ,@Diastolic decimal(10,1)
        ,@BPAvg decimal(10,1)
        ,@BPAvgInt int;
 
TRUNCATE TABLE [dbo].[PatientsBloodPressure];
 
WHILE @i < @Patients BEGIN
    SET @Systolic = (RAND(CHECKSUM(NEWID())) * 26 + 111);
    SET @Diastolic = (RAND(CHECKSUM(NEWID())) * 33 + 61);
    SET @BPAvg = ((@Systolic + @Diastolic * 2) / 3.0);
    SET @BPAvgInt = @BPAvg * POWER(10,@DecimalPlaces) + 0.5;
 
    INSERT INTO         [dbo].[PatientsBloodPressure]
        VALUES        (@i + 1
                    ,@Systolic
                    ,@Diastolic
                    ,@BPAvg
                    ,[AsmMath].[HEncrypt] (@pkn,@pkg,@BPAvgInt));
 
    SET @i += 1;
END
 
SELECT   [Id]
        ,[DummySystolic]
        ,[DummyDiastolic]
        ,[DummyAvg]
        ,[BPEncrypted]
    FROM [dbo].[PatientsBloodPressure];
GO

Resulting in:

Individual values

The only number that will be sent is the BPEncrypted, I kept the other values for demonstration purposes to illustrate how this tip works.

Now at the hospital side I want to know the average value of the encrypted average blood pressure. Now I need both keys.

-- MSSQLTips (TSQL)
 
-- PUBLIC KEYS ==================================
DECLARE @pkn bigint = '953070681873550127';
DECLARE @pkg bigint = '953070681873550128';
 
-- PRIVATE KEYS =================================
DECLARE @sklambda bigint = '476535339907528560';
DECLARE @skmu bigint = '180581948158477459';
 
DECLARE  @DecimalPlaces int = 1
        ,@m1 nvarchar(MAX)
        ,@count float = 0
        ,@Sum nvarchar(MAX) = '';
 
DECLARE @n2 nvarchar(MAX) = [AsmMath].[BigMultiply] (@pkn,@pkn);
 
DECLARE crsPat CURSOR FAST_FORWARD READ_ONLY FOR 
    SELECT         [BPEncrypted]
        FROM     [dbo].[PatientsBloodPressure]
 
OPEN crsPat
    FETCH NEXT FROM crsPat INTO @m1
 
    WHILE @@FETCH_STATUS = 0 BEGIN
            
        IF LEN(@Sum) = 0
            SET @Sum = [AsmMath].[BigMultiply](@m1,1);
        ELSE
            SET @Sum = [AsmMath].[BigMod] ([AsmMath].[BigMultiply](@m1,@Sum),@n2); -- multiply and rescale
                
        SET @count += 1;
 
        FETCH NEXT FROM crsPat INTO @m1
    END
CLOSE crsPat
DEALLOCATE crsPat
 
DECLARE @u nvarchar(MAX) = [AsmMath].[BigModPow] (@Sum,@sklambda,@n2);
DECLARE @l nvarchar(MAX)= [AsmMath].[BigDivide] ([AsmMath].[BigSubtract] (@u,1), @pkn); 
 
SELECT     AVG(DummySystolic) AS BPSyst
        ,AVG(DummyDiastolic) AS BPDiast
        ,(AVG(DummyDiastolic) * 2 + AVG(DummySystolic)) / 3.0 AS BPCalc
        ,AVG(DummyAvg) AS BPAvg 
        ,[AsmMath].[BigMod] ([AsmMath].[BigMultiply] (@l,@skmu),@pkn) / @count / POWER(10,@DecimalPlaces)  AS BPAvgFromEnc
    FROM [dbo].[PatientsBloodPressure];
GO

Resulting in:

Comparison encrypted x dummy

Observe that I have the average of the measures without decrypting the original values. The dummy columns are here only to confirm this.

If we want to show the decryption of the blood pressure values, run this code:

-- MSSQLTips (TSQL)
 
-- PUBLIC KEYS ==================================
DECLARE @pkn bigint = '953070681873550127';
DECLARE @pkg bigint = '953070681873550128';
 
-- PRIVATE KEYS =================================
DECLARE @sklambda bigint = '476535339907528560';
DECLARE @skmu bigint = '180581948158477459';
 
DECLARE  @DecimalPlaces int = 1
 
SELECT   [Id]
        ,[DummySystolic]
        ,[DummyDiastolic]
        ,[DummyAvg]
        ,CONVERT(float,[AsmMath].[HDecrypt] (@pkn,@pkg,@sklambda,@skmu,BPEncrypted)) / POWER(10,@DecimalPlaces) AS BPDecrypted
        ,[BPEncrypted]
    FROM [dbo].[PatientsBloodPressure];
GO

Resulting in:

Individual values decrypted

Next Steps

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *