Using JDBC to Microsoft Fabric and Azure SQL Endpoints with AAD/Entra Authentication

Community Show & Tell
,
1

Hi,

This applies to Retool self-hosted...

Following on from this discussion: Is it possible to connect and build CRUD tables for Microsoft Fabric Data Warehouse using Retool?

I'm writing this post to share my own, now successful, experience in connecting to a MS Fabric SQL endpoint using the JDBC connector and including AzureAD (now Entra), authentication.

This example is using AAD/Entra username and password, but using other connection string options, I believe you can also use the same method to connect using Service Principle accounts.

First, you must ensure JDBC is setup and you've mapped your paths properly. I won't go into the whole thing because it's documented on Retool's docco here:

What I would say, is there is a slight tweak where it mentions adding volume mappings:

- `./jdbc:/retool_backend/jdbc`

You don't need the " ' " so it should be:

- ./jdbc:/retool_backend/jdbc

Once you have setup the volume mappings and the environment variable, you need the right .JAR files and their dependencies...

I used a tool called DBVizualiser which helped me work out which ones I needed, in the end I brought over a whole load.

The core driver .JAR for MSSQL you need is:
mssql-jdbc-12.2.0.jre11.jar

And the listed dependencies to get the authentication working is:

accessors-smart-2.4.9.jar
asm-9.3.jar
content-type-2.2.jar
jackson-annotations-2.13.4.jar
jackson-core-2.13.4.jar
jackson-databind-2.13.4.2.jar
javamsalruntime-0.13.10.jar
jcip-annotations-1.0-1.jar
jna-5.12.1.jar
jna-platform-5.12.1.jar
json-smart-2.4.10.jar
lang-tag-1.7.jar
msal4j-1.14.3-beta.jar
msal4j-brokers-1.0.3-beta.jar
nimbus-jose-jwt-9.30.2.jar
oauth2-oidc-sdk-10.7.1.jar
slf4j-api-1.7.36.jar

Once you have copied them into your /jdbc folder and reloaded your Retool application containers, you should be able to connect to Azure/Fabric SQL using a JDBC Resource and connection string similar to:

jdbc:sqlserver://<servername>.datawarehouse.pbidedicated.windows.net:1433;DatabaseName=<dbname>;authentication=ActiveDirectoryPassword;UserName=<user>;Password=<password>;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.datawarehouse.pbidedicated.windows.net;

(the servername and domain info will need to match whatever resource you're connecting to, datawarehouse.pbidedicated.windows.net is what Fabric SQL endpoints use).

As I say, if you want to use other AAD/Entra authentication methods, like a Service Principle, you'll have to tweak the connection string accordingly.

This worked for me, I hope it works for you.

Thanks
Dave

1 Like
2

Also to add, an important thing to note when querying Fabric SQL endpoints is case sensitivity - all objects must be referenced exactly as they are named.

So, in the example where we're setting up our JDBC Resource, if we want to pull down the Schema to use in our query building, we must use the correct case on the SQL objects (which are all UPPER case):

SELECT
  c.column_name as column_name,
  c.data_type as data_type,
  c.table_name as table_name,
  c.table_schema as table_schema
FROM
  INFORMATION_SCHEMA.COLUMNS c
  JOIN (
    SELECT
      table_name
    FROM
      INFORMATION_SCHEMA.TABLES
    WHERE
      table_schema != 'INFORMATION_SCHEMA'
  ) AS t ON t.table_name = c.table_name
ORDER BY
  c.table_name
3

The documentation you linked seems to omit the steps for mapping paths and setting up JDBC if you'd be willing to share?

I am also running into issues with pods crashing when updating the security context to be able to copy files to the PVC.

Not sure if you'd run into any of this but would appreciate any and all help.

Thank you!

4

For further context, this was with Retool self hosted with Helm.