How to use JDBC to store and retrieve data from Azure SQL Database

I got the new case last week that was kind of new for myself! I am not a Java developer and could only advise about SQL Server, my plan is when I have time do the Java code and test all of these, I’ll try to update here later this but let’s see the problem.
The client tried to connect to Azure SQL Server however they needed to use service principal method and had issue with connection.


When someone asked us about authentication in SQL Server we say AAD or SQL Authentication, lets here from Azure Active Directory that could be one of the following or even more, You can use of these based on your case. see all details here.

ActiveDirectoryMSI

ActiveDirectoryIntegrated

ActiveDirectoryInteractive

ActiveDirectoryServicePrincipal

Btw for this case Firstly, we’ve checked the connection string that they used, that was something like the following jdbc string. Obviously, I’ve changed the server and database name.

jdbc_connection_string => "jdbc:sqlserver://azu-xxxxx.database.windows.net:1433;database=databasename_xxx;encrypt=true;hostNameInCertificate=*.database.windows.net"

I’ve advised like you can see from that link: since driver version v9.2, authentication=ActiveDirectoryServicePrincipal can be used to connect to an Azure SQL Database/Synapse Analytics by specifying the application/client ID in the userName property and secret of a service principal identity in the password property.

Here also you can see how to use ActiveDirectoryServicePrincipal authentication mode

That means for this method you need to use code to connect to the database and can’t directly open SSMS and use that.


The second thing is The T-SQL command CREATE USER [Azure_AD_Object] FROM EXTERNAL PROVIDER on behalf of an Azure AD application is now supported for SQL Database.

See the referral link here.

The following link from Microsoft also could useful if you want to use JDBC to store and retrieve data from Azure SQL Database