Authentication Error. Server Returned Error [sys/temp] Server Error - Please Try Again Later.
Troubleshooting Error 18456
Updated in July 2020 with a few new states
I recollect we've all dealt with error 18456, whether information technology be an awarding unable to admission SQL Server, credentials changing over time, or a user who can't type a password correctly. The play a joke on to troubleshooting this error number is that the error bulletin returned to the client or awarding trying to connect is intentionally vague (the fault bulletin is similar for most errors, and the state is always 1). In a few cases, some additional information is included, but for the most part several of these weather condition appear the same to the finish user. In guild to figure out what is really going incorrect, you need to accept alternative access to the SQL Server and inspect the log for the truthful country in the error message. I helped our support squad just today solve a client'southward 18456 bug – once we tracked downward the error log and saw that it was state 16, it was easy to determine that their login had been set up with a default database that had been detached long ago.
In SQL Server 2012, in that location is a new feature called "contained databases" – I've blogged about it hither and hither. With this characteristic comes a new layer of security that may creep onto your radar if you utilise this functionality: independent user hallmark failures. In that location are a variety of things that tin can go wrong here. If you connect with a contained user but forget to specify a database name, SQL Server will attempt to qualify you every bit a SQL login, and you volition neglect with state 5 (if there is no SQL login with that proper name) or state eight (if there is also a SQL login with the same name and the countersign doesn't match). There is also a new country 65 which occurs if you have specified the correct username and contained database, but entered an incorrect countersign. The way that the authentication process works is, if SQL Server doesn't find your user in the contained database you specified, it tries again at the server level, so gives upward (it won't go bank check all the other contained databases in example you lucifer at that place – I promise you agree that this is a expert thing). If you don't specify a database in your connection string, then it won't succeed unless – by coincidence – y'all have a contained user with the aforementioned username and password as a server-level login who also has access to your independent database. This is disruptive and I strongly recommend against information technology.
When I see folks struggling with this problem, I almost always meet the respond point to this former (and now horribly formatted) MSDN web log mail service (see this other version from MSDN), which has a very cursory partial list and a lot of unanswered questions. A newer list appears here, with some useful info, but it is still 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 country 1 for abyss.
| State | Example / Clarification (note: the verbose message usually has [CLIENT: <IP>] suffix) | |
|---|---|---|
| 1 | Error: 18470, Severity: xiv, State: 1. Login failed for user '<x>'. Reason: The account is disabled. | |
| State i now occurs when a login is disabled – but actually, the error in the log is 18470, not 18456 – because the login is disabled, it doesn't go that far. See state vii.Prior to SQL Server 2005, State i always appeared in the log for all login failures, making for fun troubleshooting. 🙂 | ||
| 2 | Error: 18456, Severity: 14, Country: 2. Login failed for user '<x>'. Reason: Could not find a login matching the proper noun provided. | |
| The login (whether using SQL or Windows Authentication) does not exist. For Windows Auth, it likely means that the login hasn't explicitly been given access to SQL Server – which may mean it is not a member of an advisable domain group. It could as well mean that you've created a server-level login, mapped a database user with a different name to that login, and are trying to connect using the user proper noun, non the login name. This is the aforementioned as State v, but Land 2 indicates that the login attempt came from a remote machine. | ||
| 5 | Error: 18456, Severity: 14, State: 5. Login failed for user '<ten>'. Reason: Could not find a login matching the proper noun provided. | |
| Like state 2, the login does not exist in SQL Server, but the login attempt came from the local machine. For both state two and 5, prior to SQL Server 2008, the reason was non included in the error log – simply the login failed message. And starting in Denali, for both land 2 and 5, this fault tin can happen if you specify the correct username and countersign 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 lot try to <Browse server…> for the database instead of typing the name explicitly, yous will first receive a prompt "Browsing the bachelor databases on the server requires connecting to the server. This may have a few moments. Would y'all similar to continue?" If the SQL auth credentials do not likewise friction match a login at the server level, you will then receive an error message, because your contained user does not have admission to primary.sys.databases. The fault message in the UI is, "Failed to connect to server <server>. (Microsoft.SqlServer.ConnectionInfo)Login failed for user '<x>'. (Microsoft SQL Server, Error: 18456)." The takeaway here: always specify the database proper name explicitly in the options tab of the connexion dialog; do not use the scan feature. | ||
| 6 | Error: 18456, Severity: 14, Land: 6. Login failed for user '<x\y>'. Reason: Attempting to employ an NT account name with SQL Server Authentication. | |
| This means you tried to specify SQL hallmark but entered a Windows-style login in the course of Domain\Username. Make certain you lot choose Windows Authentication (and you shouldn't accept to enter your domain / username when using Win Auth unless yous are using runas /netonly to launch Management Studio). In SQL Server 2012 at to the lowest degree, y'all volition only get country 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 actual Windows business relationship in that domain, it volition revert to state 5 (for local attempts) or country 2 (for remote attempts), since the login doesn't exist. | ||
| 7 | Error: 18456, Severity: 14, Land: 7. Login failed for user '<10>'. Reason: An mistake occurred while evaluating the password. | |
| The login is disabled *and* the password is wrong. This shows that password validation occurs first, since if the password is correct and the login is disabled, you get error 18470 (run into state 1 above). It's possible that your application is sending cached credentials and the password has been changed or reset in the concurrently – you may try logging out and logging back in to refresh these credentials. | ||
| viii | Fault: 18456, Severity: 14, Country: 8. Login failed for user '<ten>'. Reason: Password did not match that for the login provided. | |
| Probably the simplest of all: the password is incorrect (case sEnsiTiVitY catches a lot of folks hither). Note that it will say "the login provided" even if you attempted to connect as a independent database user only forgot to specify a database, specified the incorrect database, or typed the password incorrectly – unless information technology finds a match, SQL Server doesn't have whatever idea you were attempting to use a independent database user. An interesting case here is Docker containers – | ||
| 9 | Mistake: 18456, Severity: fourteen, State: ix. Login failed for user '<x\y>'. | |
| Like country 2, I have not seen this in the wild. It allegedly means that the password violated a countersign policy cheque, but I tried creating a login conforming to a weak password policy, strengthened the policy, and I could withal log in fine. And plain you tin can't create a login with, or later ready, a password that doesn't meet the policy. Let me know if you've seen it. | ||
| 10 | Error: 18456, Severity: 14, Land: 10. Login failed for user '<x>'. | |
| This is a rather complicated variation on state 9; every bit KB #925744 states, this means that password checking could not be performed because the login is disabled or locked on the domain controller (note that if SQL Server does non showtime, 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 error log. | ||
| xi 12 | Error: 18456, Severity: 14, State: xi. Login failed for user '<10>'. Reason: Login -based server access validation failed with an infrastructure error. Check for previous errors. Error: 18456, Severity: xiv, State: 12. | |
| States eleven and 12 mean that SQL Server was able to authenticate you, simply weren't able to validate with the underlying Windows permissions. It could exist that the Windows login has no profile or that permissions could not exist checked due to UAC. Endeavour running SSMS as administrator and/or disabling UAC. Some other reason could be that the domain controller could not be reached. You may demand to resort to re-creating the login (see this post from Simon Sabin). Finally, PSS has recently released more data about states 11 and 12; see this mail service for potential scenarios and solutions, and besides come across states 146-149 below for changes in SQL Server 2016. | ||
| xiii | Fault: 18456, Severity: 14, Country: 13. Login failed for user '<x>'. Reason: SQL Server service is paused. No new connections can be accepted at this time. | |
| This state occurs when the SQL Server service has been paused (which you tin practice easily and even accidentally from the context menu in Object Explorer). | ||
| 16 | Error: 18456, Severity: fourteen, Land: 16. Login failed for user '<x>'. You may too see: A connectedness was successfully established with the server, but then 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 exist because the database has been removed, renamed, or is offline (it may exist prepare to AutoClose). This country does not indicate a reason in the error log. In 2008 and beyond, this is reported as state 40 (see below), with a reason. In SQL Server 2005, this state may also be reported if the user'southward default database is online but the database they explicitly requested is not bachelor for the reasons stated higher up (likewise see state 27). If you get the pre-login handshake bulletin, it may exist because you've disabled SSL on the server. | ||
| xviii | Error: 18456, Severity: xiv, 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 found this was raised as fault 18488, non 18456; this is considering for SQL logins the change password dialog just delays logging in, and is not actually a login failure. I suspect that, similar state 16, this state will no longer announced in time to come versions of SQL Server. | ||
| 23 | Fault: 18456, Severity: 14, State: 23. Login failed for user '<x>'. Reason: Admission to server validation failed while revalidating the login on the connection. | |
| There could exist a few reasons for country 23. The most common one is that connections are being attempted while the service is existence shut down. Even so if this error occurs and it is not surrounded in the log by messages almost SQL Server shutting down, and in that location is no companion reason along with the message, I would expect at KB #937745, which implies that this could be the outcome of an overloaded server that can't service whatsoever additional logins because of connection pooling issues. Finally, if there *is* a companion reason, it may be the message indicated to the right, indicating that SQL Server was running every bit a valid domain business relationship and, upon restarting, it tin't validate the account because the domain controller is offline or the account is locked or no longer valid. Try changing the service account to LocalSystem until you can sort out the domain issues. | ||
| 27 | Fault: 18456, Severity: 14, State: 27. Login failed for user '<x>'. | |
| Land 27, similar state 16, only occurs prior to SQL Server 2008. Information technology ways 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, information technology was reported equally land sixteen. This land does not indicate a reason in the error log. In 2008 and onward this is reported equally state 38 (see beneath), with a reason. | ||
| 28 | Mistake: 18456, Severity: xiv, State: 28. Login failed for user '<x>'. | |
| I have not experienced this effect only I doubtable it involves overloaded connectedness pooling and connection resets. I think you will only see country 28 prior to SQL Server 2008. | ||
| 38 | Error: 18456, Severity: fourteen, Land: 38. Login failed for user '<x>'. Reason: Failed to open the database specified in the login backdrop. or Reason: Cannot open database "<database>" requested by the login. The login failed. | |
| The database specified in the connection string, or selected in the Options > Connectedness Backdrop tab of the SSMS connection dialog, is no longer valid or online (information technology might be set to AutoClose or the user may simply not accept permission). I came across this once when I typed <default> here instead of picking that choice from the list. This is reported equally state 27 or country xvi prior to SQL Server 2008. Note that this could also be a symptom of an orphaned login. Subsequently establishing mirroring, Availability Groups, log shipping, etc. you may have created a new login or associated a user with a login on the primary database. The database-level user data gets replayed on the secondary servers, but the login information does not. Everything volition work fine – until yous have a failover. In this situation, you will need to synchronize the login and user data (for i example, see this script from the late Robert Davis). | ||
| forty | Error: 18456, Severity: 14, Land: xl. Login failed for user '<x>'. Reason: Failed to open the explicitly specified database. | |
| Ordinarily this ways the login'south default database is offline (perhaps due to AutoClose) or no longer exists. Resolve by fixing the missing database, or changing the login'south default database using Modify LOGIN (for older versions, use sp_defaultdb, which is now deprecated). This is reported equally state 16 prior to SQL Server 2008. | ||
| 46 | Error: 18456, Severity: 14, Land: 46. Login failed for user '<x>'. Reason: Failed to open the database configured in the login object while revalidating the login on the connexion. | |
| State 46 may occur when the login (or login mapping to the service business relationship) does not have a valid database selected as their default database. (I am guessing here simply I think this may occur when the login in question is attempting to perform log aircraft. Again, merely a guess based on the few conversations I discovered online.) It tin also occur if the classifier function (Resource Governor) or a logon trigger refers to a database that is offline, no longer exists, or is set up to AutoClose. | ||
| 50 | Error: 18456, Severity: xiv, State: 50. Login failed for user '<x>'. Reason: Electric current collation did not match the database'due south collation during connexion reset. | |
| Every bit the bulletin 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 cord). It tin also happen if they are using a customer tool similar Management Studio which may, when they take been asunder, try to connect to main upon reconnection instead of their default database. | ||
| 51 | Mistake: 18456, Severity: 14, Land: 51. Login failed for user '<x>'. Reason: Failed to send an environment change notification to a log aircraft partner node while revalidating the login. | |
| Like 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 confronting the log shipping partner, or that the log shipping partner was downwardly. 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 instance is accessible, equally well equally the database that is existence requested in the connection cord and the default database of the login. Note that this could be trigged by the failover partner connection string attribute, and that the database may no longer exist or may be offline, single user, etc. | ||
| 56 | Error: 18456, Severity: 14, Country: 56. Login failed for user '<x>'. Reason: Failed attempted retry of a procedure token validation. | |
| Land 56 is not very common – again, like states eleven & 12, this could take to exercise with UAC, or that the domain controller could non be reached. Try irresolute the service account for SQL Server to a known domain or local account, rather than the built-in local service accounts. | ||
| 58 | Error: 18456, Severity: 14, Country: 58. Login failed for user '<10>'. Reason: An effort to login using SQL hallmark failed. Server is configured for Windows authentication only. | |
| State 58 occurs when SQL Server is ready to employ Windows Authentication merely, and a client attempts to log in using SQL Hallmark. It can also occur when SIDs do not match (in which case the fault text might exist slightly different). | ||
| 62 | Error: 18456, Severity: xiv, State: 62. Login failed for user '<x>'. | |
| State 62 occurs when a Windows Authentication account tries to access a contained database, and the contained database exists, merely the SIDs practice non match. | ||
| 65 | Error: 18456, Severity: fourteen, State: 65. Login failed for user '<x>'. Reason: Password did non match that for the user provided. [Database: '<x>'] | |
| Contained user exists, the database is correct, but the password is invalid. This tin also happen if y'all apply a SQL login to connect to a contained database that has a contained user with the same name only a dissimilar countersign (ane of several reasons this is non recommended). | ||
| 102 103 … 110 111 | Mistake: 18456, Severity: xiv, Land: 102. Error: 18456, Severity: fourteen, Country: 103. Fault: 18456, Severity: 14, State: 104. Mistake: 18456, Severity: 14, State: 105. Error: 18456, Severity: fourteen, State: 106. Error: 18456, Severity: 14, State: 107. Error: 18456, Severity: 14, Country: 108. Error: 18456, Severity: 14, State: 109. Error: 18456, Severity: 14, State: 110. Error: 18456, Severity: 14, State: 111. | |
| Documented past Microsoft every bit Azure Active Directory login failures. | ||
| 122 123 124 | Error: 18456, Severity: 14, State: 122. Error: 18456, Severity: xiv, Land: 123. Error: 18456, Severity: 14, State: 124. | |
| According to Microsoft, these indicate a bare or missing username and/or password. | ||
| 126 | Fault: 18456, Severity: xiv, State: 126. | |
| The docs say "Database requested by user does not exist." But it'southward not clear why yous would get 126 instead of, say, 38 or xl. | ||
| 132 133 | Error: 18456, Severity: 14, Land: 132. Mistake: 18456, Severity: 14, State: 133. | |
| Documented by paschott and by Microsoft as Azure Agile Directory login failures. | ||
| 146 147 148 149 | Fault: 18456, Severity: 14, State: 146. Login failed for user '< Windows auth login>'. Reason: Token-based server access validation failed with an infrastructure error. Login lacks Connect SQL permission. Mistake: 18456, Severity: 14, State: 147. Mistake: 18456, Severity: 14, State: 148. Error: 18456, Severity: 14, State: 149. | |
| These states replace states 11 and 12 higher up, but but in SQL Server 2016 or amend. The goal was to make the bodily underlying issue easier for the sysadmin to diagnose between SQL auth and Windows auth logins, and betwixt connect and endpoint permissions (all without giving any farther info to the user trying to log in). For more details, see the latter part of this mail service. | ||
I am sure I missed some, but I hope that is a helpful summary of well-nigh of the 18456 errors you are likely to come up across. Please let me know if you lot spot whatever inaccuracies or if you know of any states (or reasons) that I missed.
If you are using contained databases, there volition exist a little extra complexity in solving login failures, especially if y'all try to create independent users with the same name as server-level logins. This is a ball of wax you just probably don't want to go into…
Thanks to Jonathan Kehayias (web log | twitter), Bob Ward (CSS blog | twitter), and Rick Byham for input and sanity checking.
Aaron Bertrand
I am a passionate technologist with industry experience dating dorsum to Classic ASP and SQL Server vi.5. I am a long-time Microsoft MVP, write at SQLPerformance and MSSQLTips, and have had the honor of speaking at more conferences than I can think. In non-tech life, I am a father of two, 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 "Authentication Error. Server Returned Error [sys/temp] Server Error - Please Try Again Later."
إرسال تعليق