The state 38 error is probably a red herring: focus on the state 10 errors.

While KB Article #925744 says it is for SQL Server 2005, that is simply because of the date it was written. The symptom it causes would affect all versions of SQL Server that have been released since then in similar or identical ways.

Now, if user is a Windows login, perhaps you are somehow trying to authenticate as a Windows login without specifying the domain, or perhaps you are specifying the domain but the domain controller is unreliable or on an unreliable network connection. You should post your actual connection string in the question (fudging our real names/passwords of course).

You should also consider creating a new, separate login with the same permissions, and see if you intermittently get the same error with that other user as well - my guess this is an architectural problem in some way as opposed to a problem with just this user.

Another possibility is if your .net application is using Windows auth and the underlying password has been changed. Seems like a long shot, but check out the article and there is a hotfix available: KB Article #2545850.

How Do I See the Full Error Message?

The SQL Server error log on the instance you are trying to connect to will have any errors that SQL Server generated about the logon failure. If SQL rejected the connection/failed the logon for some reason it will generate an 18456 error typically. On the SQL side, this error will have an error state that gives clues to the cause of the error. On the client side, however, you may or may not see the useful details - it likely that you'll just see an error state of 1.

To see the full error message it is best to look at the SQL Server that you are trying to connect to and look in the SQL Server Error Log. You should see the error for the connection attempt if the connection is making it to SQL Server. From there the information is more useful than some other error messages if you know where to look.

What If There is no Error in the SQL Server Error Log?

This could be caused by several factors but it is quite likely that you are not dealing with a logon failure. Logon failures do a good job of presenting themselves in the error log. In this case it is quite possible that you are dealing with a failure to connect to SQL Server. You can troubleshoot this problem first and at least verify that you are connecting to SQL Server in the first place and that there are no issues here. Rather than get into all the potential steps for troubleshooting that here in this answer, this Technet Twiki article is an excellent resource to at least get you started.

How do I Read the Error Messages in the Log?

There are several resources for the meaning of the various error states and error messages. The below is pasted from this msdn link to get you started with some of the more common states of the 18456 error. These states may change in future versions or in the version you are on so it is a good idea to pay attention to both the message and the state and to dig deeper to understand the issue.

State ---- Meaning

2  ---- User ID is not valid.
5  ---- User ID is not valid.
6  ---- An attempt was made to use a Windows login name with SQL Server Authentication.
7  ---- Login is disabled, and the password is incorrect.
8  ---- The password is incorrect.
9  ---- Password is not valid.
11 ---- Login is valid, but server access failed.
12 ---- Login is valid login, but server access failed.
18 ---- Password must be changed.

There are many other states as well and in edits I'll add more or in other answers you'll see more. This link has a lot of other states, most of which are valid for most versions of SQL Server above 2000. The important take away here is to look in your error log on the server side and read the message - with logon failures the messages tend to be useful a lot more often than not.

And again State 1 is a generic message on the client.

Other Errors There are other errors for SQL Server login or connection failures. In future edits or answers from other people more information can be added as well.

What Do I Do With That Data?

It depends..

The Obvious States

States 2 & 5 signify an invalid User ID. Check the user you are working with. Does your login exist? Are you specifying the database and does the login exist as a user - has it been mapped as a user in that database.

State 6 - Oops! You are trying to pass an Active Directory login name in a manner that uses SQL authentication.. Basically trying to pass Login Name of 'FunDomain\Mwalsh' but you are connecting with SQL authentication, not trusted. This is sort of like a 2 or a 5, just a bit more descriptive.

State 7 - Login is disabled.. See where it is disabled, why it is disabled and rectify the situation.

States 8 & 9 - Bad password. Is it mistyped? Has the password changed?

State 18 - I've never encountered this one in the wild. But it is telling us that our password is set to be changed. I suppose you could see this if you are connecting from an application that uses SQL authentication (like Great Plains...) and have setup a new account with a password that needs to be changed on first use - but your application doesn't understand that or have a functionality to change the password. If you just use SSMS, you just get a dialog to change the password and all is well.

Less Obvious States

11 & 12 sort of mean the same thing- These are ones you have to pay attention to the message closely with.

Is the word "Token" in the error message? Like "Login failed for user 'myDomain\UserName'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors."

This can be a case of your SQL Server Service running as local system instead of a domain account with access to domain info potentially.

It can also be a case of needing to delegate authority through kerberos - for instance when accessing a Linked Server through Windows Authentication and passing the credentials - that double hop requires Kerberos, not NTLM. So you'll need to work through Kerberos.

Even this message can also mean other things - I've seen this recently after a migration to a server with an already existing logon - it was a very old logon at my client's environment. Dropping and recreating the logon resolved the issue. Why? It could be a few things but this post from Simon Sabin a couple years ago is an excellent resource here.

UPDATE After answering this I wanted to look an error code about default databases and I found a great blog post from Aaron Bertrand who answers a lot of questions here :-) Go check out his resource for more error messages including many that aren't in SQL Server 2012 or 2008 but still in the versions in use today. Great stuff there :-)