AlanKohl

I have created a pivot table based on a sql server 2005 relational DB connection, with the userid and password stored in the ODC (sql server authentication). The ODC is in the data connection library (DCL) and is approved.

I created an Excel sheet with a pivot table based on the ODC.

I can refresh this pivot table in Excel 2005, and I have deployed the sheet to the documents library.
When accessing the sheet via Excel Services Web access, it displays properly, but refuses to refresh, errorring with : "Unable to retrieve external data for the following connections: xxxxx
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 that the workbook data authentication is correctly set"


In the Excel Services Authentication settings, I have chosen None as opposed to SSO or Windows, since the userid and password are stored in the OCL.

Where do I start to debug this kind of issue How do I find out whether it has to do with the connection, the DCL, the Documents Library, or the Excel sheet

What I would appreciate most is a general guideline on how to investigate such issues (kind of like give me the tools to grow rice rather than a kilo rice, if you see what I mean)

Thanks for any help



Re: SharePoint - Excel Services Accessing a SQL Server table using sql authentication as opposed to windows authentication

Sean Boon

This paper should have everything you need to get this configured. Let us know if you have any issues.

http://technet2.microsoft.com/Office/en-us/library/7e6ce086-57b6-4ef2-8117-e725de18f2401033.mspx mfr=true

Sean






Re: SharePoint - Excel Services Accessing a SQL Server table using sql authentication as opposed to windows authentication

AlanKohl

Yes, this is the paper I followed at first when I tried accessing SSAS 2005 using SSO.

However, after going through the whole procedure without issues, I tried refreshing my Excel sheet in Excel Services, and I got an error with no indication as of which part of the setup was causing the error. So I decided, let's forget SSO for now, and let's start with something easier.

That's why I'm now trying to access a relational database, instead of SSAS 2005, and using SQL authentication (= userid + password) instead of SSO or Windows auth.

Unfortunately I am getting the same error, still with no indication whatsoever of what is causing the problem. How do you guys debug this kind of issues I can't believe that you'd try the same 97 steps procedure over and over until it works.





Re: SharePoint - Excel Services Accessing a SQL Server table using sql authentication as opposed to windows authentication

just doug

I had the same problem when I was trying to refresh data in a pivot table based on sample data in the Adventure Works analysis cube, even though I could refresh standard SQL data (I pulled data from the MOSS databases).

This got me to thinking it might have something to do with access accounts (had a similar problem with PWA) and found that this ... highly unusual method worked:

  • Even though you may not have anonymous access enabled on your "Office Web Server Services" web site in IIS you still need to make sure that the account you would use is not a network service account, but rather a domain account - i know, it makes no sense! (well to me anyways).
  • So, go to IIS (on all servers running excel services) and enable IIS anoymous access.
    • Set the anonymous access account to use a domain account.
    • Save and close.
  • Check your workbook again, it should be working now, if it is UNCHECK the IIS anonymous access for the "Office Web Server Services".

All should be happy once again in the land of MOSS.

Regards,

just doug

"I may not know what fixed it, all I know is that I have put the hammer down" - just doug 1998






Re: SharePoint - Excel Services Accessing a SQL Server table using sql authentication as opposed to windows authentication

just doug

"I can't believe that you'd try the same 97 steps procedure over and over until it works." - actually, try 98 times :) (plus the other two just in case they "might be it"), problem with me is I try them in parralell, so I am not sure which one fixed it.

just doug






Re: SharePoint - Excel Services Accessing a SQL Server table using sql authentication as opposed to windows authentication

Ira Levin

To connect to SQL server using SQL authentcation you'll need to:

1. Set the connection authentication setting to None. Specify the userid and password in the connection, make sure to check "Save password".

2. Specify a valid Unattended Account.

Both of these steps are detailed in the document that Sean has refered to.

How to debug these kind of issues:

You can check the applcation event log for additional information as to why excel services failed to refresh the connection.

Also, you can enable Verbose logging for Excel Services External Data or the All category (from Central Administration->Operations->Logging Diagnostics) and check the logs for additional tracing information for this category.





Re: SharePoint - Excel Services Accessing a SQL Server table using sql authentication as opposed to windows authentication

Nick Swan

Hey all,

It's weird I read this on Sunday and thought that's an interesting problem, then today I come up against it myself trying to organise a demo! Well here's how I fixed it (copy and pasted from Technet)

Unattended service account

The unattended service account is a low-permissions account that Excel Calculation Services can impersonate when establishing a data connection that uses SSO credentials from an environment that is not Windows-based, or None, as the authentication method. If an unattended service account is not configured, data connections will fail if SSO from an environment other than Windows, or None, is used as the authentication method.

Impersonating the unattended account protects Office SharePoint Server 2007 databases, and any other data sources that Excel Services can directly access, from unauthorized connections by client computers that are using Excel Calculation Services to open external data connections. When an unattended service account is impersonated, the credentials associated with an Excel Calculation Services application thread cannot be used to access any other databases. Also, when an unattended service account is impersonated, external data queries are run under the security context of a low-permissions account, instead of running under the security context of an Excel Calculation Services application thread that has greater permissions.

You can configure the unattended service account either as a domain account or as a local computer account. If the unattended service account is configured as a local computer account, ensure that the configuration is identical on every application server running Excel Calculation Services. Restrict the permissions of the unattended service account to enable only logging on to the network. Verify that the unattended service account does not have access to any data sources or Office SharePoint Server 2007 databases. Use the following procedure to enable the unattended service account.

Enable the unattended service account

1.

In the Name and Password boxes in the External Data section of the Excel Services Settings page, type the name and password that you want to use.

2.

Click OK.

You can find the entire article here:

http://technet2.microsoft.com/Office/en-us/library/a49883a7-de84-4a66-8fa0-7c7d125f237b1033.mspx mfr=true

I did find out about the Unattended service account by switching the Excel External Data log to verbose so thanks to Ira for that tip

Hope that helps

Nick






Re: SharePoint - Excel Services Accessing a SQL Server table using sql authentication as opposed to windows authentication

AlanKohl

I have a local user account set up for anonymous access (IUSR_servername).

Do I still have to change it to a domain account





Re: SharePoint - Excel Services Accessing a SQL Server table using sql authentication as opposed to windows authentication

Nick Swan

Once I had setup the unattended service account it all worked fine. I suggest trying that first.

Nick






Re: SharePoint - Excel Services Accessing a SQL Server table using sql authentication as opposed to windows authentication

Ira Levin

If you asking whether you need to modify the anonymous use account as defined in IIS to a domain account, then the answer is no. The anonymous access settings shouldn't have any affect on your scenario. What you need to do is set the unattended account to either a domain account or local machine account. Have you tried that





Re: SharePoint - Excel Services Accessing a SQL Server table using sql authentication as opposed to windows authentication

AlanKohl

Yes. Setting the unattended account solved the problem. And it also comes in to the sql view as the user defined in the ODC, which is perfect.

Many thanks to all.





Re: SharePoint - Excel Services Accessing a SQL Server table using sql authentication as opposed to windows authentication

Hernan123

I'm having the same problem while refreshing the conection in the Exel Web Access WebPart. I Have defined a pivot Table which consumes a List from SharePoint. The workbook is beeing updated automaticaly when changing the List data. But I'm getting the same error as you described when refreshing the connection. Do you have a list with the steps to follow to fix it Thanks...