使用 Azure 多重身份验证连接到 Azure SQL 数据库Connect to Azure SQL Database with Azure Multi-Factor Authentication

本文提供了用于连接到 Azure SQL 数据库的 C# 程序。This article provides a C# program that connects to Azure SQL Database. 该程序使用支持 Azure 多重身份验证的交互模式身份验证。The program uses interactive mode authentication, which supports Azure Multi-Factor Authentication.

有关 SQL 工具的多重身份验证支持的详细信息,请参阅 SQL Server Data Tools (SSDT) 中的 Azure Active Directory 支持For more information about Multi-Factor Authentication support for SQL tools, see Azure Active Directory support in SQL Server Data Tools (SSDT).

Azure SQL 数据库的多重身份验证Multi-Factor Authentication for Azure SQL Database

自 .NET Framework 版本 4.7.2 开始,枚举 SqlAuthenticationMethod 具有一个新值:ActiveDirectoryInteractiveStarting in .NET Framework version 4.7.2, the enum SqlAuthenticationMethod has a new value: ActiveDirectoryInteractive. 在客户端 C# 程序中,该枚举值指示系统使用支持多重身份验证的 Azure Active Directory (Azure AD) 交互模式连接到 Azure SQL 数据库。In a client C# program, the enum value directs the system to use the Azure Active Directory (Azure AD) interactive mode that supports Multi-Factor Authentication to connect to an Azure SQL database. 然后,运行该程序的用户将看到以下对话框:The user who runs the program sees the following dialog boxes:

  • 一个显示 Azure AD 用户名并要求输入用户密码的对话框。A dialog box that displays an Azure AD user name and asks for the user's password.

    如果用户域与 Azure AD 联合,则不会显示此对话框,因为不需要密码。If the user's domain is federated with Azure AD, this dialog box doesn't appear, because no password is needed.

    如果 Azure AD 策略对用户实施多重身份验证,则会显示接下来的两个对话框。If the Azure AD policy imposes Multi-Factor Authentication on the user, the next two dialog boxes are displayed.

  • 当用户首次执行多重身份验证时,系统会显示一个对话框,要求提供短信所要发送到的手机号码。The first time a user goes through Multi-Factor Authentication, the system displays a dialog box that asks for a mobile phone number to send text messages to. 每条短信提供了用户必须在下一对话框中输入的验证码。 Each message provides the verification code that the user must enter in the next dialog box.

  • 一个对话框,它要求提供系统已发送到手机的多重身份验证验证码。A dialog box that asks for a Multi-Factor Authentication verification code, which the system has sent to a mobile phone.

有关如何将 Azure AD 配置为要求多重身份验证的信息,请参阅云中的 Azure 多重身份验证入门For information about how to configure Azure AD to require Multi-Factor Authentication, see Getting started with Azure Multi-Factor Authentication in the cloud.

有关这些对话框的屏幕截图,请参阅为 SQL Server Management Studio 和 Azure AD 配置多重身份验证For screenshots of these dialog boxes, see Configure multi-factor authentication for SQL Server Management Studio and Azure AD.

Tip

可以使用 .NET API 浏览器工具页搜索 .NET Framework API。You can search .NET Framework APIs with the .NET API Browser tool page.

也可以使用可选的 ?term=<搜索值> 参数直接进行搜索。You can also search directly with the optional ?term=<search value> parameter.

在 Azure 门户中配置 C# 应用程序Configure your C# application in the Azure portal

在开始之前,Azure SQL 数据库服务器应已创建且可用。Before you begin, you should have an Azure SQL Database server created and available.

注册应用并设置权限Register your app and set permissions

若要使用 Azure AD 身份验证,必须将 C# 程序注册为 Azure AD 应用程序。To use Azure AD authentication, your C# program has to register as an Azure AD application. 只有 Azure AD 管理员或者分配有 Azure AD“应用程序开发人员”角色的用户才能注册应用。 To register an app, you need to be either an Azure AD admin or a user assigned the Azure AD Application Developer role. 有关分配角色的详细信息,请参阅使用 Azure Active Directory 将管理员和非管理员角色分配给用户For more information about assigning roles, see Assign administrator and non-administrator roles to users with Azure Active Directory.

完成应用注册后,会生成并显示一个应用程序 IDCompleting an app registration generates and displays an application ID. 程序中必须包含此 ID 才能建立连接。Your program has to include this ID to connect.

注册应用程序并为其设置所需的权限:To register and set necessary permissions for your application:

  1. 在 Azure 门户中,依次选择“Azure Active Directory” > “应用注册” > “新建注册”。 In the Azure portal, select Azure Active Directory > App registrations > New registration.

    应用注册

    创建应用注册后,将生成并显示“应用程序 ID”值。 After the app registration is created, the application ID value is generated and displayed.

    显示的应用 ID

  2. 选择“API 权限” > “添加权限”。 Select API permissions > Add a permission.

    已注册应用的权限设置

  3. 选择“我的组织使用的 API” ,在搜索中键入“Azure SQL 数据库” ,并选择“Azure SQL 数据库” 。Select APIs my organization uses > type Azure SQL Database into the search > and select Azure SQL Database.

    为 Azure SQL 数据库添加对 API 的访问

  4. 选择“委托的权限” > “user_impersonation” > “添加权限”。 Select Delegated permissions > user_impersonation > Add permissions.

    为 Azure SQL 数据库委托对 API 的权限

为 SQL 数据库服务器设置 Azure AD 管理员Set an Azure AD admin for your SQL Database server

要使 C# 程序正常运行,Azure SQL 服务器管理员需要为 SQL 数据库服务器分配 Azure AD 管理员。For your C# program to run, an Azure SQL server admin needs to assign an Azure AD admin for your SQL Database server.

在“SQL Server” 页上,选择“Active Directory 管理员” > “设置管理员”。 On the SQL Server page, select Active Directory admin > Set admin.

有关 Azure SQL 数据库 Azure AD 管理员和用户的详细信息,请参阅通过 SQL 数据库配置和管理 Azure Active Directory 身份验证部分中的屏幕截图。For more information about Azure AD admins and users for Azure SQL Database, see the screenshots in Configure and manage Azure Active Directory authentication with SQL Database.

将非管理员用户添加到特定的数据库(可选)Add a non-admin user to a specific database (optional)

SQL 数据库服务器的 Azure AD 管理员可以运行 C# 示例程序。An Azure AD admin for a SQL Database server can run the C# example program. 数据库中的 Azure AD 用户可以运行该程序。An Azure AD user can run the program if they are in the database. 已在数据库中并对该数据库拥有 ALTER ANY USER 权限的 Azure AD SQL 管理员或 Azure AD 用户可以添加用户。An Azure AD SQL admin or an Azure AD user who exists already in the database and has the ALTER ANY USER permission on the database can add a user.

可以使用 SQL Create User 命令将用户添加到数据库。You can add a user to the database with the SQL Create User command. 例如 CREATE USER [<username>] FROM EXTERNAL PROVIDERAn example is CREATE USER [<username>] FROM EXTERNAL PROVIDER.

有关详细信息,请参阅将 Azure Active Directory 身份验证与 SQL 数据库、托管实例或 SQL 数据仓库结合使用For more information, see Use Azure Active Directory Authentication for authentication with SQL Database, Managed Instance, or SQL Data Warehouse.

新的身份验证枚举值New authentication enum value

该 C# 示例依赖于 System.Data.SqlClient 命名空间。The C# example relies on the System.Data.SqlClient namespace. 枚举 SqlAuthenticationMethod 对于多重身份验证而言特别重要,其值如下:Of special interest for Multi-Factor Authentication is the enum SqlAuthenticationMethod, which has the following values:

  • SqlAuthenticationMethod.ActiveDirectoryInteractive

    将此值与 Azure AD 用户名一起使用,以实现多重身份验证。Use this value with an Azure AD user name to implement Multi-Factor Authentication. 此值是本文的重点。This value is the focus of the present article. 它通过显示用户密码的对话框,接着显示多重身份验证的对话框(如果多重身份验证应用于此用户)来生成交互式体验。It produces an interactive experience by displaying dialog boxes for the user password, and then for Multi-Factor Authentication validation if Multi-Factor Authentication is imposed on this user. 此值自 .NET Framework 版本 4.7.2 起提供。This value is available starting with .NET Framework version 4.7.2.

  • SqlAuthenticationMethod.ActiveDirectoryIntegrated

    对联合帐户使用此值 。Use this value for a federated account. 对于联合帐户,Windows 域已知用户名。For a federated account, the user name is known to the Windows domain. 此身份验证方法不支持多重身份验证。This authentication method doesn't support Multi-Factor Authentication.

  • SqlAuthenticationMethod.ActiveDirectoryPassword

    使用此值进行需要 Azure AD 用户名和密码的身份验证。Use this value for authentication that requires an Azure AD user name and password. Azure SQL 数据库执行身份验证。Azure SQL Database does the authentication. 此方法不支持多重身份验证。This method doesn't support Multi-Factor Authentication.

在 Azure 门户中设置 C# 参数值Set C# parameter values from the Azure portal

要使 C# 程序成功运行,需将适当的值分配到静态字段。For the C# program to successfully run, you need to assign proper values to static fields. 此处显示了包含示例值的字段。Shown here are fields with example values. 此外,还显示了 Azure 门户中可以获取所需值的位置。Also shown are the Azure portal locations where you can obtain the needed values.

静态字段名称Static field name 示例值Example value Azure 门户中的位置Where in Azure portal
Az_SQLDB_svrNameAz_SQLDB_svrName "my-sqldb-svr.database.chinacloudapi.cn""my-sqldb-svr.database.chinacloudapi.cn" “SQL 服务器” > “按名称筛选” SQL servers > Filter by name
AzureAD_UserIDAzureAD_UserID "auser@abc.partner.onmschina.cn""auser@abc.partner.onmschina.cn" “Azure Active Directory” > “用户” > “新建来宾用户” Azure Active Directory > User > New guest user
Initial_DatabaseNameInitial_DatabaseName "myDatabase""myDatabase" “SQL 服务器” > “SQL 数据库” SQL servers > SQL databases
ClientApplicationIDClientApplicationID “a94f9c62-97fe-4d19-b06d-111111111111”"a94f9c62-97fe-4d19-b06d-111111111111" “Azure Active Directory” > “应用注册” > “按名称搜索” > “应用程序 ID” Azure Active Directory > App registrations > Search by name > Application ID
RedirectUriRedirectUri new Uri("https://mywebserver.com/")new Uri("https://mywebserver.com/") “Azure Active Directory” > “应用注册” > “按名称搜索” > “[你的应用注册]” > “设置” > “RedirectURI” Azure Active Directory > App registrations > Search by name > [Your-App-registration] > Settings > RedirectURIs

在本文中,任何有效值均适用于 RedirectUri,因为此处并不使用 RedirectUri。For this article, any valid value is fine for RedirectUri, because it isn't used here.
     

使用 SQL Server Management Studio 进行验证Verify with SQL Server Management Studio

在运行 C# 程序之前,最好在 SQL Server Management Studio (SSMS) 中检查设置和配置是否正确。Before you run the C# program, it's a good idea to check that your setup and configurations are correct in SQL Server Management Studio (SSMS). 然后,可将任何 C# 程序错误范围缩小到源代码级别。Any C# program failure can then be narrowed to source code.

验证 SQL 数据库防火墙 IP 地址Verify SQL Database firewall IP addresses

从同一建筑物中你要运行 C# 程序的同一台计算机运行 SSMS。Run SSMS from the same computer, in the same building, where you plan to run the C# program. 可以使用任何身份验证模式来运行此测试。For this test, any Authentication mode is OK. 如果有任何迹象表明数据库服务器防火墙不接受 IP 地址,请参阅 Azure SQL 数据库服务器级和数据库级防火墙规则获得帮助。If there's any indication that the database server firewall isn't accepting your IP address, see Azure SQL Database server-level and database-level firewall rules for help.

验证 Azure Active Directory 多重身份验证Verify Azure Active Directory Multi-Factor Authentication

再次运行 SSMS,这一次将“身份验证”设为“具有 MFA 支持的 Active Directory - 通用” 。Run SSMS again, this time with Authentication set to Active Directory - Universal with MFA support. 此选项需要 SSMS 17.5 或更高版本。This option requires SSMS version 17.5 or later.

有关详细信息,请参阅为 SSMS 和 Azure AD 配置多重身份验证For more information, see Configure Multi-Factor Authentication for SSMS and Azure AD.

Note

如果你是数据库中的来宾用户,则还需要提供数据库的 Azure AD 域名:选择“选项” > “AD 域名或租户 ID”。 If you are a guest user in the database, you also need to provide the Azure AD domain name for the database: Select Options > AD domain name or tenant ID. 若要在 Azure 门户中查找域名,请选择“Azure Active Directory” > “自定义域名”。 To find the domain name in the Azure portal, select Azure Active Directory > Custom domain names. 在 C# 示例程序中,不必要提供域名。In the C# example program, providing a domain name is not necessary.

C# 代码示例C# code example

示例 C# 程序依赖于 Microsoft.IdentityModel.Clients.ActiveDirectory DLL 程序集。The example C# program relies on the Microsoft.IdentityModel.Clients.ActiveDirectory DLL assembly.

若要安装此包,请在 Visual Studio 中选择“项目” > “管理 NuGet 包”。 To install this package, in Visual Studio, select Project > Manage NuGet Packages. 搜索并安装 Microsoft.IdentityModel.Clients.ActiveDirectorySearch for and install Microsoft.IdentityModel.Clients.ActiveDirectory.

这是 C# 源代码的示例。This is an example of C# source code.


using System;

// Reference to Azure AD authentication assembly
using Microsoft.IdentityModel.Clients.ActiveDirectory;

using DA = System.Data;
using SC = System.Data.SqlClient;
using AD = Microsoft.IdentityModel.Clients.ActiveDirectory;
using TX = System.Text;
using TT = System.Threading.Tasks;

namespace ADInteractive5
{
    class Program
    {
        // ASSIGN YOUR VALUES TO THESE STATIC FIELDS !!
        static public string Az_SQLDB_svrName = "<Your SQL DB server>";
        static public string AzureAD_UserID = "<Your User ID>";
        static public string Initial_DatabaseName = "<Your Database>";
        // Some scenarios do not need values for the following two fields:
        static public readonly string ClientApplicationID = "<Your App ID>";
        static public readonly Uri RedirectUri = new Uri("<Your URI>");

        public static void Main(string[] args)
        {
            var provider = new ActiveDirectoryAuthProvider();

            SC.SqlAuthenticationProvider.SetProvider(
                SC.SqlAuthenticationMethod.ActiveDirectoryInteractive,
                //SC.SqlAuthenticationMethod.ActiveDirectoryIntegrated,  // Alternatives.
                //SC.SqlAuthenticationMethod.ActiveDirectoryPassword,
                provider);

            Program.Connection();
        }

        public static void Connection()
        {
            SC.SqlConnectionStringBuilder builder = new SC.SqlConnectionStringBuilder();

            // Program._  static values that you set earlier.
            builder["Data Source"] = Program.Az_SQLDB_svrName;
            builder.UserID = Program.AzureAD_UserID;
            builder["Initial Catalog"] = Program.Initial_DatabaseName;

            // This "Password" is not used with .ActiveDirectoryInteractive.
            //builder["Password"] = "<YOUR PASSWORD HERE>";

            builder["Connect Timeout"] = 15;
            builder["TrustServerCertificate"] = true;
            builder.Pooling = false;

            // Assigned enum value must match the enum given to .SetProvider().
            builder.Authentication = SC.SqlAuthenticationMethod.ActiveDirectoryInteractive;
            SC.SqlConnection sqlConnection = new SC.SqlConnection(builder.ConnectionString);

            SC.SqlCommand cmd = new SC.SqlCommand(
                "SELECT '******** MY QUERY RAN SUCCESSFULLY!! ********';",
                sqlConnection);

            try
            {
                sqlConnection.Open();
                if (sqlConnection.State == DA.ConnectionState.Open)
                {
                    var rdr = cmd.ExecuteReader();
                    var msg = new TX.StringBuilder();
                    while (rdr.Read())
                    {
                        msg.AppendLine(rdr.GetString(0));
                    }
                    Console.WriteLine(msg.ToString());
                    Console.WriteLine(":Success");
                }
                else
                {
                    Console.WriteLine(":Failed");
                }
                sqlConnection.Close();
            }
            catch (Exception ex)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine("Connection failed with the following exception...");
                Console.WriteLine(ex.ToString());
                Console.ResetColor();
            }
        }
    } // EOClass Program.

    /// <summary>
    /// SqlAuthenticationProvider - Is a public class that defines 3 different Azure AD
    /// authentication methods.  The methods are supported in the new .NET 4.7.2.
    ///  . 
    /// 1. Interactive,  2. Integrated,  3. Password
    ///  . 
    /// All 3 authentication methods are based on the Azure
    /// Active Directory Authentication Library (ADAL) managed library.
    /// </summary>
    public class ActiveDirectoryAuthProvider : SC.SqlAuthenticationProvider
    {
        // Program._ more static values that you set!
        private readonly string _clientId = Program.ClientApplicationID;
        private readonly Uri _redirectUri = Program.RedirectUri;

        public override async TT.Task<SC.SqlAuthenticationToken>
            AcquireTokenAsync(SC.SqlAuthenticationParameters parameters)
        {
            AD.AuthenticationContext authContext =
                new AD.AuthenticationContext(parameters.Authority);
            authContext.CorrelationId = parameters.ConnectionId;
            AD.AuthenticationResult result;

            switch (parameters.AuthenticationMethod)
            {
                case SC.SqlAuthenticationMethod.ActiveDirectoryInteractive:
                    Console.WriteLine("In method 'AcquireTokenAsync', case_0 == '.ActiveDirectoryInteractive'.");

                    result = await authContext.AcquireTokenAsync(
                        parameters.Resource,  // "https://database.chinacloudapi.cn/"
                        _clientId,
                        _redirectUri,
                        new AD.PlatformParameters(AD.PromptBehavior.Auto),
                        new AD.UserIdentifier(
                            parameters.UserId,
                            AD.UserIdentifierType.RequiredDisplayableId));
                    break;

                case SC.SqlAuthenticationMethod.ActiveDirectoryIntegrated:
                    Console.WriteLine("In method 'AcquireTokenAsync', case_1 == '.ActiveDirectoryIntegrated'.");

                    result = await authContext.AcquireTokenAsync(
                        parameters.Resource,
                        _clientId,
                        new AD.UserCredential());
                    break;

                case SC.SqlAuthenticationMethod.ActiveDirectoryPassword:
                    Console.WriteLine("In method 'AcquireTokenAsync', case_2 == '.ActiveDirectoryPassword'.");

                    result = await authContext.AcquireTokenAsync(
                        parameters.Resource,
                        _clientId,
                        new AD.UserPasswordCredential(
                            parameters.UserId,
                            parameters.Password));
                    break;

                default: throw new InvalidOperationException();
            }
            return new SC.SqlAuthenticationToken(result.AccessToken, result.ExpiresOn);
        }

        public override bool IsSupported(SC.SqlAuthenticationMethod authenticationMethod)
        {
            return authenticationMethod == SC.SqlAuthenticationMethod.ActiveDirectoryIntegrated
                || authenticationMethod == SC.SqlAuthenticationMethod.ActiveDirectoryInteractive
                || authenticationMethod == SC.SqlAuthenticationMethod.ActiveDirectoryPassword;
        }
    } // EOClass ActiveDirectoryAuthProvider.
} // EONamespace.  End of entire program source code.

 

这是 C# 测试输出的示例。This is an example of the C# test output.

[C:\Test\VSProj\ADInteractive5\ADInteractive5\bin\Debug\]
>> ADInteractive5.exe
In method 'AcquireTokenAsync', case_0 == '.ActiveDirectoryInteractive'.
******** MY QUERY RAN SUCCESSFULLY!! ********

:Success

[C:\Test\VSProj\ADInteractive5\ADInteractive5\bin\Debug\]
>>

后续步骤Next steps

Note

本文进行了更新,以便使用新的 Azure PowerShell Az 模块。This article has been updated to use the new Azure PowerShell Az module. 你仍然可以使用 AzureRM 模块,至少在 2020 年 12 月之前,它将继续接收 bug 修补程序。You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. 若要详细了解新的 Az 模块和 AzureRM 兼容性,请参阅新 Azure Powershell Az 模块简介To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. 有关 Az 模块安装说明,请参阅安装 Azure PowerShellFor Az module installation instructions, see Install Azure PowerShell.

Important

PowerShell Azure 资源管理器模块仍受 Azure SQL 数据库的支持,但所有未来的开发都是针对 Az.Sql 模块的。The PowerShell Azure Resource Manager module is still supported by Azure SQL Database, but all future development is for the Az.Sql module. 若要了解这些 cmdlet,请参阅 AzureRM.SqlFor these cmdlets, see AzureRM.Sql. Az 模块和 AzureRm 模块中的命令参数大体上是相同的。The arguments for the commands in the Az module and in the AzureRm modules are substantially identical.