locked
error 18456, severity 14 , stage 38 RRS feed

  • Question

  • login failed for user "nt authority \network service". reason :reason failed to open explicitly specified database

    How to fix this error

    thanks

    Friday, March 7, 2014 11:28 AM

All replies

  • Where are you getting this error? It seems like its using default network service account for connection and doesnt seem to have access to database specified

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, March 7, 2014 11:35 AM
  • Is this account nt authority \network service added as a login in SQL server security--logins.Can you check that or ask DBA to check it.Can you also ask what rights it has been provided I guess this issue can be due to login not added or no rights provided.AFAIK for security reasons this account is removed from logins.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Friday, March 7, 2014 11:49 AM

  • this login has given sysadmin and public......

    when login have a sysadmin it will have access of every thing right...

    But still am unable to understand why this is showing SQL SERVER LOGs in  Management

    as login failed, with error msge 18456...

    Please do me need full....  

    And small doubt does this error effect any thing in server.

    Thanks...........


    Friday, March 7, 2014 12:08 PM

  • this login has given sysadmin and public......

    when login have a sysadmin it will have access of every thing right...

    But still am unable to understand why this is showing SQL SERVER LOGs in  Management

    as login failed, with error msge 18456...

    Please do me need full....  

    And small doubt does this error effect any thing in server.

    Thanks...........



    It can be possible that database was recovering at that time,did you see this message after database was trying to come online ,i means may be restoration was going on,may be SQL Server services restarted and it took quite bit of time for SQL Server databases to come online.Who uses this login can you ask him to check if he is still facing the issue.I guess if it is syadmin then possible cause could be DB was recovering or not online.Can you post errorlog where you saw this message complete errorlog

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, March 7, 2014 1:23 PM
  • 03/07/2014 14:51:28,Logon,Unknown,Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]

    03/07/2014 14:51:28,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.

    03/07/2014 14:51:24,Logon,Unknown,Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]

    03/07/2014 14:51:24,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.

    03/07/2014 14:48:36,Logon,Unknown,Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
    03/07/2014 14:48:36,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.

    This i got in Sql Server Logs

    it came repetitively , i haven't faced any issue due to this error , every thing going smooth in server. But want to know what it is and to fix error.

    NOTE : Do i get any issues further due to this issue , if please tell me.

    Thanks

     
    Friday, March 7, 2014 1:28 PM
  • Is the specified database online and available?  If no database is specified, make sure the default database for the login is available.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, March 7, 2014 1:30 PM
  • Hello,

    State 38 points to permission issue with user but you said it is added as administrator so I guess database to which user wants to connect was either not present or was not online.Just change default database to some DB which is online and try to connect

    Check below connect also

    http://connect.microsoft.com/SQLServer/feedback/details/435782/sever-login-failure-storm-for-sql-server-2008-x64-sql-service-account

    If you want to dig deep setup a profiler trace using below link to identify this cause

    http://www.eraofdata.com/sql-18456-login-failures/


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers



    Friday, March 7, 2014 1:36 PM
  • Bro,

    you mean for login 'NT AUTHORITY\NETWORK SERVICE' i have to provide some database which is in Online right.

    But where am worried is login 'NT AUTHORITY\NETWORK SERVICE' is a Default one , if i provide access to the Database which is in online , it wont trouble the server right.

    And as i said This login 'NT AUTHORITY\NETWORK SERVICE' having sysadmin still why do it hits a error 18456.

    And login 'NT AUTHORITY\NETWORK SERVICE' in UserMapping it  is mapped with Master , MSDB  and ReportServer .

    Thanks...

    Friday, March 7, 2014 1:47 PM
  • I again say same thing please use profiler to track more.Check whether autoclose is set ON for database Reportserver( RK database..properties..options) .This might cause DB release all connection and seems like it is not online and can cause this error .This can be one of cause .Again why it is not allowed can be due to fact that it was not online.Was this server a part of mirroring or always on.Reason for state 38 is clearly mentioned in below article,i dont guess more than that there can be any reaosn.

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

    Can you post complete erro log on skydrive for analysis so that I could get something concrete


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, March 7, 2014 2:08 PM
  • And login 'NT AUTHORITY\NETWORK SERVICE' in UserMapping it  is mapped with Master , MSDB  and ReportServer .

    What is the default database specified for this login?  You will not get the error if it is master.  The only way you will get the error with a sysadmin role member is if 1) the default database associated with the login is unavailable or 2) the database specified in the connection string is unavailable.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Sofiya Li Tuesday, March 11, 2014 2:32 AM
    Friday, March 7, 2014 2:15 PM
  • You are looking at the problem from the wrong angle. Does this login need to be in sysadmin? I don't think that is a good idea. It may give you fewer error messages, but error messages are necessarily not bad. If some application is not working that needs fixing. But if you have no reports of anything being broken why worry? Do you know for sure that the stranger that is knocking on your door is welcome?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, March 7, 2014 9:52 PM