How do I connect to Azure SQL DB with AD password using JDBC configuration?

Question

I am trying to connect from my Spring Boot (2.0.1) Web Application to an Azure SQL DB using Azure AD (with the Application as the DB user) but cannot seem to connect the dots.

I followed the steps for Connecting using Azure AD Auth. However, I get a failed login.

I'm assuming that's because without setting the 'authentication' property in the JDBC url, it's just authenticating against the database directly (user created using CREATE USER <> FROM EXTERNAL PROVIDER from instructions)?

Is there a way to configure the spring datasource/jdbc libraries to use the proper Azure AD authentication (adal4j-1.6.3) while connecting? The example does this in code, but I'm having trouble finding the proper configurations.

I tried using 'authentication=ActiveDirectoryPassword', but kept getting a 'AADSTS50034: The user account does not exist in the directory' error.

spring.datasource.url="jdbc:sqlserver://myServer.database.windows.net:1433;database=myDB;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;applicationName=myApp;"

spring.datasource.username: <user>
spring.datasource.accessToken: <key>

The bigger goal is to secure my database access to specific apps. I had added my app as a Reader on my server and ideally would authenticate as that application to track usage/analytics/etc from that app.

I apologize if this is way off, I'm new to Azure and Oauth. Thanks.

Answer 1

For me the below properties and dependencies worked:

Instead of using adal4j I have used msal4j(recommended by Microsoft)

spring:
  datasource:
    url: jdbc:sqlserver://<Azure SQL Server Name>.database.windows.net:1433;databaseName=<DB Name>;encrypt=true;loginTimeout=30
    username: <UserName>
    password: <Password>
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
    hikari:
      data-source-properties:
        authentication: ActiveDirectoryPassword

pom.xml

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>9.2.1.jre8</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.microsoft.azure/msal4j -->
<dependency>
    <groupId>com.microsoft.azure</groupId>
    <artifactId>msal4j</artifactId>
    <version>1.9.1</version>
</dependency>

Answer 2

There is no way to configure Spring to use an application Id + key from Azure AD in place of username/password when connecting to Azure SQL DB. The only way I found was to modify code and create a @Bean method that returned an SQLServerDataSource where I manually retrieve a Client Credential access token and pass that into the data source.

Connecting using an access token - shows how to retrieve token and set on data source

Creating a custom Data Source - shows how to use Spring to create and use custom data source

Also be aware of token management (see my other question).

Answer 3

Including dependency adal4j with below configuration would help

spring.datasource.username=\<your AD username\>
spring.datasource.password=\<your AD password\>
spring.datasource.hikari.data-source-properties.authentication=ActiveDirectoryPassword

Answer 4

Please confirm that you are using a User that exists in the database and is a user in the same tenant where your database exists. If you are using a version of the Microsoft JDBC Driver that is earlier than the 4.0 version, you are also required to append the server name to the UserId to the connection string.

https://docs.microsoft.com/en-us/sql/connect/jdbc/connecting-to-an-azure-sql-database?view=sql-server-2017