I think we've all dealt with error 18456, whether it be an application unable to access SQL Server, credentials changing over time, or a user who can't type a password correctly.
The trick to troubleshooting this error number is that the error message returned to the client or application trying to connect is intentionally vague – the error message 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 conditions appear the same to the end user. The reason for this is to be careful not to disclose too much information to a would-be attacker.
But this makes troubleshooting hard.
TL;DR: Check the SQL Server errorlog for the state and any additional context, then consult the list below.
In order to figure out what is really going wrong, you need to have alternative access to the SQL Server (either through a different login or direct physical access) and inspect the errorlog for the true state in the error message. I helped our support team just today solve a client's issue – once we tracked down the errorlog and saw that it was state 16, we knew their login had been assigned a default database that had since been detached. Easy fix.
When I see folks struggling with this problem, I almost always see them pointed to this old MSDN blog post (or this other version from MSDN), which has a very brief partial list and a lot of unanswered questions. A newer list appears here, with some useful info, but it is still incomplete.
So here is what I consider a more complete listing 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 message usually has [CLIENT: <IP>] suffix) |
|
|---|---|---|
| 1 |
Error: 18470, Severity: 14, State: 1. Login failed for user '<x>'. Reason: The account is disabled. |
|
| State 1 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 get that far. See state 7.Prior to SQL Server 2005, State 1 always appeared in the log for all login failures, making for fun troubleshooting. 🙂 | ||
| 2 |
Error: 18456, Severity: 14, State: 2. Login failed for user '<x>'. Reason: Could not find a login matching the name 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 appropriate domain group. It could also 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 name, not the login name. This is the same as State 5, but State 2 indicates that the login attempt came from a remote machine. | ||
| 5 |
Error: 18456, Severity: 14, State: 5. Login failed for user '<x>'. Reason: Could not find a login matching the name provided. |
|
| Like state 2, the login does not exist in SQL Server, but the login attempt came from the local machine. For both state 2 and 5, prior to SQL Server 2008, the reason was not included in the error log – just the login failed message. And starting in Denali, for both state 2 and 5, this error 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 try 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 like to continue?" If the SQL auth credentials do not also match a login at the server level, you will then receive an error message, because your contained user does not have access to master.sys.databases. The error 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 name explicitly in the options tab of the connection dialog; do not use the browse feature. | ||
| 6 |
Error: 18456, Severity: 14, State: 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 but entered a Windows-style login in the form of Domain\Username. Make sure you choose 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 only get state 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 account in that domain, it will revert to state 5 (for local attempts) or state 2 (for remote attempts), since the login doesn't exist. | ||
| 7 |
Error: 18456, Severity: 14, State: 7. Login failed for user '<x>'. Reason: An error occurred while evaluating the password. |
|
| The login is disabled *and* the password is incorrect. This shows that password validation occurs first, since if the password is correct and the login is disabled, you get error 18470 (see state 1 above). It's possible that your application is sending cached credentials and the password has been changed or reset in the meantime – you may try logging out and logging back in to refresh these credentials. | ||
| 8 |
Error: 18456, Severity: 14, State: 8. Login failed for user '<x>'. 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 here). Note that it will say "the login provided" even if you attempted to connect as a contained database user but forgot to specify a database, specified the wrong database, or typed the password incorrectly – unless it finds a match, SQL Server doesn't have any idea you were attempting to use a contained database user. An interesting case here is Docker containers – |
||
| 9 |
Error: 18456, Severity: 14, State: 9. Login failed for user '<x\y>'. |
|
| Like state 2, I have not seen this in the wild. It allegedly means that the password violated a password policy check, but I tried creating a login conforming to a weak password policy, strengthened the policy, and I could still log in fine. And obviously you can't create a login with, or later set, a password that doesn't meet the policy. Let me know if you've seen it. | ||
| 10 |
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 password checking could not be performed because the login is disabled or locked on the domain controller (note that if SQL Server does not start, it could be because the account that is locked or disabled is the SQL Server service account). No reason or additional information is provided in the "verbose" message in the error log. | ||
| 11 12 |
Error: 18456, Severity: 14, State: 11. Login failed for user '<x>'. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors. Error: 18456, Severity: 14, State: 12. Login failed for user '<x>'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. |
|
| States 11 and 12 mean that SQL Server was able to authenticate you, but 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 administrator and/or disabling UAC. Another reason could be that the domain controller could not be reached. You may need to resort to re-creating the login (see this post from Simon Sabin). Finally, PSS has recently released more information about states 11 and 12; see this post for potential scenarios and solutions, and also see states 146-149 below for changes in SQL Server 2016. | ||
| 13 |
Error: 18456, Severity: 14, State: 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 can do easily and even accidentally from the context menu in Object Explorer). | ||
| 16 |
Error: 18456, Severity: 14, State: 16. Login failed for user '<x>'. You may also see: A connection was successfully established with the server, but then an error 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 set to AutoClose). This state 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's default database is online but the database they explicitly requested is not available for the reasons stated above (also see state 27). If you get the pre-login handshake message, it may be because you've disabled SSL on the server. | ||
| 18 |
Error: 18456, Severity: 14, State: 18. Login failed for user '<x>'. |
|
| 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 error 18488, not 18456; this is because for SQL logins the change password dialog just delays logging in, and is not actually a login failure. I suspect that, like state 16, this state will no longer appear in future versions of SQL Server. | ||
| 23 |
Error: 18456, Severity: 14, State: 23. Login failed for user '<x>'. Reason: Access to server validation failed while revalidating the login on the connection. |
|
| There could be a few reasons for state 23. The most common one is that connections are being attempted while the service is being shut down. However if this error occurs and it is not surrounded in the log by messages about SQL Server shutting down, and there is no companion reason along with the message, I would look at KB #937745, which implies that this could be the result of an overloaded server that can't service any 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 as a valid domain account and, upon restarting, it can'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 |
Error: 18456, Severity: 14, State: 27. Login failed for user '<x>'. |
|
| State 27, like state 16, only 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 16. This state does not indicate a reason in the error log. In 2008 and onward this is reported as state 38 (see below), with a reason. | ||
| 28 |
Error: 18456, Severity: 14, State: 28. Login failed for user '<x>'. |
|
| I have not experienced this issue but I suspect it involves overloaded connection pooling and connection resets. I think you will only see state 28 prior to SQL Server 2008. | ||
| 38 |
Error: 18456, Severity: 14, State: 38. Login failed for user '<x>'. Reason: Failed to open the database specified in the login properties. 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 > Connection Properties tab of the SSMS connection dialog, is no longer valid or online (it might be set to AutoClose or the user may simply not have permission). I came across this once when I typed <default> here instead of picking that option from the list. This is reported as state 27 or state 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 have created a new login or associated a user with a login on the primary database. The database-level user information gets replayed on the secondary servers, but the login information does not. Everything will work fine – until you have a failover. In this situation, you will need to synchronize the login and user information (for one example, see this script from the late Robert Davis). |
||
| 40 |
Error: 18456, Severity: 14, State: 40. Login failed for user '<x>'. Reason: Failed to open the explicitly specified database. |
|
| Usually this means the login's default database is offline (perhaps 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 as state 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 connection. |
|
| State 46 may occur when the login (or login mapping to the service account) does not have a valid database selected as their default database. (I am guessing here but I think this may occur when the login in question is attempting to perform log shipping. Again, just a guess based on the few conversations I discovered online.) It can 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 to AutoClose. | ||
| 50 |
Error: 18456, Severity: 14, State: 50. Login failed for user '<x>'. Reason: Current collation did not match the database's collation during connection reset. |
|
| As 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 can also happen if they are using a client tool like Management Studio which may, when they have been disconnected, try to connect to master upon reconnection instead of their default database. | ||
| 51 |
Error: 18456, Severity: 14, State: 51. Login failed for user '<x>'. Reason: Failed to send an environment change notification to a log shipping partner node while revalidating the login. |
|
| Like states 11 & 12, this could have to do with UAC, or that the domain controller could not be reached, or that the domain account could not authenticate against the log shipping partner, or that the log shipping partner was down. Try changing the service account for SQL Server to a known domain or local account, rather than the built-in local service accounts, and validating that the partner instance is accessible, as well as the database that is being requested in the connection string 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, State: 56. Login failed for user '<x>'. Reason: Failed attempted retry of a process token validation. |
|
| State 56 is not very common – again, like states 11 & 12, this could have to do 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 built-in local service accounts. | ||
| 58 |
Error: 18456, Severity: 14, State: 58. Login failed for user '<x>'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. |
|
| State 58 occurs when SQL Server is set to use Windows Authentication only, and a client attempts to log in using SQL Authentication. It can also occur when SIDs do not match (in which case the error text might be slightly different). | ||
| 62 |
Error: 18456, Severity: 14, 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, but the SIDs do not match. | ||
| 65 |
Error: 18456, Severity: 14, State: 65. Login failed for user '<x>'. Reason: Password did not match that for the user provided. [Database: '<x>'] |
|
| Contained user exists, the database is correct, but the password is invalid. This can also happen if you use a SQL login to connect to a contained database that has a contained user with the same name but a different password (one of several reasons this is not recommended). | ||
| 102 103 … 110 111 |
Error: 18456, Severity: 14, State: 102. Error: 18456, Severity: 14, State: 103. Error: 18456, Severity: 14, State: 104. Error: 18456, Severity: 14, State: 105. Error: 18456, Severity: 14, State: 106. Error: 18456, Severity: 14, State: 107. Error: 18456, Severity: 14, State: 108. Error: 18456, Severity: 14, State: 109. Error: 18456, Severity: 14, State: 110. Error: 18456, Severity: 14, State: 111. |
|
| Documented by Microsoft as Azure Active Directory login failures. | ||
| 122 123 124 |
Error: 18456, Severity: 14, State: 122. Error: 18456, Severity: 14, State: 123. Error: 18456, Severity: 14, State: 124. |
|
| According to Microsoft, these indicate a blank or missing username and/or password. | ||
| 126 | Error: 18456, Severity: 14, State: 126. | |
| The docs say "Database requested by user does not exist." But it's not clear why you would get 126 instead of, say, 38 or 40. | ||
| 132 133 |
Error: 18456, Severity: 14, State: 132. Error: 18456, Severity: 14, State: 133. |
|
| Documented by paschott and by Microsoft as Azure Active Directory login failures. | ||
| 146 147 148 149 |
Error: 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. Error: 18456, Severity: 14, State: 147. Login failed for user '<SQL auth login>'. Reason: Login-based server access validation failed with an infrastructure error. Login lacks Connect SQL permission. Error: 18456, Severity: 14, State: 148. Login failed for user '<Windows auth login>'. Reason: Token-based server access validation failed with an infrastructure error. Login lacks connect endpoint permission. Error: 18456, Severity: 14, State: 149. Login failed for user '<SQL auth login>'. Reason: Login-based server access validation failed with an infrastructure error. Login lacks connect endpoint permission. |
|
| These states replace states 11 and 12 above, but only in SQL Server 2016 or better. The goal was to make the actual underlying issue easier for the sysadmin to diagnose between SQL auth and Windows auth logins, and between connect and endpoint permissions (all without giving any further info to the user trying to log in). For more details, see the latter part of this post. | ||
I am sure I missed some, but I hope that is a helpful summary of most of the 18456 errors you are likely to come across. Please let me know if you spot any inaccuracies or if you know of any states (or reasons) that I missed.
If you are using contained databases, there will be a little extra complication in solving login failures, especially if you try to create contained users with the same name as server-level logins. This is a ball of wax you just probably don't want to get into…
Thanks to Jonathan Kehayias, Bob Ward, and Rick Byham for their input and sanity checking.
I ran into a case where I received the dreaded 18456 error. I could connect with a domain login, but he application account, which was a contained database account could not connect. Creating a new constained account did not work either.
The key point is that this was post-migration to a new AlwaysOn 2014 cluster. The problem turned out to be that I did not check to see that the new server was set to allow SQL Logins. Dope slap.
Thanks. I will give that a try.
@JohnL have you had them highlight every appearance of that instance in the connect to dialog and press [Delete]?
I have run into a case where a database name is being saved under the Connection Properties…Connect to database but this database doesn't exist on the server. Despite the developer's best efforts to remove this database name (Reset all), it persists. Thoughts?
thank you so much Matthew Darwin.. article help me lot to resolved the issue..
I am new to both SQL and Sharepoint and the error is occuring between the two. Using SQL Server 2008 R2.
The initial error I was trying to troubleshoot is…
"Login failed for user "sharepoint admin". Reason: An exception was raised while revalidating the login on the connection. Check for previous errors.
I found that at the exact second this error occurs (every few hours) I also get…
"Error: 18456, Severity: 14, State:29" with no other information
The only information I've found is that State 29 is a generic state that just informs that an exception has occured that doesn't fit into any of the defined states.
Jeff, that's a new one to me, sorry. If you do find anything more out, let me know. Anything special about your instance, e.g. is it clustered, using AGs, have contained databases, logon triggers, etc.? What is @@version and edition? What is the full text of both error messages from the SQL Server error log?
Looking for Severity 14, State 29. I can't find any information on this error anywhere.
Thank~
my blog copy~
http://blog.naver.com/waws01
Gary Mazzone, to solve 18456 error with state 5
remove the 'CORP\garymazzone'from the security.
Just use NT AUTHORITY\SYSTEM assign the master as default database and in Server Role select sysadmin
Matthew interesting, thanks, I will be sure to incorporate that info next time I work on this post.
Thanks for the quick response; just figured out the issue. I had been testing some endpoints on this particular server and so had created a named endpoint and subsequently deleted it after finishing testing. I'd forgotten that that results in the public role being revoked from the TSQL Default TCP endpoint and so the remote connections were being blocked.
Running GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO public resolved the issue.
Matthew that's the first time I've seen state 12 with a SQL auth login. I really don't believe it's related to UAC in that case, but I don't have a better answer for you off the top of my head. Can you see if the same thing happens for a *different* SQL login with the same permissions / default database etc. from the same remote machine?
I'm encountering Error: 18456, Severity: 14, State: 12. when connecting remotely to a named instance of SQL Server using a SQL Login. The login can connect fine when run locally on the server.
The server is running Windows 2012 R2, and SQL Server is 2012 SP2. Windows logins are able to connect remotely without issue.
Is this going to be UAC related or something else?
Arron a question I have a domain group called DBAdmin and I am a member. Whe I attemt to log in using windows auth I get the 18456 error with state 5. There are no contained dbs in the instance:
Login failed for user 'CORP\garymazzone'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
I noticed a
Error: 18456, Severity: 14, State: 11
Login failed for user 'domain\account'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: xxx.xxx.xxx.xxx]
( SQL2008R2 sp2 CU4 on win2008R2 )
Turns out this login didn't have any auth to get to this sql instance.
———————————————————————-
Hi Aaron,
Thanks for your post. It is very useful but I am still struggling with setting the password in T-SQL. Whenever I do so, I get:
2013-09-02 22:43:24.86 Logon Error: 18456, Severity: 14, State: 8.
2013-09-02 22:43:24.86 Logon Login failed for user 'testLogin'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]
If I then reset it in MSSMS from Login > Properties it works fine. I wonder if you know what the problem could be?
My T-SQL:
USE [master]
GO
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'testLogin')
CREATE LOGIN [testLogin] WITH PASSWORD='password', DEFAULT_DATABASE=[dbTest], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER LOGIN [testLogin] ENABLE
GO
USE [dbTest]
GO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'testLogin')
CREATE USER [testLogin] FOR LOGIN [testLogin] WITH DEFAULT_SCHEMA=[dbo]
GO
sp_addrolemember 'db_owner', 'testLogin';
GO
ALTER AUTHORIZATION ON SCHEMA::db_owner TO testLogin
GO
Many thanks,
Jo
Many thanks for this page Aaron, it's very useful!
my account was not disable . but i recive this errorr with state1 please help me
Thank you for this post it has been very useful.
Also I would like to add some extra information about State 58. When SQL authentication fails due to not supplying a user name you get this very misleading error state in the server log.The full message is:
Login failed for user ". Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.
Error 18456, Severity: 14, State: 58
In fact the SQL Server database is correctly configured for mixed mode authentication.
I suspect this is a general problem when the user name is not supplied for SQL authentication but I have only tested it with ODBC. If you use the ODBC function SQLDriverConnect against a SQL Server database correctly configured for mixed mode authentication with an ODBC DSN set to use SQL authentication but do not supply a username then you get the error described above.
I have this error at the client "Msg 18456, Level 14, State 1" and nothing logged in SQL server logs… any thoughts on what could be the problem.
I haven’t tested other versions, but on SQL Server 2008 R2 there is another way to get State 58 even with Mixed Mode enabled: Attempt a SQL Server authenticated connection but have the login & password blank.
ntxdba sorry, all I know about State 11 is the description I posted above… 🙁
Aaron,
I'm receiving this error on SQL2008R2 cluster on Windows 2008R2. The error that is being raised on NodeA is: Login failed for user 'domain\NodeB$'. Reason: …. Error 18456, Severity State 11. NodeB is the other node in the cluster and it is also running SQL2008R2. Both are named instances.
I've added domain\NodeB$ as a user on NodeA but the error persists.
Any assistance would be appreciated.
Aaron,
I just spent an hour on a an login failure with state 38 because I didn't realize that the database name listed in profiler wouldn't be the name of the database it was trying to connect to. I had to look for user error messages in the trace as explained here:
http://www.mssqltips.com/sqlservertip/2581/sql-server-error-18456-finding-the-missing-databases/
Do you know if the error message was clarified in 2012. If not, do you think its worth adding a connect request?
I've had severity 58 errors in the past that were not related to authentican mode but were instead related to ODBC trying to use Named Pipes inappropriately
Thanks! That helped.
Aaron Bertrand
Thank you for the post this as been very helpful.
This usually means that your connection request was successfully received by the server name you specified but the server is not able to grant you access for a number of reasons and throws error: 18456. This eventID sometimes provides also state 1 which actually isn’t that useful as due to security reasons any error is converted to state 1 unless you can check logging on the server. Microsoft does not provide very usefull message boxes so below are some explanations why you get the error.
Hi Aaron,
Very useful information. I am facing an issue while trying to install MS SQL SERVER 2005 EE on our Windows Server 2003 R2.
Using Mixed Authentication Mode. (Windows + SQL Server)
I am facing the error 18456, sev 14 and state 10.
Login failed for user sa.
I checked my password meets the local password policy of my server and the user that i am trying to install MS SQL SERVER 2005 with is also not locked.
It has Admin rights on my system. But still is the same error.
What could be the reason? Am i missing something here?
It will be much appreciated if i can get your expert advise.
Regards,
Bharat
Thanks
Thanks. I was getting Error Code # 18456 and went to several websites for help, but in vain. Finally your tip "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 true state in the error message." helped me. So logical, isn't it? But I didn't try before you pointed it out:)
My server was restricted to Windows authentication only. I changed it to SQL Server & Windows Authentication and it did the magic!!! BOOM!
When connecting to Microsoft SQL Server and trying to use usually SQL Authentication method you may get error 18456 login failed for user you provided. This usually means that your connection request was successfully received by the server name you specified but the server is not able to grant you access for a number of reasons and throws error: 18456. It sometimes provides also state 1 which actually isn’t that useful as due to security reasons any error is converted to state 1 unless you can check logging on the server. Check for more info <a href="http://www.sql-server-business-intelligence.com/sql-server/error-code/mic rosoft-sql-server-error-18456-login-failed-for-user">Microsoft sql server error 18456</a>
Ok, never mind the last post. Had forgotten to switch to "Mixed mode" authentication from windows only authentication. (but apparently contained databases can't do replication, so I might switch back for that reason.)
However, I can't login as a contained user (I mean with a user created within the contained database context). Get the same error there: 18456.
Addendum: I should add that I'm not sure step 1 (changing the virtual Denali account to the user) is necessary, but it worked for me.
Thanks, Aaron. That came from the "technical details" button section on the SSMS error message box (or vs 2010 msg box, now I forget).
However, I found the solution after posting. The security behavior of Denali has changed–if you are running locally on windows 7 with UAC on. Everyone running locally on windows 7 with UAC has to change from the virtual Denali user to the actual user name AND add the sysadmin role (–which was not necessary with sql 2008 R2 and previous, if I remember correctly).
See my post at:
http://social.msdn.microsoft.com/Forums/en/sqldensetup/thread/7b0d25d5-1e4c-481d-af45-9adffb492788
TechVsLife, if you look in the error log, do you see an associated error message with an error number of 18470? Just wondering if there is some other cause other than disabled user that would cause State 1. Where is the error message you're posting about? It may also be masking the true state that is recorded in the SQL Server error log (this looks like it came from elsewhere).
With denali ctp3, I get this on a local standalone win7 x64 pc, an 18456 error with severity 14 and state 1 (when starting SSMS). Note that I do NOT get this error and can connect if I run SSMS in elevated mode. No app can connect unless I run it in elevated mode):
Login failed for user '(computername)\(username)'. (.Net SqlClient Data Provider)
——————————
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
——————————
Server Name: (computer name)
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536
One of the gotchas is to make sure that there is a ;Integrated Security=True statement in the connection string, otherwise, the sql client may arbitrarily default to sql server authentication.
No azl, I think that's going a few steps too far. However, the solution depends on your goals.
If you intend to require users to log in with Windows Authentication, then you need to make sure they are connecting appropriately (e.g. specifying Windows Authentication in the SSMS connect dialog, making sure all connection strings specify trusted, etc). If you need to run SSMS from outside of the domain and still use Windows auth, you can see this blog post from James Kovacs:
http://jameskovacs.com/2009/10/11/tip-how-to-run-programs-as-a-domain-user-from-a-nondomain-computer/
Otherwise, if you do require SQL authentication, you will need to switch to mixed mode. To enable mixed mode authentication, you just need to go to Object Explorer, right-click the server node, click Properties, and on the Security tab, change "Server authentication" to "SQL Server and Windows Authentication mode" You'll need to restart SQL Server but you certainly won't need to reinstall.
I've got error state 58. what am I supposed to do? Must I re-install my sql server or not?
This would be really usefull, esp as it contains Denali
very useful post.
Thanks Aaron. This would really be helpful.