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-wareho...
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.
