Excel services external data access problem (Kerberos)

Question

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?

 

Answer 1

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.

 

Answer 2

I have virtually an identical log file.

 

I get the Data Refresh Failed error. I believe that all the trusted locations are set correctly and that Kerberos is configured correctly

 

your link to your Whitepaper is broken.

 

Any other suggestions would be awesome

 

Eric VanRoy

 

Answer 3

 

Sorry, I don't know why the link to the paper changed.  This seems to be the updated link:  http://technet.microsoft.com/en-us/library/cc262899(TechNet.10).aspx

 

I would need more information to help troubleshoot the issue. 

 

Do any other services on that box use Kerberos?  And if so, do they work? 

Do you get a successfull data refresh for any other types of connections (i.e. maybe for one where the server authentication type is None)?   

 

-John

Answer 4

Hi people,

 

I'm living the same nightmare at least for 5 days.

 

I've read and worked on some blogs and tech sites but unfortunately they were not enough so far.

 

Sites like:

 

http://msdn.microsoft.com/en-us/library/cc514223.aspx

http://sqljunkies.com/WebLog/mosha/archive/2005/01/25/6905.aspx

http://technet.microsoft.com/en-us/library/cc262899(TechNet.10).aspx

http://www.sharepointblogs.com/tonstegeman/archive/2007/03/11/using-analysis-services-data-in-excel-services-part-1-preparing-the-ad-for-kerberos.aspx

http://blogs.msdn.com/james_world/archive/2007/08/20/essential-guide-to-kerberos-in-sharepoint.aspx

http://msdn.microsoft.com/en-us/office/bb203828.aspx

and so on....

 

Any clues ?

 

We thank you in advance,

Alex Berenguer

 

 

Answer 5

After looking through all the settings sometimes the simplest item is the solution......

 

In the authentication provider make sure you have select Negotiate and not NTLM.....

 

After looking into everything... that is what my issue turned out to be. I did also have some other items that I needed to investigate that are not documented well is that you have to remember your excel services is using a custom por tin IIs (most likely the site is named something like Office Server web Services. Since that is the site that holds the Shared Services application, you need to create the SPN for that also.

 

Check the account that the SharedServices application (under office server web services) is running as. If you have not made any changes this should be the same as the SSP account (for example domain\ssp_service). Also check to see the port that the Office Server web Services is configured for (lets say 5547- it is pretty random). you will need to then set the SPN with a command similiar to http/servername:5547 domain\ssp_service.

 

Don't forget the FQDN also. That account should be set to allow delegation if it is not already.

 

After we did that, then I realized the actual web sites authentication provider was not set to Negotiate. That means that it would never attempt Kerberos.

 

I hope that this helps someone else.

 

Eric VanRoy

 

Answer 6

Hi Everyone,

It was possible to take the Excel Services working against a SSAS cube in other machine by setting all the steps below:

Creating and associating AD user account to the SSAS service (Domain Admins member of)
Creating and associating AD user account to the Sharepoint Application Pool service (Domain Admins member of)
Setting SPN to SSAS Service under the user account created (including extended fqdn)
Setting SPN to SSAS Service under the user account created (including extended fqdn)
Setting Trust Delegation to any service (Kerberos only) to the Sharepoint user account (AD)
Enabling Kerberos authentication to the shared services by runningstsadm commands:
  stsadm -o set-ecssecurity -accessmodel delegation -ssp SharedServices1
  stsadm -o setsharedwebserviceauthn –negotiate
  stsadm -o execadmsvcjobs
Setting Sharepoint Excel Services settings properly
Setting Sharepoint Excel Sharepoint Authentication provider to Negociate (Default Zone)
Publishing the Excel Services report

It's pretty a hard work but we could see Excel Services working finally.

Our big problem now is under the security. We are not security specialist guys but the Excel Services appears work only if we Trust the Sharepoint User account mentioned  to delegate authentication to any service (Trust this user for delegation to any service). All documents we could find in the network tell us it’s a very big security breach and it is highly not recommended to keep in.

Does anybody know the services the user must delegate? If so, could we use “Trust this user for delegation to specified services only” and setting the appropriated service to? It could reducing our security risk, isn't it? We have tried to set the SPN’s services created but it wasn’t enough.

Against all documents we have read, it wasn’t necessary to set delegation to the computer (sharepoint server) in this case.

Also, it could help us so much if someone could explain better what kind of risk we are supposed to be by keeping this set enabled in production environment.

Other issue is under the user accounts created to run the SQL and Sharepoint services once it wasn’t specified which profile permission they must be associated properly. (Domain Admins?).

In other hands, the solution we have map is able to keep the Excel Services working with 2 machines but it looks like to be a security breach with very low safety. What would you say guys?

Best regards,

Alex Berenguer

---

Alex Berenguer

Answer 7

John,

Good day to you!  I have a similar problem with an OLAP data source.  In trying to solve it, I decided NOT use ODC files in a pivot report with an OLAP data source. I just wanted to use whatever data is stored in the workbook, and NOT connect to the data source.  I already disabled all refresh options, both in the workbook and in the web part. But excel services STILL queries the data source when interactivity within the workbook is used, like sorting/filtering, then the DATA REFRESH FAILED message appears. The authentication settings are already set to NONE, the save password is already checked. Below are the data connection properties, do I need to change any of the parameters?

Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Prepaid_TopUpReport;Data Source=paps060904\dwdb01;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error

Funny thing, when we were using SQLOLEDB as data provider, excel services was working fine,there is full workbook interactivity without querying the data source. Now with an OLAP source, this situation appears.

Thank you in advance.

mareza

Answer 8

you require to trust the end user web application application pool account to delegate to the SSP Application Pool account/spn.

please see slide deck 2 at:
http://www.harbar.net/presentations/sdn2009/Kerberos2.pdf

you *do not* need any computer delegation for this scenario.

hth
s.

---

Cheers
Spence
www.harbar.net
Microsoft Certified Master | SharePoint 2007