Sam Debruyn

Freelance Data Platform Architect / Data Engineer

Specialized in Microsoft Azure, Fabric & dbt. Microsoft Data Platform MVP. dbt Community Award. Public speaker & meetup organizer. OSS contributor.

about Sam

services

public speaking & meetups

blog

Sam Debruyn

Sam Debruyn

Freelance Data Platform Architect / Data Engineer

about Sam

services

public speaking & meetups

blog

Sam Debruyn / Blog posts / All the different ways to authenticate to Azure SQL, Synapse, and Fabric

All the different ways to authenticate to Azure SQL, Synapse, and Fabric

9 minutes
MICROSOFT FABRIC, DATA WAREHOUSE, MICROSOFT AZURE, AZURE SYNAPSE, SYNAPSE DEDICATED, AUTHENTICATION

In this post I’ll go over all the details on acquiring access tokens to authenticate to any Microsoft SQL engine, including Azure SQL, Azure Synapse and Microsoft Fabric. We’ll explore users, service principals, managed identities, and Fabric Workspace Identity.

Why would you want to do this? Well, if you’re looking to get programmatic access to your database / data warehouse, you’ll need to authenticate. In 2025, more often than not, you’ll be using Microsoft Entra ID to do so and this is where the fun begins.

header

Let’s quickly go over the concepts. There are three main components we’re going to be dealing with:

SQL vs. Microsoft Entra ID

Databases on SQL Server or Azure SQL often support SQL-based authentication which is quite simply a username and a password. We’re not going to cover this in detail and I would even go as far as saying you shouldn’t use this anymore today.

Instead, we’re going to focus on Microsoft Entra ID authentication. This is the preferred method of authentication and in Fabric it’s even the only accepted method of authentication.

What is an access token?

Microsoft Entra ID works with a protocol called OAuth 2.0. In the end, you’ll end up with an access token which you have to provide when connecting to your database/data warehouse/API. This token is valid for a limited time. The access token is a JSON Web Token (JWT) which contains information about the user, the permissions they have, and the scope of the access.

JWT tokens are base64 encoded strings that contain three parts: a header, a payload, and a signature. This is a standard format for tokens used in OAuth 2.0 and OpenID Connect. If you want to learn more about JWT tokens, I recommend checking out the JWT.io website. You can paste your token there and it will decode it for you. This is a great way to see what information is contained in the token and how it’s structured.

jwt_token

The image above shows you a token I used to access Microsoft Fabric. We can inspect its content and find a few interesting details all contained in the token:

You can also see it contains my name, IP address, and a bit further down you’ll also find your username / email address.

Typically, when you’re running into issues and you’re trying to figure out what’s going on, you’ll look at the aud, exp, and oid values to verify that they match what you’d expect.

Which authentication principal to use when?

There are 2 scenarios in which you might need to authenticate:

  1. You are a user and you want to use a service from your own computer while you’re using it interactively.

  2. Your code or application is running somewhere automatically (e.g. in a pipeline, on a schedule, etc.) and you want to authenticate without user interaction.

In the first case you’ll always be using a user account. In the second, there are 2 options:

  1. Any form of managed identity. This should always be your preferred option as it doesn’t require you to manage any credentials. This is the most secure option.

  2. A service principal. Here you’ll need to manage credentials (client secret or certificate) and make sure your application has access to these during runtime.

Often, a managed identity is available:

Common ways to get a Microsoft Entra ID access token

Let’s look at a few common ways to get an access token. In the examples, I left out the scope as we’ll cover that in the next section.

Using Fabric Workspace Identity or Synapse service identity

In Synapse notebooks, Spark Jobs, Livy sessions, … you can acquire access tokens to access anything as the Synapse service identity.

1from notebookutils import credentials
2
3token = credentials.getToken(scope)

⚠️ This is not possible in Microsoft Fabric at the time of writing. Workspace Identity in Fabric can only be used for authentication in Shortcuts and Network Security.

Note that Microsoft does not support regular audiences/scopes in this case. You can only use the following scopes:

Inside Fabric

Inside Synapse

In code: Python, C#, Java, JavaScript, …

For most programming languages, Microsoft provides a library named Azure Identity.

This library has about the same set of classes in every language and is very easy to use. It comes with a set of Credential classes that can be used to acquire tokens. The most common ones are:

There are a few more, but the best practice is to use the DefaultAzureCredential class which will try all of the above (except InteractiveBrowserCredential) and a few more until it finds one that works. This is the most flexible option and works well in most scenarios.

1from azure.identity import DefaultAzureCredential
2
3credential = DefaultAzureCredential()
4token = credential.get_token(scope)

Using the Azure CLI

You can use the Azure CLI to acquire an access token, whichever way you are logged in.

1az account get-access-token --resource <scope>

Using PowerShell

You can do the same in PowerShell as well:

1$token = (Get-AzAccessToken -ResourceUrl <scope>).Token

Using the Power BI / Fabric web UI

In your web developer console (open with F12 or CTRL+SHIFT+J or CMD+OPT+J), enter the following:

1powerBIAccessToken

This prints out the token you’re actively using while using the Power BI / Fabric web UI. Note that you cannot issue a new one here or change the token scope. You can also copy it to your clipboard with the following code:

1document.querySelector('#copy').remove()
2copy(powerBIAccessToken)

Access token scopes

One thing you migth have noticed is that I kept mentioning a scope. Also, confusingly, the terms “resource”, “scope”, and “audience” are often used interchangeably.

The scope is usually a predefined URL. At the end of the scope, you append the permissions you want to request. However, unless you’re directly calling the Microsoft Entra ID API, you can often only provide a single permission set. In this case, we’ll append .default at the end of the URL to request all permissions available in the scope.

These are the different scopes you typically use in the context of working with data:

Usage with PyODBC

If you’re using the above to connect to SQL using PyODBC, you have to convert your token to bytes and pass it along the connection attributes, like so:

 1import struct
 2from azure.identity import DefaultAzureCredential
 3import pyodbc
 4
 5cred = DefaultAzureCredential()
 6
 7token = cred.get_token("https://api.fabric.microsoft.com/.default")
 8b_token = bytes(token.token, "UTF-8")
 9exp_token = b""
10for i in b_token:
11    exp_token += bytes({i})
12    exp_token += bytes(1)
13token_struct = struct.pack("=i", len(exp_token)) + exp_token
14
15connection_string = "Driver={ODBC Driver 18 for SQL Server};Server=yourendpoint.database.fabric.microsoft.com,1433;Database=yourdwh"
16
17with pyodbc.connect(connection_string, attrs_before={1256: token_struct }) as handle:
18    with handle.cursor() as cursor:
19        cursor.execute("select 1 as id")
20        records = cursor.fetchall()
21        print(records)

Usage with SQLAlchemy

Alec Van den broeck also commented on this post that it’s not very straightforward to use the access token with SQLAlchemy . The following code shows how to do this:

 1from sqlalchemy import create_engine, event
 2from sqlalchemy.engine.url import URL
 3
 4connection_string = "mssql+pyodbc://@my-server.database.windows.net/myDb?driver=ODBC+Driver+18+for+SQL+Server"
 5engine = create_engine(connection_string)
 6
 7@event.listens_for(engine, "do_connect")
 8def provide_token(dialect, conn_rec, cargs, cparams):
 9    # remove the "Trusted_Connection" parameter that SQLAlchemy adds
10    cargs[0] = cargs[0].replace(";Trusted_Connection=Yes", "")
11
12    # get token and convert it to a struct as demonstrated above for the ODBC driver
13    token_struct = ...
14
15    # apply it to keyword arguments
16    cparams["attrs_before"] = {1256: token_struct}

Conclusion

With the information above, you can use any combination. E.g. you can authenticatie to a SQL Server Managed Instance from a Notebook in Fabric using Workspace Identity, or you can use a GitHub Action to connect to a Fabric Database, and so on. I hope this post helps you understand the different ways to authenticate and how to acquire access tokens. If you have any questions, feel free to reach out.

You might also like

If you liked this article, follow me on LinkedIn or Bluesky to stay up-to-date with my latest posts. You might also like the following 2 posts about related topics:

Migrating Azure Synapse Dedicated SQL to Microsoft Fabric

10 minutes
Read the post titled Migrating Azure Synapse Dedicated SQL to Microsoft Fabric

If all those posts about Microsoft Fabric have made you excited, you might want to consider it as your next data platform. Since it is very new, not all features are available yet and most are still in preview. You could already adopt it, but if you want to deploy this to a production scenario, you’ll want to wait a bit longer. In the meantime, you can already start preparing for the migration. Let’s dive into the steps to migrate to Microsoft Fabric. Today: starting from Synapse Dedicated SQL Pools.

Read more

Is Microsoft Fabric just a rebranding?

10 minutes
Read the post titled Is Microsoft Fabric just a rebranding?

It’s a question I see popping up every now and then. Is Microsoft Fabric just a rebranding of existing Azure services like Synapse, Data Factory, Event Hub, Stream Analytics, etc.? Is it something more? Or is it something entirely new? I hate clickbait titles as much as you do. So, before we dive in, let me answer the question right away. No, Fabric is not just a rebranding. I would not even describe Fabric as an evolution (as Microsoft often does), but rather as a revolution! Now, let’s find out why.

Read more