Deterministic encryption algorithm for encrypting a data column

Question

Deterministic encryption algorithm for encrypting a data column

pmscorca 1,032

Hi,

I need to encrypt only a table column of data possibly with a deterministic algorithm and a fixed key. I've tried to use the simple simmetric encryption, but for each execution changing the related encrypted column.

Any suggests to me, please? Thanks

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
phemanth 14,560 Reputation points Microsoft External Staff

2024-03-08T15:38:34.0766667+00:00
@pmscorca No, Azure Synapse Analytics currently does not support the "Always Encrypted" feature available in Azure SQL Database and SQL Server [1, 3]. This feature allows encryption on the client-side with keys never reaching the database engine, offering strong data protection.

However, there are alternative approaches to achieve data encryption for your specific column in Synapse before copying it to Oracle:

Client-Side Encryption:

Implement encryption logic within your data pipeline tool (e.g., Azure Data Factory) before loading data into Synapse. Here, you can use libraries or functions provided by your chosen language (Python, Java etc.) to encrypt the desired column using a deterministic algorithm with padding (like AES-CTR) and a strong key.

Store the encrypted data in a separate column within the Synapse table.

During data transfer to Oracle, decrypt the column data using the same key within your pipeline before writing it to the target table.

Customer-Managed Key (CMK) Encryption:

While Synapse doesn't offer column-level encryption, it supports data encryption at rest using customer-managed keys stored in Azure Key Vault [2]. You can configure your Synapse workspace to use a CMK for additional security.

This encrypts the entire data stored within the workspace, including your table, but not specific columns.

Choosing the Right Approach:

If you require strong encryption specifically for the chosen column and want to control the encryption/decryption process, client-side encryption is a better fit.

If overall data security within the Synapse workspace is your primary concern, consider using customer-managed key encryption.
phemanth 14,560 Reputation points Microsoft External Staff

2024-03-11T04:42:07.45+00:00

@pmscorca We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

1 answer

Your answer

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
phemanth 14,560 Reputation points Microsoft External Staff

2024-03-08T15:38:34.0766667+00:00

@pmscorca No, Azure Synapse Analytics currently does not support the "Always Encrypted" feature available in Azure SQL Database and SQL Server [1, 3]. This feature allows encryption on the client-side with keys never reaching the database engine, offering strong data protection.

However, there are alternative approaches to achieve data encryption for your specific column in Synapse before copying it to Oracle:

Client-Side Encryption:

Implement encryption logic within your data pipeline tool (e.g., Azure Data Factory) before loading data into Synapse. Here, you can use libraries or functions provided by your chosen language (Python, Java etc.) to encrypt the desired column using a deterministic algorithm with padding (like AES-CTR) and a strong key.

Store the encrypted data in a separate column within the Synapse table.

During data transfer to Oracle, decrypt the column data using the same key within your pipeline before writing it to the target table.

Customer-Managed Key (CMK) Encryption:

While Synapse doesn't offer column-level encryption, it supports data encryption at rest using customer-managed keys stored in Azure Key Vault [2]. You can configure your Synapse workspace to use a CMK for additional security.

This encrypts the entire data stored within the workspace, including your table, but not specific columns.

Choosing the Right Approach:

If you require strong encryption specifically for the chosen column and want to control the encryption/decryption process, client-side encryption is a better fit.

If overall data security within the Synapse workspace is your primary concern, consider using customer-managed key encryption.
phemanth 14,560 Reputation points Microsoft External Staff

2024-03-11T04:42:07.45+00:00

@pmscorca We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Answer 1

VasimTamboli 5,120

2 Option i can suggest here

Option 1 - Deterministic Encryption Functions: Synapse Analytics has built-in functions like DETERMINISTIC_ENCRYPT and DETERMINISTIC_DECRYPT. These functions use a special algorithm that always gives the same encrypted result for the same data and key.

SQL Script - SELECT DETERMINISTIC_ENCRYPT('Secret data', 'your_fixed_key') AS encrypted_data

FROM your_table;

Option 2 - Combining Symmetric Encryption with Hashing: While not directly supported in Synapse Analytics, you can still achieve deterministic encryption. Here's how:

Hash the data: Use a hashing function like SHA256 on your data to get a fixed-length string.

Combine hash and data: Mix the obtained hash with your original data.

Encrypt the combined string: Use a symmetric encryption method with your fixed key to encrypt the mix of hash and data.

Please let me know if u have any concerns. please accept as answer if it helped you.

pmscorca 1,032 Reputation points

2024-03-03T22:29:18.9666667+00:00

Hi, I've not found any T-SQL DETERMINISTIC_ENCRYPT() function.
VasimTamboli 5,120 Reputation points

2024-03-04T06:27:19.15+00:00

This is not straight forward, Please refer this MS Kb article which may help you - https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16#encrypted-with

Full Article - https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver16

for example -

-- Declare variables

DECLARE @OriginalData NVARCHAR(100) = 'Your original data';

DECLARE @HashValue VARBINARY(64);

DECLARE @CombinedString NVARCHAR(164); -- Length of hash value (64 bytes) + length of original data (100 characters)

-- Generate SHA256 hash value

SET @HashValue = HASHBYTES('SHA2_256', @OriginalData);

-- Convert hash value to hexadecimal string

SET @CombinedString = CONVERT(NVARCHAR(164), @HashValue, 2) + @OriginalData;

-- Encrypt the combined string using symmetric encryption (e.g., AES)

-- Use your preferred encryption method and fixed key here

-- Output or store the encrypted data as needed

PRINT @CombinedString; -- For demonstration purposes

Please let me know if this helps, you need go through Above articles.
pmscorca 1,032 Reputation points

2024-03-04T13:50:45.4766667+00:00

Hi, I'm a bit confused.

Does Synpase support the Always Encrypted feature? In order to encrypt only one column by adding an encrypted column, do I need to configure a feature as the Always Encrypted one?

I'm talking about Synapse Analytics and not SQL Server.
The goal is to have an encrypted column in a Synapse table before copying the table data from Synapse to an Oracle sink.

Thanks
Vasili Seniuts 0 Reputation points

2025-03-12T16:42:08.53+00:00

@pmscorca did you find any approach how to use Always Encrypted feature in Synapse Analytics?

Share via

Deterministic encryption algorithm for encrypting a data column

1 answer

Your answer