Connecting to SQL Azure from Excel 2007

Sometimes I have an idea, spend a bit of time testing it out, and it ends up being a dead end. That’s what happened this evening but there’s at least one interesting bit of info that resulted so I thought I’d blog anyway…

My original thought was this:
* Excel 2007 can connect to SQL Server to retrieve data for use in reports in a worksheet
* SQL Azure is SQL Server in the cloud
* Office Web Apps gives us the ability to view Excel spreadsheets in the cloud
* So can I create an Excel spreadsheet that connects to SQL Azure, build a report using data from there, upload it to the Excel Web App and then refresh the connection so that my spreadsheet in the cloud displays live data from the cloud?

The short answer is no, at least not at the moment. But after a bit of trial-and-error I did get halfway there and manage to hook Excel 2007 up to SQL Azure (nb I’m not talking about using PowerPivot, which does work with SQL Azure, but the built-in Excel functionality). Here’s how:

  • None of the built-in functionality for connecting to SQL Server from Excel 2007 seems to work. However it is possible to connect to ODBC data sources from Excel and SQL Azure can be exposed as an ODBC data source.
  • So, in Excel, go to the Data tab and click on the “From Other Data Sources” and “From Data Connection Wizard”.
  • Select “Other/Advanced” and then the Microsoft OLE DB Provider for ODBC Data Sources and then click Next
  • Select the ‘Use Connection String’ option and paste the ODBC connection string that SQL Azure generates for you into the box.
  • Paste the value of the Uid property in the User name box, and put your password in the Password box. Delete the Uid and Pwd properties from the connection string.
  • Type the name of your database into the Initial Catalog box, then click OK
  • Finally a list of tables in your SQL Azure database appears; choose one, create an Excel data source and then create either a table or pivot table from the data. Click OK, enter your password one last time, and bingo!

Unfortunately, as I said, when I uploaded the resulting spreadsheet to the Excel Web App, I got the following error message:


Shame – I can understand why it makes sense for most external data connections not to be supported, but in this case, when the external data you’re connecting to is also in the cloud, it would be nice if an exception could be made.

One day, though, I’m sure a scenario like this will work. When I think about what Microsoft’s story for cloud BI might be like, the Excel Web App is the obvious candidate for the reporting tool. Whether you’re reporting direct from relational data stored in SQL Azure, or from some kind of cube (PowerPivot in the cloud is another obvious direction), Excel is going to be the easiest way to do it for the largest number of people. I do see a role for some kind of SSRS in the cloud too, but even in the Microsoft BI stack at the moment there’s a lot of overlap between SSRS and Excel/Excel Services for reporting; I wonder if this will be rationalised at some point? For example Report Builder has never really caught on as a way of letting end-users build their own reports, so why not forget it, develop Excel for this purpose and somehow extend SSRS’s rich functionality for managing and scheduling reports to work with Excel-based reports? Just a thought.

8 thoughts on “Connecting to SQL Azure from Excel 2007

  1. Hi Chris,I might be wrong but just thought to mention it anyway.Did you create the firewall rule (IP address range) for your Excel Web App running in the cloud to be able to access SQL Azure database?Regards,Raj

  2. Hi Rajat,It works just like you stated it.I noticed that in the connection string the name of your computer is stored under wsid=<computer_name>, and also the user id picked por logging on sql server. Command type of this connection can be "sql" instead of "table", clearly meaning you can craft select statements and maybe even call stored procedures. Finally, there is a connection property for refreshing which can be set to one minute. I guess this last setting takes care of thinking in a timer for a Excel spreadsheet.Thank you very much,Tonci Korsano.

  3. Hi Tonci,Sorry, does this mean you\’ve got a spreadsheet deployed up to the Excel Web App to refresh while connected to Azure? And this is the Windows Live version of the Excel Web App?Chris

  4. hi chris,i am sorry i didn\’t answer before. i didn\’t get a notice about your question in your blog.i used, just as you stated, excel from office 2007 with an odbc connection.i noticed a connection property that looks like a working timer.you can see it visually here –> http://tchips2.astgaming.com/sql.azure.timer.excel.2007.jpgit appears as getting a table form sql azure with excel 2007 can refresh at specified minutes intervals.best regards and thank you very much,tonci korsano

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.