Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SharePoint Products and Technologies >> SharePoint - Excel Services

Single Sign-On problems

Hi All,

We have two virtual machines set up, one with Sharepoint 2007, the other with SQL server 2005. I'm having a lot of trouble trying to get a dashboard page set up to work properly. I have created a number of PivotTables within Excel 2007 as well as an ODC. From the desktop of my client machine these tables work well, returning data and allowing the refresh/drill through of the data from my analysis cube. The cube resides on the virtual SQL server. When i upload my Excel document and the office data connection file to SharePoint this is where the problems start happening. On my dashboard page i assign one of the web parts to one of my pivot tables from the Excel document. It initially loads no problem. When i try to drill in or refresh the data i get the error:

Data Refresh Failed
Unable to retrieve external data for the following connections:
<ODC>
The data sources may be unreachable, may not be responding or may have denied you access.
Verify that data refresh is enabled for the trusted file location and the work book authentication is correctly set.

This happens when i try both SSO and windows authentication. Interestingly, upon using SSO i get errors in the event log of the SHarepoint server as follows:

ERROR:
The Microsoft Single Sign-on (SSOSrv) service failed to retrieve credentials for user <Username>, enterprise application definition SSOTest. The error returned was 0x80040e14. For more information, see the Microsoft SharePoint Products and Technologies Software Development Kit (SDK).

WARNING:
SSO has failed with the following error code -2147217900, exception: A call into SPS Single Sign-on failed. The error code returned was '-2147217900'.

FAILURE AUDIT:
Unable to establish a connection using credentials retrieved from SSO. This could be because the Unattended Service Account is not configured or because the credentials retrieved from SSO are not valid.

I'm really not sure what to try next. I've been through the options for setting up delegation and these have made no difference.

I'm really hoping that someone can help me find a solution to this. Many thanks in advance.

Cheers,

Grant

[2202 byte] By [GrantSwan] at [2008-2-7]
«« Is there a Groups property that can be used to iterate Named captures
»» CustomRollupColumn and aggregates
# 1

Please make sure:

1. The SSOService is started and configured properly on the Sharepoint 2007 server. The service should be running under an identity that is member of the SSO Administrators group.

2. Verify that you have defined the application correctly in SSO, and that the user viewing the workbook has access to the sso application entry you defined.

3. If all of SharePoint is installed on the same machine, I suggest you switch the accessmodel of Excel Services to delegation, by running:

stsadm -o set-ecssecurity -accessmodel delegation -ssp <your ssp name>

stsadm -o execadmsvcjobs

iisreset

IraLevin at 2007-9-5 > top of Msdn Tech,SharePoint Products and Technologies,SharePoint - Excel Services...
# 2
I have done all of what you suggested already and it still doesn't work. I seem to have resolved the problem however. I recreated the encryption key and also ran the "Enable features on existing sites" option and it seemed to work after that. I'm not sure if there was anything else that i have done that could have resolved it. It is however working now.

Thanks for your input,

Grant

GrantSwan at 2007-9-5 > top of Msdn Tech,SharePoint Products and Technologies,SharePoint - Excel Services...
# 3
I have done everything that has been suggested as well. SSO is setup and running on the same account the SSP is running on, plus all software is on the same machine but I still cannot refresh data. Any ideas would be greatly appreciated.
bigwahoo at 2007-9-6 > top of Msdn Tech,SharePoint Products and Technologies,SharePoint - Excel Services...
# 4

How is the connection in the workbook defined?

How is the sso application, that the workbook connection consumes, defined?

Can you provide verbose logs for the scearion that is failing.

You can change the verbosity level of the logs to Verbose from Central Admin -> Operations (tab) -> Diagnostic Logging. You'll want to change the category to 'All', and the least level to trace to 'Verbose'.

IraLevin at 2007-9-6 > top of Msdn Tech,SharePoint Products and Technologies,SharePoint - Excel Services...
# 5

Hi, I can't seem to get pivot tables to work from a sharepoint webpart either. I have followed all the instructions in http://technet2.microsoft.com/Office/en-us/library/7e6ce086-57b6-4ef2-8117-e725de18f2401033.mspx?mfr=true as well as trying things in this thread and some others on the forums. Everything works if I try to update/dill down in the pivot take from Excel but still no joy from sharepoint. The worksheet is in a trusted file location and the .odc file is in a trusted data connection library. so I turned on verbose logging and this seems to be the relevant part of the log:

Code Snippet
04/27/2007 10:58:53.03 w3wp.exe (0x0FE8) 0x0E18 Excel Services Excel Services External Data 6k5c Verbose Credentials.TryLogonUser: called for connection username: sharepointUser, domain: MARTYWATER 00000006-7053-3b39-0000-000050f7b00b
04/27/2007 10:58:53.03 w3wp.exe (0x0FE8) 0x0E18 Excel Services Excel Calculation Services 0000 Medium PF_CHECK_ERROR returned 'critical hresult error' 0x80004005 in file d:\office\source\bibls\engine\data\oledbconnection.cpp @ line 380 00000006-7053-3b39-0000-000050f7b00b
04/27/2007 10:58:53.03 w3wp.exe (0x0FE8) 0x0E18 Excel Services Excel Services External Data 6k27 Medium OLEDBConnection::InitConnection: The following system error occurred: The system cannot find the file specified. . 00000006-7053-3b39-0000-000050f7b00b
04/27/2007 10:58:53.03 w3wp.exe (0x0FE8) 0x0E18 Excel Services Excel Services External Data 6k51 Medium ConnectionManager.CreateConnection: failed to create a connection due to ConnectionException - 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) a... 00000006-7053-3b39-0000-000050f7b00b
04/27/2007 10:58:53.03* w3wp.exe (0x0FE8) 0x0E18 Excel Services Excel Services External Data 6k51 Medium ...t Microsoft.Office.Excel.Server.CalculationServer.CredentialsIntegrated.TryExecuteImpersonated(ExcuteImpersonatedMethod method) at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.CreateConnection(Credentials credentials, ConnectionInfo connInfo, Int32 keyLcid) 00000006-7053-3b39-0000-000050f7b00b
04/27/2007 10:58:53.03 w3wp.exe (0x0FE8) 0x0E18 Excel Services Excel Services External Data 6k4q Medium ConnectionManager.GetConnection: Failed to create new connection, message=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.Ex... 00000006-7053-3b39-0000-000050f7b00b
04/27/2007 10:58:53.03* w3wp.exe (0x0FE8) 0x0E18 Excel Services Excel Services External Data 6k4q Medium ...cel.Server.CalculationServer.CredentialsIntegrated.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), sessionId=21.2H27NE0nQZcaxFgORe94090.5.en-US5.en-US73.+0300#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-0060, connectionString=Provider=MSOLAP.3;Password=********;Persist Security Info=True;User ID=xxxxx;Data Source=bwc.berkeley.edu;Initial Catalog=LatestORNLDaily poo... 00000006-7053-3b39-0000-000050f7b00b
04/27/2007 10:58:53.03* w3wp.exe (0x0FE8) 0x0E18 Excel Services Excel Services External Data 6k4q Medium ...l count=1 00000006-7053-3b39-0000-000050f7b00b
04/27/2007 10:58:53.03 w3wp.exe (0x0FE8) 0x0E18 Excel Services Excel Services External Data 3slq Information Refresh failed for 'BWC ORNL Cube' in the workbook 'http://water/Documents/TestCube3.xlsx'. [Session: 21.2H27NE0nQZcaxFgORe94090.5.en-US5.en-US73.+0300#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-0060 User: MARTYWATER\Administrator] 00000006-7053-3b39-0000-000050f7b00b

note log was edited to remove username/password information.

I'm not sure if the problem is the PF_CHECK_ERROR or the ConnectionException. Unfortunately there isn't any exception text with the ConnectionException to indicate what might be causing the problem.

MartyBeekwilder at 2007-9-6 > top of Msdn Tech,SharePoint Products and Technologies,SharePoint - Excel Services...
# 6

Marty,

You need to modify the sso application definition to state that the credentials are windows credentials, so that excel services will use them to do "logonuser" before trying to connect to ssas. It's currently not set as such, so excel services sets the credentials in the connection string, instead.

IraLevin at 2007-9-6 > top of Msdn Tech,SharePoint Products and Technologies,SharePoint - Excel Services...
# 7

Ira,

Thanks for your reply. Unfortunately I'm not sure I understand what you mean. When I go to the "Edit Enterprise Application Definition" screen in central administration under authentication type it has a checked and grayed "windows authentication" check box. Is this what you mean? If so it appears that I am already using windows credentials. Or do you mean that I should use windows credentials to connect to the DB? If this is the case then I can't do that, the DB is at a different university. There is a good chance that the server isn't a windows machine and I don't have a user account on that machine. Hopefully you had a third option in mind.

MartyBeekwilder at 2007-9-6 > top of Msdn Tech,SharePoint Products and Technologies,SharePoint - Excel Services...
# 8

Are you trying to connect to SASS using an http connetion? if so, then I'm afraid excel services doesn't support refreshing data from SSAS using HTTP connections.

IraLevin at 2007-9-6 > top of Msdn Tech,SharePoint Products and Technologies,SharePoint - Excel Services...
# 9
I don't think so. In Excel the connection is using the MSOLAP.3 provider and I'm not doing anything to override the default transport that the provider uses. Going back to your earlier post when you said "modify the sso application definition to state that the credentials are windows credentials" are you talking about changing the .odc file or changing something in sharepoint central administration?
MartyBeekwilder at 2007-9-6 > top of Msdn Tech,SharePoint Products and Technologies,SharePoint - Excel Services...
# 10

I'm refering to SSO administration, where you sepecify the sso application definition.

So I guess I'm confused... I thought that in your previous posts you said you can't use windows authentication, since the SSAS server is on a remote domain.

Can you describe how you would like excel services to authenticate with SSAS?

How does the excel client authenticates with it? Is excel client using the same connection string which has the username and password specified in it?

IraLevin at 2007-9-6 > top of Msdn Tech,SharePoint Products and Technologies,SharePoint - Excel Services...
# 11

Ok, you’re talking about the "operations" -> "Manage settings for enterprise application definitions" -> "Create/Edit Enterprise Application Definition" page, right? On this screen it shows that I have the SSO app configured to be a group account and under auth type the "Windows authentication" check box is checked. Note that if I edit the SSO app both the account type and auth type are grayed out.

Yes the SSAS server is on a different domain and is not necessarily a Microsoft product.

well from what I have read I think I want/need to have the unattended account connect to the SSAS server with a connect string that contains a user name and password that is not the same as the unattended account or the user viewing the workbook.

At the moment the Excel client is configured to use an .odc file that was created with user name/password log on credentials and Excel Services: authentication settings set to SSO with my ssoID. Also "save password in file" is checked and the .odc file is saved in a trusted data connection library. I can refresh and drill down in the Excel client. If I publish the work book to sharepoint and open it in excel from there refresh and drill down still work. It only fails if I view the workbook in the browser; however, that is the access mode that I need for the workbook.

MartyBeekwilder at 2007-9-6 > top of Msdn Tech,SharePoint Products and Technologies,SharePoint - Excel Services...
# 12

>> Yes the SSAS server is on a different domain and is not necessarily a Microsoft product.

The SSAS isn't an MS product or the domain? or the OS hosting SASS?

AFAIK, Micrsoft Sql Analysis Service can only run on Windows OS with windows accounts.

I'm afraid, that excel services doesn't supports connecting the SSAS where the connection string contains username and password, rather it only support SSPI authetication (i.e. impersonate the user and establish the connection using the user windows security context).

IraLevin at 2007-9-6 > top of Msdn Tech,SharePoint Products and Technologies,SharePoint - Excel Services...
SharePoint Products and Technologies
SharePoint - Excel Services

SharePoint Products and Technologies Hot Topic

SharePoint Products and Technologies New Topic

SharePoint Products and Technologies

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified