locked
Error Number:18456,State:1,Class:14 Login failed for user 'sa'. RRS feed

  • Question

  • I'm trying to learn to perform CRUD operations in VS Code with MVC architecture using database first approach and Entity Framework.

    I'm following (this link) to add add model from database. I have created database in SQL Sever and am using SQL Server Authentication to log in.

    According to the link i'm following this is the command i'm running to add model in the integrated terminal in VS Code.

    dotnet ef dbcontext scaffold "Server=xxx\xxx;initial catalog=xxx;user id=sa;password=xxxxx;" Microsoft.EntityFrameworkCore.SqlServer -o Models -c "xxxxxx"

    output:

    System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'sa'.
       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, 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.SqlServer.Scaffolding.Internal.SqlServerDatabaseModelFactory.Create(DbConnection connection, IEnumerable`1 tables, IEnumerable`1 schemas)
       at Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal.SqlServerDatabaseModelFactory.Create(String connectionString, IEnumerable`1 tables, IEnumerable`1 schemas)
       at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ReverseEngineerScaffolder.ScaffoldModel(String connectionString, IEnumerable`1 tables, IEnumerable`1
    schemas, String namespace, String language, String contextDir, String contextName, ModelReverseEngineerOptions modelOptions, ModelCodeGenerationOptions codeOptions)
       at Microsoft.EntityFrameworkCore.Design.Internal.DatabaseOperations.ScaffoldContext(String provider, String connectionString, String outputDir, String outputContextDir, 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 outputDbContextDir, 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:b8ed46b8-af23-4235-9338-c63e8ed9d8f2
    Error Number:18456,State:1,Class:14
    Login failed for user 'sa'.

    My 'sa' user is enabled and i'm able to do all the operation with it perfectly fine in SQL Server Management studio as well as was able to connect to database with code first approach with 'sa' user and its also working fine, this issue is just with the database approach while creating the Model class when I run that above command and I'm using the same connection string which I used in code first approach.

    I'm trying to learn MVC using VS Code so if you could provide other reference to help me learn will also be of great help.


    • Edited by Amir_Shaikh Monday, November 12, 2018 7:14 AM
    Monday, November 12, 2018 6:48 AM

Answers

  • You can look in the SQL Server errorlog to see what the exact reason for the login failure is. I would guess that either the database name or the password is misspelled.

    However, that is not the real error. The real error is that you use sa in the connection string. An application should never log in as sa! An application should either run with the credentials of the actual user, or run under a login with limited permissions; preferrably only permission to run stored procedures. This is an important thing that you should embrace early in your learning process.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, November 12, 2018 9:11 PM

All replies

  • Hi  Amir_Shaikh,

     

    Per your description, I think one possibility  is that the issue may be related to connection string. Please check if your user has the permission to connect to the database .

     

    In your posting's title, you showed that 'Login failed for user 'sa''. Please check if the user 'sa' is enable.  For more details about how to enable sa account in SQL Server , you can refer to this article : https://sudeeptaganguly.wordpress.com/2010/04/20/how-to-enable-sa-account-in-sql-server/

     

    There is a profession document, please refer to it: https://support.microsoft.com/en-sg/help/555332/login-failed-for-user-error-message-when-you-log-on-to-sql-server

     

    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 12, 2018 6:52 AM
  • My 'sa' user is enabled and i'm able to do all the operation with it perfectly fine in SQL Server Management studio as well as was able to connect to database with code first approach with 'sa' user and its also working fine, this issue is just with the database approach while creating the Model class when I run that above command and I'm using the same connection string which I used in code first approach.

    • Edited by Amir_Shaikh Monday, November 12, 2018 7:11 AM
    Monday, November 12, 2018 7:09 AM
  • You can look in the SQL Server errorlog to see what the exact reason for the login failure is. I would guess that either the database name or the password is misspelled.

    However, that is not the real error. The real error is that you use sa in the connection string. An application should never log in as sa! An application should either run with the credentials of the actual user, or run under a login with limited permissions; preferrably only permission to run stored procedures. This is an important thing that you should embrace early in your learning process.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, November 12, 2018 9:11 PM
  • Thanks @Erland Sommarskog it worked.

    My log file showed that my password string was wrong but actually the problem was not that, I passed the correct password string but it contains '$' sign in it and when I passed it in as a command the CMD happened to read it as some parameter starting with '$' as a command and it read half of my password string as a different value. That was the actual issue.

    So I created another user in SQL Server with normal password string and that ran smoothly.

    Actually Now I have Another doubt, considering the same command as mentioned in the question if I have '$' sign in my string and want to pass it through a command how am I suppose to do that? There might be some escape sequence to read special characters as normal text when passed in a command which would prevent CMD to read it as a parameter of command and read it as plain string. And also is it OK to ask it like this or should I raise another Thread for it?.


    • Edited by Amir_Shaikh Tuesday, November 13, 2018 5:04 AM
    Tuesday, November 13, 2018 5:04 AM
  • Actually Now I have Another doubt, considering the same command as mentioned in the question if I have '$' sign in my string and want to pass it through a command how am I suppose to do that? There might be some escape sequence to read special characters as normal text when passed in a command which would prevent CMD to read it as a parameter of command and read it as plain string. And also is it OK to ask it like this or should I raise another Thread for it?.
    Yes, that's a different thread. And more importantly, it's a different forum, as it is related to VS Code, and has no relation to SQL Server.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, November 13, 2018 7:57 AM
  • Ok, Thanks once again @Erland Sommarskog
    Tuesday, November 13, 2018 10:53 AM