Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Error:  Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Error clearly state that login is not associated with the trusted connection.

What is trusted connection and what is the advantage of it? Integrated authentication allows for SQL Server to leverage Windows NT authentication to validate SQL Server logon accounts. This allows the user to bypass the standard SQL Server logon process. With this approach, a network user can access a SQL Server database without supplying a separate logon identification or password because SQL Server obtains the user and password information from the Windows NT network security process.

Advantage of this process is that we don’t have to store credential with the connection string.

However sometimes you will see the error above error.

Resolution

1. Change the authentication mode to mixed mode from SSMS or Enterprise Manager

2. Check the user/login is associated with the appropriate SQL login

3. Host is in the same domain or Host machine is network, sometime rebooting of the host machine will resolve this issue.

Login Issue

Recently I received below error while executing XP_ReadErrorlog command from SSMS.

Msg 22004, Level 16, State 1, Line 0

Failed to open loopback connection. Please see event log for more information.

Msg 22004, Level 16, State 1, Line error log location not found 

As per the above error message I have checked event viewer log and got the below information from event viewer log. 

The description for Event ID ( 17052 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Severity: 16 Error:18456, OS: 18456 [Microsoft][SQL Native Client][SQL Server]Login failed for user 'domain\service-sql'..

I have opened error log file from log directory and observed the number of login service-sql with the state 11. 

2009-09-30 22:45:31.69 Logon       Login failed for user 'domain\service-sql'. [CLIENT: 10.10.10.10]

2009-09-30 22:45:34.26 Logon       Error: 18456, Severity: 14, State: 11.

Service-SQL account has sysAdmin rights on SQL Server and it is added through SQL Server Service – Production AD group. I can see this AD group has SysAdmin rights on SQL Server. 

Resolution: Remove account from local administrator group and from SQL Server Logins. Add account again with the same privileges in local administrator group and SQL Server logins.