Articles in this section

See more

Connecting QuerySurge to a Microsoft Fabric Lakehouse Using Service Principal Authentication

Overview

Microsoft Fabric Lakehouses include a read-only SQL analytics endpoint that exposes its Delta tables through a standard SQL connection.

QuerySurge can connect to this endpoint using the Microsoft JDBC Driver for SQL Server. Because Fabric uses Microsoft Entra ID authentication, additional configuration is required in Microsoft Entra ID, Microsoft Fabric, and QuerySurge.

This article explains how to connect QuerySurge to a Microsoft Fabric Lakehouse using service principal authentication.

Note: The SQL analytics endpoint exposes Delta tables located in the Lakehouse Tables area. Files stored only in the Files area are not available through the endpoint.

Prerequisites

1. Create a Microsoft Entra Service Principal

Create a Microsoft Entra App Registration for QuerySurge and record the following:

  • Application (client) ID
  • Client secret value

The client ID and secret will be used as the QuerySurge connection credentials.

2. Enable Service Principal Access in Fabric

A Fabric administrator must enable service principal access:

  1. Sign in to Microsoft Fabric.
  2. Select Settings → Admin portal.
  3. Open Tenant settings → Developer settings.
  4. Expand Service principals can use Fabric APIs.
  5. Enable the setting.

Access can be limited to a security group containing the QuerySurge service principal instead of enabling it for the entire organization.

3. Grant Access to the Fabric Workspace

Grant the service principal read access to the workspace containing the Lakehouse:

  1. Open the target Fabric workspace.
  2. Select Manage access.
  3. Select Add people or groups.
  4. Search for the service principal by name or Object ID.
  5. Assign the Viewer role.
  6. Select Add.

The Viewer role provides read access to the SQL analytics endpoints in the workspace. For stricter access control, grant the service principal access only to the required SQL analytics endpoint and apply any necessary SQL permissions.

4. Install the Microsoft SQL Server JDBC Driver

A Microsoft SQL Server JDBC driver is bundled with QuerySurge and can be installed using the QuerySurge Installer. Ensure the driver is installed on every QuerySurge Agent that will use this connection.

If you install the driver manually, select a supported version that is compatible with the Java version used by your QuerySurge Agent. Refer to the Microsoft JDBC Driver for SQL Server support matrix.

Note: Service principal authentication requires a JDBC driver that supports ActiveDirectoryServicePrincipal authentication, along with the corresponding Microsoft Entra authentication libraries and dependencies.

 

Setting Up the Connection in QuerySurge

Before creating the connection, gather the following information from Microsoft Fabric:

  • Lakehouse SQL analytics endpoint
  • Lakehouse name
  • Microsoft Entra Application (client) ID
  • Client secret value

To locate the Fabric connection information, open the Lakehouse Settings and select the SQL analytics endpoint option. Select Copy SQL connection string and record the server endpoint.

 

The server endpoint will resemble:

xxxxxxxxxx.datawarehouse.fabric.microsoft.com

Record the exact Lakehouse name displayed in the Fabric Explorer. Every Lakehouse automatically includes its own SQL analytics endpoint.

Launch the Connection Wizard

  1. Log into QuerySurge as an Admin user.
  2. In the Administration view, select Connections, and then click Add to launch the Connection Wizard. Click Next.
  3. Provide a name for the connection and select All Other JDBC Connections (Connection Extensibility) as the Data Source. Click Next.
  4. Enter the following connection information:
    • Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver
    • Username: Microsoft Entra Application (client) ID
    • Password: Client secret value
    • Connection URL:
jdbc:sqlserver://<fabric_sql_endpoint>:1433;databaseName=<lakehouse_name>;authentication=ActiveDirectoryServicePrincipal;encrypt=true;trustServerCertificate=false;pdw=true;

Replace <fabric_sql_endpoint> and <lakehouse_name> with the values collected from Microsoft Fabric.

Note: The QuerySurge-specific pdw=true; property enables compatibility handling for Fabric’s distributed SQL architecture.

If you have a Test Query, feel free to enter it to help verify that your Connection parameters are correct. It should be a standard query that returns a small amount of information - one row is enough. 

  1. If you entered a Test Query, you can use the Test Connection button to test whether your Connection is set up properly:

A successful test confirms that the QuerySurge Agent can authenticate to the Lakehouse SQL analytics endpoint and retrieve table metadata.

  1. Save the Connection.