Skip to content
A sample application for using database services on Cloud Foundry with Spring Framework. Showcasing SQL Server AlwaysEncrypted
Branch: master
Clone or download
Pull request Compare This branch is 13 commits ahead, 2 commits behind cloudfoundry-samples:master.
Fetching latest commit…
Cannot retrieve the latest commit at this time.

README.md

Spring Music

This is a sample application for using database services on Cloud Foundry with the Spring Framework and Spring Boot.

This application has been built to store the same domain objects in one of a variety of different persistence technologies - relational, document, and key-value stores. This is not meant to represent a realistic use case for these technologies, since you would typically choose the one most applicable to the type of data you need to store, but it is useful for testing and experimenting with different types of services on Cloud Foundry.

The application use Spring Java configuration and bean profiles to configure the application and the connection objects needed to use the persistence stores. It also uses the Spring Cloud Connectors library to inspect the environment when running on Cloud Foundry. See the Cloud Foundry documentation for details on configuring a Spring application for Cloud Foundry.

Building

This project requires Java 8 to compile. It will not compile with Java 9 or later.

To build a runnable Spring Boot jar file, run the following command:

$ ./gradlew clean assemble

Running the application locally

One Spring bean profile should be activated to choose the database provider that the application should use. The profile is selected by setting the system property spring.profiles.active when starting the app.

The application can be started locally using the following command:

$ java -jar -Dspring.profiles.active=<profile> build/libs/spring-music.jar

where <profile> is one of the following values:

  • mysql
  • postgres
  • mongodb
  • redis

If no profile is provided, an in-memory relational database will be used. If any other profile is provided, the appropriate database server must be started separately. Spring Boot will auto-configure a connection to the database using it's auto-configuration defaults. The connection parameters can be configured by setting the appropriate Spring Boot properties.

If more than one of these profiles is provided, the application will throw an exception and fail to start.

Running the application on Cloud Foundry

When running on Cloud Foundry, the application will detect the type of database service bound to the application (if any). If a service of one of the supported types (MySQL, Postgres, Oracle, MongoDB, or Redis) is bound to the app, the appropriate Spring profile will be configured to use the database service. The connection strings and credentials needed to use the service will be extracted from the Cloud Foundry environment.

If no bound services are found containing any of these values in the name, an in-memory relational database will be used.

If more than one service containing any of these values is bound to the application, the application will throw an exception and fail to start.

After installing the 'cf' command-line interface for Cloud Foundry, targeting a Cloud Foundry instance, and logging in, the application can be built and pushed using these commands:

$ cf push

The application will be pushed using settings in the provided manifest.yml file. The output from the command will show the URL that has been assigned to the application.

Creating and binding services

Using the provided manifest, the application will be created without an external database (in the in-memory profile). You can create and bind database services to the application using the information below.

System-managed services

Depending on the Cloud Foundry service provider, persistence services might be offered and managed by the platform. These steps can be used to create and bind a service that is managed by the platform:

# view the services available
$ cf marketplace
# create a service instance
$ cf create-service <service> <service plan> <service name>
# bind the service instance to the application
$ cf bind-service <app name> <service name>
# restart the application so the new service is detected
$ cf restart

User-provided services

Cloud Foundry also allows service connection information and credentials to be provided by a user. In order for the application to detect and connect to a user-provided service, a single uri field should be given in the credentials using the form <dbtype>://<username>:<password>@<hostname>:<port>/<databasename>.

These steps use examples for username, password, host name, and database name that should be replaced with real values.

# create a user-provided Oracle database service instance
$ cf create-user-provided-service oracle-db -p '{"uri":"oracle://root:secret@dbserver.example.com:1521/mydatabase"}'
# create a user-provided MySQL database service instance
$ cf create-user-provided-service mysql-db -p '{"uri":"mysql://root:secret@dbserver.example.com:3306/mydatabase"}'
# bind a service instance to the application
$ cf bind-service <app name> <service name>
# restart the application so the new service is detected
$ cf restart

Changing bound services

To test the application with different services, you can simply stop the app, unbind a service, bind a different database service, and start the app:

$ cf unbind-service <app name> <service name>
$ cf bind-service <app name> <service name>
$ cf restart

Database drivers

Database drivers for MySQL, Postgres, Microsoft SQL Server, MongoDB, and Redis are included in the project.

To connect to an Oracle database, you will need to download the appropriate driver (e.g. from http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html). Then make a libs directory in the spring-music project, and move the driver, ojdbc7.jar or ojdbc8.jar, into the libs directory. In build.gradle, uncomment the line compile files('libs/ojdbc8.jar') or compile files('libs/ojdbc7.jar') and run ./gradle assemble

PCF Azure SQL DB Failover services

Setup Failover group

cf create-service azure-sqldb PremiumP2 springdemodb -c ./springmusicdb.json

cf create-service azure-sqldb-failover-group SecondaryDatabaseWithFailoverGroup springfailoverdb -c ./failover.json

cf bind-service spring-music springfailoverdb

Configure application

To use Always Encrypted feature of SQLServer configure SevicePrincipal to have access to the Azure KeyVault with Master Encryption keys. This principal has to have following permissions get,wrapKey,unwrapKey,sign,verify,list

Set the following environment settings for the application:

  microsoft.vault.clientId: <CLIENT ID for Vault SPN>
  microsoft.vault.clientSecret:  <CLIENT Secret for Vault SPN>
  spring.datasource.dataSourceProperties.ColumnEncryptionSetting: Enabled

Once spring.datasource.dataSourceProperties.ColumnEncryptionSetting is set to Enabled, JDBC driver will be set to use Azure Key Vault Provider to encrypt and decrypt column values.

The code that does the enablement could be found in the class org.cloudfoundry.samples.music.config.data.DataSourceBeanPostProcessor

Important!!

If the keys are being changed, table dropped and re-encypted clear database cache

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

Otherwise you may see errors like

SQL Error: 206, SQLState: S0002
 .h.engine.jdbc.spi.SqlExceptionHelper   : Operand type clash: varchar(6) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'springmusicdb2') collation_name = 'SQL_Latin1_General_CP1_CI_AS' is incompatible with varchar(6) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEKPosh1', column_encryption_key_database_name = 'springmusicdb2') collation_name = 'SQL_Latin1_General_CP1_CI_AS'

or if old key was deleted

o.h.engine.jdbc.spi.SqlExceptionHelper : Some parameters or columns of the batch require to be encrypted, but the corresponding column encryption key cannot be found. Use sp_refresh_parameter_encryption to refresh the module parameters metadata.
2018-07-10T10:34:35.117-04:00 [APP/PROC/WEB/0] [OUT] 2018-07-10 14:34:35.117 ERROR 15 --- [nio-8080-exec-5] o.h.i.ExceptionMapperStandardImpl : HHH000346: Error during managed flush
You can’t perform that action at this time.