Authenticating using Service Principal in Microsoft Fabric SQL Endpoint

Microsoft Fabric provides a SQL Endpoint for Lakehouse and Warehouse, enabling you to run SQL queries and stored procedures on your data. However, to execute SQL statements, the SQL Engine requires authentication to verify the principal’s permissions. Unlike SQL Server or Azure SQL DB, Microsoft Fabric does not support SQL Authentication. Instead, you must use either a Microsoft Entra ID user principal or a Microsoft Entra ID service principal.

When setting up automated refreshes or connecting third-party applications through the SQL Endpoint, it’s best to use a service account or service principal. Relying on a user’s account can lead to complications, such as:

Access changes: If a user’s permissions are modified, it may break existing applications.

Password changes: Depending on policies, a password update could disrupt application connections.

Microsoft Entra ID service principals simplify this process by providing a centralized way to manage secrets, monitor their expiration, and rotate them seamlessly. You can create a new secret while the old one is still active, ensuring no downtime during the transition.

In this blog, we’ll explore how to use Microsoft Entra ID service principals for authentication in Microsoft Fabric.

Creating service principal

To create a service principal, the first step is to register an application in Azure. Detailed steps for this process are available in the Microsoft documentation:

Register a Microsoft Entra app and create a service principal – Microsoft identity platform | Microsoft Learn

We recommend using Option #3, which involves creating a client secret. This approach works similarly to using a password for authentication and is straightforward to implement.

Once the service principal is created, you’ll need three key pieces of information to use it in applications that support service principals:

1. Tenant ID
2. Application/Client ID
3. Client Secret

Providing access to service principal

To grant access to the service principal, you can share the Lakehouse or Warehouse with the service principal as shown below:

For more granular control, you can provide specific access to tables using a SQL statement like this:

GRANT SELECT ON [WH_Name].[dbo].[Table] TO [AzureServiceAccount];

Using service principal

To test whether the service principal is working correctly, you can use SQL Server Management Studio (SSMS). Simply change the Authentication type to ‘Microsoft Entra Service Principal’, then enter the Application/Client ID as the username and the Client Secret as the password.

If you’re integrating the service principal into a C# program, such as an ASP.NET website, you can connect to the SQL Endpoint using code similar to the following:

        string clientId = "<Use-Client-ID>";
        string clientSecret = "<Use-Client-Secret>";
        string sqlServer = "<SQL-Endpoint>";
        string database = "<Database-Name>"; //Database name is nothing but Lakehouse/Warehouse name

        // Connect to SQL Server
        string connectionString = $"Server={sqlServer}; Authentication=Active Directory Service Principal; Encrypt=True; Database={database}; User Id={clientId}; Password={clientSecret}";
        SqlConnection connection = new SqlConnection(connectionString);

Conclusion

We’ve walked through how to create a service principal and use it to connect and run SQL statements in applications. As a best practice, it’s highly recommended to establish connections in your applications using a service principal. This approach ensures your applications remain independent of individual user accounts, reducing the risk of disruptions caused by access changes or password updates.