9/8/2024 M. Washington (Admin)

Connect to Microsoft Fabric Using TSQL and Microsoft Blazor

You can access the data in your Microsoft Fabric Warehouses using the SQL endpoint.

This allows you to import, query and update the data using tools such as SQL Server Management Studio and applications such as Microsoft Blazor.

What is Microsoft Fabric

Microsoft Fabric is an end-to-end analytics and data platform designed to unify various data-related tasks into a single, cohesive solution. It integrates data movement, processing, ingestion, transformation, real-time event routing, and report building, all within a Software as a Service (SaaS) model.

By combining components from Power BI, Azure Synapse Analytics, and Azure Data Factory, Microsoft Fabric offers a comprehensive suite of services including Data Engineering, Data Science, Real-Time Analytics, and Data Warehousing. This integration enables businesses to transition raw data into actionable insights seamlessly

Getting Started With Microsoft Fabric

If you do not already have a Microsoft Fabric capacity in your organization, you can get started with a free trial. See the following link: https://learn.microsoft.com/en-us/fabric/get-started/fabric-trial.

Using Microsoft Fabric

Adding data to a Microsoft Fabric Data Warehouse is a straightforward process that leverages its integrated data services.

For example, you can start by ingesting your raw data through Fabric’s data pipelines, which can handle various data sources.

Note: The fastest way to import data is to use the method described here: Import Data Fast into Fabric Using Parquet Files

Once the data is ingested, use the data transformation tools to clean and prepare your data. With the prepared data, you can easily create tables within the data warehouse component of Microsoft Fabric. These tables can then be used for further analysis, reporting, or visualization, making it simple to turn raw data into structured, actionable insights.

Connecting to Microsoft Fabric using the SQL Endpoint

Once your data is in your Fabric Data Warehouse the fastest way to query it is using the Microsoft SQL Server Management Studio. You can download it free from: https://learn.microsoft.com/en-us/sql/ssms/release-notes-ssms?view=sql-server-ver16.

Connecting to Microsoft Fabric using the SQL Endpoint is a seamless process that allows you to interact with your data warehouse using familiar SQL queries.

First, create and enable Service Principals. Next, you obtain the SQL Endpoint connection details from the Microsoft Fabric portal. Then, use Microsoft SQL Server Management Studio or development environment such as Microsoft Blazor to establish a connection using these details.

Once connected, you can execute SQL queries to retrieve, manipulate, and analyze your data directly within Microsoft Fabric. This capability enables efficient data management and integration with existing SQL-based tools and workflows, enhancing your data analytics and reporting processes.

Creating and Enabling Service Principals

The first step is to log into the Microsoft Azure Portal (https://portal.azure.com/) for your organization and create a Service Principal.

Once logged in as an Administrator, navigate to Microsoft Entra ID.

Next navigate to App registrations.

Make a New registration.

Give the Service Principal a name and click Register.

Copy the Application (client) ID.

This will be the username you will need later.

Create a New client secret.

Copy the Value.

This will be the password you will need later.

Log into Microsoft Fabric: https://app.fabric.microsoft.com/

Click the Gear icon and select Admin portal.

Under Tenant settings, enable Service principals can use Fabric APIs under Developer settings.

Select Workspaces.

Create or select a non personal Workspace.

In the Workspace, select Manage access.

Select Add people or groups.

Search for the Service Principal you created and select it.

Set the access to Admin and click Add.

Note: This will give access to all objects in the workspace.

Obtain SQL Connection Details

Create or select a Data Warehouse.

Click the Gear icon.

Copy the database name and connection string.

You will need these values in the next step.

Connect using Microsoft SQL Server Management Studio

Open the Microsoft SQL Server Management Studio and in the Object Explorer select Connect then Database Engine.

Enter the connection information from the values saved in the earlier steps.

Ensure Microsoft Entra Service Principal is selected for Authentication.

Trust the server certificate.

Click Connect.

You will be connected to your Data Warehouse.

Connect using Microsoft Blazor

You can find code for connecting to Microsoft Fabric, using Microsoft Blazor, in the Github project located at: https://github.com/ADefWebserver/FabricDataExplorer

The key thing is to include the following NuGet packages:

This class is used to store the connection values:

    public class ConnectionSetting
    {
        public string DatabaseName { get; set; }
        public string ServerName { get; set; }
        public bool IntegratedSecurity { get; set; }
        public string Username { get; set; }
        public string Password { get; set; }
    }

This method will take those values and constrict a Fabric connection string:

    private string FabricCreateDatabaseConnectionString(ConnectionSetting objConnectionSetting)
    {
        StringBuilder SB = new StringBuilder();
        string strConnectionString = "";
        string strUserInfo = (!objConnectionSetting.IntegratedSecurity) ?
            String.Format("Authentication=Active Directory Service Principal;uid={0};pwd={1}",
            objConnectionSetting.Username,
            objConnectionSetting.Password) :
            "integrated security=True";
        strConnectionString = String.Format("{0}data source={1};initial catalog={2};{3};TrustServerCertificate=True",
            SB.ToString(),
            objConnectionSetting.ServerName,
            objConnectionSetting.DatabaseName,
            strUserInfo);
        return strConnectionString;
    }

See this page for sample code that uses the Fabric connection string:

https://github.com/ADefWebserver/FabricDataExplorer/blob/main/Components/Pages/DatasheetEditor.razor

    private void GetTablesInDatabase()
    {
        tableNames = new List<string>();
        // Use the FabricConnectionString to get the tables in the database
        using (SqlConnection connection = new SqlConnection(FabricConnectionString))
        {
            connection.Open();
            // Get the tables in the database
            DataTable dt = connection.GetSchema("Tables");
            // Get the table names
            foreach (DataRow row in dt.Rows)
            {
                string tablename = $"[{(string)row[1]}].[{(string)row[2]}]";
                tableNames.Add(tablename);
            }
            connection.Close();
        }
    }

Links

Import Data Fast into Fabric Using Parquet Files

Ingest data into the Warehouse

Microsoft Fabric trial capacity

Back