TOC
Why we use it
Architecture
How to use it
References
Why we use it
This tutorial will introduce how to integrate Microsoft Entra with Azure SQL Server to avoid using fixed usernames and passwords. By utilizing user-assigned managed identities as a programmatic bridge, it becomes easier for Azure-related PaaS services (such as Function App or App Services) to communicate with the database without storing connection information in plain text.
Architecture
I will introduce each service or component and their configurations in subsequent chapters according to the order of A-D:
A: The company's account administrator needs to create or designate a user as the database administrator. This role can only be assigned to one person within the database and is responsible for basic configuration and the creation and maintenance of other database users. It is not intended for development or actual system operations.
B: The company's security department needs to create one or more user-assigned managed identities. In the future, the Web App will issue access requests to the database under different user identities.
C: The company's data department needs to create or maintain a database and designate Microsoft Entra as the only login method, eliminating other fixed username/password combinations.
😧 The company's development department needs to create a Web App (or other service) as the basic unit of the business system. Programmers within this unit will write business logic (e.g., accessing the database) and deploy it here.
How to use it
A: As this article does not dive into the detailed configuration of Microsoft Entra, it will only outline the process. The company's account administrator needs to create or designate a user as the database administrator. In this example, we will call this user "cch," and the account, "cch@thexxxxxxxxxxxx" will be used in subsequent steps.
B: Please create a user-assigned managed identity from Azure Portal. And copy the Client ID and Resource ID once you've created the identity for the further use.
C-1: Create a database/SQL server. During this process, you need to specify the user created in Step A as the database administrator. Please note that to select "Microsoft Entra-only authentication." In this mode, the username/password will no longer be used. Then, click on "Next: Networking."
Since this article does not cover the detailed network configuration of the database, temporarily allow public access during the tutorial. Use the default values for other settings, click on "Review + Create," and then click "Create" to finish the setup.
During this process, you need to specify the user-assigned managed identity created in Step B as the entity that will actually operate the database.
And leave it default from the rest of the parts
C-2: After the database has created, you can log in using the identity "cch@thexxxxxxxxxxxx" you've get from Step A which is the database administrator. Open a PowerShell terminal and using the "cch" account, enter the following command to log in to SQL Server. You will need to change the <text> to follow your company's naming conventions.
sqlcmd -S <YOUR_SERVER_NAME>.database.windows.net -d <YOUR_DB_NAME> -U <YOUR_FULL_USER_EMAIL> -G
You will be prompt for a 2 step verification.
Returning to the console, we will now create user accounts in SQL Server for the managed identities setup from Step B. First, we will introduce the method for the user-assigned managed identity. The purpose of the commands is to grant database-related operational permissions to the newly created user. This is just an example. In actual scenarios, you should follow your company's security policies and make the necessary adjustments accordingly. Please enter the following command.
CREATE USER [<YOUR_IDENTITY_NAME>] FROM EXTERNAL PROVIDER;
USE [<YOUR_DB_NAME>];
EXEC sp_addrolemember 'db_owner', '<YOUR_IDENTITY_NAME>';
For testing purposes, we will create a test table, and insert some data.
CREATE TABLE TestTable (
Column1 INT,
Column2 NVARCHAR(100)
);
INSERT INTO TestTable (Column1, Column2) VALUES (1, 'First Record');
INSERT INTO TestTable (Column1, Column2) VALUES (2, 'Second Record');
D-1: In this example, we can create a Web App with any SKU/region. For the development language (stack), we choose Python as a demonstration, though other languages also support the same functionality. Since this article does not cover the detailed network configuration or other specifics of the Web App, we will use the default values for other settings. Simply click on "Review + Create," and then click on "Create" to complete the process.
D-2: After Web App has created, please open Azure Cloud Shell in the bash mode and enter a command. You will need to change the <text> to follow your company's naming conventions.
az webapp identity assign --resource-group <YOUR_RG_NAME> --name <YOUR_APP_NAME> --identities <RESOURCE_ID_IN_STEP_B>
D-3: Programmer can now deploy the code to the Web App. In this tutorial, we use Quickstart: Deploy a Python (Django, Flask, or FastAPI) web app to Azure - Azure App Service | Microsoft Learn to complete the example. Other languages also have their respective SQL Server connectors and follow the same principles.
In requirements.txt, in addition to the existing ones, please add the following packages: mssql-django
In quickstartproject/settings.py, include the following example content, you will need to change the <text> to follow your company's naming conventions
DATABASES = {
'default': {
'ENGINE': 'mssql',
'NAME': '<YOUR_DB_NAME>',
'HOST': '<YOUR_SERVER_NAME>.database.windows.net',
'PORT': '1433',
"USER": "<CLIENT_ID_IN_STEP_B>",
'OPTIONS': {
'driver': 'ODBC Driver 18 for SQL Server',
'extra_params': 'Authentication=ActiveDirectoryMsi',
}
}
}
In hello_azure/views.py, include the following example content.
def index(request):
raw_text = ""
with connection.cursor() as cursor:
cursor.execute("SELECT Column2 FROM TestTable")
rows = cursor.fetchall()
for row in rows:
raw_text = row
return HttpResponse(raw_text, content_type='text/plain')
Please note that the code I provided in this tutorial is only suitable for the testing phase. Its purpose is to verify usability and it is not intended for production use. Ultimately, please make the corresponding modifications based on the business functionality and security guidelines of your own environment.
Once the deployment is complete, you can proceed with testing. We can observe that the Web App will call the authentication endpoint in the background to get an access token. It will then use this token to interact with the database and subsequently print out the queried data.
References:
Authenticate with Microsoft Entra ID in sqlcmd - SQL Server | Microsoft Learn
Microsoft
