Author: Tobias Zimmergren
http://www.zimmergren.net | http://www.tozit.com | @zimmergren

Introduction

After many-a-requests I’ve decided to do an article on how you can work with an Azure-hosted SQL Server and consume that data in SharePoint 2010.

Related articles for working with external data in SharePoint 2010:

A few introductory details about this article…

In this article I will discuss and guide you through how you can utilize the power, scalability, flexibility and awesomeness that comes with the cloud. By following this article you will get an introduction to how you can work with SharePoint together with Windows Azure to store business data.

This article will be an introduction to developing SharePoint solutions to work with Windows Azure, and in later articles I will discuss other approaches where Windows Azure may be a good solution to incorporate in the plans for your organization together with Office 365 and SharePoint Online.

Please note that this article is NOT intended to be an introduction to setting up Windows Azure. Its an introduction to setting up the connection from SharePoint to SQL Azure. More in-depth articles are coming up later.

Prerequisites

In order to follow along with this article and repro these steps yourself, you will need to have the following things in place already:

• A Windows Azure developer account
• An SQL Azure database and a table in that database
• Visual Studio 2010
• SharePoint Designer 2010
• A few sprinkles of awesomeness in your pocket would be nice, just for fun

Please note that in SQL Azure you’d need to hook up the IP-address of the machine running this code or service in order to enable it for connectivity with the SQL Azure database. You’ll see more about that in your SQL Azure portal.

Connect to SQL Azure using Business Connectivity Services in SharePoint 2010

In this section I will talk about how we can create a connection to our SQL Azure database from SharePoint by utilizing BCS. I will for the ease of demo use SharePoint Designer to set it up – and to prove that it works!

1. Make sure you’ve got existing data in one of your SQL Azure databases

In my setup, I’ve got a database called ZimmergrenDemo and a table called ProductSales. I can access the database either from the Windows Azure Platform portal or directly from the SQL Server Management Studio:

I’ve got some sample data that I’ve popped into the SQL Azure Database:

2. Setting up a Secure Store Service configuration for your SQL Azure connection

In order for the BCS runtime to easily be able to authenticate to the SQL Azure database (which is using different credentials than your Windows Server/Domain), you can create a Secure Store application and use that for authentication.

1. Create a new Secure Store Application

Go to Central Admin > Mange Service Applications > Secure Store Service > New

Create a new Secure Store application, looking something like this:

2. Configure the Secure Store application fields

I add one field for User Name and one for Password, something like this:

3. Add the administrator account(s) needed

Voila! Your Secure Store application is setup, now let’s move on to working with the data in our SQL Azure database.

3. Working with the data though Business Connectivity Services

Now that the SQL Azure database is available and your Secure Store application is configured, it’s time to get the BCS up and running with SharePoint Designer.

The first and foremost option to get up and running quickly is of course to hook up an External List and be able to see your data straight through the standard SharePoint UI.

For a detailed step-by-step instruction for the whole routine to set up a new BCS connection, please refer to my previous articles.

1. Configure the BCS connection using SharePoint Designer

Launch SharePoint Designer and create a new External Content Type and select the SQL option for the data source. Enter the information to your SQL Azure database and the application ID for your Secure Store application.

Connecting to your SQL Azure database through BCS via SPD:

Since you need to enter the credentials for your impersonated custom identity (the SQL Azure database credentials) – you’ll get this dialog:

Enter the credentials to your SQL Azure database:

Once that is taken care of, you will be able to follow the normal routines for configuring your BCS connection.

My SQL Azure database, right inside of SPD:

2. Create an external list and navigate to it in your browser

In whatever way you prefer, create an external list for this External Content Type and navigate to it. You will probably see a link saying “Click here to authenticate“.

Click the link, and you will be provided with this interface:

I probably don’t have to explain that this is where you’ll enter your SQL Azure User Name and Password to make sure your BCS connection authenticates to your SQL Azure database properly.

Okay, when the external list is created and you’ve configured the authentication – you’ll see your data flying in directly from SQL Azure into your SharePoint external list for consumption!

And as always, the coolest thing here is that it’s read and write enabled straight away – you can work with the items in the list much like normal items in any list. Sweet.

Consume the data programmatically from SQL Azure instead

If you don’t want to go with the BCS-approach and just do code directly instead then all you need to do is make sure that you wear the developer-hat and start hacking away a few simple lines of code.

Working with SQL Azure is like working with any other data source, so there’s really no hunky dory magic going on behind the scenes – it’s all just pretty basic.

Here’s a sample Web Part I created to collect the data from SQL Azure and display in SharePoint 2010.

Here’s most of what the code could look like:

     public partial class VisualProductSalesUserControl : UserControl

     {

         private const string connectionString = "Server=tcp:YOURSERVER.database.windows.net;Database=ZimmergrenDemo;User ID=Username@YOURSERVER;Password=myAwesomePassword++;Trusted_Connection=False;Encrypt=True;" ;

         private string selectCommand = "select * from ZimmergrenDemo.dbo.ProductSales;" ;

         private DataTable productSalesData = new DataTable ("ProductSales" );

         protected void FetchAndFill(string connectionString, string selectCommand)

         {

             using (var connection = new SqlConnection (connectionString))

             {

                 var adaptor = new SqlDataAdapter

                 {

                     SelectCommand = new SqlCommand (selectCommand, connection),

                 };

                 adaptor.Fill(productSalesData);

                 salesGrid.DataSource = productSalesData;

                 salesGrid.DataBind();

             }

         }

         protected void Button1_Click(object sender, System.EventArgs e)

         {

             FetchAndFill(connectionString, selectCommand);

         }

     }

Summary

In this article I talked briefly about how you can connect to your SQL Azure database using BCS and then utilize that information from SharePoint – or create a custom solution to access the data.

The reason for this article is to show you that working with Azure isn’t a big and scary task to take upon you – it’s actually all very straight forward!

Enjoy.