Forgot Login Password RRS feed

  • Question

  • I created a login  xxxxuser.  I needed the password for an application and forgot what the password was.   I deleted associated users in dbs and then deleted the Login. I re-created the same login of course noting the new password. Now when I login in I keep getting Login failed error.  Below is the error.

    What am I missing here to use the same login again? Iwas able to create a new Login with the credentials and was able to LOGIN.

    Message I get:

    TITLE: Connect to Server

    Cannot connect to DTCFFC5533C8602\MSSQLSERVER_2008.


    Login failed for user 'xxxxUser'. (Microsoft SQL Server, Error: 18456)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476



    DBA Developer

    Tuesday, November 20, 2012 10:20 PM

All replies

  • Look in the SQL Server error log for the login error. There you will find a State number. You can then go to http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx
    to see what that state number means.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, November 20, 2012 10:37 PM
  • Here is the errors:

    Error Number: 18456
    Severity: 14
    State: 1
    Line Number: 65536

    DBA Developer

    Tuesday, November 20, 2012 10:56 PM
  • Look for the login failed message in the SQL errorlogs


    Tuesday, November 20, 2012 11:04 PM
  • If you are using the SQL authentication then please check
    A)SQL Server is configured to Mixed Authentication Mode.

    B)If you are trying to connect using SQL Server Authentication, verify that SQL Server login exists and that you have spelled it properly.

    If it s windows authentication then can you please check from the AD(domain) the login get succeeds...I mean no problem for connecting to domain.

    C)Verify with the same login there is no two Modes i,e SQL server account & windows ID ->i,e for ex test(SQL id) & windows ID (Domain\test).

    D)Verify that did for any other users it is allowing in case for the same domain like your ID

    E)verify that you are connecting to right SQL server name (just ensure)

    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Wednesday, November 21, 2012 2:00 AM
  • Here is the errors:

    Error Number: 18456
    Severity: 14
    State: 1
    Line Number: 65536

    DBA Developer

    No. We want it from errorlog

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog | Team Blog | @Twitter

    Wednesday, November 21, 2012 2:04 AM
  • IF you forgot your password for a login,then the password need to be changes,

    1.Login in SA account to change the XXXuser password.

    2.If you forget the password for SA then check if any user equivalent to SA crediential and change the password.

    3.IF you forget SA and don't have other users equivalent to SA then login to windows service account and change the password(If it is in Mixed mode).

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, November 21, 2012 6:04 AM
  • To figure out the exact reason, this error number 18456 with its STATE number is logged into the SQL server error log file . The resolution completley depends on the state nnumber .

    If you have gained access or can gain access to SQL server with different logon credentials then always use sp_readerrorlog, xp_readerrorlog or use the GUI option of opening up Management node –> SQL server Logs –> View –> SQL server log.

    For more information please refer following :

    Thanks,Suhas Vallala

    Wednesday, November 21, 2012 9:35 AM
  • please share login failure details from SQL Serve error log.

    To find error log location : http://blogs.msdn.com/b/varund/archive/2009/03/21/how-to-verify-path-for-sql-server-error-log-files.aspx

    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Wednesday, November 21, 2012 6:26 PM
  • Hi Paul,

    Did you tried by checking as said above by all also paste the errorlog details as well.. for further assistance!

    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Friday, November 23, 2012 2:00 AM
  • See this  KBA http://support.microsoft.com/kb/925744 also

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Friday, November 23, 2012 4:27 AM
  • one possibility is mismatch of SID(Security Identifier). If windows SID is different from SQL SID your login fails.

    every time user is created a randam SID will be generated. make sure server_principals and database_principals contains teh same SID.



    Monday, November 26, 2012 7:29 PM