Sharepoint 2019 and office online server 2019 - External Data Refresh Failed

Question

Hi,

For days I have been facing a problem accessing external data on sharepoint. When I try to access the data using Secure Store authentication, everything works perfectly. Now, when I try to access the same data using windows authentication, it always gives the below error message:

**
Part of the Office Online Server logs:**

Excel Calculation Services a1tj3 Medium CredentialsDelegation.UpnLogon: logon elapsed 0ms> Excel Calculation Services c9la Medium CredentialsProvider.GetCredentials: Failed to get WindowsIdentity.> Excel Calculation Services aq0kx Medium EcsCommandResultExtendedInfo.AddExternalDataException: There's an existing error for connection 'SRV-DB' with the same exception Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException: Exception of type 'Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException' was thrown.. This error message will not be added:> Excel Calculation Services ajhkx Medium SessionUser.EnsureCredentialsForExternalData: Failed to get credentials. User=i:0#.w|empresa\srv.adm> Excel Calculation Services 3sia Medium ExcelServerRegionalSettings.CurrentUICulture: Set the current thread's UI culture to LCID 1046 'pt-BR' (Portuguese (Brazil)). > Excel Calculation Services a1tj8 Medium CredentialsProvider.TranslateSidToUpn translation elapsed 23ms> Excel Calculation Services a1tj2 Monitorable CredentialsDelegation.UpnLogon: logon failed (C2WT / KCD not configured?): exception: System.ServiceModel.EndpointNotFoundException: There was no endpoint listening at net.pipe://localhost/s4u/022694f3-9fbd-422b-b4b2-312e25dae2a2 that could accept the message. This is often caused by an incorrect address or SOAP action. See InnerException, if present, for more details. ---> System.IO.PipeException: The pipe endpoint 'net.pipe://localhost/s4u/022694f3-9fbd-422b-b4b2-312e25dae2a2' could not be found on your local machine. --- End of inner exception stack trace --- Server stack trace: at System.ServiceModel.Channels.PipeConnectionInitiator.GetPipeName(Uri uri, IPipeTransportFactorySettings transportFactorySettings) at System.ServiceModel.Channels.NamedPipeConnectionPoolRegistry.NamedPipeConnectionPool.GetPoolKey(EndpointAddress address, Uri via)

**Part of the SharePoint logs:
**
SharePoint Foundation Logging Correlation Data xmnv Medium Name=Request (POST:http://srv-spoint/_layouts/15/WsaUpload.ashx) > SharePoint Foundation Asp Runtime avwhz Medium SPRequestModule.BeginRequestHandler End, SP Build Version: '16.0.10337.12109' > SharePoint Foundation Claims Authentication bjkuz Medium Using input cookie name. CookieName: 'FedAuth'. > SharePoint Foundation Audience Validation a9fy6 Medium The absolute uri validation succeeded. AudienceUri: 'http://srv-spoint/'. > SharePoint Foundation Application Authentication bjvyg Medium SPApplicationAuthenticationModule: Clear outgoing token context from SpThreadContext > SharePoint Foundation Application Authentication bnksk Medium SPApplicationAuthenticationModule: Clear cross tenant token context from SpThreadContext > SharePoint Foundation Asp Runtime avwh6 Medium SPRequestModule.PostAuthenticateRequestHandler Begin 56ec0da1-a796-40da-1e35-4d7feba77548> SharePoint Foundation Authentication Authorization agb9s Medium Non-OAuth request. IsAuthenticated=True, UserIdentityName=0#.w|empresa\srv.adm, ClaimsCount=81 > SharePoint Foundation Runtime ajd6k Medium Value for isAnonymousAllowed is : True > SharePoint Foundation Runtime ajd6l Medium Value for checkAuthenticationCookie is : True > SharePoint Foundation Runtime ajd6m Medium ExistingCookie is System.Web.HttpCookie

Could anyone help me please?

Answer 1

Hi @Cleiton Carlos da Silva ,

The most important thing is that your problem is solved.

And thanks for sharing the update here.

By the way, since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others.". and according to the scenario introduced here: Answering your own questions on Microsoft Q&A, I would make a brief summary of this thread:

[Sharepoint 2019 and office online server 2019 - External Data Refresh Failed]

Issue Symptom:

For days I have been facing a problem accessing external data on sharepoint. When I try to access the data using Secure Store authentication, everything works perfectly. Now, when I try to access the same data using windows authentication, it always gives the below error message:

Current status:

I used EffectiveUserName. It's much simpler to set up, it's working great and it solved my problem.

You could click the "Accept Answer" button for this summary to close this thread, and this can make it easier for other community members to see the useful information when reading this thread. Thanks for your understanding!

Answer 2

Hi @Cleiton Carlos da Silva ,

The error message you provided suggests that there is a problem with Windows authentication and getting the credentials for external data. The logs indicate that there was no endpoint listening at the specified address. This could be caused by an incorrect address or SOAP action. It is recommended to check the configuration for C2WT/KCD and ensure that the server is allowed to delegate to the back-end data source. Additionally, you may want to check the c2wtshost.exe.config file and ensure that the NT AUTHORITY\Network Service allowedCallers list is updated.

References:

• Data authentication for Excel Online in Office Online Server
• Data authentication for Excel Services in SharePoint Server 2013

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Helo @Yanli Jiang - MSFT , sorry for taking so long to get back to you.

I ended up using another method...

I used EffectiveUserName. It's much simpler to set up, it's working great and it solved my problem.

Thank you very much for your help and willingness!

I appreciate that!