locked
Login Failed when MSSQLServer Start RRS feed

  • Question

  • Hello,

    I've got a windows service that needs to connect to sql server databases (SQL Server 2008 R2, Windows Server 2008 R2). This service depends on MSSQLServer. The problem is when the MSSQLServer service start : 

    Login failed for user 'Database'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]

    2011-09-20 17:18:29.47 Logon       Error: 18456, Severity: 14, State: 38.

    After reading severall posts on internet, i checked the auto close property db (false), checked user permissions but can't be this issue because my service works fine few seconds after mssqlser service start.

    Got something strange in my log : 

    Recovery completed for database database (database ID 8) in 5 second(s) (analysis 909 ms, redo 0 ms, undo 809 ms.) This is an informational message only. No user action is required.

    Any idea about this ?

     

    Thanks

    Tuesday, September 20, 2011 3:39 PM

Answers

  • Yes,

    the user connect sucessfully after database recovery.

    I also checked before that the default db of the user is the db i want to connect.

    And this db exist.

     

    Don't know why this does not work

    It is working exactly as it should.  When the instance starts, it has to perform crash recovery on the database which involves opening the database file, mounting the database, running recovery and then finally bringing the database online so that users can access it.  Your application is trying to connect before the recovery process completes for the database and it is online in multi_user mode and available for users to access it.  Nothing wrong here, it is completely normal.  According to you messages, it took 5 seconds to perform crash recovery, so the user would fail to login during that 5 seconds plus the time to setup the instance startup environment, etc.


    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Proposed as answer by Abhay_78 Tuesday, September 20, 2011 7:19 PM
    • Marked as answer by Peja Tao Thursday, October 6, 2011 9:06 AM
    Tuesday, September 20, 2011 7:15 PM

All replies

  • Hi

     

    Recovery completed for database database (database ID 8) in 5 second(s) (analysis 909 ms, redo 0 ms, undo 809 ms.) This is an informational message only. No user action is required.

    This mean ..

    It means..
    A restart recovery runs every time SQL Server is
    started. The process runs on each database because each database has its own transaction
    log. Your SQL Server error log reports the progress of restart recovery, and for each
    database, the error log tells you how many transactions were rolled forward and how many
    were rolled back. This type of recovery is sometimes referred to as crash recovery because
    a crash, or unexpected stopping of the SQL Server service, requires the recovery process
    to be run when the service is restarted. If the service was shut down cleanly with no open
    transactions in any database, only minimal recovery is necessary upon system restart. In SQL
    Server 2008, restart recovery can be run on multiple databases in parallel, each handled by a
    different thread.

     

    VT

     


    • Edited by SimpleSQL Tuesday, September 20, 2011 3:57 PM
    Tuesday, September 20, 2011 3:52 PM
  • Hello,

    You might have to look at the State of the error(In your case its 38).A good ref point for trouble shooting 18456 errors are

    http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx

    Hope this helps.

    Thank you,

     


    AnupSivaDas | Forum Support| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer.
    Tuesday, September 20, 2011 5:01 PM
  • Does the account ever establish a connection successfully (like after the database recovery for db 8 is complete)?  Also, ensure the user's default database is a database that actually exists on the server.  When a database is removed from the server it can leave some accounts with an "orphaned" default database which will throw this error.

    Hope that helps,

    John


    John Eisbrener - http://dbaeyes.com/
    Tuesday, September 20, 2011 5:41 PM
  • Yes,

    the user connect sucessfully after database recovery.

    I also checked before that the default db of the user is the db i want to connect.

    And this db exist.

     

    Don't know why this does not work

    Tuesday, September 20, 2011 7:10 PM
  • Yes,

    the user connect sucessfully after database recovery.

    I also checked before that the default db of the user is the db i want to connect.

    And this db exist.

     

    Don't know why this does not work

    It is working exactly as it should.  When the instance starts, it has to perform crash recovery on the database which involves opening the database file, mounting the database, running recovery and then finally bringing the database online so that users can access it.  Your application is trying to connect before the recovery process completes for the database and it is online in multi_user mode and available for users to access it.  Nothing wrong here, it is completely normal.  According to you messages, it took 5 seconds to perform crash recovery, so the user would fail to login during that 5 seconds plus the time to setup the instance startup environment, etc.


    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Proposed as answer by Abhay_78 Tuesday, September 20, 2011 7:19 PM
    • Marked as answer by Peja Tao Thursday, October 6, 2011 9:06 AM
    Tuesday, September 20, 2011 7:15 PM
  • Well, i think it's the windows service that will handle this problem, trying to connect each time db is not loaded. Not so good :s
    Tuesday, September 20, 2011 7:26 PM
  • Well, i think it's the windows service that will handle this problem, trying to connect each time db is not loaded. Not so good :s
    I don't follow what you are saying here? I can only help you understand the reason you get the login failure in SQL, which I explained above, I can't speak to how your application or service functions unless you provide more information.  What you asked about SQL Server is the way every SQL Server works during instance startup and there is nothing that is going to change about it.

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!


    Tuesday, September 20, 2011 7:29 PM
  • I understood what you said, and that's true its the answer to my problem, but not the solution. 

    Thank you.

    What i said is : now i need to update my service to apply the workaround : Try to connect until connection successed,which is not a really good workaround in my view.

    Thanks Jonathan

    Tuesday, September 20, 2011 8:06 PM
  • I understood what you said, and that's true its the answer to my problem, but not the solution. 

    Thank you.

    What i said is : now i need to update my service to apply the workaround : Try to connect until connection successed,which is not a really good workaround in my view.

    Thanks Jonathan

    It isn't a work around, it is appropriate application design for exception management of an external resource that you depend on for your application to function correctly.  It should have been included in the original design, but exception management is rarely included in code until it becomes a problem because it requires planning to think of the failure conditions and how to handle them.  (I never used to write it into application code when I was developer either).

    This is where Test Driven Development is really useful because you write code to test the code you are developing an the process is geared at handling failures like the SQL database not being online or available due to Instance Startup time.  

    Alternately, you could set a service dependency on the SQL Server Service being running for your service, and then maybe you wouldn't have to write exception management into your code, but managing a connection error for SQL Server should be a standard part of application logic that should be included in your code if you rely on an external service to be available to run.  


    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!


    Tuesday, September 20, 2011 8:13 PM
  • The exception is handled, the problem is that service is build to work even if the connection is not opened (in fact, there are potentially lots of db connections, so if one failed the service can still work). So when handled, the exception is logged and the connection that failed does not launch the process corresponding.

     

    Thanks for your advices

    EDIT : My service is already depending on MSSQLServer service
    Tuesday, September 20, 2011 8:22 PM
  • The error is reported when the login used to connect to SQL Server is not able to open the default database it is assigned to. To get the information of the default database for the login, execute the below query against the master database.

    select name, dbname from sys.syslogins

    There could be 3 reasons for the database not being available:

    1. Database no longer exists.

             -*- Try to point the default database to a different database, or consider pointing it to MASTER database

    2. Database is still recovering after a server restart.

             -*- Try reconnection after the recovery manager has completed and the database comes online  

    3. Database is not online

             -*- Troubleshoot why the database is not online and consider changing the default database.

    Tuesday, September 20, 2011 8:30 PM
  • Is there a way you can cause the service to wait prior to initiating a connection to SQL.  For instance it will wait for 10 or 15 seconds of time after it sees the SQL Server Service coming up.  Not elegant (or really good), but it may be a workaround for the issue at hand.

    John


    John Eisbrener - http://dbaeyes.com/
    Tuesday, September 20, 2011 8:51 PM