Skip to content

Always Encrypted with secure enclaves

v-reye edited this page Feb 6, 2020 · 15 revisions

Always Encrypted with secure enclaves is a SQL Server 2019 (15.x) feature that extends the existing Always Encrypted feature to enable richer functionality on sensitive data while keeping the data confidential. As of the 8.2.0 release, the Microsoft JDBC Driver for SQL Server adds support for Always Encrypted with secure enclaves.

To use Always Encrypted with secure enclaves, Always Encrypted must be enabled by setting the column encryption setting connection string property:

  • columnEncryptionSetting=Enabled

Then add the following the connection string properties for Always Encrypted with secure enclaves:

  • enclaveAttestationUrl - the URL for attesting the server-side enclave
  • enclaveAttestationProtocol - the enclave attestation protocol (currently only "HGS" is supported)

Note the SQL Server instance must support and be configured to enable Always Encrypted with secure enclaves, otherwise an error will be thrown:

The SQL Server instance does not support enclave based computations.

Once a connection to an enclave-enabled SQL Server instance is established, the driver will support executing queries which perform in-place encryption and/or rich computations.

For example, initial data encryption or rotating a column encryption key which can be performed inside the secure enclave will not require moving the data outside the database since you will be able to issue in-place encryption using the ALTER TABLE (Transact-SQL) statements.

e.g. Adding a new enclave-enabled column which supports rich queries and in-place encryption to an existing table Employees using the column encryption key CEK1 can be done using queries such as:

ALTER TABLE [dbo].[Employees]
ADD [BirthDate] [Date] ENCRYPTED WITH (
COLUMN_ENCRYPTION_KEY = [CEK1],
ENCRYPTION_TYPE = Randomized,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL;

e.g. Encrypting an existing plaintext column SSN using the column encryption key CEK1 in-place can be done using rich queries such as:

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char] COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
WITH
(ONLINE = ON);
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

In addition, operations on encrypted columns, including pattern matching (the LIKE predicate) and range comparisons, can be performed as they will be supported inside the secure enclave.

e.g. A rich query such as the following would not be possible without secure enclaves as it would require server-side decryption of the encrypted columns SSN and Salary:

DECLARE @SSNPattern [char](11) = '%1111%';
DECLARE @MinSalary [money] = 1000;
SELECT * FROM [dbo].[Employees]
WHERE SSN LIKE @SSNPattern AND [Salary] >= @MinSalary;

The following is a simple sample java program that uses the Microsoft JDBC Driver for SQL Server to connect to a SQL Server that supports enclaves.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement;

import java.math.BigDecimal;

public class testEnclaves {
	public static void main(String[] args) {

		// Create a variable for the connection string.
		String connectionUrl = "jdbc:sqlserver://<server>:<port>;databaseName=ContosoHR;user=<user>;password=<password>;columnEncryptionSetting=enabled;enclaveAttestationUrl=<enclaveAttestationUrl>;enclaveAttestationProtocol=HGS";

		try (Connection con = DriverManager.getConnection(connectionUrl);PreparedStatement pstmt = con.prepareStatement("SELECT Salary FROM Employees WHERE Employees.Salary > ?")) {
			((SQLServerPreparedStatement) pstmt).setMoney(1, new BigDecimal(0), true);
			ResultSet rs = pstmt.executeQuery();
			// Iterate through the data in the result set and display it.
			while (rs.next()) {
				System.out.println("Salary: " + rs.getString("Salary"));
			}
		}
		// Handle any errors that may have occurred.
		catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

Note that the code above requires the mssql-jdbc_auth-<version>-<arch>.dll. The dll can be provided to the JVM via -Djava.library.path. For more description on how to configure Always Encrypted with secure enclaves please see Configure Always Encrypted with secure enclaves.

To get started with secure enclaves using SSMS, please see Tutorial: Getting started with Always Encrypted with secure enclaves using SSMS.

Known Issues

Always Encrypted with secure enclaves uses the RSASSA-PSS signature algorithm, which is only available in JDK 11 and later versions. JDK-8 users must either load a security provider capable of handling the RSASSA-PSS signature algorithm or include the BouncyCastle Provider optional dependency.

Clone this wiki locally