[CRM 2013] Error: 18456, Severity: 14, State: 11.

2015-04-30

Message
Login failed for user ‘Domain\MachineName$'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: MachineName]

It’s related to SSRS Reporting Server and run as Network Service account. It’s supposed to be good. Because when you added a computer to domain, it will register Local system and Network Service account permission to the SPN records.
That’s why sometime you don’t know WHY you put Network Service account as a service account, then everything will be fixed. (Reference 2)
I’ve been encountered the error for al long time, and I’ve tried lots methods from Google.com
  • Set up SPN
http://mssqlwiki.com/2013/12/09/sql-server-connectivity-kerberos-authentication-and-sql-server-spn-service-principal-name-for-sql-server/
  • Troubleshooting Error 18456
http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx
  • Login failed for user x. Reason Token based server access validation failed and error - 18456
http://sqlblogcasts.com/blogs/simons/archive/2011/02/01/solution-login-failed-for-user-x-reason-token-based-server-access-validation-failed-and-error-18456.aspx
  • Check inappropreated permissions for NT AUTHORITY\NETWORK SERVICE and SQLAccessGroup on SQL server
etc…
I still received the errors. It must be something wrong with the permissions
Kerberos? No, because we have a testing machine using NTLM authentication/connections
Because you can run the query below to know your connections
SELECT dc.net_transport, dc.auth_scheme,dc.* FROM sys.dm_exec_connections dc

ORDER by dc.session_id;

Yes, NTLM. but when I saw the result, I found an interesting thing..

It’s the Endpoint_ID. The net_transport is TCP, but why the endpoint_id was running on the other EndPoint rather than the default EndPoint name “TSQL Default TCP” , so I ran another query below


SELECT dc.net_transport, dc.auth_scheme,se.name, dc.* FROM sys.dm_exec_connections dc

inner join sys.endpoints se on dc.endpoint_id = se.endpoint_id

ORDER by dc.session_id;

I found another Endpoint name running on the same port as “TSQL Default TCP” and there were sessions connected to.

So I ran another query to see what sessions are?


SELECT DISTINCT NAME AS database_name, session_id, host_name, login_time, login_name, reads, writes

FROM sys.dm_exec_sessions

LEFT OUTER JOIN sys.dm_tran_locks ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id

INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id

ORDER by session_id;

Two host_name showed up

AppSiteMachineName.mmc

AppSiteMachineName.ReportingServicesService

So after I deleted the customized EndPoint.. anything is going fine

[Solution]:

Check your ENDPOINT

Reference:

1. SQL Server Endpoints: Soup to Nuts

https://www.simple-talk.com/sql/database-administration/sql-server-endpoints-soup-to-nuts/

2. Enabling Kerberos for Microsoft Dynamics CRM 2011

http://blogs.msdn.com/b/crm/archive/2012/09/19/enabling-kerberos-for-microsoft-dynamics-crm-2011.aspx

3. CRM 2011: Installation issues and way to resolve it

http://www.furnemont.eu/2010/09/15/crm-2011-installation-issues-and-way-to-resolve-it/

4. CRM 2011 Integration How to Video #1: BizTalk On-Premise to CRM 2011

http://blogs.msdn.com/b/pkelcey/archive/2011/03/10/crm-2011-integration-how-to-video-1-biztalk-on-premise-to-crm-2011.aspx
Newer Older