How can I locally develop my Spring Boot application against an Azure SQL DB using passwordless connections?

Oblio 21

According to the documentation, the passwordless option is only for the hosted environment. This creates a challenge for developing locally because the authentication mechanism is different. How do I set up my local development so that it works the same way (perhaps uses the current user's credentials)?

1 answer

Amira Bedhiafi 28,066 Reputation points

2025-01-16T15:56:56.5766667+00:00

You can configure AAD authentication to use your current user credentials. So you need to set up a Managed Identity or Service Principal for your hosted environment and levrage AAD Integrated Authentication locally.

Then update your application to use the azure-identity library to fetch AAD tokens and include the AAD URL in your JDBC connection string.

In your local machine, verify that you're authenticated with az login and enable AAD authentication by adding the Authentication=ActiveDirectoryIntegrated parameter in your connection string which will allow your app to use your AAD credentials during local development.
Please sign in to rate this answer.
Oblio 21 Reputation points

2025-01-16T17:18:32.78+00:00

Ok, I'll use AAD authentication for both local and remote connections. To be clear, this is in context of the application's connection to the database; nothing to do with users.

For the Azure hosted environment, I want a managed identity assigned to the app host, and granted permission in the db with a create user <> from external provider command. When the application attempts to connect to the database, it's the managed identity credential that will be used.

For my local environment, my user needs to be logged into azure using az login. When I run the application, my credentials will be used.

Both will work, and I don't have any credentials in the code or vault or anywhere. I can use a datasource url of jdbc:sqlserver://<asqldb>.database.windows.net:1433;databaseName=<db>;Authentication=ActiveDirectoryIntegrated;

to connect

Vijayalaxmi Kattimani 985 Reputation points Microsoft Vendor

2025-01-17T11:40:38.1733333+00:00

Hi @Oblio,

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

We would like to inform you that, To set up your local development environment to work with the passwordless connection option, you can use the Active Directory Integrated authentication mechanism. This allows you to use your current user's credentials to authenticate with Azure SQL Database, without the need for a password.

Here are the steps to set up your local development environment to use Active Directory Integrated authentication:

Install the latest version of the Microsoft JDBC Driver for SQL Server on your local machine.

In your Java application, use the following connection string to connect to Azure SQL Database:

jdbc:sqlserver://<serverName>.database.windows.net;database=<databaseName>;authentication=ActiveDirectoryIntegrated

Make sure that your local machine is joined to the same Active Directory domain as your Azure SQL Database server. This will allow your machine to authenticate with Azure SQL Database using your current user's credentials.

If your machine is not joined to the same Active Directory domain as your Azure SQL Database server, you can use Azure Active Directory (AAD) authentication instead. To do this, you will need to create an AAD application and grant it access to your Azure SQL Database. You can then use the following connection string to connect to Azure SQL Database:

jdbc:sqlserver://<serverName>.database.windows.net;database=<databaseName>;authentication=ActiveDirectoryInteractive;client_id=<clientId>;redirect_uri=<redirectUri>

Kindly note: <clientId> with the client ID of your AAD application, and <redirectUri> with the redirect URI of your AAD application.

You can use Azure Active Directory (AAD) authentication for both local and remote connections to your Azure SQL Database, without the need for any credentials in the code or vault. For the Azure hosted environment, you can assign a managed identity to the app host and grant it permission in the database using the CREATE USER <managedIdentityName> FROM EXTERNAL PROVIDER command. This will allow the application to connect to the database using the managed identity credential. For your local environment, you can log in to Azure using the az login command and run the application using your own credentials. This will allow the application to connect to the database using your own user credential.

Both approaches will work, and you can use the same datasource to connect to the database in both environments.

jdbc:sqlserver://<asqldb>.database.windows.net:1433;databaseName=<db>;Authentication=ActiveDirectoryIntegrated;

Please refer to this link https://learn.microsoft.com/en-us/azure/developer/java/spring-framework/deploy-passwordless-spring-database-app?source=recommendations&tabs=mysql

I hope this information helps. Please do let us know if you have any further queries.

Oblio 21 Reputation points

2025-01-17T15:14:32.7033333+00:00

Focusing on local dev for the moment - I have Authentication=ActiveDirectoryIntegrated; in my connection string. I'm using

<dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>mssql-jdbc</artifactId> <version>12.8.1.jre11</version> </dependency>

for my driver.

if I include

<dependency> <groupId>com.microsoft.azure</groupId> <artifactId>msal4j</artifactId> </dependency>

I get the error: "Failed to load both mssql-jdbc_auth-12.8.1.x64 and MSAL4J Java library for performing ActiveDirectoryIntegrated authentication."

if I include

<dependency> <groupId>com.microsoft.azure</groupId> <artifactId>msal4j</artifactId> <version>1.18.0</version> </dependency>

I get the error: "Failed to authenticate the user username@<domain> in Active Directory (Authentication=ActiveDirectoryIntegrated)"

note, "username" in the above is literal - I only redacted the domain name (which was correct). It's acting like it can't read the user.

Vijayalaxmi Kattimani 985 Reputation points Microsoft Vendor

2025-01-20T15:12:36.0466667+00:00

Hi @Oblio,

It appears that you are experiencing difficulties with setting up Active Directory Integrated authentication for your local development environment. Let's review the errors you are encountering and explore some potential solutions.

Error: "Failed to load both mssql-jdbc_auth-12.8.1.x64 and MSAL4J Java library for performing ActiveDirectoryIntegrated authentication."

This error indicates that the required native authentication library (mssql-jdbc_auth-12.8.1.x64.dll) and the MSAL4J library are not being loaded correctly. Here are some steps to resolve this:

Ensure the Native Library is Available: Make sure that the mssql-jdbc_auth-12.8.1.x64.dll file is available in your system's library path. You can download it from the link https://learn.microsoft.com/en-us/sql/connect/jdbc/release-notes-for-the-jdbc-driver?view=sql-server-ver16#128

Set the Library Path: Add the directory containing the mssql-jdbc_auth-12.8.1.x64.dll file to your system's library path. You can do this by setting the java.library.path system property when running your application:
java -Djava.library.path=<path_to_dll> -jar your-application.jar

Verify MSAL4J Dependency: Ensure that the MSAL4J dependency is correctly included in your project. You can specify the version explicitly to avoid any compatibility issues:
<dependency> <groupId>com.microsoft.azure</groupId> <artifactId>msal4j</artifactId> <version>1.18.0</version>

</dependency>

> Error: "Failed to authenticate the user username@ in Active Directory (Authentication=ActiveDirectoryIntegrated)" This error suggests that the authentication process is unable to read the user credentials. Here are some steps to troubleshoot this: 1. Check User Credentials: Ensure that the user credentials are correctly configured, and that the user has the necessary permissions to access the Azure SQL Database. 1. Use Azure CLI for Authentication: Authenticate with Azure using the Azure CLI to ensure that your current user's credentials are being used:

az login

1. Verify Connection String: Double-check your connection string to ensure it is correctly formatted: ```yaml jdbc:sqlserver://<serverName>.database.windows.net:1433;databaseName=<db>;Authentication=ActiveDirectoryIntegrated;

Update JDBC Driver: Ensure that you are using the latest version of the Microsoft JDBC Driver for SQL Server. Sometimes, updating to the latest version can resolve compatibility issues.

By following these steps, you should be able to resolve the issues with Active Directory Integrated authentication for your local development environment.

If the above steps do not resolve the issue, If you have a support plan could you please file a support ticket for deeper investigation. In case if you don't have a support plan please let us know here.

Vijayalaxmi Kattimani 985 Reputation points Microsoft Vendor

2025-01-21T13:45:43.54+00:00

Hi @Oblio,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Oblio 21 Reputation points

2025-01-21T17:57:25.9733333+00:00

Apologies, yesterday was a holiday for me.

Currently, my pom file has

<dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>mssql-jdbc</artifactId> <version>12.8.1.jre11</version> </dependency> <dependency> <groupId>com.microsoft.azure</groupId> <artifactId>msal4j</artifactId> <version>1.18.0</version> </dependency>

My connection string contains Authentication=ActiveDirectoryIntegrated;

I've confirmed that my user (that I'm logged into my workstation as) has access to the db by connecting through SSMS and querying the db.

I've performed an az login.

I'm still getting the "Failed to authenticate the user username@<domain> in Active Directory" error. It seems like it cannot get the current user's credentials.

Vijayalaxmi Kattimani 985 Reputation points Microsoft Vendor

2025-01-23T12:15:54.0866667+00:00

Hi @Oblio,

We sincerely apologize for the inconvenience caused and appreciate your patience.

We are checking with the internal team to get more information related to your query and will get back to you as soon as we have an update.

Thank you,

Oury Ba-MSFT 20,176 Reputation points Microsoft Employee

2025-01-23T22:57:38.4533333+00:00

Hi Oblio Thank you for reaching out.

For local development, Java developer can use Azure Identity client library for Java | Microsoft Learn. Azure CLI, device login all supports password-less authentication in local development.

Are you trying to use Azure Identity or use MSAL4j directly? We recommend the former. You should be able to use DefaultAzureCredential as the document you pointed to instructs so that users running locally will be able to use credentials from their IDE or one of the CLI tools.

Are you using azure-identity-extensions at all?

Regards,

Oury

Oury Ba-MSFT 20,176 Reputation points Microsoft Employee

2025-01-24T16:39:32.52+00:00

Oblio I wanted to follow up to see if you are still facing issues and need additional support. Please do not hesitate to reach out.

Please do also note that Azure SQL database passwordless connections require upgrading the MS SQL Server Driver to version 12.1.0 or higher. The connection option is authentication=DefaultAzureCredential in version 12.1.0 and authentication=ActiveDirectoryDefault in version 12.2.0. https://learn.microsoft.com/en-us/azure/developer/java/spring-framework/configure-spring-data-jdbc-with-azure-sql-server?tabs=passwordless

Oblio 21 Reputation points

2025-01-24T18:36:01.4166667+00:00

I added

<dependencyManagement> <dependencies> <dependency> <groupId>com.azure</groupId> <artifactId>azure-sdk-bom</artifactId> <version>1.2.30</version> <type>pom</type> <scope>import</scope> </dependency> </dependencies> </dependencyManagement>

and replaced the msal4j dependency with

<dependency> <groupId>com.azure</groupId> <artifactId>azure-identity</artifactId> </dependency>

and that appears to have resolved it.

When I move to deploy this to the Azure environment, I shouldn't need to do anything to the code as long as I have a service principal that has been setup on the database, and Authentication=ActiveDirectoryIntegrated; in the connection string (I'm using mssql-jdbc v12.8.1.jre11)?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How can I locally develop my Spring Boot application against an Azure SQL DB using passwordless connections?

1 answer

Your answer