Open Closed

Preventing tenant's SQL user name and password from being in plain text in the SaasTenantConnections table #4853

User avatar
0
balessi75 created

ABP Commercial 7.0.1 / Blazor Server / EF / Non tiered / Separate Host DB, Separate Tenant DBs

For security reasons, we are currently saving the tenant connection strings to the database without a user name and password

We then overrode the MultiTenantConnectionStringResolver so that we can fill in the user name and password at run time based on values we have stored in Azure Key Vault.

The problem we are having is that at certain point, when logging into a tenant, the method below in our MultiTenantConnectionStringResolver is called by ABP with _currentTenant.Id == null and the framework returns the host connection string, but immediately after that there is an exception occurring because, in the tenant database, there is no user name or password in the connection string.

There must be somewhere else in the frame work that we need to override, but we can't seem to figure it out from the stack trace.

I've included the stack trace below...

    public override async Task<string> ResolveAsync(string connectionStringName = null)
    {

        if (_currentTenant.Id == null)
        {
            //No current tenant, fallback to default logic
            return await base.ResolveAsync(connectionStringName);
        }
        
        // we have logic below here to update the connection string when _currentTenant.Id is a tenant and not the host....
        ....
        //return our custom connection string
        ....


Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user ''.
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open()
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.OpenDbConnection(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternal(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.Enumerator.&lt;&gt;c.&lt;MoveNext&gt;b__22_0(DbContext _, Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Volo.Abp.LanguageManagement.EntityFrameworkCore.EfCoreLanguageTextRepository.GetList(String resourceName, String cultureName)
   at Castle.Proxies.Invocations.ILanguageTextRepository_GetList.InvokeMethodOnTarget()
   at Castle.DynamicProxy.AbstractInvocation.Proceed()
   at Castle.DynamicProxy.AbstractInvocation.ProceedInfo.Invoke()
   at Castle.DynamicProxy.AsyncInterceptorBase.ProceedSynchronous[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo)
--- End of stack trace from previous location ---
   at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapterWithReturnValue`1.ProceedAsync()
   at Volo.Abp.Uow.UnitOfWorkInterceptor.InterceptAsync(IAbpMethodInvocation invocation)
   at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter`1.InterceptAsync[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo, Func`3 proceed)
   at Castle.DynamicProxy.NoCoverage.RethrowHelper.Rethrow(Exception exception)
   at Castle.DynamicProxy.NoCoverage.RethrowHelper.RethrowInnerIfAggregate(Exception exception)
   at Castle.DynamicProxy.AsyncInterceptorBase.InterceptSynchronousResult[TResult](AsyncInterceptorBase me, IInvocation invocation)
   at Castle.DynamicProxy.AbstractInvocation.Proceed()
   at Castle.Proxies.IBasicRepository`1Proxy_20.GetList(String resourceName, String cultureName)
   at Volo.Abp.LanguageManagement.DynamicResourceLocalizer.CreateCacheItem(LocalizationResourceBase resource, String cultureName)
   at Volo.Abp.LanguageManagement.DynamicResourceLocalizer.&lt;&gt;c__DisplayClass10_0.RDyaMGpmi()
   at Volo.Abp.Caching.DistributedCache`2.GetOrAdd(TCacheKey key, Func`1 factory, Func`1 optionsFactory, Nullable`1 hideErrors, Boolean considerUow)
   at Volo.Abp.LanguageManagement.DynamicResourceLocalizer.GetCacheItem(LocalizationResourceBase resource, String cultureName)
   at Volo.Abp.LanguageManagement.DynamicResourceLocalizer.GetOrNull(LocalizationResourceBase resource, String cultureName, String name)
   at Volo.Abp.Localization.LocalizationResourceContributorList.GetOrNull(String cultureName, String name, Boolean includeDynamicContributors)
   at Volo.Abp.Localization.AbpDictionaryBasedStringLocalizer.GetLocalizedStringOrNull(String name, String cultureName, Boolean tryDefaults)
   at Volo.Abp.Localization.AbpDictionaryBasedStringLocalizer.GetLocalizedString(String name, String cultureName)
   at Volo.Abp.Localization.AbpDictionaryBasedStringLocalizer.GetLocalizedString(String name)
   at Volo.Abp.Localization.AbpDictionaryBasedStringLocalizer.get_Item(String name)
   at Microsoft.AspNetCore.Mvc.Localization.HtmlLocalizer.get_Item(String name)
   at AspNetCoreGeneratedDocument.Pages_Account_Login.ExecuteAsync()
   at Microsoft.AspNetCore.Mvc.Razor.RazorView.RenderPageCoreAsync(IRazorPage page, ViewContext context)
   at Microsoft.AspNetCore.Mvc.Razor.RazorView.RenderPageAsync(IRazorPage page, ViewContext context, Boolean invokeViewStarts)
   at Microsoft.AspNetCore.Mvc.Razor.RazorView.RenderAsync(ViewContext context)
   at Microsoft.AspNetCore.Mvc.ViewFeatures.ViewExecutor.ExecuteAsync(ViewContext viewContext, String contentType, Nullable`1 statusCode)
   at Microsoft.AspNetCore.Mvc.ViewFeatures.ViewExecutor.ExecuteAsync(ViewContext viewContext, String contentType, Nullable`1 statusCode)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.&lt;InvokeResultAsync&gt;g__Logged|22_0(ResourceInvoker invoker, IActionResult result)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.&lt;InvokeNextResultFilterAsync&gt;g__Awaited|30_0[TFilter,TFilterAsync](ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResultExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.ResultNext[TFilter,TFilterAsync](State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeResultFilters()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.&lt;InvokeNextResourceFilter&gt;g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.&lt;InvokeAsync&gt;g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.&lt;InvokeAsync&gt;g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.&lt;Invoke&gt;g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Volo.Abp.AspNetCore.Serilog.AbpSerilogMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
   at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.&lt;&gt;c__DisplayClass6_1.&lt;&lt;UseMiddlewareInterface&gt;b__1>d.MoveNext()
--- End of stack trace from previous location ---
   at Volo.Abp.AspNetCore.Auditing.AbpAuditingMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
   at Volo.Abp.AspNetCore.Auditing.AbpAuditingMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
   at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.&lt;&gt;c__DisplayClass6_1.&lt;&lt;UseMiddlewareInterface&gt;b__1>d.MoveNext()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Volo.Abp.AspNetCore.Uow.AbpUnitOfWorkMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
   at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.&lt;&gt;c__DisplayClass6_1.&lt;&lt;UseMiddlewareInterface&gt;b__1>d.MoveNext()
--- End of stack trace from previous location ---
   at Volo.Abp.AspNetCore.ExceptionHandling.AbpExceptionHandlingMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
   at Volo.Abp.AspNetCore.ExceptionHandling.AbpExceptionHandlingMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
   at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.&lt;&gt;c__DisplayClass6_1.&lt;&lt;UseMiddlewareInterface&gt;b__1>d.MoveNext()
--- End of stack trace from previous location ---
   at Volo.Abp.AspNetCore.MultiTenancy.MultiTenancyMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
   at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.&lt;&gt;c__DisplayClass6_1.&lt;&lt;UseMiddlewareInterface&gt;b__1>d.MoveNext()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Builder.ApplicationBuilderAbpOpenIddictMiddlewareExtension.&lt;&gt;c__DisplayClass0_0.&lt;&lt;UseAbpOpenIddictValidation&gt;b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Volo.Abp.AspNetCore.Security.AbpSecurityHeadersMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
   at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.&lt;&gt;c__DisplayClass6_1.&lt;&lt;UseMiddlewareInterface&gt;b__1>d.MoveNext()
--- End of stack trace from previous location ---
   at Volo.Abp.AspNetCore.Tracing.AbpCorrelationIdMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
   at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.&lt;&gt;c__DisplayClass6_1.&lt;&lt;UseMiddlewareInterface&gt;b__1>d.MoveNext()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Localization.RequestLocalizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.RequestLocalization.AbpRequestLocalizationMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
   at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.&lt;&gt;c__DisplayClass6_1.&lt;&lt;UseMiddlewareInterface&gt;b__1>d.MoveNext()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)
ClientConnectionId:2535e505-863b-4d41-8a6c-58bb63378db7
Error Number:18456,State:1,Class:14

Please advise asap and thank you in advance...

Go to accepted answer
2 Answer(s)
  • User Avatar
    0
    liangshiwei created
    Support Team Fullstack Developer

    Hi,

    It works for me:

    Server=localhost;Database=MyProjectName_test;user={user};password={password};TrustServerCertificate=True

    [Dependency(ReplaceServices = true)]
public class MyMultiTenantConnectionStringResolver: MultiTenantConnectionStringResolver
{
    protected ICurrentTenant CurrentTenant { get; } 
    
    public MyMultiTenantConnectionStringResolver(IOptionsMonitor<AbpDbConnectionOptions> options, ICurrentTenant currentTenant, IServiceProvider serviceProvider) : base(options, currentTenant, serviceProvider)
    {
        CurrentTenant = currentTenant;
    }

    public async override Task<string> ResolveAsync(string connectionStringName = null)
    {
        var connectionString = await base.ResolveAsync(connectionStringName);

        if (CurrentTenant.IsAvailable)
        {
            connectionString = connectionString.Replace("{user}", "sa").Replace("{password}","admin@123456");
        }

        return connectionString;
    }

    public override string Resolve(string connectionStringName = null)
    {
        var connectionString = base.Resolve(connectionStringName);

        if (CurrentTenant.IsAvailable)
        {
            connectionString = connectionString.Replace("{user}", "sa").Replace("{password}","admin@123456");
        }

        return connectionString;
    }
}```
  • User Avatar
    0
    balessi75 created

    Hi @liangshiwei

    Thank you for taking the time to setup the scenario!

    The problem was that I didn't override the obsolete non-async version of the Resolve method.

    I'll close the issue.

Assignee
User avatar liangshiwei
Labels
None yet
Made with ❤️ on ABP v9.1.0-preview. Updated on December 12, 2024, 07:15