SQL Server ASP.Net Application which uses SQLProvider and MemberShip Fails to connect to DB when using Azure Managed Identity

When migrating from sql server authentication to Azure Managed Identity we are facing the below login issue
Exception information: Exception type: SqlException Exception message: Login failed for user ''. 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.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource 1 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)

Below is the 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>

I referred this MS Document and created one Asp.Net app connected to Azure SQL and it loaded successfully after Deployment like below:-

Now in order to successfully enable managed Identity, I referred this MS Document2 to connect my Azure Web app managed Identity with Azure SQL like below:-

I have added one User as an Admin in my Azure SQL server like below:-

And then I visited my Azure Web app > Environment Variables > Connection String and added below ConnectionString to MyDbConnection:-

"Server=tcp:sqlservername.database.windows.net;Authentication=Active Directory Default; Database=dotnetsiliconsqldb;"

You can update this connection string in your local web.config file or your appsettings.json file in your asp.net code.

Now, Enable Azure Web App managed identity and assign it the required role to Azure SQL like below:-

Connect to your Azure SQL Server with your Azure AD credentials and run the below command to add the Web app managed identity roles to access the Azure SQL.

The name of Web app managed Identity is same as the name of your Web app:-

SQL Query:-

CREATE USER [web-app-name<identity-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [web-app-name<identity-name>];
ALTER ROLE db_datawriter ADD MEMBER [web-app-name<identity-name>];
ALTER ROLE db_ddladmin ADD MEMBER [web-app-name<identity-name>];
GO

Make sure you check - Allow Azure services and resources to access this server for our Web app to access the Azure SQL server via firewall. I have also allowed connection from All Ips in my Azure SQL :-:-

You can refer this SO answer- Connect local asp dotnet core app to Azure SQL using managed identity with VSCode - Stack Overflow by Mark McGookin to perform all these steps via Azure CLI.

SQL Server ASP.Net Application which uses SQLProvider and MemberShip Fails to connect to DB when using Azure Managed Identity

1条答案

相关问题

热门标签

最新问答