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.