Excel Online – Refreshing Data to a SQL Azure Database


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
http://www.microsoft.com/en-us/download/details.aspx?id=35580

ENU\x86\sqlncli.msi
ENU\x64\sqlncli.msi

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.

7. Finish

8. Choose (in this example) Pivot Table ReportImportant: 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.

Good luck!

Tom

Comments (3)

  1. Fred says:

    Hi Tom, our company is rolling out Office 365 and I currently have access only to Yammer and an Azure based SQL Server v12. I am having an issue that I hoped would be resolved in following you instructions.

    I have set up an Excel 2013 workbook, and connected to a very small SQL table in my Azure server, exactly as per your instructions above. The connection properties page towards the end of your article is identical, and I have added the connection the data model. However on attaching this file to a Yammer post, a refresh is not possible and instead I receive the message “This workbook contains external data connections or BI features that are not supported.”.

    Could this indicate that there is an admin setting that there is a Sharepoint 2013 admin setting that needs to be changed to allow the use of Excel services in this way?

  2. Dave says:

    How would you go about this with a Stored Procedure with parameters?

Skip to main content