Sql-server – Login failed for User 18456 State 5. User id works on other machines

credentialsdatagriploginssql serversql-server-2016

I have a really weird login issue I've been pursuing. I experience this only on my Macbook Pro (2018) and only when logging into this one specific SQL Server 2016 database directly through a database client.

Login attempts through any database client to this specific database are met with:

Login failed for user ''. (Msg 18456, Level 14).

Failure state is 5, "invalid user id".

These credentials work fine when used as credentials for a local copy of the java application that this DB backs.

These credentials work fine when used to login from another machine.

User ID/password does not work only when used to log in through a database client, and only on my machine. Tested with DataGrip and SQLPro for SQL Server. Both applications can log into other SQL Server databases without issues.

I generated a new user account to no avail. The issue persists. I can connect to other database servers through 1433, and my application can connect to this database when running locally with the same credentials

I am connected through a VPN, but I don't think that has anything to do with it.

I would suspect that it's some kind of encoding issue with the request, but the problem persists across multiple applications.

Anybody have any ideas?

Best Answer

According to Microsoft documentation it could be:

The error reason An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. can be returned in the following situations.

  • When the server is configured for mixed mode authentication, and an ODBC connection uses the TCP protocol, and the connection does not explicitly specify that the connection should use a trusted connection.

  • When the server is configured for mixed mode authentication, and an ODBC connection uses named pipes, and the credentials the client used to open the named pipe are used to automatically impersonate the user, and the connection does not explicitly specify that the connection should use a trusted connection.

To resolve this issue, include TRUSTED_CONNECTION = TRUE in the connection string.

Related Question