Sql-server – Unable to connect from remote computer, while local connection works

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.

The short answer is you can't see the password being used to connect. That being said, you can restore a backup copy of the master database to a test instance, get the hashed password, and then apply it with the HASHED option of ALTER LOGIN.

The password hash can be retrieved from the restored master database with:

SELECT LOGINPROPERTY('your-sql-login', 'PasswordHash');

The password can then be applied to the other instance, substituting the value from the script above:

ALTER LOGIN your-sql-login
WITH PASSWORD = 0x01000CF35567C60BFB41EBDE4CF700A985A13D773D6B45B90900 HASHED;