Arabic
French
Spanish
We are the Best Consulting web site as part of the annual WebAward Competition!
Microsoft Fabric provides SQL Endpoint on Lakehouse and Warehouse through which we can run SQL queries and Stored Procedures on the stored data. Whenever we run SQL statements, they need to be authenticated by a principal so that the SQL Engine can check whether the principal is authorized to access and execute statements. But unlike SQL Server or Azure SQL DB, you cannot use SQL Authentication for authenticating. You can only use the Microsoft Entra ID user principal and Microsoft Entra ID service principal.
If you are running automated refreshes or connecting third-party applications through SQL Endpoint, it is always best practice to use a service account or service principal so that the authentication is not dependent on a user’s account as:
- When a user’s access changes, the existing applications break.
- Depending on the policy, a change in the user’s password will break the application’s connection.
Microsoft Entra ID Service principals provide a centralized portal to monitor the expiry of secrets and change it before expiry. A new secret can be created while an existing secret is still active. There is no downtime when a secret change needs to be made.
In this blog, we will see how we can leverage Microsoft Entra ID service principal for authentication in Fabric.
Creating Service Principal
To create a service principal, we need to create an app registration in Azure. The steps are provided in the Microsoft documentation:
We recommend going with Option #3, where we create a client secret. This helps to authenticate just like using a password.
Once we create the service principal, we would need 3 things for us to use it in applications that support the Service Principal.
- Tenant ID
- Application/Client ID
- Client Secret
Providing access to service principal
To provide access to the service principal, share the lakehouse or warehouse with the service principal like below:
You can provide granular access to the tables by using a statement like below:
GRANT SELECT ON [WH_Name].[dbo].[Table] TO [AzureServiceAccount];
Using service principal
You can easily test whether the service principal works by opening SQL Server Management Studio (SSMS) and changing the Authentication type to ‘Microsoft Entra Service Principal’. For Username, use the Application/Client ID and for password, use the client secret.
If you are using a C# Program, like ASP.NET website where you want to use data from SQL Endpoint, you can do it by using a code like below:
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 have seen how we can create a service principal and use it to connect and run SQL statements for use in applications. It is always a best practice to establish connections in the applications using a service principal so that they are not dependent on a user’s account.
Tags:
microsoft fabric
Recent News
Working with Data Activator in Microsoft
16th February 2024
Transformations at Scale: Using Microsoft Fabric
9th April 2024
To Pie or not to Pie
9th October 2024Stay ahead in a rapidly world. Subscribe to Prysm Insights,our monthly look at the critical issues facing global business.
[mc4wp_form]
We come with an experienced engineering and management team to offer strategy and consulting, technology, and operations services.
© 2024 Data Crafters | All rights reserved