Setting Up Microsoft Entra Service Principal for Azure RBAC and Connecting to Fabric SQL Database

In this article, you will learn how to create a Microsoft Entra application and service principal for use with Role-Based Access Control (RBAC). When you register a new application in Microsoft Entra ID, a service principal is automatically created for the app registration. The service principal acts as the application's identity within the Microsoft Entra tenant. Access to resources is determined by the roles assigned to the service principal, allowing fine-grained control over which resources can be accessed and at what level. For security reasons, it's always recommended to use service principals with automated tools rather than allowing sign-ins with a user identity.

Prerequisites

Before you can register an application in your Microsoft Entra tenant, make sure you have the following.

• A Microsoft Entra user account. If you don’t have one, you can create a free account.
• Sufficient permissions to register an application within your Microsoft Entra tenant and assign a role to the application in your Azure subscription.

Register an Application with Microsoft Entra ID and Create a Service Principal

1. Sign in to Azure Portal.
2. Navigate to Microsoft Entra ID (formerly known as Azure Active Directory).
3. Click +New, then select App registration.
   
4. Name the application (e.g., MyAppServicePrincipal).
5. Under Supported account types, select Accounts in this organizational directory only.
6. Leave the Redirect URI set to the default value.
7. Click Register.
   
8. Go to Microsoft Entra -> App registrations, select your application, and note the Application (Client) ID.
   
9. Go to Certificates & secrets > New client secret.
10. Provide a description and set an expiration period for the secret.
11. Click Add and note down the secret value (you will not be able to see it again).
    

Assign a Role to the Application

To enable access to resources in your subscription, you need to assign a role to the application. Choose the role that best suits your application’s requirements.

You can assign roles at the following levels: subscription, resource group, or resource. Permissions are inherited down to lower levels of scope.

1. Sign in to the Azure portal.
2. Select the scope for the role assignment. For example, to assign a role at the subscription level, search for and select Subscriptions. If you don’t see your subscription, use the global subscription filter. Make sure your desired subscription is selected for the tenant.
3. Select Access control (IAM).
4. Click Add, then select Add role assignment.
5. In the Role tab, select the role you wish to assign to the application from the list.
6. Click Next.
7. In the Members tab, select User, group, or service principal for Assign access to.
8. Click Select members. By default, Microsoft Entra applications may not be listed. To find your application, search by name.
9. Select your application, then click Review + assign.
   

Fabric Configuration

Microsoft Entra authentication for use with a Warehouse or Lakehouse SQL analytics endpoint requires configuration in both the Tenant, Workspace, and SQL user settings.

Tenant Setting

A Fabric admin in your tenant must permit Service Principal Names (SPNs) to access Fabric APIs, which are necessary for SQL connection strings to interact with Fabric warehouse or SQL analytics endpoints.

This setting is located in the Developer settings section and is labeled Service principals can use Fabric APIs. Ensure this setting is Enabled.

Workspace Setting

A Fabric admin in your workspace must grant access to a user or SPN to access Fabric items. Access can be granted in two ways.

1. Grant membership to a role: Assign any workspace role (Admin, Member, Contributor, or Viewer) to enable the user or SPN to connect to warehouse or lakehouse items using a SQL connection string.
   • Navigate to the Manage access section in the Workspace, and assign the Contributor role.
     
     
     
2. Assign access to a specific item: Grant access to a specific Warehouse or SQL analytics endpoint in a Lakehouse. A Fabric admin can select from different permission levels.
   • Navigate to the relevant Warehouse or SQL analytics endpoint.
   • Select More options, then Manage Permissions.
     
   • Add the user or SPN on the Grant people access page.
   • Assign the necessary permissions. Select No Additional permissions if you only want to grant connection permissions.

Since the SPN has been granted access at the workspace level, it inherits permissions for the Fabric SQL database, as shown in the diagram below.

SQL User Settings

To grant access at the SQL level.

1. Navigate to Fabric SQL Database.
2. Under the Security tab, click Manage SQL Security. You can either create a new role and assign it to your SPN, or directly assign the default role to your SPN.
   
   

Alternatively, you can run the following commands to create a user and assign roles.

CREATE USER [YourServicePrincipalName] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [YourServicePrincipalName];
ALTER ROLE db_datawriter ADD MEMBER [YourServicePrincipalName];

To confirm the user has been created, execute the following query.

SELECT name, type, type_desc, 
       CAST(CAST(sid AS varbinary(16)) AS uniqueidentifier) AS appId 
FROM sys.database_principals 
WHERE name = '<YourServicePrincipalName>';

Connecting to Fabric SQL Database via SSMS

If you've already created tables and views in your Fabric SQL database, you can copy the connection string by navigating to Settings > Connection String, and copying the server and database details.

You can also see my previous article on how to set up fabric SQL Database https://www.csharp.com/article/getting-started-with-dbt-data-build-tool-in-microsoft-fabric/

1. Open SSMS (SQL Server Management Studio).
2. Click Connect > Database Engine.
3. Enter the following details.
   • Server Name: <server name you copied>
   • Authentication: Microsoft Entra Service Principal
   • Username: <Application (Client) ID>
   • Password: <secret value you copied while creating the SPN secret>
   • Connect to Database: <Database name>
     
     
4. Click Connect.

You have now successfully connected to your Fabric SQL database via SSMS. To validate the connection, you can execute a query to read data from a table.

Conclusion

In this article, we covered the process of creating a service principal in Microsoft Entra, assigning roles, configuring access in the Fabric workspace and SQL database, and connecting via SSMS.

Stay tuned for my next article!