SharePoint Business Connectivity Services (BCS) Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
- by g18c
I am running SharePoint 2010 with SQL 2012, I am trying to get Business Connectivity Services (BCS) running but I am facing a double-hope authentication issue.
Everytime I try to connect to the external BCS list created in SharePoint designer, I get the error Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
In the event viewer on the SQL server I see a login failure for an anonymous user from the SP server IP address.
Background information below:
I have enabled Kerberos under SharePoint Central admin.
I have the following AD domain accounts:
SP_Farm - main website pool
SP_Services - for SharePoint services (including BCS)
SQL_Engine - SQL database engine
I then created the following with SetSPN:
SetSPN -S http/intranet mydomain\SP_Farm
SetSPN -S http/intranet.mydomain.local mydomain\SP_Farm
SetSPN -S SPSvc/SPS mydomain\SP_Farm
SetSPN -S MSSQLSvc/SQL1 mydomain\SQL_DatabaseEngine
SetSPN -S MSSQLSvc/SQL1.mydomain.local mydomain\SQL_DatabaseEngine
SetSPN -S MSSQLSvc/SQL1:1433 mydomain\SQL_DatabaseEngine
SetSPN -S MSSQLSvc/SQL1.mydomain.local:1433 mydomain\SQL_DatabaseEngine
I then delegated the AD accounts for any authentication protocol to the following:
SP_Farm - SP_Farm (http service type, intranet)
SP_Farm - SQL_DatabaseEngine (MSSQLSvc, sql1)
SP_Service - SP_Service (SPSvc)
SP_Service - SQL_DatabaseEngine (MSSQLSvc, sql1)
I have also checked the WFE is being logged on to with Kerberos, with the WFE server event log showing event ID 4624 with Kerberos authentication, this is OK.
The SQL is also showing connections authenticated as Kerberos from the WFE with the following query:
Select s.session_id, s.login_name, s.host_name, c.auth_scheme from sys.dm_exec_connections c inner join sys.dm_exec_sessions s on c.session_id = s.session_id
Despite the above, credentials are not passed from the client through the SharePoint server to the SQL server, only the anonymous account is used.
I get the following error in the WFE server for 'BusinessData' ID 8080:
Could not open connection using 'data source=sql1.mydomain.local;initial catalog=MSCRM;integrated security=SSPI;pooling=true;persist security info=false' in App Domain '/LM/W3SVC/1848937658/ROOT-1-129922939694071446'. The full exception text is: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
If I set a username and password with the Secure Store Service and set the external list to use the impersonated credentials, the list works.
Any ideas what I have missed and what can be tried next?
