Microsoft Fabric Lakehouse & Qlik Cloud Analytics

[Jamie_T]

Has anyone managed to connect a MS Fabric Lakehouse to Qlik Cloud Analytics as a data source without going through the Data Movement Gateway and QDI (not an option at the moment for us)?

There isn't a native connector for Fabric as there is for Synapse, and there doesn't seem to be anyway to force a connection through any other way at the moment.

The closest we've got is by using ODBC (via Direct Access Gateway) as the ODBC on one of our servers can connect to the Lakehouse, but we then get an error stating invalid near 'Field Name' which stops the data preview from loading correctly and just trying to connect direct form the Load Editor doesn't even recognise the database\schema\field name as being valid

[celindho]

I managed to connect to a Fabric SQL endpoint using Microsoft ODBC driver. The test was done using Qlik Sense on Windows, not Qlik Cloud Analytics, but the result should be identical in both cases. If I remember correctly I used a service principal with the AppId as the username and a Client Secret (=Secret Value) as the password. This however requires a quite recent version of the Microsoft SQL DB ODBC driver to work. I did this just for testing purposes and have not yet had the need to make a production deployment, so i can't say anything about performance.

None the less, I'm really disappointed in Qlik not yet releasing an update to the Synapse connector to also allow for Fabric SQL endpoints. It should be a quite simple thing to remove the artificial built-in blocker of the said connector.

[zkazimov · Accepted solution]

Hello community members,

I have found the solution using ODBC Driver for SQL Server. This applies to Qlik Cloud. I use service principal account to connect to fabric. 

Steps

1. Setup VM for Qlik Direct Access Gateway: Setting up Qlik Data Gateway - Direct Access | Qlik Cloud Help

2. Install latest ODBC Driver for SQL Server:  Download ODBC Driver for SQL Server - ODBC Driver for SQL Server | Microsoft Learn

3. Setup account in Azure to read data from Fabric workspace. Any of the options mentioned in here should work: Microsoft Entra authentication in Synapse Data Warehouse - Microsoft Fabric | Microsoft Learn

4. Copy SQL endpoint from Fabric Workspace. Lakehouse or Warehouse endpoint

5. Setup ODBC connection in Qlik Cloud

5.1. Choose Direct Access Gateway you setup in step 1

5.2. Choose ODBC Driver 18 for SQL Server in Database Properties

5.3. Connection string: SERVER: <fabric endpoint from step 4>; DATABASE: <Lakehouse or Warehouse Name>; Authentication: <Method you decided in step 3. Refer to documentation for values>

5.4. Encrypted Properties: Add UID for username. Add PWD for password you created in step 3 

5.5 Database Specific Properties: Choose Custom SQL Syntax. Select Detect

5.6 Test Connection and Save

6. DO NOT ATTEMPT TO PREVIEW DATA. You will get errors

7. Insert Connection String. Example, LIB CONNECT TO 'Fabric Bronze';

8. Run queries you need.  Format [Lakehouse or Warehouse Name].[Schema Name].[Table Name]. Example. 

SELECT *
FROM [LH_Bronze].[dbo].[TMS_Load] ;

See attached screenshot for more details. 

[Jamie_T]

Thanks for this - I've now got this working against my lakehouse!

It seems a bit odd that this is the only way to do this so far as we're pulling data from the cloud, to an on-premise server before pushing it back to a cloud platform.

Qlik need to provide a connector that work natively and removes the requirement for the direct access gateway

[morgankejerhag]

Is this still working? I am not able to get it working. I have followed the method you have used (but with a service principal). The connection settings works fine on a QlikView Desktop on the gateway server, but when I run it from SaaS there is Internal Error. I can list the tables and see the columns (but your mentioned error message when previewing data) so authentication seems to be working.

[Leigh_Kennedy]

Just wanted to add that selecting 'Sybase' or 'SAP Hana' SQL works for preview (at least in the limited testing i've done.  Given SQL Server was originally a fork of Sybase that makes sense.  

[kelvinlawveloci]

I've been following this method to setup the ODBC connection with MS Fabric and got it work with Qlik Sense Destkop by using Authentication as ActiveDirectoryInteractive.  However, when I test it in Qlik Cloud, it only keeps running without any message return and got my Direct Access Gateway disconnected.  Any tip to set this up in Qlik Cloud?  Does the ActiveDirectoryInteractive authentication support in Direct Access Gateway?

Thanks in advance!!

[Leigh_Kennedy]

I used the connection string format with my AD credentials:

"Server=xxxxxxxxx.datawarehouse.fabric.microsoft.com,1433;Database=FabSource;UID=abcd@qlik.com;PWD=XXXXXX;".

[kelvinlawveloci]

Thanks @Leigh_Kennedy for your prompt reply.

Our AD got MFA.  In Qlik Sense Desktop, a windows was prompted for my password input with MFA code generated for our Authenticator app.  However, seems like that windows cannot be prompted in Qlik Cloud.  Do you have MFA setup for your Fabric access on your side?    

[zkazimov]

@kelvinlawveloci  I would recommend using service principal to avoid MFA requirements. Register an app and create service principal account. Option # 2.