Excel client does not use Secure Store Service credential to connect to database

Applies to: Microsoft Office Excel 2007Excel 2010Excel 2013 More

Symptoms



In order to allow user who doesn't have access to SQL Server to view Excel report, you have set up Secure Store Service for Excel Service 2010 connecting to SQL Server. After logging in windows with this user, you can refresh Excel report in browser. Then you open the workbook in Excel client, you try to refresh data and Excel pops up “SQL Server Login” dialog.

Cause


Secure Store Service is intended for Excel Service authentication only.

Excel client uses current windows credential to login SQL Server, disregarding Secure Store Service target application in database connection file (.ODC), if current user doesn't have access to SQL Server, user is prompted for SQL Login dialog.

Resolution


To resolve this problem, please ask user to refresh data in browser, or grant user permission to SQL Server.