Lotus Notes This Database Cannot Be Read Due to an Invalid on Disk Structure
Troubleshooting Mistake 18456
Updated in July 2020 with a few new states
I call up we've all dealt with error 18456, whether information technology be an application unable to access SQL Server, credentials changing over fourth dimension, or a user who tin can't type a password correctly. The trick to troubleshooting this error number is that the mistake bulletin returned to the client or application trying to connect is intentionally vague (the error bulletin is like for most errors, and the land is ever 1). In a few cases, some additional information is included, but for the most role several of these conditions announced the same to the end user. In order to figure out what is really going wrong, you need to have alternative access to the SQL Server and inspect the log for the truthful state in the mistake message. I helped our support squad just today solve a client's 18456 issues – in one case we tracked downward the error log and saw that information technology was state 16, it was easy to determine that their login had been gear up with a default database that had been detached long agone.
In SQL Server 2012, there is a new characteristic chosen "contained databases" – I've blogged about it hither and here. With this feature comes a new layer of security that may creep onto your radar if you lot use this functionality: contained user authentication failures. There are a diversity of things that tin can go wrong hither. If y'all connect with a contained user simply forget to specify a database name, SQL Server volition attempt to qualify y'all as a SQL login, and you will neglect with state 5 (if in that location is no SQL login with that proper noun) or state eight (if there is also a SQL login with the same proper name and the password doesn't match). There is as well a new state 65 which occurs if you have specified the right username and contained database, but entered an incorrect password. The way that the hallmark process works is, if SQL Server doesn't notice your user in the contained database you lot specified, it tries once again at the server level, and then gives upwards (it won't go cheque all the other contained databases in case you friction match there – I hope yous concord that this is a skilful matter). If you don't specify a database in your connection string, and then information technology won't succeed unless – by coincidence – you have a contained user with the same username and password as a server-level login who also has access to your independent database. This is confusing and I strongly recommend against it.
When I see folks struggling with this problem, I near always see the answer betoken to this old (and now horribly formatted) MSDN blog post (run across this other version from MSDN), which has a very brief partial list and a lot of unanswered questions. A newer listing appears here, with some useful info, just it is nevertheless incomplete.
So hither is what I consider a more complete list of all the various states for login failures. I included an instance of 18470 under state 1 for completeness.
| State | Example / Description (note: the verbose bulletin normally has [CLIENT: <IP>] suffix) | |
|---|---|---|
| 1 | Mistake: 18470, Severity: 14, State: 1. Login failed for user '<x>'. Reason: The account is disabled. | |
| State one now occurs when a login is disabled – but really, the error in the log is 18470, not 18456 – because the login is disabled, it doesn't go that far. Meet land seven.Prior to SQL Server 2005, State 1 always appeared in the log for all login failures, making for fun troubleshooting. 🙂 | ||
| 2 | Error: 18456, Severity: fourteen, State: 2. Login failed for user '<ten>'. Reason: Could not find a login matching the proper name provided. | |
| The login (whether using SQL or Windows Authentication) does non be. For Windows Auth, it likely means that the login hasn't explicitly been given admission to SQL Server – which may mean it is not a member of an advisable domain group. It could also mean that you've created a server-level login, mapped a database user with a dissimilar name to that login, and are trying to connect using the user proper name, not the login proper noun. This is the same equally State 5, simply State two indicates that the login attempt came from a remote motorcar. | ||
| 5 | Error: 18456, Severity: 14, State: 5. Login failed for user '<10>'. Reason: Could non discover a login matching the proper name provided. | |
| Like state 2, the login does not be in SQL Server, but the login endeavour came from the local machine. For both country ii and 5, prior to SQL Server 2008, the reason was non included in the error log – but the login failed message. And starting in Denali, for both state two and five, this error tin can happen if you specify the correct username and password for a contained database user, but the wrong (or no) database. Note that if you are trying to connect to a contained database using the connection dialog in SSMS, and you effort to <Browse server…> for the database instead of typing the name explicitly, you will first receive a prompt "Browsing the available databases on the server requires connecting to the server. This may take a few moments. Would you lot like to continue?" If the SQL auth credentials do not also match a login at the server level, you volition then receive an error message, because your contained user does not have access to master.sys.databases. The error bulletin in the UI is, "Failed to connect to server <server>. (Microsoft.SqlServer.ConnectionInfo)Login failed for user '<ten>'. (Microsoft SQL Server, Fault: 18456)." The takeaway here: always specify the database name explicitly in the options tab of the connectedness dialog; exercise not utilise the browse feature. | ||
| 6 | Mistake: 18456, Severity: 14, Country: 6. Login failed for user '<x\y>'. Reason: Attempting to use an NT account name with SQL Server Authentication. | |
| This means you tried to specify SQL authentication just entered a Windows-style login in the form of Domain\Username. Make sure you cull Windows Authentication (and you shouldn't have to enter your domain / username when using Win Auth unless you are using runas /netonly to launch Management Studio). In SQL Server 2012 at least, you will but get land 6 if the domain\username format matches an actual domain and username that SQL Server recognizes. If the domain is invalid or if the username isn't an bodily Windows account in that domain, it will revert to state five (for local attempts) or state ii (for remote attempts), since the login doesn't exist. | ||
| vii | Error: 18456, Severity: fourteen, Country: vii. Login failed for user '<10>'. Reason: An mistake occurred while evaluating the password. | |
| The login is disabled *and* the password is incorrect. This shows that countersign validation occurs get-go, since if the password is correct and the login is disabled, you become error 18470 (meet state one to a higher place). Information technology's possible that your application is sending cached credentials and the password has been changed or reset in the meantime – you may endeavour logging out and logging back in to refresh these credentials. | ||
| 8 | Error: 18456, Severity: 14, State: viii. Login failed for user '<x>'. Reason: Password did not lucifer that for the login provided. | |
| Probably the simplest of all: the password is incorrect (case sEnsiTiVitY catches a lot of folks here). Note that information technology will say "the login provided" fifty-fifty if y'all attempted to connect as a independent database user simply forgot to specify a database, specified the wrong database, or typed the password incorrectly – unless it finds a friction match, SQL Server doesn't have whatever idea you were attempting to use a contained database user. An interesting case here is Docker containers – | ||
| 9 | Mistake: 18456, Severity: xiv, Country: nine. Login failed for user '<x\y>'. | |
| Similar state two, I have not seen this in the wild. Information technology allegedly means that the password violated a countersign policy check, merely I tried creating a login conforming to a weak password policy, strengthened the policy, and I could notwithstanding log in fine. And obviously you can't create a login with, or afterward set, a password that doesn't meet the policy. Permit me know if you've seen it. | ||
| ten | Error: 18456, Severity: 14, State: 10. Login failed for user '<x>'. | |
| This is a rather complicated variation on state 9; as KB #925744 states, this means that countersign checking could not be performed because the login is disabled or locked on the domain controller (note that if SQL Server does not first, it could be because the account that is locked or disabled is the SQL Server service account). No reason or boosted information is provided in the "verbose" message in the fault log. | ||
| 11 12 | Error: 18456, Severity: xiv, State: 11. Login failed for user '<x>'. Reason: Login -based server admission validation failed with an infrastructure error. Check for previous errors. Error: 18456, Severity: 14, State: 12. | |
| States eleven and 12 mean that SQL Server was able to authenticate you, just weren't able to validate with the underlying Windows permissions. It could be that the Windows login has no profile or that permissions could not be checked due to UAC. Try running SSMS as ambassador and/or disabling UAC. Some other reason could be that the domain controller could not be reached. You may need to resort to re-creating the login (encounter this mail from Simon Sabin). Finally, PSS has recently released more than information well-nigh states 11 and 12; see this post for potential scenarios and solutions, and likewise see states 146-149 beneath for changes in SQL Server 2016. | ||
| 13 | Error: 18456, Severity: 14, State: thirteen. Login failed for user '<x>'. Reason: SQL Server service is paused. No new connections can be accustomed at this time. | |
| This state occurs when the SQL Server service has been paused (which you can do easily and even accidentally from the context menu in Object Explorer). | ||
| sixteen | Fault: 18456, Severity: 14, State: xvi. Login failed for user '<x>'. You may likewise see: A connection was successfully established with the server, but and so an fault occurred during the pre-login handshake. | |
| State 16, which only occurs prior to SQL Server 2008, means that the default database was inaccessible. This could be because the database has been removed, renamed, or is offline (it may be ready to AutoClose). This state does not indicate a reason in the mistake log. In 2008 and across, this is reported as state twoscore (see below), with a reason. In SQL Server 2005, this state may also be reported if the user'south default database is online but the database they explicitly requested is not available for the reasons stated above (also see country 27). If yous get the pre-login handshake message, information technology may be because you've disabled SSL on the server. | ||
| xviii | Error: 18456, Severity: 14, State: 18. Login failed for user '<10>'. | |
| Supposedly this indicates that the user needs to change their password. In SQL Server 2005, 2008 R2 and SQL Server 2012, I establish this was raised as error 18488, not 18456; this is because for SQL logins the change password dialog just delays logging in, and is not really a login failure. I doubtable that, like state xvi, this state volition no longer appear in future versions of SQL Server. | ||
| 23 | Fault: 18456, Severity: 14, State: 23. Login failed for user '<x>'. Reason: Access to server validation failed while revalidating the login on the connexion. | |
| There could be a few reasons for country 23. The most common one is that connections are being attempted while the service is existence close downwardly. However if this error occurs and it is not surrounded in the log by letters about SQL Server shutting downward, and there is no companion reason forth with the message, I would await at KB #937745, which implies that this could exist the result of an overloaded server that can't service any additional logins because of connection pooling issues. Finally, if at that place *is* a companion reason, it may be the message indicated to the right, indicating that SQL Server was running as a valid domain account and, upon restarting, information technology can't validate the account because the domain controller is offline or the business relationship is locked or no longer valid. Try changing the service account to LocalSystem until you can sort out the domain bug. | ||
| 27 | Error: 18456, Severity: fourteen, State: 27. Login failed for user '<ten>'. | |
| State 27, like state 16, just occurs prior to SQL Server 2008. It means that the database specified in the connection string has been removed, renamed, or is offline (possibly due to AutoClose) – though in every case I tried, it was reported as state sixteen. This country does not indicate a reason in the error log. In 2008 and onward this is reported every bit state 38 (see below), with a reason. | ||
| 28 | Error: 18456, Severity: 14, Land: 28. Login failed for user '<x>'. | |
| I accept not experienced this result just I suspect it involves overloaded connectedness pooling and connectedness resets. I think you volition only come across state 28 prior to SQL Server 2008. | ||
| 38 | Error: 18456, Severity: xiv, State: 38. Login failed for user '<10>'. Reason: Failed to open the database specified in the login properties. or Reason: Cannot open database "<database>" requested past the login. The login failed. | |
| The database specified in the connectedness string, or selected in the Options > Connection Properties tab of the SSMS connection dialog, is no longer valid or online (it might exist set to AutoClose or the user may simply non have permission). I came across this one time when I typed <default> here instead of picking that option from the list. This is reported as state 27 or country 16 prior to SQL Server 2008. Note that this could also be a symptom of an orphaned login. After establishing mirroring, Availability Groups, log shipping, etc. you may take created a new login or associated a user with a login on the principal database. The database-level user information gets replayed on the secondary servers, merely the login data does not. Everything volition work fine – until you lot take a failover. In this state of affairs, y'all will need to synchronize the login and user information (for ane example, see this script from the late Robert Davis). | ||
| 40 | Error: 18456, Severity: fourteen, Land: 40. Login failed for user '<x>'. Reason: Failed to open the explicitly specified database. | |
| Usually this means the login'south default database is offline (mayhap due to AutoClose) or no longer exists. Resolve by fixing the missing database, or changing the login's default database using Alter LOGIN (for older versions, use sp_defaultdb, which is now deprecated). This is reported equally country 16 prior to SQL Server 2008. | ||
| 46 | Error: 18456, Severity: 14, State: 46. Login failed for user '<x>'. Reason: Failed to open the database configured in the login object while revalidating the login on the connectedness. | |
| State 46 may occur when the login (or login mapping to the service account) does non have a valid database selected as their default database. (I am guessing here but I recall this may occur when the login in question is attempting to perform log aircraft. Once again, just a gauge based on the few conversations I discovered online.) It can likewise occur if the classifier function (Resource Governor) or a logon trigger refers to a database that is offline, no longer exists, or is fix to AutoClose. | ||
| l | Error: 18456, Severity: 14, State: 50. Login failed for user '<x>'. Reason: Current collation did not match the database's collation during connectedness reset. | |
| Equally the message implies, this can occur if the default collation for the login is incompatible with the collation of their default database (or the database explicitly specified in the connection string). It tin can also happen if they are using a client tool like Direction Studio which may, when they have been disconnected, effort to connect to master upon reconnection instead of their default database. | ||
| 51 | Fault: 18456, Severity: 14, State: 51. Login failed for user '<ten>'. Reason: Failed to send an environment alter notification to a log shipping partner node while revalidating the login. | |
| Similar states 11 & 12, this could have to exercise with UAC, or that the domain controller could not exist reached, or that the domain account could non authenticate against the log shipping partner, or that the log aircraft partner was down. Endeavour changing the service account for SQL Server to a known domain or local account, rather than the congenital-in local service accounts, and validating that the partner case is attainable, as well as the database that is beingness requested in the connectedness string and the default database of the login. Note that this could be trigged by the failover partner connection cord attribute, and that the database may no longer exist or may be offline, single user, etc. | ||
| 56 | Mistake: 18456, Severity: xiv, State: 56. Login failed for user '<10>'. Reason: Failed attempted retry of a process token validation. | |
| Country 56 is not very common – again, like states 11 & 12, this could have to practise with UAC, or that the domain controller could not be reached. Try changing the service account for SQL Server to a known domain or local account, rather than the congenital-in local service accounts. | ||
| 58 | Error: 18456, Severity: 14, State: 58. Login failed for user '<ten>'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows hallmark only. | |
| State 58 occurs when SQL Server is ready to apply Windows Authentication simply, and a customer attempts to log in using SQL Hallmark. It can also occur when SIDs do not match (in which instance the error text might exist slightly different). | ||
| 62 | Error: 18456, Severity: 14, Country: 62. Login failed for user '<x>'. | |
| State 62 occurs when a Windows Hallmark account tries to access a contained database, and the independent database exists, but the SIDs practise not match. | ||
| 65 | Fault: 18456, Severity: 14, State: 65. Login failed for user '<x>'. Reason: Password did not match that for the user provided. [Database: '<ten>'] | |
| Contained user exists, the database is correct, but the countersign is invalid. This tin can also happen if you use a SQL login to connect to a independent database that has a contained user with the aforementioned name but a dissimilar password (one of several reasons this is not recommended). | ||
| 102 103 … 110 111 | Mistake: 18456, Severity: 14, Country: 102. Error: 18456, Severity: xiv, State: 103. Error: 18456, Severity: 14, Land: 104. Error: 18456, Severity: xiv, State: 105. Error: 18456, Severity: 14, Land: 106. Error: 18456, Severity: 14, State: 107. Error: 18456, Severity: fourteen, State: 108. Error: 18456, Severity: 14, State: 109. Mistake: 18456, Severity: 14, State: 110. Fault: 18456, Severity: xiv, State: 111. | |
| Documented past Microsoft as Azure Active Directory login failures. | ||
| 122 123 124 | Error: 18456, Severity: 14, Land: 122. Error: 18456, Severity: 14, Land: 123. Error: 18456, Severity: fourteen, Country: 124. | |
| Co-ordinate to Microsoft, these indicate a bare or missing username and/or password. | ||
| 126 | Error: 18456, Severity: xiv, State: 126. | |
| The docs say "Database requested by user does not exist." But it's not articulate why you lot would get 126 instead of, say, 38 or twoscore. | ||
| 132 133 | Mistake: 18456, Severity: fourteen, State: 132. Error: 18456, Severity: 14, State: 133. | |
| Documented by paschott and past Microsoft as Azure Active Directory login failures. | ||
| 146 147 148 149 | Error: 18456, Severity: 14, Country: 146. Login failed for user '< Windows auth login>'. Reason: Token-based server admission validation failed with an infrastructure error. Login lacks Connect SQL permission. Error: 18456, Severity: fourteen, Land: 147. Mistake: 18456, Severity: 14, State: 148. Error: 18456, Severity: 14, State: 149. | |
| These states supercede states 11 and 12 above, only only in SQL Server 2016 or better. The goal was to brand the actual underlying issue easier for the sysadmin to diagnose betwixt SQL auth and Windows auth logins, and between connect and endpoint permissions (all without giving whatsoever farther info to the user trying to log in). For more than details, see the latter function of this post. | ||
I am sure I missed some, but I hope that is a helpful summary of nearly of the 18456 errors you are likely to come beyond. Delight allow me know if you lot spot whatsoever inaccuracies or if you know of whatsoever states (or reasons) that I missed.
If you are using contained databases, there volition be a little actress complication in solving login failures, especially if you attempt to create independent users with the same proper noun as server-level logins. This is a ball of wax you just probably don't desire to get into…
Cheers to Jonathan Kehayias (weblog | twitter), Bob Ward (CSS web log | twitter), and Rick Byham for input and sanity checking.
Aaron Bertrand
I am a passionate technologist with industry feel dating back to Archetype ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at SQLPerformance and MSSQLTips, and have had the honour of speaking at more than conferences than I tin can recollect. In not-tech life, I am a father of 2, a huge hockey and football fan, and my pronouns are he/him.
Source: https://sqlblog.org/2020/07/28/troubleshooting-error-18456
0 Response to "Lotus Notes This Database Cannot Be Read Due to an Invalid on Disk Structure"
Post a Comment