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

3. Choose From Data Connection Wizard

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

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

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

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

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

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

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

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

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

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

Happy Hacking!