Here is how to create a Scheduled Refresh to an Database that does not accept domain credentials in PowerPivot:Create 2 SSS IDs:
a. Create a SSS application using Group leaving application Page URL to none.
b. Utilize the Windows Username/Password template.
c. Setup members that need to access this SSS application ID.
d. Then set credentials with domain windows credentials.
b. Utilize Username/Password for example: (“Oracle Username” “Oracle Password”) template.
d. Then set credentials with Oracle credentials.
On the PowerPivot "Configure Service Application Settings" page, set the "PowerPivot Unattended Data Refresh Account" to PowerPivotDataRefresh.
Browse to the PowerPivot Gallery > Locate the workbook you want to Schedule a Data Refresh for > Click “Manage Data Refresh”:
Under “Data Refresh” tick “Enable”
Under “Schedule Details” tick “Also refresh as soon as possible”
Under Credentials > Select "Use the data refresh account configured by the administrator"
Unselect "All Data Sources"
(In this case) Select "Custom"
Under "Data Source Schedule:" > Select "Use Default Schedule"
Under "Data Source Credentials:" > Select "Connect using the credentials saved in the Secure Store Service (SSS) to log on to the data source. Enter the ID used to look up the credentials in the SSS ID box.”
I have PowerPivot setup in our SP 2013 environment, SQL 2012 SP1, CU4. We are using kerberos.
PowerPivots work just fine, I can schedule refreshes in SQL no problem, also can schedule refreshes to External data (Oracle) in some ways.
If I use Secure Store Service setup for the Oracle account, refresh works just fine. If I type in the credentials for the data source, it works fine too.
If I use "Use the credentials contained in the workbook" it fails. From tracking the logs it is essentially giving an oracle logon error, null password. I know the password is in the excel book, and I know all the oracle drivers etc are setup correctly or none of the refreshes would work.
Before the refresh fails, the new PowerPivot instance does spin up in analysis services, it just fails with the no password error.
I am seeing the exact same behavior as Jack. Any ideas if this is a bug?
Jack: Make sure that the "Save password" checkbox is enabled by opening the workbook in Excel and going to Data > Connections > Properties > Definition tab