本地同步Azure AD账户编程连接Azure SQL失败及IIS部署疑问

问题背景 #

尝试用已同步到Azure AD的本地AD账户连接Azure SQL：

• 通过SSMS以其他用户身份运行可成功连接，但编程方式连接失败
• 使用ActiveDirectoryIntegrated认证时，会覆盖指定凭据，用本地机器AD ID登录（因无权限失败）
• 疑问：是否无法通过编程方式传递自定义AD凭据认证？未找到微软文档说明此限制，当前正在做POC，需从SQL账户切换到AD账户认证
• 额外疑问：应用将部署在IIS上，是否可使用runas？有哪些挑战？

报错信息 #

Login failed for user 'DEVUser'

尝试的代码示例 #

方式1：使用SqlConnectionStringBuilder指定ActiveDirectoryPassword认证 #

public static void testrun()
{
    DataTable dt = new DataTable();
    try
    {
        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
        builder.DataSource = "AZURESQLDEV";
        builder.InitialCatalog = "DataBaseName";
        builder.Authentication = SqlAuthenticationMethod.ActiveDirectoryPassword;
        builder.UserID = "DEVUser";
        builder.Password = "DEVPass";
        builder.TrustServerCertificate = true;
        builder.PersistSecurityInfo = false;
        //ServicePointManager.ServerCertificateValidationCallback = ValidateServerCertificate;
        using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
        {
            connection.Open();
            string strQuery = "SELECT * FROM [FRU].[FRU_BU_PLANT_PRECEDENCE_INFO]";
            SqlCommand command = new SqlCommand(strQuery, connection);
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(dt);
        }

    }
    catch (Exception ex)
    { 

    }
}

方式2：先显式认证AD用户再连接SQL #

string username = "DEVUser";
string password = "DEVPass";
try
{
    if (AuthenticateUser(username, password))
    {
        string connectionString = "Data Source=DCA-DEV-123;Initial Catalog=DBATOOLS;User ID=" + username + ";Password=" + password;

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            // Perform SQL operations using the connection

            connection.Close();
        }
    }
    else
    {
        Console.WriteLine("Invalid username or password.");
    }
}
catch (Exception ex)
{
    throw;
}
private static bool AuthenticateUser(string username, string password)
{
    using (PrincipalContext context = new PrincipalContext(ContextType.Domain))
    {
        return context.ValidateCredentials(username, password);
    }
} 

堆栈跟踪 #

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at TesSP.Program.Main(String[] args) in C:\TesSP\Program.cs:line 36

解决方案与说明 #

1. 编程方式传递自定义AD凭据的正确方法 #

使用ActiveDirectoryPassword认证的思路可行，但需修正几个关键点：

• 用户ID必须用完整UPN：同步到Azure AD的本地AD账户，Azure SQL仅识别完整的用户主体名称（比如DEVUser@yourdomain.com），而非单纯的用户名DEVUser，这是最常见的失败原因。
• 确认Azure SQL配置：确保Azure SQL服务器已启用Azure AD身份验证，且该同步账户已被添加为SQL登录用户/数据库用户，并分配了对应权限。
• 升级SQL客户端库：旧版System.Data.SqlClient对Azure AD密码认证支持有限，建议切换到Microsoft.Data.SqlClient，兼容性更好。

修改后的代码示例（使用Microsoft.Data.SqlClient）：

using Microsoft.Data.SqlClient;

public static void testrun()
{
    DataTable dt = new DataTable();
    try
    {
        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
        builder.DataSource = "AZURESQLDEV.database.windows.net"; // 使用完整Azure SQL服务器FQDN
        builder.InitialCatalog = "DataBaseName";
        builder.Authentication = SqlAuthenticationMethod.ActiveDirectoryPassword;
        builder.UserID = "DEVUser@yourdomain.com"; // 完整UPN
        builder.Password = "DEVPass";
        builder.TrustServerCertificate = true;

        using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
        {
            connection.Open();
            string strQuery = "SELECT * FROM [FRU].[FRU_BU_PLANT_PRECEDENCE_INFO]";
            using (SqlCommand command = new SqlCommand(strQuery, connection))
            using (SqlDataAdapter adapter = new SqlDataAdapter(command))
            {
                adapter.Fill(dt);
            }
        }
    }
    catch (Exception ex)
    {
        // 添加错误日志便于排查
        Console.WriteLine(ex.ToString());
    }
}

2. IIS部署时使用runas的可行性与挑战 #

可以通过runas让IIS应用池以指定AD账户身份运行，进而用ActiveDirectoryIntegrated认证连接Azure SQL，但需应对以下挑战：

• 权限配置：应用池身份需加入本地IIS_IUSRS组，且需在本地安全策略中分配“作为服务登录”权限；同时该AD账户需同步到Azure AD并拥有SQL权限。
• 密码管理：AD账户密码过期会导致应用池崩溃，需手动更新密码，增加运维成本。
• 安全性：应用池配置中存储AD密码存在泄露风险，建议用Azure Key Vault或本地凭据管理器存储，配合自动化更新。
• Kerberos约束：应用服务器与域控制器的Kerberos配置错误会导致认证失败，需确保SPN配置正确。

3. 常见排查步骤 #

• 确认AD账户在Azure AD中同步状态正常（可通过Azure AD门户查看）。
• 用该账户的UPN通过SSMS登录Azure SQL，验证权限是否正常。
• 检查应用服务器能否访问Azure SQL的1433端口，以及Azure AD认证端点。
• 启用SQL客户端日志，查看更详细的认证失败原因。

内容的提问来源于stack exchange，提问作者Ishan