Below are the steps to connect to SQL Azure Database and refresh this Data in Excel Online.
1. Open the Excel Rich Client > Data > From Data Connection Wizard > From Data Connection Wizard
2. Choose Other/Advanced
3. Choose SQL Server Native Client 11.0
Note: if you do not have see SQL Server Native Client 11.0, you will need to install it:
Microsoft® SQL Server® 2012 SP1 Feature Pack
Microsoft® SQL Server® 2012 SP1 Native Client Microsoft SQL Server Native Client (SQL Server Native Client) is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver. It contains run-time support for applications using native-code APIs (ODBC, OLE DB and ADO) to connect to Microsoft SQL Server 2005, 2008, 2008 R2, and SQL Server 2012. SQL Server Native Client should be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2012 features. This redistributable installer for SQL Server Native Client installs the client components needed during run time to take advantage of new SQL Server code name ‘Denali’ features, and optionally installs the header files needed to develop an application that uses the SQL Server Native Client API.
4. Enter your SQL Azure Database Name > OK.
5. Choose Use specific user name and password enter the User name: & Password: Choose the correct database under Select the database:. Important: tick the check box next to Allow saving password. Then click Test Connection to ensure it is successful.
6. Choose the table you want to use > Next.
8. Choose (in this example) Pivot Table Report. Important: for this report to refresh in Excel only, you MUST choose Add this data to the Data Model.
9. you may get prompted to re-enter the Password. If so, re-enter it.
10. You now will want to ensure that password is embedded in the connection string. To do this, in the Excel Rich Client choose Data > Connections > Properties > Definition Tab. Make sure there is a tick next to Save Password and also ensure the password is indeed embedded in the connection string (it should say Password=<yourpassword> at the end of the connection string > OK.
11. If you did have to select Save password:, the connection string will change so you will be informed the connection string is now different than the .odc file, which is ok. Just click Yes.
12. You will now see that a Data Model was now added to the workbook. Again, this is vital to a successful refresh in Excel Online.
13. Your last step is to upload this workbook to Excel Online and test refreshing in the browser via Data > Refresh All Connections.