Entity Framework Scaffold-DbContext Login failed for user

32

I am trying to build an API using Visual Studio 2017 and .NET Core 2 with Entity Framework Core. I am following the directions from This Link. I am on the section titled: Reverse engineer your model

The command they give to run is as follows:

Scaffold-DbContext "Server=(localdb)\mssqllocaldb;Database=Blogging;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

My database is an Azure SQL database so I am trying to run this command using the connection string provided from the Azure portal:

Scaffold-DbContext "Server=tcp:dbname.database.windows.net,1433;Initial Catalog=DBNAME_DB;Persist Security Info=False;User ID=username;Password=password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

I am getting returned a

System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'username'.
   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.Scaffolding.Internal.SqlServerDatabaseModelFactory.Create(DbConnection connection, IEnumerable`1 tables, IEnumerable`1 schemas)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.SqlServerDatabaseModelFactory.Create(String connectionString, IEnumerable`1 tables, IEnumerable`1 schemas)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.Create(String connectionString, IEnumerable`1 tables, IEnumerable`1 schemas, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ModelScaffolder.Generate(String connectionString, IEnumerable`1 tables, IEnumerable`1 schemas, String projectPath, String outputPath, String rootNamespace, String contextName, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Design.Internal.DatabaseOperations.ScaffoldContext(String provider, String connectionString, String outputDir, String dbContextClassName, IEnumerable`1 schemas, IEnumerable`1 tables, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContextImpl(String provider, String connectionString, String outputDir, String dbContextClassName, IEnumerable`1 schemaFilters, IEnumerable`1 tableFilters, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext.<>c__DisplayClass0_1.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:ba6ca255-04c8-46c2-9d4f-77204728e9f2
Error Number:18456,State:1,Class:14
Login failed for user 'username'.

From this same machine and username and password I am able to login to the database using SSMS. Does anyone have any suggestions why I am unable to run this command from Visual Studio?

c#
entity-framework
azure
azure-sql-database
asp.net-core-2.0
asked on Stack Overflow Nov 20, 2017 by BigDevJames

4 Answers

93

I figured out my problem. My password contained the character $ and this needs to be escaped out by using ` before the $.

answered on Stack Overflow Nov 20, 2017 by BigDevJames
0

Did you tried to update your web.config file for connection string. This might be a reason which doesn't allow you to login to azure.

answered on Stack Overflow Nov 20, 2017 by Harish
0

Please make sure the user login exists on the target SQL Azure database and not only on the Azure SQL Database server.

-- On master database CREATE LOGIN MaryLogin WITH PASSWORD = '';

-- On the user database

CREATE USER MaryUser FROM LOGIN MaryLogin;

Create a firewall rule on Azure portal to allow access to the Azure SQL Database as explained here. Make sure port TCP 1433 is open from your computer.

answered on Stack Overflow Nov 20, 2017 by Alberto Morillo
0

Change double quotes " for single quotes ' It is not necessary to add a escape character individually, it works for the rest of the parameters too.

Scaffold-DbContext 'Server=tcp:dbname.database.windows.net,1433;Initial Catalog=DBNAME_DB;Persist Security Info=False;User ID=username;Password=password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;' Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models
answered on Stack Overflow May 19, 2021 by mainmind83

User contributions licensed under CC BY-SA 3.0