How to refresh data to a SQL Azure Database in Excel Online

  • Article
  • Applies to:
    Excel Online

This article was written by Tom Schauer, Technical Specialist.

This article describes how to connect to Microsoft SQL Azure Database and refresh the data in Excel Online.

  1. Open Excel client, click Data > From Other Sources > From Data Connection Wizard.

    Screenshot to select the From Data Connection Wizard item in the Data tab.

  2. Select Other/Advanced.

    Screenshot of the Data Connection Wizard page with Other/Advanced selected.

  3. Select SQL Server Native Client 11.0.

    Note

    If you don't have SQL Server Native Client 11.0.

    Screenshot to select the SQL Server Native Client 11.0 item.

  4. Enter your SQL Azure Database name, and then click OK.

    Screenshot to log in with your SQL Azure Database name.

  5. Select the Use a specific user name and password option, and then enter the User name and Password. Select the correct database under Select the database dropdown, and then click Test Connection to make sure that the connection is successful.

    Important

    Select the Allow saving password check box.

    Screenshot shows steps to make sure that the connection is successful.

  6. Select the table that you want to use, and then click Next.

    Screenshot to select the table on the Data Connection Wizard window.

  7. On the Save Data Connection File and Finish window, click Finish.

    Screenshot to select the Finish option on the Save Data Connection File and Finish window.

  8. Select Pivot Table Report (in this example).

    Important

    For this report to refresh in Excel only, you must select the Add this data to the Data Model check box.

    Screenshot shows an example of selecting the PivotTable Report option.

  9. If you are prompted to re-enter the password, re-enter it.

    Screenshot of prompting to re-enter the password.

  10. Make sure that the password is embedded in the connection string. To do this, in the Excel client, select Data > Connections > Properties > Definition tab. Make sure that there is a check box next to Save Password and also make sure that the password is indeed embedded in the connection string (it should say Password=<YourPassword> at the end of the connection string), and then click OK.

    Screenshot to make sure the password is embedded in the connection string.

  11. If you did have to select the Save password check box, the connection string will change. Therefore, you will be informed that the connection string is now different from the .odc file. The warning is ok, and you just click Yes.

    Screenshot of the warning says that the connection string is now different.

  12. Now, you see that a Data Model is added to the workbook. This is important to a successful refresh in Excel Online.

    Screenshot shows a Data Model is added to the workbook.

  13. Upload this workbook to Excel Online, and then test refreshing in the browser through Data > Refresh All Connections.

    Screenshot to select the Refresh All Connections item under the Data tab.