Andrey Larin

We have problem, that appeared in forums and blogs many times, but none of advices was helpful.

Shortly we have problem to refresh data from OLAP Cube (SSAS2005) in published Excel file in SharePoint, we setup Kerberos authorization as described here: http://www.sharepointblogs.com/tonstegeman/archive/2007/03/12/using-analysis-services-data-in-excel-services-part-2-preparing-the-moss-server.aspx

Let¡¯s describe the installation.

We¡¯ve got 3 servers. One of them is a SharePoint2007 server with Excel Services called ws2003r2.domain. Ws2003r2.domain is a vmware machine with Windows Server 2003 R2 Standard Sp1 installed. Bases for SharePoint are located at second server called srvsql.domain. The last server is SQL with OLAP Services installed called ssas.domain.

SharePoint web application URL is http:// Ws2003r2.domain:7000 and Application Pool Account is DOMAIN\spdev. Domain account for SSAS2005 is DOMAIN\SQLService.

We¡¯ve created SPNs:

setspn -a http/ws2003r2:7000 DOMAIN\spdev

setspn -a http/ ws2003r2.domain:7000 DOMAIN\spdev

C:\>setspn -l DOMAIN\spdev

Registered ServicePrincipalNames for CN=spdev,OU=special_users,DC=domain:

http/ws2003r2.domain:56737

http/ws2003r2:56737

HTTP/ws2003r2.domain:7000

HTTP/ws2003r2:7000

C:\>setspn -l DOMAIN\SQLService

Registered ServicePrincipalNames for CN=SQLService,OU=special_users,DC=domain:

MSOLAPSvc.3/ssas.domain:1433

MSOLAPSvc.3/ssas:1433

MSSQLSvc/ssas.domain:1433

MSSQLSvc/ssas:1433

For both server in Delegation tab in ADUC set Trust this computer for delegation to any service (Kerberos only)

Also we enabled kerberos authenticaiton to the shared services by running the following stsadm commands on the shared services machine:

stsadm -o set-ecssecurity -accessmodel delegation -ssp SharedServices1

stsadm -o setsharedwebserviceauthn -negotiate

stsadm -o execadmsvcjobs

iisreset

Published trusted ODC file with the connection string: Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Service statistic;Data Source=srvtest;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error

Set Authentication Settings in ODC to ¡°Windows Authentication¡±


And got the following error in SharePoint log:

Bac09/PF_CHECK_ERROR returned 'critical hresult error' 0x80004005 in file d:\office\source\bibls\engine\data\oledbconnection.cpp @ line 380 0000000b-5fa3-0647-0000-000050f7b00b

09/OLEDBConnection::InitConnection: An error was encountered in the transport layer. 0000000b-5fa3-0647-0000-000050f7b00b

09/Refresh failed for 'srvtest Service statistic1' in the workbook 'http://ws2003r2d2:7000/sites/stat/ReportsLibrary/ .xlsx'. [Session: 21.51c1SjIVrZp1mxhH9jfpM90.5.ru-RU5.ru-RU73.-0180#0000-10-00-05T03:00:00:0000#+0000#0000-03-00-05T02:00:00:0000#-0060 User: IMMO\makarsky] 0000000b-5fa3-0647-0000-000050f7b00b

09/ExternalSource.ValidateConnection: Unable to get a connection: Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException: Exception of type 'Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException' was thrown. at Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInterop.InitConnection() at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.<>c__DisplayClass1.<CreateConnection>b__0() at Microsoft.Office.Excel.Server.CalculationServer.Credentials.TryExecuteImpersonated(WindowsIdentity wi, ExcuteImpersonatedMethod method, Boolean dispose) at Microsoft.Office.Excel.Server.CalculationServer.Credentials.TryExecuteImpersonated(ExcuteImpersonatedMethod method, Boolean dispose) at Microsoft.Office.Excel.Server.... 0000000b-5fa3-0647-0000-000050f7b00b

09/...CalculationServer.Credentials.TryExecuteImpersonated(ExcuteImpersonatedMethod method) at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.CreateConnection(Credentials credentials, ConnectionInfo connInfo, Int32 keyLcid) at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.GetConnection(ConnectionRequest connectionRequest, ExtendedConnectionInfo extendedConnInfo, Credentials credentials, Int64 privateConnectionId, Boolean auditConnection) at Microsoft.Office.Excel.Server.CalculationServer.ExternalSource.TryGetValidatedConnection(Request request, Credentials credentials, ExtendedConnectionInfo extendedConnectionInfo, Boolean shouldReportFailure, Boolean auditConnection, Connection& connectionOut). sessionId=21.51c1SjIVrZp1mxhH9jfpM90.5.ru-RU5.ru-R... 0000000b-5fa3-0647-0000-000050f7b00b

09/...U73.-0180#0000-10-00-05T03:00:00:0000#+0000#0000-03-00-05T02:00:00:0000#-0060, externalSource=srvtest Service statistic1 0000000b-5fa3-0647-0000-000050f7b00b

09/ExternalSource.ExecuteOperation: We exhausted all available connection information. Exception: Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInfoException: Exception of type 'Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInfoException' was thrown. at Microsoft.Office.Excel.Server.CalculationServer.ConnectionInfoManager.GetConnectionInfo(Request request, String externalSourceName, Int32 externalSourceIndex, Boolean& shouldReportFailure) at Microsoft.Office.Excel.Server.CalculationServer.ExternalSource.ExecuteOperation(Request request, ExternalSourceStateInfo externalSourceStateInfo, ExternalSourceStateInfo prevExternalSourceStateInfo, Int32 index, ConnectionInfoManager connectionInfoManager, ExternalDataScenario scenario, DataOperation dataOpe... 0000000b-5fa3-0647-0000-000050f7b00b

09/...ration), Data Connection Name: srvtest Service statistic1, SessionId: 21.51c1SjIVrZp1mxhH9jfpM90.5.ru-RU5.ru-RU73.-0180#0000-10-00-05T03:00:00:0000#+0000#0000-03-00-05T02:00:00:0000#-0060, UserId: IMMO\makarsky 0000000b-5fa3-0647-0000-000050f7b00b

09/ExcelService.PostProcessRequest: finished request of type TogglePivotDrill

Does anybody have any suggesions



Re: SharePoint - Excel Services Excel services external data access problem (Kerberos)

John Campbell

What is the error you see displayed in the UI Are you getting an Access Denied alert or a Data Refresh Failed alert If it is access denied, take a second look at your spns. Also, does it refresh if you launch the browser from your server box and try

Also note that you are not required to use ODC files and DCLs. You can enable refresh just using the connection that is stored with the workbook. See a whitepaper I put together for a more full treatment. It doesn't have step by step Kerberos help unfortunately - but this is being worked on.