Connecting to SQL Azure with Excel 2010

April 28, 2011

If you need to quickly view data from a SQL Azure database, Excel 2010 can do the heavy lifting and handle connecting to SQL Azure and viewing all your tables with ease. From there you can filter and mash your data however you would like.

1. Open a new Excel document

2. Select the Data tab and click From Other Sources
clip_image001

3. Choose From Data Connection Wizard

4. From the wizard select Other/Advanced and click Next
clip_image002

5. In the ”Data Link Properties” dialog that come up choose Microsoft OLE DB Provider for ODBC Drivers and click Next
clip_image003

6. Click the radio button Use connection string then clickBuild…
clip_image004

7. The Select Data Source” dialog will pop up. Click New…
clip_image005

8. From the “Create New Data Source” list choose SQL Server Native Client 10.0 and click Next
clip_image006

9. Choose a name to save the connection file as and click Next on the following screen click Finish
clip_image007

10. On the following “Create a New Data Source to SQL Server” dialog enter your connection Description, Server address, and click Next
clip_image008

11. Choose With SQL Server authentication…. Input the Login ID specified as the Uid from your database connection string in the Windows Azure Portal. Input your password then click Next
clip_image009

12. For the next few wizard screens I left everything at defaults and simply clicked next. When the “ODBC Microsoft SQL Server Setup” dialog popped up I clicked Test Data Source.. to ensure my connection was successful.

13. Click OK until you get back to the “Data Link Properties” dialog and a “SQL Server Login” box should pop up asking for your password. Enter it and click OK

14. You will be returned to the “Data Link Properties” dialog finally and your connection should be all set to go now. You can click Test Connection if you want to make sure the final product works (never a bad idea), but at this point all you need to do to start using your SQL Azure DB connection is click OK
clip_image010

15. From here on out you can use your connection just as you would any other connection

Happy Hacking!

2 thoughts on “Connecting to SQL Azure with Excel 2010

  1. Michael (May 24, 2011)

    Hello, thanks for the explainer.
    Can you show (if it is possible) how to upload data from excel back to the azure db.
    Assume I access data from Azure db, modify it to needs, and want to save the changes to the azure db – so others can see it immediately after it is saved (or in real time).

  2. Data Connection from Excel to SQL Azure | Sladescross's Blog