Homomorphic Encryption Algorithm in SQL Server

Explore the Homomorphic Encryption Algorithm with SQL Server and learn how it secures computations on encrypted data without decryption. The post Homomorphic Encryption Algorithm in SQL Server appeared first on MSSQLTips.com.

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. 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.

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 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 is a probabilistic asymmetric algorithm for public key cryptography.

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 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.

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. 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. It is necessary for security reasons to add the dll as trusted assembly. Change the path to your destination. Now it is necessary to register the assembly, creating it.

Change the path to your destination. In case of you are updating the already created AsmMath, execute it again and change the path to your destination. 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.

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. Resulting in the expected sum of values encrypted where I show the decrypted values to confirm. 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. 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. Observe that I have the average of the measures without decrypting the original values. The dummy columns are here only to confirm this.

---