Error: 17806, Severity: 20, State: 2.

Error: 17806, Severity: 20, State: 2.
SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: xxx.xxx.x.x]

Error: 18452, Severity: 14, State: 1.
Login failed for user ”. The user is not associated with a trusted SQL Server connection. [CLIENT: xxx.xxx.x.x]

SQL Server 2012/2014

I have seen several types of login errors but this one seems to be a confusing one. Today while trying to connect from a third party I noticed that I had three options for Authentication

• SQL Server Authentication
• Windows Authentication
• NTLM2 Windows Authentication

When I tried using just 'Windows Authentication' I got the above error, so I created the login explicitly and it worked. But then I tried to use the 'NTLM2 Windows Authentication' the login was successful. To confirm this I ran the below query to confirm that SQL Server was using NTLM authentication. I was connected from a client when I ran this query.

SELECT DISTINCT auth_scheme FROM sys.dm_exec_connections

So lesson for today is to use NTLM authentication when you get this error. However, I am going to investigate what client I can use which has this authentication option.