June 27, 2013 at 3:24 am
In one of my vb.net application i am getting error as "Login failed for user 'user'."
But the user has full permission(sysadmin). Also this error does not come always.(So it is not due to permissions i guess, also this application works properly from a long time). So i am not able to troubleshoot it. So anyone experienced the same or can anyone help me to find out the error?
June 27, 2013 at 3:29 am
Look in the SQL Server log of the instance you're trying to connect to. There should be a bit more specific error message, including a "severity" and "state". Depending on the "state" a more specific cause can be found.
Aaron Bertrand has an nice list of all possible errors and causes: http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx
June 27, 2013 at 3:32 am
are you able to connect to the database using ssms with same user?
Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
June 27, 2013 at 5:03 am
Check that password is not being changed..
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
June 27, 2013 at 7:24 am
Password is not changed.Also this error will not come always. Only sometimes it comes,so no particular sequence which can repeat the error.
June 27, 2013 at 7:38 am
What is the default database of the user being used to connect? If it is a database that doesn't exist, the connection will fail. I've seen this with user account not being dropped after the database is archived, but it could apply here.
June 27, 2013 at 7:41 am
Again: what is the message in the SQL Server log of the instance you're connecting to? Even if the login failes not all the times, you should be able to see the error message in the log when it does fail. Post that error message if you're unable to find the cause.
June 28, 2013 at 5:31 am
error log has so many errors.I filtered only login errors.
06/25/2013 19:04:29,Logon,Unknown,Login failed for user 'user'. Reason: Failed to open the explicitly specified database. [CLIENT: 192.168.1.69]
06/25/2013 19:04:29,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.
06/25/2013 09:21:01,Logon,Unknown,Login failed for user 'user'. Reason: Password validation failed with an infrastructure error. Check for previous errors. [CLIENT: 192.168.1.61]
06/25/2013 09:21:01,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 10.
06/25/2013 09:20:59,Logon,Unknown,Login failed for user 'user'. Reason: Password validation failed with an infrastructure error. Check for previous errors. [CLIENT: 192.168.1.88]
06/25/2013 09:20:59,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 10.
But most of the time i got Severity: 14 State: 10 error.Also user has sysadmin permission.
June 28, 2013 at 5:37 am
As from Aaron Bertrands blog http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx:
The first error in your post (State 38:)
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.
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 Robert Davis).
The other two errors in your post (State 10:)
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.
June 28, 2013 at 5:44 am
Yes. I saw that article.
But in my case all database are set to autoclose OFF.
Also the user exist and has sysadmin permission.
Also KB #92574 is for sql server 2005 as mentioned in the article.But i am using sql server 2008 r2.
So what may be the reason for errors.
June 28, 2013 at 5:52 am
The "state 10" could also refer to problems with the domain controller. The domain controller could be under stress or it could (temporarily) not be reached due to network issues.
Are you able to connect using SSMS with the login that gave the "state 38" message?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply