When a user fails to log in to SQL Server, they receive this error:
Login failed for user 'foo'. (Microsoft SQL Server, Error: 18456)
On the client side, this error is always State 1. This is deliberate; per the documentation:
To increase security, the error message that is returned to the client deliberately hides the nature of the authentication error. However, in the SQL Server error log, a corresponding error contains an error state that maps to an authentication failure condition. Compare the error state to the following list to determine the reason for the login failure.
This makes sense, of course. However, in a non-production environment that is scrubbed of any sensitive data, it would be nice to allow users to see why their connection is failing instead of needing an administrator to check the logs. Is there any way to override this behavior and allow a user to get the true error state?