CODEWITHSUNDEEP
postgresqlentity-frameworkasp.net-core
user3555216
user3555216

Reputation:

Cannot authenticate user in .NET Core EF migration with Postgres

I am having trouble authenticating EF to perform migration in PostgreSQL. I am following this tutorial using Postgre instead of SQL Server: https://learn.microsoft.com/en-us/ef/core/get-started/aspnetcore/new-db

This is my appsettings.development.json:

{
  "Logging": {
    "IncludeScopes": false,
    "LogLevel": {
      "Default": "Debug",
      "System": "Information",
      "Microsoft": "Information"
    }
  },
  "ConnectionStrings": {
    "DASDBConnection": "Server=localhost;Database=das;User Id=dasdev;Password=password"
  }
}

I can run the Add-Migration command without problem. However, when I attempt to migrate, I received following error: 

PM> Update-Database
System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'dasdev'.
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling)
   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.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerDatabaseCreator.<>c__DisplayClass18_0.<Exists>b__0(DateTime giveUp)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.<>c__DisplayClass12_0`2.<Execute>b__0(DbContext c, TState s)
   at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, Func`2 operation, Func`2 verifySucceeded, TState state)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, TState state, Func`2 operation)
   at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerDatabaseCreator.Exists(Boolean retryOnNotExists)
   at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerDatabaseCreator.Exists()
   at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.Exists()
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:5e5acc10-e1c3-4b09-8379-2f5c2c8ae866
Error Number:18456,State:1,Class:14
Login failed for user 'dasdev'.

I have these NuGet packages in my dependency:

Microsoft.AspNetCore.All (2.0.5)
Microsoft.EntityFrameworkCore.Tools (2.0.1)
Microsoft.VisualStudio.Web.Codegeneration.Design(2.0.2)
Npgsql.EntityFrameworkCore.PostgreSQL(2.0.0)

I have created the role dasdev on the database server and I can login using 'dasdev' without any issue using psql. Password was prompted at login and the password was correct.

So, what did I miss?

Upvotes: 1

Views: 2518

Answers (2)

rajeshchauhan23102008
rajeshchauhan23102008

Reputation: 681

I believe you can also make your migration work with User Name and Password Authentication. The problem i can see is with your connectionString it should be like this for PostgreSQL: 

"DASDBConnection": "User ID=dasdev;Password=password;Host=localhost;Database=das;Pooling=true;"

Note: Make sure your User ID and Password is working, to confirm this log-in directly to your postgres db and check.

Upvotes: 2

user3555216
user3555216

Reputation:

I added Integrated Security=true;Pooling=true at the end of the configuration string and the authentication will pass. It seems like the Integrated Security=true was what mainly caused this issue.

Also, in Startup.cs, I should use Npgsql for DBContext:

services.AddDbContext<DataTypeHere>(option => options.UseNpgsql(connectionStringHere));

Hope it helps for someone who is trying .NET Core EF with Postgre.

Upvotes: 2

Related Questions