Azure SQL connections with Spring Boot

Recently, I was standing up a new Azure SQL database instance for a project, and I decided to explore using Azure Active Directory to authenticate to the database rather than database credentials. Using Azure AD should allow better management of identities, credentials, and access as well as providing support for Managed Identities from Azure services like App Service. I've written up my notes here and in the feature/azure-sql branch of my Spring Boot Azure sample app on GitHub to help keep notes for myself and others.

There are two categorical ways to authenticate to an Azure SQL database: database credentials managed by the database server or using an Azure AD account. When using an Azure AD account, there are several variations of authenticating depending on whether you're using a service account, Managed Identity, or your own Azure AD credentials. In this post, I'll document some common steps for authenticating with standard database credentials as well as the developer workflow of using your own Azure AD account on your local machine.

Getting started

Follow the quickstart in the Microsoft Docs to set up a resource group, SQL server, and SQL database for use with the sample app linked at the top. The commands include a flag to automatically populate the database with a sample data set provided by Azure. Make sure to provide your IP address in the startIp and endIp variables. Note the output of the commands to feed into your app configuration. You can use the id property output of the az sql db create command to feed into az sql db show-connection-string --client jdbc --ids $fullResourceId in order to get a JDBC URL.

After standing up the database successfully, configure the application to authenticate and run the Spring Boot app. Un-comment the set of properties in application.properties for the desired authentication method and update values as appropriate. When the app starts up, use the http://localhost:8080/api/products endpoint to test the connection and Spring Data setup.

Authentication with database credentials

Authenticating to Azure SQL with plain old database credentials is the most straightforward method of those available. When you create an Azure SQL database, you provision an admin account. It is best practice not to use the admin account for your application, and instead provision an app-specific account with a constrained set of permissions. For instance:

USE my_database; 

CREATE ROLE app_role; 
GO 

GRANT SELECT, INSERT, UPDATE, DELETE TO app_role; 
GO 

CREATE USER username WITH PASSWORD = 'password'; 
GO 

ALTER ROLE app_role ADD MEMBER username; 
GO 

When you configure your Spring datasource, your properties should look something like:

spring:
  datasource:
    url: "jdbc:sqlserver://sqlservername.database.windows.net:1433;database=sqldbname;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30"
    username: ${DB_USERNAME}
    password: ${DB_PASSWORD}

Of course, handle the account credentials appropriately using secure environment variables or a secret management tool like Azure KeyVault.

Authentication with Azure AD credentials

A more centrally manageable method of authenticating to an Azure SQL database is with Azure AD credentials. This method allows, among other benefits, the use of Managed Identities in Azure. For instance, if the app is deployed to App Service, the Managed Identity feature allows the app to have an identity automatically provisioned and made available to the application runtime. This helps protect credentials from being leaked.

One potential pain point with using Managed Identities is the developer experience. Managed Identities are provided by the Azure runtime, but developers are often coding, compiling, and running their applications on local machines during the development lifecycle.

One way of allowing developers to authenticate from their local environment while still leveraging Managed Identities during deployment, is making use of the Azure Default Credential chain. In version 12+ of the Microsoft SQL Server JDBC driver, the driver can authenticate based on a chain of identities including an Azure CLI token. A developer can install the Azure CLI, authenticate to a tenant via az login, and the database driver can detect that authentication token as a fallback.

Before configuring the application, the developer's Azure AD account must be given permissions to use the database. Check the Microsoft documentation on assigning roles for up-to-date steps. At the time of writing, the following SQL commands should be executed on the target database where $AZ_DATABASE_AD_MI_USERNAME is the developer's Azure AD username. These commands also apply to the Managed Identity created by Azure services such as App Service.

CREATE USER "$AZ_DATABASE_AD_MI_USERNAME" FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER "$AZ_DATABASE_AD_MI_USERNAME";
ALTER ROLE db_datawriter ADD MEMBER "$AZ_DATABASE_AD_MI_USERNAME";
GO

Configuring the Spring Boot app requires using the dependency management feature of Maven or the Gradle plugin to pull in the com.azure.spring:spring-cloud-azure-dependencies artifact. Then, the application depends on the com.azure.spring:spring-cloud-azure-starter package to add classes to the classpath for use in authenticating with Azure AD. Also make sure to include the database driver: com.microsoft.sqlserver:mssql-jdbc:12.2.0.jre11. Check the build.gradle file in the sample app for how to declare these dependencies in Gradle.

With the Azure Spring Cloud dependencies in the classpath, ensure that the Spring datasource URL includes the property authentication=ActiveDirectoryDefault for developer machines:

spring:
  datasource:
    url: "jdbc:sqlserver://sqlservername.database.windows.net:1433;database=sqldbname;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;authentication=ActiveDirectoryDefault"

As long as the developer has a valid authentication token with the Azure CLI, they should be able to start the Spring Boot app and the Microsoft SQL Server driver will find that credential and authenticate to the database.

Spring profiles or environment variables can be used to override the datasource URL once deployed and leverage the Managed Identity of the compute service while changing the authentication property to ActiveDirectoryMSI.

I hope this helps you work with Azure AD authentication to Azure SQL with your Spring Boot apps. If you have additional suggestions or questions about these approaches, feel free to reach out on Twitter or leave an issue in the GitHub repository.