Microsoft Fabric - connection to SQL analytics endpoint

Use this step-by-step guide to connect a Microsoft Fabric SQL analytics endpoint to your TARGIT server.

Requirements

• Authentication method: Microsoft Entra Service Principal
• Make sure that you have downloaded and installed the correct OLE DB driver: Microsoft OLE DB Driver 19 for SQL Server
  • For On-prem solutions, install this driver on the machine running the TARGIT Server
  • For Cloud solutions, install this driver on the machine running Management

Allowing for Service Principal authentication

On your Microsoft Fabric platform you should allow for Microsoft Entra Service Principals authentication.

You will need to be either a Power Platform Administrator or a Fabric Administrator. See Fabric Admin Roles

Make sure that the use of Service Principals is enabled:

Make sure that external access to data is enabled:

Setting up Service Principal in Microsoft Entra

Open your Azure Portal to register a new app.

1. Go to Microsoft Entra ID
2. Go to App Registrations
3. Create a New registration and give it a proper name

4. Copy the Application ID and store it in e.g., Notepad. When setting up the TARGIT connection, we will use the Application ID as user name.
5. For this application, go to Certificates and secrets to create a New client secret
6. Give the secret a proper name and save it
7. Important: Copy the Secret Value and store it in e.g., Notepad. You will not be able to retrieve the Secret Value later. When setting up the TARGIT connection, we will use the Secret Value as password.

Granting access to Lakehouse data

Go to your Fabric OneLake catalogue (http://app.fabric.microsoft.com/) and open Share options for your Lakehouse:

Enter the name of your Entra ID app and make sure that it gets the Read all SQL endpoint data permission:

Open Settings for the Lakehouse:

Copy the SQL connection string of the SQL analytics endpoint:

When setting up the TARGIT connection, we will use the SQL connection string as server name.

Connect to Fabric SQL analytics endpoint from TARGIT Management

Add a new Relational Database connection in TARGIT Management - use the Microsoft SQL Server provider:

1. Give the connection a proper name
2. In the next dialog, click Change connection string and select the Microsoft OLE DB Driver 19 for SQL Server provider:
   
3. On the Connection tab, in Select or enter a server name paste in the Lakehouse SQL connection string
4. In Enter information to log on to the server select Active Directory - Service Principal
5. In User name and Password paste the Application ID and Secret Value respectively.
6. Now you should be able to select the Lakehouse database.
   
7. On the Advanced tab, make sure that Connection encryption is set to Strict:
   

You have now created a connection to your Microsoft Fabric SQL analytics endpoint.

Next step is to create a data model from these data. See Creating a Data Model