topic Re: Connect Cognos to Fabric SQL Endpoint - Lakehouse using JDBC in Data Warehouse

Connect Cognos to Fabric SQL Endpoint - Lakehouse using JDBC

Anonymous — Fri, 29 Aug 2025 20:41:37 GMT

This is less of a question and more of a how-to/tip. Hopefully if you are searching for connecting to fabric via JDBC or for Cognos, you will come across this. Reply for any additional details. (Note I am not a Cognos expert). This was assisting a customer on several screen share sessions. I suspect this would also work for most uses of the JDBC driver, regardless of the system you are connecting from. I will test this with DBeaver in the next few days.

The main thing is that this particular answer does not seem to work:

https://community.fabric.microsoft.com/t5/Data-Warehouse/Connecting-IBM-COGNOS-to-fabric-data-warehouse/m-p/4675416#M2957

Mainly because the SQL Analytics Endpoint does not support SQL Authentication. We may have been missing something obvious? Unsure. In this context the only two real options are an entra user or a service principal. Entra Users are typically going to be annoying because of password changes and/or MFA issues. Service Principals tend to be the best option here.

Create a Service Principal and then assign that Service Principal the proper roles on the workspace. Note that if you give permissions at the Workspace level, you are effectively giving access to all lakehouses and warehouses in that Workspace. A simple way to manage this is creating a separate workspace just for the service principal.

I suspect that you could follow the instructions from that other post, and attempt to use Service Principal authentication in the DSN. However, we did try this and it seemed as if Cognos did not like this. We kept getting an error that the Service Principal was not supported - even though the DSN was configured to use it and we had the latest driver.

Our next attempt was with a JDBC connection - this was a little trickier, and required some trial and error.

In short you need the following dependencies in your drivers directory. All can be downloaded from maven.

slf4j-api-1.7.36 json-smart-2.5.2 jackson-databind-2.18.1 content-type-2.3 jackson-core-2.19.2 jackson-annotations-2.19.2 jackson-databind-2.19.2 azure-json-1.5.0 asm-9.8 accessors-smart-2.6.0 json-smart-2.6.0 oauth2-oidc-sdk-11.23.1 msal4j-1.22.0 mssql-jdbc-13.2.0.jre8

It is entirely plausible that we have one too many 😑. But this was us first upgrading to the latest microsoft sql server for jdbc driver, and then, adding the Microsoft Authentication Library for Java msal4j, and going through its dependencies, and then any subsequent dependency errors.

Lastly we created the connection in the cognos admin portal with a specific jdbc connection string:

jdbc:sqlserver://[sqlendpoint url];database=[lakehouse/warehouse name];authentication=ActiveDirectoryServicePrincipal;user=[client id];password=[client secret];encrypt=true;trustServerCertificate=false;

this did work, and we got a successful connection.

Re: Connect Cognos to Fabric SQL Endpoint - Lakehouse using JDBC

v-ssriganesh — Fri, 29 Aug 2025 10:22:40 GMT

Hello Anonymous,
Thank you for sharing this detailed walkthrough. It will be very valuable for other community members looking to connect IBM Cognos (or any other JDBC-based client) with the Fabric SQL Analytics Endpoint (Lakehouse/Warehouse). I’m sure it will also help users who are evaluating Cognos, DBeaver, or other JDBC-compatible clients with Fabric.

Thanks again for contributing this knowledge to the community.

Best regards,
Ganesh Singamshetty.

Re: Connect Cognos to Fabric SQL Endpoint - Lakehouse using JDBC

Anonymous — Fri, 29 Aug 2025 20:40:10 GMT

I was able to use DBeaver and connect successfully with the list of dependencies above.

It is possible that the asm library is not necessary. Try without it first.

Re: Connect Cognos to Fabric SQL Endpoint - Lakehouse using JDBC

Shahid12523 — Mon, 01 Sep 2025 06:57:30 GMT

To connect Cognos to Fabric Lakehouse SQL Endpoint via JDBC:

Use Service Principal (SQL Auth not supported).
Assign SPN access to the Fabric workspace/item.
Add JDBC + MSAL4J dependencies (e.g., mssql-jdbc, msal4j, jackson, slf4j, etc.).
Connection string:

jdbc:sqlserver://<endpoint>;
database=<lakehouse>;
authentication=ActiveDirectoryServicePrincipal;
user=<client-id>;
password=<client-secret>;
encrypt=true;
trustServerCertificate=false;

✅ Works in Cognos and other JDBC tools like DBeaver.

Re: Connect Cognos to Fabric SQL Endpoint - Lakehouse using JDBC

Anonymous — Tue, 16 Sep 2025 16:33:51 GMT

An additional note here - for those in the future who may find this useful. In some versions of cognos we noticed that while you could connect with mssql-jdbc-13.2.0, the metadata wizard was not able to fully discover all the actual schemas, tables and views.

Switching to mssql-jdbc-12.6 (yes an older version) resolved this issue and the schemas were fully discoverable by the metadata wizard.