asked
18456, Severity 14, stage 38 error on SQL Server208 R2
On the SQL Server 2008 R2, I keep getting Login Failed for user'DOMAIN\username' Reason: Failed to open the explicitly specified database' [Client. IP address]. Where to look for and how to resolve it.
@vivekchandra09 The @JohnM comment is actually the answer to this question. In case if the error occurs when attempting to connect to the instance via SSMS then it means that the ***default*** database of the login is either misspelled or, as John said, the database is offline. State 38 points to that pretty clearly. It is also possible that the login was originally created on the different instance and then scripted out to this instance which does not have the database (default database of the login or the explicit database specified in the app connection string, Initial Catalog part of it). In case if the error is associated with the connection string, please check the spelling of the name of the database there.
Thanks Oleg for your explanation. I am ready to accept @JohnM answer.
The database does exist on the server and is online. However, I still get lot of error message related to my domain account not able to open the database (it gives me the ip address, not the actual name of the database.)
@vivekchandra09 How are you trying to access the database, from SSMS or from some app using the connection string? In any case, what is the default database of the login? Is it set to master (usual default) or to the database to which the login cannot connect? If you can connect to the instance via SSMS (using some other login), please check the properties of the database user in question (should be mapped to that login), i.e. find the user under [Database] - Security, right click, properties, check the **Login name** under General, needs to be filled, not blank. State 38 indicates that most likely the login is valid, but something is wrong with the connection parameters. Also possible that the database user mapped to the login lacks permissions to it. Please check the [blog by Aaron Bertrand][1], scroll to the section about state 38. If connecting via SSMS, click on **Options** (**Connect to Server** dialog), click **Connection Properties** tab, check the value in the **Connect to database** box. [1]:
http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx
does that database actually exist on the instance? Is the database online and in MULTI_USER mode?
19 People are following this question.
