Setup SQL Database with Azure Active Directory Only Enabled
Introduction
Azure Active Directory (AAD) integration with SQL Database enhances security and simplifies user management for your Azure SQL resources. In this blog post, I will guide you through the process of configuring SQL Database with AAD exclusively through the Azure Portal and demonstrate how to connect to it using C#.
Prerequisites
Before I begin, ensure you have the following prerequisites in place:
1. Azure Subscription: You need an active Azure subscription to use Azure SQL Database and Azure Active Directory.
2. Azure SQL Database: Create an Azure SQL Database if you don’t have one already.
3. Azure Active Directory: Ensure you have an Azure AD tenant and admin privileges to configure Azure AD settings.
4. Azure Portal Access: Access to the Azure Portal with sufficient permissions to configure resources.
Configuring SQL Database with Azure Active Directory
1. Sign into Azure Portal: Open a web browser and navigate to the Azure Portal. Sign in with your Azure account credentials.
2. Locate Your SQL Database: In the Azure Portal, find the SQL Database you want to configure with AAD. Use the search bar at the top of the portal for quick access.
3. Azure AD Admin Configuration: In the left-hand menu of your SQL Database’s overview page, scroll down to the “Settings” section and click on “Active Directory admin” Now known as Microsoft Entra ID.
4. Add Azure AD Admin: Click on the “+ Set admin” button. In the “Set admin” blade, select your Azure AD tenant and search for the Azure AD user or group you want to assign as the admin for your SQL Database. This user or group will have the necessary permissions to manage the database.
Note: If you want to disable SQL authentication and only use Microsoft Entra ID then select the check box with the “Select only Microsoft Entra authentication for this server” option.
5. You will have multiple options to choose from when Set Admin is clicked. Groups are recommended as you will need developers with various permissions to access the DB and establish a connection between the application and the database.
a. Users — Select an individual user to be admin.
b. Groups — Create multiple groups with RBAC restrictions and set only the ones with admin privileges here.
c. Enterprise Applications — Use this to set the Service Principle to be as admin to the DB.
6. Save Changes: After selecting the admin, click the “Save” button to save your changes. This action associates your Azure AD user or group with the SQL Database.
7. Testing AAD Connection: To verify the AAD integration, you can use the Azure Portal’s built-in query editor to connect to your SQL Database using Azure AD credentials. Go to your database’s overview page, select “Query editor (preview),” and log in with your Azure AD credentials.
8. Connection String: Navigate to your database and click on Connection Strings to pick up the connection string required to connect to the database via code.
Now that we have SQL Database configured with Azure AD(Entra ID), we will see how we can connect to it programmatically using C#.
1. Install Libraries: In your C# project, make sure to install the following NuGet packages if you haven’t already:
a. ‘System.Data.SqlClient’: This package provides the SQL data provider for .NET applications.
2. Sample Code:
a. Replace the placeholders in the code with your specific values.
b. The “Authentication=ActiveDirectoryManagedIdentity” part in the connection string specifies the use of Azure AD for authentication.
3. Run Your C# Application: Build and run your C# application. It will connect to the SQL Database using Azure AD credentials.
Conclusion
Configuring SQL Database with Azure Active Directory through the Azure Portal and connecting to it using C# provides a secure and manageable way to access your database resources. By following the steps outlined in this blog post, you can strengthen your database security and simplify user management while programmatically connecting to your SQL Database using C# and Azure AD.
About the author
Darshan Raviprakash is a Microsoft Technical Lead here at Version 1.
