SQL Azure is the database offering on the Windows Azure cloud computing platform. It is important for developers to adapt their technologies to work with SQL Azure. In this article, we will learn how to use SQL Server Reporting Services (SSRS) 2008 R2 to connect to SQL Azure.

Step 1: Create a Database and Populate with Test Data

Firstly, create a database called “AdventureWorks” and within this database, create a table called “Contact Details” and populate it with some test data. If you are new to SQL Azure, you can refer to this tip on how to create an account on SQL Azure and connect to it using SQL Server Management Studio (SSMS).

Step 2: Create a New Report Project

Open Business Intelligence Development Studio (BIDS) and create a new report project named “MyFirstReportProject”. Right-click the Reports folder in the solution explorer pane and select “Add” -> “New Item” and then select “Report”. A blank report will be added to your solution, name this report “MyFirstR2Report”.

Step 3: Configure Data Source

After creating the report, on the right-hand side, you should see the “Report Data” pane. Right-click “DataSources” in this pane and select “Add Data Source”. In the Data Source dialog box, select the provider type. You can choose between “Microsoft SQL Server” and “OLE DB”. For this exercise, we will use “Microsoft SQL Server”. In the connection properties dialog box, make sure to enter the server name in the format of .database.windows.net. Use SQL Authentication and enter your login credentials. Enter the database name as well. Your dialog box should look similar to the provided figure.

Step 4: Create a Dataset

Create a dataset by right-clicking on the Dataset directory and selecting “Add Dataset”. Name this dataset as “MyDataSet”. Select the data source that we created in the previous step as the data source for this dataset. Enter the query to retrieve records from the “ContactDetails” table. Click “Refresh Fields” and verify that all the fields are displayed in the “Fields” tab of this dialog box.

Step 5: Populate Data into a Table Control

Place a “Table” control on the report to populate all the records from our dataset. Drag the fields from the dataset onto this table control.

Step 6: Deploy the Report

Right-click your solution explorer and select “Properties”. Enter your report server URL in the “TargetServerURL” property. Deploy the report by right-clicking your solution and selecting “Deploy”.

Step 7: Execute the Report from Report Manager

Open the Report Manager URL in Internet Explorer. You should see the directory “MyFirstR2ReportProject” and under it, there should be a report named “MyFirstReport”. Click on this report to execute it. It should work smoothly with SQL Azure as it does with any locally installed database.

Make sure to avoid testing on any transaction or data that takes longer than 5 minutes, as SQL Azure may terminate your connection as per the specifications of SQL Azure.

By following these steps, you can successfully connect SQL Server Reporting Services to SQL Azure and create reports using data from your SQL Azure database.