spring-music-encrypted: A Java repository from lok-msft

Spring Data JPA vs Spring Data JDBC, for Always-Encrypted column in Azure SQL

Spring Boot + Spring Data (JPA vs JDBC) sample code for query always-encrypted column in Azure SQL

Table of Contents

About The Project
- Built With
Getting Started
- Prerequisites
- Installation
Usage
Roadmap
Contributing
License
Contact
Acknowledgments

About The Project

To show what's the expected behaviour when leveraging Spring Boot + Spring Data (Repository):

to query Azure SQL DB with always-encrypted columns

Here's highlight:

CRUD Repository with always-encrypted columns --> not using any @Query
Custom query with named paramter --> making use of @Query
Potential exception that it may throw, when using @Query

Please read this doc about SQL Server JDBC driver unsupported-data-type-conversion-errors

Also you can check out the expected response/exception in the file REST-client-test.http

** Focus on giving you some ideas about various Spring Data (Repository) behaviour when handling always-encrypted columns

** DO NOT take this codebase as a production-ready release, you may need extra consideration about performance optimization and security measure fit your project and organization specific requirement

For how to run query at SSMS for table with encrypted column, please refer to this blog post parameterization-for-always-encrypted-using-ssms

(back to top)

Built With

Key dependencies used to bootstrap the project:

spring-boot-starter-data-jpa
spring-boot-starter-data-jdbc
azure-identity //runtime only
azure-security-keyvault-keys //runtime only
mssql-jdbc //runtime only

(back to top)

Getting Started

Assuming you have some Azure SQL DB up and running

create relevant table and configure always-encrypted columns

CREATE SCHEMA music;
CREATE TABLE music.artist(
      [ArtistId] [int] NOT NULL,
      [PayToken] [int] NULL, -- always-encrypted
      [Email] [nvarchar](50) NULL, -- always-encrypted
      [Phone] [char](15) NULL, -- always-encrypted
      [FirstName] [nvarchar](50) NULL,
      [LastName] [nvarchar](50) NULL,
      [City] [nvarchar](50) NULL,
      [BirthDate] [date] NOT NULL,
      PRIMARY KEY CLUSTERED ([ArtistId] ASC) ON [PRIMARY] );

git clone this repo or just copy source code to a new project created by template from start.spring.io

put down the valid database connection string in application.properties

spring.datasource.url=jdbc:sqlserver://server-url:port;database=db-name;columnEncryptionSetting=Enabled;keyStoreAuthentication=KeyVaultClientSecret;keyStorePrincipalId=app-id;keyStoreSecret=app-secret;

maven build

mvnw.cmd clean package -Dmaven.test.skip=true

run localhost:8080
```
mvnw.cmd spring-boot:run
```

Prerequisites

tools and runtime needed before you can build and run the sample:

OpenJDK 11 - download
some IDE for Java project
SQL Server Management Studio (SSMS) - download - to create table and configure always-encrypted columns
An Azure account and subscription. If you don't have one, sign up for a free trial.
A database in Azure SQL Database or Azure SQL Managed Instance
permission for Azure Key Vault - create relevant keys for always-encrypted
permission to register app on Azure Active Directory (Azure AD) - for app to access key vault
Azure App Service / AKS - potentially running this app on Azure instead of desktop localhost