I have created a Excel and enabled the connection to external sQL database through Secure store service and created the odc file . When i open the excel in sharepoint it refreshes the data and it is working. When i click "Open in Excel" the data is downloaded and opened in Office excel 2010. When i refresh the data it prompts me for a password to be entered. Please advise me if we have any settings which needs to enabled for the data refreshing when we open in excel
Yes there is an option to refresh the excel file on open and set credentials as well.
Open up your excel file and go to Data>>Connections.
Find your connection and click on properties.
Under the Usage tab (which is what is shown by default)Select the checkbox "Refresh Data when opening this file". You could even use "Refresh data every:" if you wish to take that route
Click on the "Definition" tab on top (adjacent to Usage tab) and click on authentication settings at the bottom.
You have 3 choices here. Use the one that suits your/business needs. Make sure whatever account you use here has the proper access to the data. Since you already have a secure store service set up use that SSID here (as shown in the screenshot). Please note that your SSID might be named differently.
Here are a few screenshots to better aid you: