Azure entra id / active directory sql authentication

[cmc]

Hi, we have a policy the does not allow the use of username/password (aka SQL authentication) for database authentication to Azure SQL (SQL Server). 

This requires (I believe) that additional azure/ms java libraries are included. (see https://learn.microsoft.com/en-us/sql/connect/jdbc/connecting-using-azure-active-directory-authentication?view=azuresqldb-current)

Question:  Has anybody attempted to connect i2b2 to Azure SQL using ActiveDirectory/EntraID authentication rather than usename/password? 

[mabaj...@mac.com]

Hello, cmc,

I do not have direct experience with this topic. The relevant information provided in the i2b2 documentation can be found under the topic "Using Active Directory with SQL Server" on this page: https://community.i2b2.org/wiki/display/docs2/i2b2+Usage+Community+FAQ 

Three caveats: 

1. That information is generally for connections using Active Directory, and may not apply specifically to your case where the database server is running on Azure. I do not know of any reason why it would be different, but you should probably include adaptations from the instructions posted at the link that you included in your question.

2. The connection has to be between the Wildfly server and the MS SQL Server. Therefore, whether you are running Wildfly on Linux or on Windows Server, my understanding is that the "user account" that is running Wildfly must be an Active Directory account (easier to do on Windows than on Linux).

3. You may need to include a JDBC driver for the Entra connection alongside, or replacing, the JDBC driver that is already included for MSSQL in the Wildfly configuration. I think that the link you provided tells about the JDBC file that you may need. You can test the connection without the additional JDBC driver file, but then add it if necessary.

The connection URL referenced in the FAQ page will need to be updated in your "data source" files in Wildfly's standalone/deployments folder. The suggestion on the FAQ page is a good suggestion, but you may need to follow the instructions on the link you provided to set the connection URL properly.

If you have not yet created the databases using the database-loading scripts, then the connection URL will have to be set properly in i2b2's database-loading properties files as well.

I hope that someone with genuine experience with this topic will weigh in. But in the meantime, I am hopeful that this information will prove useful to you. Wishing you all the best!

Kind regards,

Mark Abajian

Abajian Consulting, LLC
P.O. Box 8017, Glendale, CA  91224
Email  maba...@mac.com   |   Mobile  +1 (818) 726–0372