Open Closed

Unable to connect to SQL Server #7926


User avatar
0
castcrewit created
  • ABP Framework version: v8.1
  • UI Type: Angular
  • Database System: EF Core (SQL Server,)
  • Tiered (for MVC) or Auth Server Separated (for Angular): yes
  • Exception message and full stack trace:
  • Steps to reproduce the issue:

I am now using AbpIo for a new project and I am using the Micro Service templates. But this time I am using SQL Server instead of mysql in the past. I used the Abp Studio and created the template and then changed all the connection strings to the below :

"Administration": "Server=localhost; User Id=sa1; Password=myPassw@rd; Database=IntegrationServices_Administration; TrustServerCertificate=true",
"AuditLoggingService": "Server=localhost; User Id=sa1; Password=myPassw@rd; Database=IntegrationServices_AuditLoggingService; TrustServerCertificate=true",
"SaasService": "Server=localhost; User Id=sa1; Password=myPassw@rd; Database=IntegrationServices_SaasService; TrustServerCertificate=true",
"AbpBlobStoring": "Server=localhost; User Id=sa1; Password=myPassw@rd; Database=IntegrationServices_BlobStoring; TrustServerCertificate=true"

I also tried the above with a remote sql server as well instead of localhost. I even tried with Trusted Connection. I have been trying every permutation and combination of the connection string to get this running however of no use.

I also made sure the credentials I am using is correct by logging into the sql server as well. I check every settings in SQL to make sure everything is turned on. I have been using this same database seerver to run one of my projects based out of ASPNETZero, so I don't think there is any issue with the connection string.

No matter how i do it I end up getting the below error :

Cannot open database "IntegrationServices_Administration" requested by the login. The login failed.
Login failed for user 'sa'.. Stack Trace:
   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.WaitForPendingOpen()
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransactionAsync(IsolationLevel isolationLevel, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransactionAsync(CancellationToken cancellationToken)
   at Volo.Abp.Uow.EntityFrameworkCore.UnitOfWorkDbContextProvider`1.CreateDbContextWithTransactionAsync(IUnitOfWork unitOfWork)
   at Volo.Abp.Uow.EntityFrameworkCore.UnitOfWorkDbContextProvider`1.CreateDbContextAsync(IUnitOfWork unitOfWork)
   at Volo.Abp.Uow.EntityFrameworkCore.UnitOfWorkDbContextProvider`1.CreateDbContextAsync(IUnitOfWork unitOfWork, String connectionStringName, String connectionString)
   at Volo.Abp.Uow.EntityFrameworkCore.UnitOfWorkDbContextProvider`1.GetDbContextAsync()
   at Volo.Abp.Domain.Repositories.EntityFrameworkCore.EfCoreRepository`2.GetDbSetAsync()
   at Volo.Abp.Domain.Repositories.EntityFrameworkCore.EfCoreRepository`2.GetQueryableAsync()
   at Volo.Abp.Domain.Repositories.EntityFrameworkCore.EfCoreRepository`2.GetListAsync(Boolean includeDetails, CancellationToken cancellationToken)
   at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo)
   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 Volo.Abp.PermissionManagement.DynamicPermissionDefinitionStore.UpdateInMemoryStoreCache()
   at Volo.Abp.PermissionManagement.DynamicPermissionDefinitionStore.EnsureCacheIsUptoDateAsync()
   at Volo.Abp.PermissionManagement.DynamicPermissionDefinitionStore.GetPermissionsAsync()
   at Volo.Abp.Authorization.Permissions.PermissionDefinitionManager.GetPermissionsAsync()
   at IntegrationServices.AdministrationService.Data.AdministrationServiceDataSeeder.SeedAdminPermissionsAsync(Nullable`1 tenantId) in C:\SourceCode\integration-microservices\services\administration\IntegrationServices.AdministrationService\Data\AdministrationServiceDataSeeder.cs:line 67
   at IntegrationServices.AdministrationService.Data.AdministrationServiceDataSeeder.SeedAsync(Nullable`1 tenantId) in C:\SourceCode\integration-microservices\services\administration\IntegrationServices.AdministrationService\Data\AdministrationServiceDataSeeder.cs:line 41
   at IntegrationServices.AdministrationService.Data.AdministrationServiceRuntimeDatabaseMigrator.SeedAsync() in C:\SourceCode\integration-microservices\services\administration\IntegrationServices.AdministrationService\Data\AdministrationServiceRuntimeDatabaseMigrator.cs:line 35
   at Volo.Abp.EntityFrameworkCore.Migrations.EfCoreRuntimeDatabaseMigratorBase`1.LockAndApplyDatabaseMigrationsAsync()
   at Volo.Abp.EntityFrameworkCore.Migrations.EfCoreRuntimeDatabaseMigratorBase`1.LockAndApplyDatabaseMigrationsAsync()
   at Volo.Abp.EntityFrameworkCore.Migrations.EfCoreRuntimeDatabaseMigratorBase`1.TryAsync(Func`1 task, Int32 maxTryCount)
2024-09-17 17:44:04.214 -07:00 [INF] Trying to acquire the distributed lock for database migration: Administration.
2024-09-17 17:44:04.249 -07:00 [INF] Distributed lock is acquired for database migration: Administration...
2024-09-17 17:44:15.183 -07:00 [INF] Seeding admin permissions.
2024-09-17 17:44:15.282 -07:00 [ERR] An error occurred using the connection to database 'IntegrationServices_Administration' on server 'localhost'.
2024-09-17 17:44:16.671 -07:00 [WRN] SqlException has been thrown. The operation will be tried 1 times more. Exception:

Note : I commented out the SQL for docker and using the SQL from my local system , rest all the docker infrastructure is working as the same.

I am using Multi Tenancy as well, if that helps with anything.

I suspect something else is wrong. I have sent the link with the code for administration micro service just fro you to understand and debug the issue .shiwei.liang@volosoft.com shiwei.liang@volosoft.com


5 Answer(s)
  • User Avatar
    0
    liangshiwei created
    Support Team Fullstack Developer

    Hi,

    I guess these databases do not exist yet. You need to run dbmigrator to create them

  • User Avatar
    0
    castcrewit created

    Yes the database do not exists and when we run any microservice solution it should create the database automatically.

    Doesn't this line of code expected to do that :

    public override async Task OnPreApplicationInitializationAsync(ApplicationInitializationContext context)
    {
        using var scope = context.ServiceProvider.CreateScope();
        await MigrateDatabase(scope.ServiceProvider);
    }
    

    However we used the Abp Studio to run the Migration as we get the below error

    0:20:44.265 	 Information  	 Starting task execution: "Updating the database: IntegrationServices.AdministrationService"20:22:24.503 	 Information  	 CLI command executed. Command: "dotnet ef database update"
     Working directory: "C:\SourceCode\integration-microservices\services\administration\IntegrationServices.AdministrationService" 
     CommandResult { ExitCode: 1, IsSuccess: False, StartTime: 09/17/2024 20:20:44 -07:00, ExitTime: 09/17/2024 20:22:24 -07:00, RunTime: 00:01:40.2136695 }
    20:22:24.505 	 Information  	 Failed task execution: "Updating the database: IntegrationServices.AdministrationService"
    20:22:24.505 	 Information  	 ERROR: "An error has occured!"
    20:22:24.505 	 Information  	 ERROR DETAILS: "Failed to update database in project IntegrationServices.AdministrationService"
    20:22:24.505 	 Warning      	 Exception of type 'Volo.Abp.Studio.AbpStudioException' was thrown.
    20:22:24.507 	 Warning      	 Code:AbpStudio:EfCoreUpdateDatabaseFailed
    20:22:24.507 	 Warning      	 Details:
    20:22:24.507 	 Warning      	 ---------- Exception Data ----------
    ProjectName = IntegrationServices.AdministrationService
    

    <br> <br> <br>

    <br> Then i went ahead and tried toe apply migration like the below

    dotnet ef database update --project IntegrationServices.AdministrationService --context AdministrationServiceDbContext

    No migrations were applied. The database is already up to date.

    When I went to the database this is what I see , only the migration history table gets created when the code is getting built :

    I am very confused how things should work, you mentioned run the migrator, where is the migrator project , it doesn't come by default.

    We never had such issues while using MySQL, why the process will be any different and if its different what's the correct process. I am doubting it may be to do with the code template or somehwere in the code, have you tried the project i sent you

  • User Avatar
    0
    liangshiwei created
    Support Team Fullstack Developer

    microservice solution it should create the database automatically.

    yes, the microservices will create database.

    No migrations were applied. The database is already up to date. When I went to the database this is what I see , only the migration history table gets created when the code is getting built :

    I guess there are no db migrations files?

    you can create it manually

    dotnet ef migrations add Initial -c ....

  • User Avatar
    0
    castcrewit created

    Thank You and that was the cause of the issue. There should be a troubleshooting guide for this, the Abp Studio is in Beta and doesn't work at times properly, like migration not getting created. You should put a guide on things to watch out when you create a project for the first time

  • User Avatar
    0
    liangshiwei created
    Support Team Fullstack Developer

    Hi,

    we will

Made with ❤️ on ABP v9.1.0-preview. Updated on November 11, 2024, 11:11