教程:使用托管标识确保从应用服务进行的 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. 在本教程中,会将托管标识添加到在教程:使用 SQL 数据库在 Azure 中构建 ASP.NET 应用中构建的示例 ASP.NET Web 应用。In this tutorial, you will add managed identity to the sample ASP.NET web app you built in Tutorial: Build an ASP.NET app in Azure with SQL Database. 完成后,示例应用就可以安全地连接到 SQL 数据库,不需用户名和密码。When you're finished, your sample app will connect to SQL Database securely without the need of username and passwords.

Note

此方案目前受 .NET Framework 4.6 及更高版本的支持,但不受 .NET Core 2.1 的支持。This scenario is currently supported by .NET Framework 4.6 and above, but not by .NET Core 2.1. .NET Core 2.2 支持此方案,但它尚未包括在应用服务的默认映像中。.NET Core 2.2 does support the scenario, but is not yet included in the default images in App Service.

学习如何:What you learn how to:

  • 启用托管标识Enable managed identities
  • 授予 SQL 数据库访问托管标识的权限Grant SQL Database access to the managed identity
  • 配置应用程序代码,以便使用 Azure Active Directory 身份验证通过 SQL 数据库进行身份验证Configure application code to authenticate with SQL Database using Azure Active Directory authentication
  • 在 SQL 数据库中向托管标识授予最低特权Grant minimal privileges to the managed identity in SQL Database

Note

在本地 Active Directory (AD DS) 中,Azure Active Directory 身份验证_不同_于集成式 Windows 身份验证。Azure Active Directory authentication is different from Integrated Windows authentication in on-premises Active Directory (AD DS). AD DS 和 Azure Active Directory 使用的身份验证协议完全不相同。AD DS and Azure Active Directory 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 应用的后续内容。This article continues where you left off in Tutorial: Build an ASP.NET app in Azure with SQL Database. 如果尚未学习该教程,请先学习该教程。If you haven't already, follow that tutorial first. 也可调整这些步骤,使用 SQL 数据库来构建自己的 ASP.NET 应用。Alternatively, you can adapt the steps for your own ASP.NET app with SQL Database.

启用托管标识Enable managed identities

若要为 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>

以下示例演示了在 Azure Active Directory 中创建标识后的输出:Here's an example of the output after the identity is created in Azure Active Directory:

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

下一步将用到 principalId 的值。You'll use the value of principalId in the next step. 若要在 Azure Active Directory 中查看新标识的详细信息,请使用 principalId 的值运行以下可选命令:If you want to see the details of the new identity in Azure Active Directory, run the following optional command with the value of principalId:

az ad sp show --id <principalid>

向标识授予数据库访问权限Grant database access to identity

接下来,请在 CLI 中使用 az sql server ad-admin create 命令向应用的托管标识授予数据库访问权限。Next, you grant database access to your app's managed identity, using the az sql server ad-admin create command in the CLI. 在以下命令中,替换 <server_name> 和 <principalid_from_last_step>。In the following command, replace <server_name> and <principalid_from_last_step>. 键入 <admin_user> 的管理员名称。Type an administrator name for <admin_user>.

az sql server ad-admin create --resource-group myResourceGroup --server-name <server_name> --display-name <admin_user> --object-id <principalid_from_last_step>

托管标识现在可以访问 Azure SQL 数据库服务器了。The managed identity now has access to your Azure SQL Database server.

修改连接字符串Modify connection string

在 CLI 中使用 az webapp config appsettings set 命令修改以前为应用设置的连接。Modify the connection you set previously for your app, using the az webapp config appsettings set command in the CLI. 在以下命令中,将 <app name> 替换为应用的名称,将 <server_name><db_name> 替换为 SQL 数据库的相应名称。In the following command, replace <app name> with the name of your app, and replace <server_name> and <db_name> with the ones for your SQL Database.

az webapp config connection-string set --resource-group myResourceGroup --name <app name> --settings MyDbConnection='Server=tcp:<server_name>.database.chinacloudapi.cn,1433;Database=<db_name>;' --connection-string-type SQLAzure

修改 ASP.NET 代码Modify ASP.NET code

在 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.1.0-preview

打开 Models\MyDatabaseContext.cs,将以下 using 语句添加到文件顶部:Open Models\MyDatabaseContext.cs and add the following using statements to the top of the file:

using System.Data.SqlClient;
using Microsoft.Azure.Services.AppAuthentication;
using System.Web.Configuration;

MyDatabaseContext 类中,添加以下构造函数:In the MyDatabaseContext class, add the following constructor:

public MyDatabaseContext(SqlConnection conn) : base(conn, true)
{
    conn.ConnectionString = WebConfigurationManager.ConnectionStrings["MyDbConnection"].ConnectionString;
    // DataSource != LocalDB means app is running in Azure with the SQLDB connection string you configured
    if(conn.DataSource != "(localdb)\\MSSQLLocalDB")
        conn.AccessToken = (new AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.chinacloudapi.cn/").Result;

    Database.SetInitializer<MyDatabaseContext>(null);
}

此构造函数将自定义 SqlConnection 对象配置为使用应用服务提供的 Azure SQL 数据库的访问令牌。This constructor configures a custom SqlConnection object to use an access token for Azure SQL Database from App Service. 有了访问令牌,应用服务应用就可以使用其托管标识通过 Azure SQL 数据库进行身份验证。With the access token, your App Service app authenticates with Azure SQL Database with its managed identity. 有关详细信息,请参阅获取 Azure 资源的令牌For more information, see Obtaining tokens for Azure resources. 可以使用 if 语句,通过 LocalDB 继续在本地测试应用。The if statement lets you continue to test your app locally with LocalDB.

Note

SqlConnection.AccessToken 目前仅在 .NET Framework 4.6 及更高版本中受支持,以及在 .NET Core 2.2 中受支持,但在 .NET Core 2.1 中不受支持。SqlConnection.AccessToken is currently supported only in .NET Framework 4.6 and above, as well as .NET Core 2.2, not in .NET Core 2.1.

若要使用这个新的构造函数,请打开 Controllers\TodosController.cs 并找到 private MyDatabaseContext db = new MyDatabaseContext(); 行。To use this new constructor, open Controllers\TodosController.cs and find the line private MyDatabaseContext db = new MyDatabaseContext();. 现有的代码使用默认的 MyDatabaseContext 控制器通过标准的连接字符串来创建数据库,该字符串在未经更改的情况下以明文形式保存用户名和密码。The existing code uses the default MyDatabaseContext controller to create a database using the standard connection string, which had username and password in clear text before you changed it.

请将整行替换为以下代码:Replace the entire line with the following code:

private MyDatabaseContext db = new MyDatabaseContext(new System.Data.SqlClient.SqlConnection());

发布更改Publish your changes

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

在“解决方案资源管理器”中,右键单击 “DotNetAppSqlDb”项目,并选择“发布”。In the Solution Explorer, right-click your DotNetAppSqlDb project and select Publish.

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

在发布页中单击“发布”。In the publish page, click Publish. 当新网页显示待办事项列表时,表明应用使用了托管标识连接到数据库。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

向标识授予最低特权Grant minimal privileges to identity

在此前的步骤中,你可能已注意到托管标识是以 Azure AD 管理员身份连接到 SQL Server。During the earlier steps, you probably noticed your managed identity is connected to SQL Server as the Azure AD administrator. 为了向托管标识授予最低特权,需以 Azure AD 管理员身份登录到 Azure SQL 数据库服务器,然后添加包含托管标识的 Azure Active Directory 组。To grant minimal privileges to your managed identity, you need to sign in to the Azure SQL Database server as the Azure AD administrator, and then add an Azure Active Directory group that contains the managed identity.

向 Azure Active Directory 组添加托管标识Add managed identity to an Azure Active Directory group

在 CLI 中,将应用的托管标识添加到名为 myAzureSQLDBAccessGroup 的新 Azure Active Directory 组,如以下脚本所示:In the CLI, add the managed identity for your app into a new Azure Active Directory group called myAzureSQLDBAccessGroup, shown in the following script:

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

若要查看每个命令的完整 JSON 输出,请删除参数 --query objectId --output tsvIf you want to see the full JSON output for each command, drop the parameters --query objectId --output tsv.

重新配置 Azure AD 管理员Reconfigure Azure AD administrator

你此前已经以 Azure AD 管理员身份为 SQL 数据库分配托管标识。Previously, you assigned the managed identity as the Azure AD administrator for your SQL Database. 不能使用此标识进行交互式登录(以添加数据库用户),因此需使用实际的 Azure AD 用户。You can't use this identity for interactive sign-in (to add database users), so you need to use your real Azure AD user. 若要添加 Azure AD 用户,请执行为 Azure SQL 数据库服务器预配 Azure Active Directory 管理员中的步骤。To add your Azure AD user, follow the steps at Provision an Azure Active Directory administrator for your Azure SQL Database Server.

Important

添加之后,除非你想完全禁用 Azure AD 对 SQL 数据库的访问(从所有 Azure AD 帐户),否则不要删除 SQL 数据库的 Azure AD 管理员。Once added, don't remove this Azure AD administrator for your SQL Database unless you want to disable Azure AD access to the SQL Database completely (from all Azure AD accounts).

向 Azure Active Directory 组授予权限Grant permissions to Azure Active Directory group

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

sqlcmd -S <server_name>.database.windows.net -d <db_name> -U <AADuser_name> -P "<AADpassword>" -G -l 30

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

CREATE USER [myAzureSQLDBAccessGroup] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [myAzureSQLDBAccessGroup];
ALTER ROLE db_datawriter ADD MEMBER [myAzureSQLDBAccessGroup];
ALTER ROLE db_ddladmin ADD MEMBER [myAzureSQLDBAccessGroup];
GO

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

后续步骤Next steps

你已了解:What you learned:

  • 启用托管标识Enable managed identities
  • 授予 SQL 数据库访问托管标识的权限Grant SQL Database access to the managed identity
  • 配置应用程序代码,以便使用 Azure Active Directory 身份验证通过 SQL 数据库进行身份验证Configure application code to authenticate with SQL Database using Azure Active Directory authentication
  • 在 SQL 数据库中向托管标识授予最低特权Grant minimal privileges to the managed identity in SQL Database

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