教程:使用托管标识确保从应用服务进行的 Azure SQL 数据库连接的安全Tutorial: Secure Azure SQL Database connection from App Service using a managed identity

应用服务在 Azure 中提供高度可缩放、自修补的 Web 托管服务。App Service provides a highly scalable, self-patching web hosting service in Azure. 它还为应用提供托管标识,这是一项统包解决方案,可以确保安全地访问 Azure SQL 数据库和其他 Azure 服务。It also provides a managed identity for your app, which is a turn-key solution for securing access to Azure SQL Database and other Azure services. 应用服务中的托管标识可以让应用更安全,因为不需在应用中存储机密,例如连接字符串中的凭据。Managed identities in App Service make your app more secure by eliminating secrets from your app, such as credentials in the connection strings. 在本教程中,你要将托管标识添加到在以下教程之一中生成的示例 Web 应用:In this tutorial, you will add managed identity to the sample web app you built in one of the following tutorials:

完成后,示例应用就可以安全地连接到 SQL 数据库,不需用户名和密码。When you're finished, your sample app will connect to SQL Database securely without the need of username and passwords.

Note

本教程所述的步骤支持以下版本:The steps covered in this tutorial support the following versions:

  • .NET Framework 4.7.2.NET Framework 4.7.2
  • .NET Core 2.2.NET Core 2.2

要学习的知识:What you will learn:

  • 启用托管标识Enable managed identities
  • 授予 SQL 数据库访问托管标识的权限Grant SQL Database access to the managed identity
  • 配置实体框架,将 Azure AD 身份验证用于 SQL 数据库Configure Entity Framework to use Azure AD authentication with SQL Database
  • 使用 Azure AD 身份验证从 Visual Studio 连接到 SQL 数据库Connect to SQL Database from Visual Studio using Azure AD authentication

Note

在本地 Active Directory 域服务 (AD DS) 中,Azure AD 身份验证不同于集成式 Windows 身份验证Azure AD authentication is different from Integrated Windows authentication in on-premises Active Directory (AD DS). AD DS 和 Azure AD 使用的身份验证协议完全不相同。AD DS and Azure AD use completely different authentication protocols.

如果没有 Azure 订阅,可在开始前创建一个试用帐户If you don't have an Azure subscription, create a trial account before you begin.

先决条件Prerequisites

本文是教程:使用 SQL 数据库在 Azure 中生成 ASP.NET 应用教程:在 Azure 应用服务中生成 ASP.NET Core 和 SQL 数据库应用This article continues where you left off in Tutorial: Build an ASP.NET app in Azure with SQL Database or Tutorial: Build an ASP.NET Core and SQL Database app in Azure App Service. 请先完成这两篇教程之一(如果尚未完成)。If you haven't already, follow one of the two tutorials first. 也可调整这些步骤,使用 SQL 数据库来生成自己的 .NET 应用。Alternatively, you can adapt the steps for your own .NET app with SQL Database.

若要使用 SQL 数据库作为后端调试应用程序,请确保已经允许从计算机连接客户端。To debug your app using SQL Database as the back end, make sure that you've allowed client connection from your computer. 否则,请遵循使用 Azure 门户管理服务器级 IP 防火墙规则中的步骤添加客户端 IP。If not, add the client IP by following the steps at Manage server-level IP firewall rules using the Azure portal.

向 Azure AD 用户授予数据库访问权限Grant database access to Azure AD user

首先,通过将 Azure AD 用户指定为 SQL 数据库服务器的 Active Directory 管理员,对 SQL 数据库启用 Azure AD 身份验证。First enable Azure AD authentication to SQL Database by assigning an Azure AD user as the Active Directory admin of the SQL Database server. 此用户与用于注册 Azure 订阅的 Microsoft 帐户不同。This user is different from the Microsoft account you used to sign up for your Azure subscription. 它必须是你在 Azure AD 中创建、导入、同步或邀请到其中的用户。It must be a user that you created, imported, synced, or invited into Azure AD. 有关允许的 Azure AD 用户的详细信息,请参阅 SQL 数据库中的 Azure AD 功能和限制For more information on allowed Azure AD users, see Azure AD features and limitations in SQL Database.

如果 Azure AD 租户还没有用户,请按照使用 Azure Active Directory 添加或删除用户中的步骤创建一个用户。If your Azure AD tenant doesn't have a user yet, create one by following the steps at Add or delete users using Azure Active Directory.

使用 az ad user list 查找 Azure AD 用户的对象 ID,并替换 <user-principal-name> 。Find the object ID of the Azure AD user using the az ad user list and replace <user-principal-name>. 结果会保存到变量中。The result is saved to a variable.

azureaduser=$(az ad user list --filter "userPrincipalName eq '<user-principal-name>'" --query [].objectId --output tsv)

Tip

若要查看 Azure AD 中所有用户主体名称的列表,请运行 az ad user list --query [].userPrincipalNameTo see the list of all user principal names in Azure AD, run az ad user list --query [].userPrincipalName.

使用 Azure CLI 中的 az sql server ad-admin create 命令,将此 Azure AD 用户添加为 Active Directory 管理员。Add this Azure AD user as an Active Directory admin using az sql server ad-admin create command in the Azure CLI. 在以下命令中,替换 <server-name> 。In the following command, replace <server-name>.

az sql server ad-admin create --resource-group myResourceGroup --server-name <server-name> --display-name ADMIN --object-id $azureaduser

有关添加 Active Directory 管理员的详细信息,请参阅为 Azure SQL 数据库服务器预配 Azure Active Directory 管理员For more information on adding an Active Directory admin, see Provision an Azure Active Directory administrator for your Azure SQL Database Server

设置 Visual StudioSet up Visual Studio

WindowsWindows

Visual Studio for Windows 集成了 Azure AD 身份验证。Visual Studio for Windows is integrated with Azure AD authentication. 若要在 Visual Studio 中启用开发和调试,请在 Visual Studio 中添加 Azure AD 用户,方法是从菜单中依次选择“文件” > “帐户设置”,然后单击“添加帐户” 。To enable development and debugging in Visual Studio, add your Azure AD user in Visual Studio by selecting File > Account Settings from the menu, and click Add an account.

若要设置进行 Azure 服务身份验证的 Azure AD 用户,请从菜单中依次选择“工具” > “选项”,然后依次选择“Azure 服务身份验证” > “帐户选择” 。To set the Azure AD user for Azure service authentication, select Tools > Options from the menu, then select Azure Service Authentication > Account Selection. 选择已添加的 Azure AD 用户,然后单击“确定” 。Select the Azure AD user you added and click OK.

现已准备好将 SQL 数据库作为后端,使用 Azure AD 身份验证来开发和调试应用程序。You're now ready to develop and debug your app with the SQL Database as the back end, using Azure AD authentication.

MacOSMacOS

Visual Studio for Mac 未集成 Azure AD 身份验证。Visual Studio for Mac is not integrated with Azure AD authentication. 不过,稍后将使用的 Microsoft.Azure.Services.AppAuthentication 库可以使用 Azure CLI 中的令牌。However, the Microsoft.Azure.Services.AppAuthentication library that you will use later can use tokens from Azure CLI. 若要在 Visual Studio 中启用开发和调试,首先需要在本地计算机上安装 Azure CLITo enable development and debugging in Visual Studio, first you need to install Azure CLI on your local machine.

在本地计算机上安装 Azure CLI 后,请使用 Azure AD 用户通过以下命令登录 Azure CLI:Once Azure CLI is installed on your local machine, sign in to Azure CLI with the following command using your Azure AD user:

az cloud set -n AzureChinaCloud
az login --allow-no-subscriptions

现已准备好将 SQL 数据库作为后端,使用 Azure AD 身份验证来开发和调试应用程序。You're now ready to develop and debug your app with the SQL Database as the back end, using Azure AD authentication.

修改项目Modify your project

要对项目执行的步骤取决于它是 ASP.NET 项目还是 ASP.NET Core 项目。The steps you follow for your project depends on whether it's an ASP.NET project or an ASP.NET Core project.

修改 ASP.NETModify ASP.NET

在 Visual Studio 中,打开包管理器控制台,并添加 NuGet 包 Microsoft.Azure.Services.AppAuthenticationIn Visual Studio, open the Package Manager Console and add the NuGet package Microsoft.Azure.Services.AppAuthentication:

Install-Package Microsoft.Azure.Services.AppAuthentication -Version 1.3.1

在 Web.config 中,从文件顶部开始工作并进行以下更改 :In Web.config, working from the top of the file and make the following changes:

  • <configSections> 中,添加以下部分声明:In <configSections>, add the following section declaration in it:

    <section name="SqlAuthenticationProviders" type="System.Data.SqlClient.SqlAuthenticationProviderConfigurationSection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
    
  • </configSections> 结束标记下方,为 <SqlAuthenticationProviders> 添加以下 XML 代码。below the closing </configSections> tag, add the following XML code for <SqlAuthenticationProviders>.

    <SqlAuthenticationProviders>
      <providers>
        <add name="Active Directory Interactive" type="Microsoft.Azure.Services.AppAuthentication.SqlAppAuthenticationProvider, Microsoft.Azure.Services.AppAuthentication" />
      </providers>
    </SqlAuthenticationProviders>
    
  • 找到名为 MyDbConnection 的连接字符串,并将其 connectionString 值替换为 "server=tcp:<server-name>.database.chinacloudapi.cn;database=<db-name>;UID=AnyString;Authentication=Active Directory Interactive"Find the connection string called MyDbConnection and replace its connectionString value with "server=tcp:<server-name>.database.chinacloudapi.cn;database=<db-name>;UID=AnyString;Authentication=Active Directory Interactive". 将 <server-name> 和 <db-name> 替换为你的服务器名称和数据库名称 。Replace <server-name> and <db-name> with your server name and database name.

这就是连接到 SQL 数据库所要完成的所有准备工作。That's every thing you need to connect to SQL Database. 在 Visual Studio 中调试时,代码将使用设置 Visual Studio 中配置的 Azure AD 用户。When debugging in Visual Studio, your code uses the Azure AD user you configured in Set up Visual Studio. 稍后你将设置 SQL 数据库服务器,以允许应用服务应用的托管标识建立连接。You'll set up the SQL Database server later to allow connection from the managed identity of your App Service app.

键入 Ctrl+F5,再次运行该应用。Type Ctrl+F5 to run the app again. 浏览器中相同的 CRUD 应用程序现使用 Azure AD 身份验证直接连接到 Azure SQL 数据库。The same CRUD app in your browser is now connecting to the Azure SQL Database directly, using Azure AD authentication. 此设置使你能够从 Visual Studio 运行数据库迁移。This setup lets you run database migrations from Visual Studio.

修改 ASP.NET CoreModify ASP.NET Core

在 Visual Studio 中,打开包管理器控制台,并添加 NuGet 包 Microsoft.Azure.Services.AppAuthenticationIn Visual Studio, open the Package Manager Console and add the NuGet package Microsoft.Azure.Services.AppAuthentication:

Install-Package Microsoft.Azure.Services.AppAuthentication -Version 1.3.1

ASP.NET Core 和 SQL 数据库教程中完全未使用 MyDbConnection 连接字符串,因为本地开发环境使用 Sqlite 数据库文件,而 Azure 生产环境使用应用服务中的连接字符串。In the ASP.NET Core and SQL Database tutorial, the MyDbConnection connection string isn't used at all because the local development environment uses a Sqlite database file, and the Azure production environment uses a connection string from App Service. 使用 Active Directory 身份验证时,最好是让这两种环境使用相同的连接字符串。With Active Directory authentication, you want both environments to use the same connection string. appsettings.json 中,请将 MyDbConnection 连接字符串的值替换为:In appsettings.json, replace the value of the MyDbConnection connection string with:

"Server=tcp:<server-name>.database.chinacloudapi.cn,1433;Database=<database-name>;"

Startup.cs 中,删除前面添加的代码节:In Startup.cs, remove the code section that you added before:

// Use SQL Database if in Azure, otherwise, use SQLite
if (Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT") == "Production")
    services.AddDbContext<MyDatabaseContext>(options =>
            options.UseSqlServer(Configuration.GetConnectionString("MyDbConnection")));
else
    services.AddDbContext<MyDatabaseContext>(options =>
            options.UseSqlite("Data Source=localdatabase.db"));

// Automatically perform database migration
services.BuildServiceProvider().GetService<MyDatabaseContext>().Database.Migrate();

并将其替换为以下代码:And replace it with the following code:

services.AddDbContext<MyDatabaseContext>(options => {
    options.UseSqlServer(Configuration.GetConnectionString("MyDbConnection"));
});

接下来,使用 SQL 数据库的访问令牌提供实体框架数据库上下文。Next, you supply the Entity Framework database context with the access token for the SQL Database. Data\MyDatabaseContext.cs 中,将以下代码添加到空的 MyDatabaseContext (DbContextOptions<MyDatabaseContext> options) 构造函数的大括号中:In Data\MyDatabaseContext.cs, add the following code inside the curly braces of the empty MyDatabaseContext (DbContextOptions<MyDatabaseContext> options) constructor:

var conn = (System.Data.SqlClient.SqlConnection)Database.GetDbConnection();
conn.AccessToken = (new Microsoft.Azure.Services.AppAuthentication.AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.chinacloudapi.cn/").Result;

Note

为清楚和简单起见,此演示代码是同步的。This demonstration code is synchronous for clarity and simplicity.

这就是连接到 SQL 数据库所要完成的所有准备工作。That's every thing you need to connect to SQL Database. 在 Visual Studio 中调试时,代码将使用设置 Visual Studio 中配置的 Azure AD 用户。When debugging in Visual Studio, your code uses the Azure AD user you configured in Set up Visual Studio. 稍后你将设置 SQL 数据库服务器,以允许应用服务应用的托管标识建立连接。You'll set up the SQL Database server later to allow connection from the managed identity of your App Service app. AzureServiceTokenProvider 类将令牌缓存在内存中,在过期前才将其从 Azure AD 检索出来。The AzureServiceTokenProvider class caches the token in memory and retrieves it from Azure AD just before expiration. 不需要任何自定义代码就可以刷新该令牌。You don't need any custom code to refresh the token.

Tip

如果你配置的 Azure AD 用户可以访问多个租户,请使用所需的租户 ID 调用 GetAccessTokenAsync("https://database.chinacloudapi.cn/", tenantid) 以检索正确的访问令牌。If the Azure AD user you configured has access to multiple tenants, call GetAccessTokenAsync("https://database.chinacloudapi.cn/", tenantid) with the desired tenant ID to retrieve the proper access token.

键入 Ctrl+F5,再次运行该应用。Type Ctrl+F5 to run the app again. 浏览器中相同的 CRUD 应用程序现使用 Azure AD 身份验证直接连接到 Azure SQL 数据库。The same CRUD app in your browser is now connecting to the Azure SQL Database directly, using Azure AD authentication. 此设置使你能够从 Visual Studio 运行数据库迁移。This setup lets you run database migrations from Visual Studio.

使用托管标识连接性Use managed identity connectivity

接下来,配置应用服务应用,使其使用系统分配的托管标识连接到 SQL 数据库。Next, you configure your App Service app to connect to SQL Database with a system-assigned managed identity.

在应用上启用托管标识Enable managed identity on app

若要为 Azure 应用启用托管标识,请在 CLI 中使用 az webapp identity assign 命令。To enable a managed identity for your Azure app, use the az webapp identity assign command in the CLI. 在以下命令中,替换 <app name>In the following command, replace <app name>.

az webapp identity assign --resource-group myResourceGroup --name <app-name>

这是一个输出示例:Here's an example of the output:

{
  "additionalProperties": {},
  "principalId": "21dfa71c-9e6f-4d17-9e90-1d28801c9735",
  "tenantId": "72f988bf-86f1-41af-91ab-2d7cd011db47",
  "type": "SystemAssigned"
}

向托管标识授予权限Grant permissions to managed identity

Note

如果需要,可将该标识添加到 Azure AD 组,然后将 SQL 数据库访问权限授予 Azure AD 组,而不是授予该标识。If you want, you can add the identity to an Azure AD group, then grant SQL Database access to the Azure AD group instead of the identity. 例如,以下命令将上一步中的托管标识添加到名为 myAzureSQLDBAccessGroup 的新组:For example, the following commands add the managed identity from the previous step to a new group called myAzureSQLDBAccessGroup:

groupid=$(az ad group create --display-name myAzureSQLDBAccessGroup --mail-nickname myAzureSQLDBAccessGroup --query objectId --output tsv)
msiobjectid=$(az webapp identity show --resource-group myResourceGroup --name <app-name> --query principalId --output tsv)
az ad group member add --group $groupid --member-id $msiobjectid
az ad group member list -g $groupid

在 Azure CLI 中,使用 SQLCMD 命令登录到 SQL 数据库。In the Azure CLI, sign in to SQL Database by using the SQLCMD command. 将 <server-name> 替换为 SQL 数据库服务器名称,将 <db-name> 替换为应用使用的数据库名称,将 <aad-user-name> 和 <aad-password> 替换为 Azure AD 用户的凭据 。Replace <server-name> with your SQL Database server name, <db-name> with the database name your app uses, and <aad-user-name> and <aad-password> with your Azure AD user's credentials.

sqlcmd -S <server-name>.database.chinacloudapi.cn -d <db-name> -U <aad-user-name> -P "<aad-password>" -G -l 30

在所需数据库的 SQL 提示符窗口中运行以下命令,从而添加 Azure AD 组并授予应用所需的权限。In the SQL prompt for the database you want, run the following commands to add the Azure AD group and grant the permissions your app needs. 例如,For example,

CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<identity-name>];
ALTER ROLE db_datawriter ADD MEMBER [<identity-name>];
ALTER ROLE db_ddladmin ADD MEMBER [<identity-name>];
GO

<identity-name> 是 Azure AD 中托管标识的名称。<identity-name> is the name of the managed identity in Azure AD. 由于它是系统分配的,它始终与应用服务应用的名称相同。Since it's system-assigned, it's always the same as the name of your App Service app. 若要为 Azure AD 组授予权限,请改为使用组的显示名称(例如,myAzureSQLDBAccessGroup)。To grant permissions for an Azure AD group, use the group's display name instead (for example, myAzureSQLDBAccessGroup).

键入 EXIT,返回到 Azure CLI 提示符窗口。Type EXIT to return to the Azure CLI prompt.

修改连接字符串Modify connection string

请记住,在 Web.configappsettings.json 中所做的相同更改适用于托管标识,因此只需删除应用服务中的现有连接字符串,该字符串由 Visual Studio 在首次部署应用时创建。Remember that the same changes you made in Web.config or appsettings.json works with the managed identity, so the only thing to do is to remove the existing connection string in App Service, which Visual Studio created deploying your app the first time. 使用以下命令,但将 <app-name> 替换为应用名称 。Use the following command, but replace <app-name> with the name of your app.

az webapp config connection-string delete --resource-group myResourceGroup --name <app-name> --setting-names MyDbConnection

发布更改Publish your changes

现在,剩下的操作是将更改发布到 Azure。All that's left now is to publish your changes to Azure.

如果你是在学完教程:使用 SQL 数据库在 Azure 中生成 ASP.NET 应用 后转到本教程,请在 Visual Studio 中发布更改。If you came from Tutorial: Build an ASP.NET app in Azure with SQL Database, publish your changes in Visual Studio. 在“解决方案资源管理器” 中,右键单击 “DotNetAppSqlDb” 项目,然后选择“发布” 。In the Solution Explorer, right-click your DotNetAppSqlDb project and select Publish.

从解决方案资源管理器发布

在发布页中单击“发布”。 In the publish page, click Publish.

如果你是在学完教程:在 Azure 应用服务中生成 ASP.NET Core 和 SQL 数据库应用 后转到本教程,请运行以下命令使用 Git 发布更改:If you came from Tutorial: Build an ASP.NET Core and SQL Database app in Azure App Service, publish your changes using Git, with the following commands:

git commit -am "configure managed identity"
git push azure master

当新网页显示待办事项列表时,表明应用使用了托管标识连接到数据库。When the new webpage shows your to-do list, your app is connecting to the database using the managed identity.

Code First 迁移后的 Azure 应用

现在应该可以像以前一样编辑待办事项列表了。You should now be able to edit the to-do list as before.

清理资源Clean up resources

若要清理资源,请运行以下命令:To clean up your resources, run the following command:

az group delete --name myResourceGroup

后续步骤Next steps

你已了解:What you learned:

  • 启用托管标识Enable managed identities
  • 授予 SQL 数据库访问托管标识的权限Grant SQL Database access to the managed identity
  • 配置实体框架,将 Azure AD 身份验证用于 SQL 数据库Configure Entity Framework to use Azure AD authentication with SQL Database
  • 使用 Azure AD 身份验证从 Visual Studio 连接到 SQL 数据库Connect to SQL Database from Visual Studio using Azure AD authentication

转到下一教程,了解如何向 Web 应用映射自定义 DNS 名称。Advance to the next tutorial to learn how to map a custom DNS name to your web app.