This case may be a one-off, but I wanted to write about it in the event someone else comes across it in the future.
Here is the synopsis. A user was creating an Excel workbook in the rich client using "Use Windows Authentication":
When you do that, the connection string looks like:
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=AdventureWorks2012;Data Source=Data;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=RICKCLIENT;Use Encryption for Data=False;Tag with column collation when possible=False
The Security Support Provider Interfacehttp://technet.microsoft.com/en-us/library/bb742535.aspx
The company had a group of Authors with Role Based permissions to the SDL Data. The Authors were able to connect to the data source and build the workbooks using their Logon Credentials. However, the Authors were changing the "Excel Services Authentication Settings" to "Use a stored account Application ID:" prior to publishing to SharePoint. The reasoning behind this made complete sense, the Members of the Secure Store Application ID did not have Role Based permissions to the SQL Data. The "Set Credentials" the account was a SQL account that did have access to the SQL Data. See below how it was built.
That Application ID "Fargo" was using SQL Credentials. This is how they built it:
Now when they use a workbook that was created in Excel using "Use Windows Authentication" with a Secure Store Service Application ID that uses SQL Credentials, things go wacky. We noticed that the account running Excel Services needs to be given permissions in SQL.
BUT, if the workbook was created (In Excel Rich Client) using "Use the following UserName and Password", "Integrated Security=SSPI" is not present and the SQL "UserID=XXXXX" is saved in the Connection String and there is no further action required, the refresh in Excel Services completes without error.
Provider=SQLOLEDB.1;Persist Security Info=True;User ID=EddieVedder;Initial Catalog=AdventureWorks2012;Data Source=Data;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=AMENTCLIENT;Use Encryption for Data=False;Tag with column collation when possible=False
This is design. You must have the workbook created (in the Excel Rich Client) with the same Authentication Type as the Secure Store Service. So if the Secure Store Service is holding SQL User ID and Password for SQL Auth, the workbook must be using SQL Auth when it is built, if you see SSPI in the connection string that means that Windows Auth Mode is used. A connection can only use one AuthMode, either SQL Auth or Windows Auth.
The database server that I'm connecting to doesn't has local SQL Login enabled. Is there any workaround? I tried putting in dummy user name and password but it doesn't allow me to proceed to "Next"
Boon: If the database server does not have SQL login enabled, you need to create the workbook with "Use Windows Authentication" to connect to the database. In Excel Services Authentication, you can select "Use the authenticated user's account", or you
can select "Use a stored account" and configure the Secure Store ID with the credentials of a Windows account that has access to the database. You can also select "None" if you want to use the Excel Services Unattended Account to perform the data refresh,
as long as the credentials configured for that account has read access to the database.