Skip to content

Access Microsoft Fabric Data from Dotnet App

Photo by Pixabay: https://www.pexels.com/photo/closed-door-and-lighted-light-sconce-277559/

Microsoft Fabric is an excellent product for storing both structured and unstructured data for data analysis and AI workloads. It is built on top of Azure Blob Storage, making data storage relatively cheap and easy to access. Fabric offers many built-in tools for data manipulation, including Dataflow Gen 2, Data Pipelines, and Notebooks. However, sometimes you need to access the data from outside of Microsoft Fabric, which is the topic of this post.

Fabric Security

Microsoft Fabric’s security is built on top of Microsoft Entra (ex-AD). Fabric does not support SAS tokens or API keys to access the system. You need to have valid Microsoft Entra authentication to access the data. Microsoft “recently” released a Microsoft Authentication Library (MSAL), which greatly simplifies the authentication process.

With MSAL you can easily acquire security token to access Fabric resources. There are ready made SDK’s for NET, JavaScript, Java and Python. Best thing about MSAL is that, it supports multiple ways to authenticate and you can just configure the authentication process depending on your login flow. MSAL NuGet package is called Microsoft.Identity.Client.

Supported scenarios from https://learn.microsoft.com/en-us/entra/msal/dotnet/getting-started/scenarios

REST API from Console and Desktop Apps

For console and desktop applications you need to write bit of code to acquire the access token. If you want to implement login without user interaction, you need to do app registration into Entra and use the client id and secret to do the authentication. In this sample I have used app registration + interactive UI (basically the normal Entra login page) to acquire the login token.

public async Task Login()
{
    string clientId = "(your client id from microsoft Entra);
    string authority = "https://login.microsoftonline.com/(tenantid)/";
    // Fabric scopes, based on your needs
    string[] scopes = ["https://api.fabric.microsoft.com/Workspace.ReadWrite.All https://api.fabric.microsoft.com/Item.ReadWrite.All"];

    PublicClientApplicationBuilder PublicClientAppBuilder =
            PublicClientApplicationBuilder.Create(clientId)
            .WithAuthority(authority)
            .WithDefaultRedirectUri();

    IPublicClientApplication PublicClientApplication = PublicClientAppBuilder.Build();

    try
    {
        AuthenticationResult result = await PublicClientApplication
            .AcquireTokenInteractive(scopes)
            .ExecuteAsync();

        if (!string.IsNullOrEmpty(result.AccessToken))
        {
            // You can return the token, or store it into _accessToken variable.
            _loginExpires = result.ExpiresOn;
            _accessToken = result.AccessToken;
        }
        else
        {
            throw new Exception("Login failed. Check application ID.");
        }
    }
    catch (Exception ex)
    {
        _logger.LogError(ex, "Failed to login.");
    }
}

After the login we can use AccessToken to do a REST API calls against the Fabric (pass it as Bearer token in authorization header). However if you just want to access the data from Fabric, there are few easier ways to do it.

Access Lakehouse Unstructured Data

As you may already know, you can store unstructured data (files) and structured data (“tables”, json etc.) at Lakehouse. If you want to access these stored files from Lakehouse, then you can use Azure.Storage.Files.DataLake NuGet package and use the Entra credentials to do the authentication. In this example I’m using DefaultAzureCredentials class, which tries to get credentials through multiple different ways (Managed Identity, Visual Studio identity etc. what is the working way in your environment)

DefaultAzureCredential tokenCredential = new(); // This will use the default credentials that we have in Visual Studio or Managed Identity in Azure.

Uri serviceUri = new("https://onelake.dfs.fabric.microsoft.com/(tenant id)"); // Contains TenantId, could be name also

var serviceClient = new DataLakeServiceClient(serviceUri, tokenCredential);
var fileSystemClient = serviceClient.GetFileSystemClient("");
var fileClient = fileSystemClient.GetFileClient("(lakehouse id)/Files/" + fileName); // Contains lakehouse id, could also be name, you can get lakehouse id from lakehouse URL at Fabric
var stopwatch = System.Diagnostics.Stopwatch.StartNew();

// Download the file
var response = await fileClient.ReadAsync();
Console.WriteLine($"Downloaded file {fileName} in {stopwatch.ElapsedMilliseconds} ms.");
stopwatch.Stop();

// Convert the stream to string
var content = await new StreamReader(response.Value.Content).ReadToEndAsync();
Console.WriteLine($"Response length was {content.Length / 1024} KBs.");

In this sample I used stopwatch to measure the amount of time file download takes, and for 1MB file it was around ~2 seconds with 300M internet connection. Authentication can add some extra time, but as in general the speed is good.

Access Lakehouse Structured Data

Every lakehouse has SQL Endpoint. You can find the endpoint from Lakehouse properties page (… next to lakehouse in workspace and click view details)

The connection string looks a bit weird, but it still works with EF Core and in Management Studio. In EF Core you need to provide access token with the connection string and in Management studio you can use MFA login. Here is complete example of working connection string. Just remember to change Database with your Lakehouse name.

"Server=xyz.datawarehouse.fabric.microsoft.com; TrustServerCertificate=false; Encrypt=True; Database=TestLakehouse;"

You can write test application easily by using DefaultAzureCredentials class and pass the token in EF Core OnCofiguring method. The example below does the token request only once, so if you want to have long living version, use EF Core interceptors to fetch the token when connection is required.

 internal class DataContext : DbContext
 {
     public DbSet<HeartRate> HeartRates { get; set; }

     public DataContext(DbContextOptions<DataContext> options) : base(options)
     { }

     protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
     {
         // You can also use Authentication=Active Directory Interactive; in connection string if you don't want to inject token.
         // In real world scenario, you should use interceptor to inject token when connection are created, because now it's injected only once.
         optionsBuilder.UseSqlServer(new SqlConnection("Server=xyz.datawarehouse.fabric.microsoft.com; TrustServerCertificate = false; Encrypt=True; Database=TestLakehouse;")
         {
             AccessToken = GetAzureSqlAccessToken()
         });
     }

     protected override void OnModelCreating(ModelBuilder builder)
     {
         base.OnModelCreating(builder);
         builder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());
         builder.Entity<HeartRate>().HasNoKey();
     }

     private static string GetAzureSqlAccessToken()
     {
         TokenRequestContext tokenRequestContext = new(["https://database.windows.net//.default"]);
         AccessToken tokenRequestResult = new DefaultAzureCredential().GetToken(tokenRequestContext);
         return tokenRequestResult.Token;
     }
 }

Summary

Microsoft Fabric can only be accessed using Microsoft Entra authentication, which provides great security and seamless integration with other Microsoft services. You have many different options for authentication: App Registrations for service-to-service, interactive authentication for user-driven access, and even DefaultAzureCredentials class for simplified development and deployment scenarios. The MSAL.NET library abstracts away much of the pain involved in authentication, handling token acquisition and renewal, and I recommend it for developers looking to implement secure authentication easily.”

Tags: