使用SQLProvider与Membership的ASP.NET应用切换Azure托管标识后数据库连接失败

异常信息 #

异常类型: SqlException
异常消息: 用户''登录失败。
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, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate)
at System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation)
at System.Web.Security.SqlRoleProvider.GetRolesForUser(String username)
at System.Web.Security.RolePrincipal.IsInRole(String role)
at System.Web.Configuration.AuthorizationRule.IsTheUserInAnyRole(StringCollection roles, IPrincipal principal)
at System.Web.Configuration.AuthorizationRule.IsUserAllowed(IPrincipal user, String verb)
at System.Web.Configuration.AuthorizationRuleCollection.IsUserAllowed(IPrincipal user, String verb)
at System.Web.Security.UrlAuthorizationModule.OnEnter(Object source, EventArgs eventArgs)
at System.Web.HttpApplication.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step)
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

应用web.config配置 #

<system.web>
  <roleManager enabled="true" cacheRolesInCookie="true" 
               defaultProvider="CustomizedRoleProvider" cookieTimeout="60">
    <providers>
      <clear />
      <add connectionStringName="ProjectConnection" 
           type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, 
                 Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" applicationName="/" 
           name="CustomizedRoleProvider" />
    </providers>
  </roleManager>
  <membership defaultProvider="CustomizedProvider">
    <providers>
      <remove name="AspNetSqlMembershipProvider" />
      <add connectionStringName="ProjectConnection" enablePasswordRetrieval="false" 
           enablePasswordReset="true" requiresQuestionAndAnswer="false" applicationName="/" 
           requiresUniqueEmail="true" passwordFormat="Hashed" 
           maxInvalidPasswordAttempts="3" minRequiredNonalphanumericCharacters="1" 
           passwordAttemptWindow="3" minRequiredPasswordLength="8" 
           passwordStrengthRegularExpression="^(?=.*\d)(?=.*[a-z])(?=.*[A-Z]).{8,16}$" 
           name="CustomizedProvider" type="System.Web.Security.SqlMembershipProvider, 
                 System.Web, Version=2.0.0.0,   Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
    </providers>
  </membership>
  <authentication mode="Forms">
    <forms slidingExpiration="true" loginUrl="Login/Login" defaultUrl="~/" 
           timeout="10800" requireSSL="false" protection="All" />
  </authentication>
  <authorization>
    <deny users="?" />
    <allow roles="Admin" />
  </authorization>
  <customErrors mode="Off" />
</system.web>
<connectionStrings>
  <add name="ProjectConnection" 
       connectionString="Server=tcp:xx.database.windows.net,1433;Database=xx;" />
</connectionStrings>

问题分析 #

空用户登录失败的核心原因是：原生SqlRoleProvider和SqlMembershipProvider不支持自动使用Azure托管标识认证，这类旧组件依赖传统SQL身份验证逻辑，不会主动获取托管标识的访问令牌并附加到数据库连接上；同时当前连接字符串未指定认证方式，导致客户端尝试匿名登录，最终失败。

解决方案 #

1. 更新连接字符串，指定托管标识认证 #

在ProjectConnection的连接字符串中添加Authentication=Active Directory Managed Identity参数，明确告知SQL客户端使用托管标识认证：

<add name="ProjectConnection" 
     connectionString="Server=tcp:xx.database.windows.net,1433;Database=xx;Authentication=Active Directory Managed Identity;" />

2. 升级SQL客户端库 #

旧版System.Data.SqlClient对Azure托管标识的支持有限，建议升级为Microsoft.Data.SqlClient（版本≥2.0），该库原生支持多种Azure AD认证方式，包括托管标识。

3. 自定义Membership/Role Provider #

原生SqlMembershipProvider和SqlRoleProvider无法自动处理托管标识的令牌获取，需要自定义扩展类，在获取数据库连接时手动注入访问令牌：

自定义RoleProvider示例

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

public class ManagedIdentitySqlRoleProvider : SqlRoleProvider
{
    protected override SqlConnection GetConnection()
    {
        var connection = base.GetConnection() as SqlConnection;
        if (connection == null) return base.GetConnection();
        
        // 获取Azure SQL的访问令牌
        var tokenProvider = new AzureServiceTokenProvider();
        string accessToken = tokenProvider.GetAccessTokenAsync("https://database.windows.net/").Result;
        connection.AccessToken = accessToken;
        
        return connection;
    }
}

自定义MembershipProvider示例

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

public class ManagedIdentitySqlMembershipProvider : SqlMembershipProvider
{
    protected override SqlConnection GetConnection()
    {
        var connection = base.GetConnection() as SqlConnection;
        if (connection == null) return base.GetConnection();
        
        var tokenProvider = new AzureServiceTokenProvider();
        string accessToken = tokenProvider.GetAccessTokenAsync("https://database.windows.net/").Result;
        connection.AccessToken = accessToken;
        
        return connection;
    }
}

更新web.config中的Provider类型

将自定义Provider的类型配置到web.config中，替换原生的类型：

<!-- RoleProvider配置 -->
<add connectionStringName="ProjectConnection" 
     type="YourNamespace.ManagedIdentitySqlRoleProvider, YourAssemblyName" 
     applicationName="/" 
     name="CustomizedRoleProvider" />

<!-- MembershipProvider配置 -->
<add connectionStringName="ProjectConnection" enablePasswordRetrieval="false" 
     enablePasswordReset="true" requiresQuestionAndAnswer="false" applicationName="/" 
     requiresUniqueEmail="true" passwordFormat="Hashed" 
     maxInvalidPasswordAttempts="3" minRequiredNonalphanumericCharacters="1" 
     passwordAttemptWindow="3" minRequiredPasswordLength="8" 
     passwordStrengthRegularExpression="^(?=.*\d)(?=.*[a-z])(?=.*[A-Z]).{8,16}$" 
     name="CustomizedProvider" type="YourNamespace.ManagedIdentitySqlMembershipProvider, YourAssemblyName" />

4. 配置托管标识的数据库权限 #

在Azure SQL数据库中，为托管标识创建数据库用户，并赋予必要的权限（如读取/修改会员、角色表的权限）：

-- 创建托管标识用户
CREATE USER [你的托管标识名称] FROM EXTERNAL PROVIDER;

-- 赋予db_owner权限（根据实际需求调整）
ALTER ROLE db_owner ADD MEMBER [你的托管标识名称];

5. 验证应用环境的托管标识配置 #

如果应用部署在Azure App Service：

• 确认已开启系统分配或用户分配的托管标识；
• 确认该托管标识已被添加到Azure SQL数据库的访问控制列表中，拥有访问权限。

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