Office 2007 Pro Plus Quick and Easy Data Access wi

Today’s author, John Campbell, a Program Manager on the Excel Services team, shows us the quick and easy way to get external data access working with your Excel Services spreadsheets.
I have seen a lot of great blog posts that tell folks how to do great things with refreshed data in an Excel spreadsheet using Excel Services. I’ve seen posts about slicing dashboard pages, about using .odc files to manage data, and more. One thing that continues to be an issue is how to get connected to that data in the first place, in way that will work on Excel Services. The key is all about ‘telling the server’ what user identity to use when connecting to your data source. I know a number of people have gotten over this hump, but there are a lot of others who struggle here. In this posting I will shed some light on why the server works the way it does here, and show you the easiest way to get this up and running. I’m abbreviating in this post – so I’m not going to cover every last detail, option, and scenario here. If you want a lot more detail, and want to go deeper on why, and other options I don’t talk about here, then see the whitepaper I wrote on the topic:
First, some quick terminology and background:
Credentials: The log in name/password of a user. These are used to establish your identity to the larger network/system.
Authentication: ‘Who you are’ – this is how the system verifies your identity.
Authorization: ‘What you can do’ – this is the process of the system determining what rights or permissions you have.
Connection String: This is a string, typically stored in a workbook or .odc file, which has the basic information needed to connect to a data source. It contains things like what server to use, how to authenticate, along with any special parameters that are needed to form the raw connection to the database.
Your goal is to get the user, who is viewing your spreadsheet in their browser, authenticated and authorized against the data source. (After all, your cool report showing sales figures sliced by time won’t be worth much if you can’t refresh the data to see the current numbers.) To be more specific, I am talking about getting an identity to use for authentication/authorization from the Excel Calculation Server (ECS) to the data source (circled in the picture):
Connection strings will typically contain information about how to authenticate to the data source, but, for various reasons, Excel Services can’t parse them to determine how to perform the authentication. So the user must explicitly set how the authentication is to be performed. There are three options for this: Windows (delegate your domain credentials to each box on the way to, and including, the data source), SSO (lookup a username/password combo to use on your behalf out of an Single Sign On database in SharePoint), or None (no specific options are set – just take the connection string and give it a try). By default, the Windows authentication is attempted because A) it is the most secure, and B) it has the best chance of working, assuming Kerberos is configured in a typical environment, without somebody (the administrator of the server or database) needing to do something special to make it work.
Ok, enough with the background info. So, you say you can’t setup Kerberos and don’t want to spend a bunch of time getting into details of authentication - so what’s the easiest thing to do? The rest of this blog is about how to configure the None option, and related server switches, to get external data refresh working on Excel Services.
Step 1: Configure the unattended account in the Excel Services administration page.
The unattended account is really just a set of credentials that Excel Services impersonates before certain types of external data connections are made. Think of it as a dummy user account that Excel Services keeps around for connecting to data. By default, many connection strings (like connections to SQL or Analysis Services), are configured to use whatever identity is available in the process at the time the connection is made. What all that boils down to is, if the connection string specifies that integrated security is used, then the identity of the unattended account will be used at the data source for authentication/authorization.
So here is how to do it:
On the server box, go to the Excel Services admin page: From the taskbar, click Start, point to All Programs, point to Microsoft Office Server, and then click SharePoint 3.0 Central Administration. Select the name of the SSP from the left-hand navigation bar. For example, SharedServices1 as shown.
Then, click Edit Excel Service Settings. Then scroll down and find the Unattended Account settings.
Enter in the name and password of an account that has access to the data sources you are planning to access, and click OK.
A few things to pay special attention to:
If any other users want to use this account for their data refresh, they will need to make sure it has access to their data sources. Security is important! You should never use an account that has access to the SQL databases that are running your SharePoint farm. If the unattended account does have access to SharePoint, then it becomes possible for users to load a workbook on the server that connects directly to the SharePoint databases, bypassing most SharePoint security.
Step 2: Configure the data connection in workbook
From the Excel Data ribbon, in the Connections section, click Connections.
In the Workbook Connections dialog box, which shows all the external data connections that are currently being used by the workbook, select the connection that needs to be changed, and then click Properties.
In the Connection Properties dialog box, which allows many properties of the connection to be changed, click the Definition tab.
Click the Authentication Settings button.
From the Excel Services Authentication dialog box,Office 2007 Pro Plus, select the None option.
Now just click OK on the various dialogs until you are back to your spreadsheet.
That’s all you need to do. Also note that with this None setting, if you use something like SQL authentication where a username and password are saved in a connection string, then SQL (or other data providers) should use those credentials to connect to the data. I say should, because Excel and Excel Services doesn’t control this – whether that username/password gets used is completely up to the specific implementation of the data provider.
If you want more information about why authentication works this way, more details about the other options, more information about managing data connections, or prescriptive guidance and Q&A’s in this area - then see the whitepaper, where I go much deeper, at .
<div