none
Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456, Severity: 14, State: 1) When I use SQL Server Authentication to connect server RRS feed

  • Question

  • When I use the following setting to connect server, it succeeds
    Server type: Database Engine
    Server name: Home-PC
    Authentication: Windows Authentication

    User name: Home-PC\John
    Password:<empty>

    However, when I try to use the following setting to connect the same server, it fails as always
    Server type: Database Engine
    Server name: Home-PC
    Authentication: SQL Server Authentication 
    Login:
    sa
    Password:
    1234567

    The Error Message as follows
    TITLE: Connect to Server
    ------------------------------
    Cannot connect to Home-PC.
    ------------------------------
    ADDITIONAL INFORMATION:
    Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

    Then, I enter the server to change some security configurations by means of Windows Authentication
    1. go to Security > Login > sa
    change 'sa' status in Login to Enabled
    reset 'sa' password as '1234567'
    2. go to this database property > Security > SQL Server and Windows Authentication mode set as selected

    Unfortunately, the same problem still occurs.
    p.s. My computer o.s. is Vista Ultimate SP2 64bit and SQL Server 2008 has been installed successfully.

    Thank you so much for helping me.

    Tuesday, February 2, 2010 5:26 PM

Answers

  • Did you restart SQL services after changing the mixed mode authentication?
    Some of the server level properties takes only after a SQL restart , one such is authentication modes.

    Thanks, Leks
    • Proposed as answer by TiborKMVP, Moderator Tuesday, February 2, 2010 6:41 PM
    • Marked as answer by enix0907 Tuesday, February 2, 2010 9:05 PM
    Tuesday, February 2, 2010 6:25 PM
    Answerer

All replies

  • Look at the state for the error 18456 from the error log. The state 1 always comes in SSMS or other tools , but the error logs will have the correct state number for this login failed error.
    See this article http://sql-articles.com/articles/troubleshooting/troubleshooting-login-failed-error-18456 for all the states and use the doc to track down the reason for your login failure.
    If you get state 7 or 8 , then the password is not correct.

    Thanks, Leks
    Tuesday, February 2, 2010 5:39 PM
    Answerer
  • Thank you for the troubleshooting info, I try to find a way to open my error logs. Could you tell me where it is located? Is that on SQL Server Management Studio > View > Error list ? If yes, there is nothing to show up in Error List.
    Tuesday, February 2, 2010 5:54 PM
  • You can see this from SSMS --> new query and execute sp_readerrorlog
    Alternatively you can open file from here - C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\errorlog
    Thanks, Leks
    Tuesday, February 2, 2010 5:58 PM
    Answerer
  • Yeah, I found those valuable info in errorlog as follows:

    Login failed for user 'sa'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <local machine>]
    Error: 18456, Severity: 14, State: 58.


    However, I have turned on the mode in database property > Security > SQL Server and Windows Authentication mode set as selected
    I am confused of its explanation...

    Tuesday, February 2, 2010 6:07 PM
  • Did you restart SQL services after changing the mixed mode authentication?
    Some of the server level properties takes only after a SQL restart , one such is authentication modes.

    Thanks, Leks
    • Proposed as answer by TiborKMVP, Moderator Tuesday, February 2, 2010 6:41 PM
    • Marked as answer by enix0907 Tuesday, February 2, 2010 9:05 PM
    Tuesday, February 2, 2010 6:25 PM
    Answerer
  • The problem is solved!

    I thought restarting SQL Server means I could just colse it and then execute it.

    Thank you again for your instruction.
    Tuesday, February 2, 2010 8:10 PM
  • Thanks It works
    "SQLSERVER DBA" "INDIA"
    Wednesday, November 24, 2010 9:59 AM
  • Thanks a lot, this solution resolved my worried of past 2 days

    Prashant

    Friday, April 20, 2012 12:02 PM
  • Thanks it worked.
    Tuesday, October 2, 2012 4:22 AM
  • Hi,

    I had the same problem. I restarted the MSSQLSERVER and It worked correctly

    Thanks!

    Saturday, April 22, 2017 4:52 PM