如何使用Active Directory登录和MultiFactor身份验证(MFA)连接到数据库 [英] How to connect to a database using Active directory Login and MultiFactor Authentication (MFA)
问题描述
我已经配置了Azure SQL Server，因此我是服务器管理员，我的帐户也启用了MFA.我试图遵循
I have already configured my Azure SQL Server so that I am Server admin, my account also has MFA enabled. I was trying to follow this documentation but it doesn't mention anything about Active directory with MFA.
我可以使用我的帐户和MFA通过SQL Management Studio正常登录服务器
I can use my account and MFA to sign into the server fine using SQL Management studio
我最初尝试过(基于新的 SqlAuthenticationMethod枚举):
Initially I tried (based on the new SqlAuthenticationMethod Enum):
SqlConnection con = new SqlConnection("Server=tcp:myapp.database.windows.net;Database=CustomerDB;Authentication=Active Directory Interactive;Encrypt=True;UID=User@User.co.uk"))
错误:
'找不到用于的身份验证提供程序 "ActiveDirectoryInteractive".
'Cannot find an authentication provider for 'ActiveDirectoryInteractive'.'
然后我看到了关于通过Azure应用程序访问SQL的信息，但这不是我想要做的.
I then saw this about accessing SQL via an Azure application But this is not what I want to do.
此 SO问题讨论在没有提供程序的情况下进行连接，并在连接字符串中设置Driver
This SO question talks about connecting without the provider and setting the Driver in the connection string
SqlConnection con = new SqlConnection("DRIVER={ODBC Driver 17 for SQL Server};Server=tcp:myapp.database.windows.net;Database=CustomerDB;Authentication=Active Directory Interactive;Encrypt=True;UID=User@User.co.uk"))
但是我得到了错误:
不支持关键字:'驱动程序'."
'Keyword not supported: 'driver'.'
总有没有写一个连接字符串，以便在尝试连接时弹出Microsoft身份验证框，以引导用户进行多因素身份验证?
Is there anyway to write a connection string so that when it tries to connect the Microsoft authentication box pops up to walk the user through Multi factor authentication?
推荐答案
要使用Azure AD身份验证，您的C#程序必须注册为Azure AD应用程序.完成应用程序注册会生成并显示应用程序ID.您的程序必须包含此ID才能进行连接.要为您的应用程序注册并设置必要的权限，请转到Azure门户，选择"Azure Active Directory">应用程序注册">新注册".
To use Azure AD authentication, your C# program has to register as an Azure AD application. Completing an app registration generates and displays an application ID. Your program has to include this ID to connect. To register and set necessary permissions for your application, go to 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.
选择API权限>添加权限.
Select API permissions > Add a permission.
选择我的组织使用的API>在搜索中键入Azure SQL数据库>并选择Azure SQL数据库.
Select APIs my organization uses > type Azure SQL Database into the search > and select Azure SQL Database.
选择委派权限> user_impersonation>添加权限.
Select Delegated permissions > user_impersonation > Add permissions.
似乎您已经为Azure SQL数据库设置了Azure AD管理员.
It seems you have already set an Azure AD admin for your Azure SQL Database.
您还可以使用SQL Create User命令将用户添加到数据库中.例如，从外部提供者创建用户[].有关更多信息，请参见此处
You can also add a user to the database with the SQL Create User command. An example is CREATE USER [] FROM EXTERNAL PROVIDER. For more information, see here.
下面是有关C#的示例.
Below an example on C#.
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.windows.net/"
_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.
上面的示例依赖于Microsoft.IdentityModel.Clients.ActiveDirectory DLL程序集.
要安装此软件包，请在Visual Studio中选择项目">管理NuGet软件包".搜索并安装Microsoft.IdentityModel.Clients.ActiveDirectory.
To install this package, in Visual Studio, select Project > Manage NuGet Packages. Search for and install Microsoft.IdentityModel.Clients.ActiveDirectory.
从.NET Framework 4.7.2版开始，枚举SqlAuthenticationMethod具有一个新值:ActiveDirectoryInteractive.
Starting in .NET Framework version 4.7.2, the enum SqlAuthenticationMethod has a new value: ActiveDirectoryInteractive.
这篇关于如何使用Active Directory登录和MultiFactor身份验证(MFA)连接到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
