Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to connect to SQL Azure using SQL Server Reporting Services 2008 R2


By:   |   Last Updated: 2010-02-25   |   Comments (3)   |   Related Tips: More > Azure

Problem

We know that SQL Azure is the database offering on the Windows Azure cloud computing platform, and it goes without saying that all the technologies that plug-in to databases need to start exercising and adapting to this flavor of databases along with the regular approach of database access. In this tip we learn how to use SQL Server Reporting Services (SSRS 2008 R2 hereafter) 2008 R2 Nov CTP to connect to SQL Azure.

Solution

Two major providers can be used in SSRS 2008 R2 to connect to SQL Azure: "Microsoft SQL Server" and "OLE DB". Using these providers, a report developer can continue to develop the report in much the same way as any locally or network installed database. The only thing that one should take care of is that, when SQL Azure goes RTM (official release), users will be charged for accessing SQL Azure. So initial development and prototyping can be done on a locally installed database, and when the report is developed to a considerable extent, testing and validation can be started against SQL Azure.

Again this is not the only option, but it really depends upon the pricing policy opted. It also may fall to a scenario that part of the data is hosted on SQL Azure and part is hosted locally. In this tip we will focus on how to connect to SQL Azure using SSRS 2008 R2, and this tip assumes that the reader has some basic working knowledge of SSRS.

Please follow the below exercise to create and test the subject of this tip.

Step 1

Firstly in order to create some data for our report (which we would develop in the following steps), create a database called "AdventureWorks" and within this database create a table called "Contact Details" and populate it with some test data. Those who are new to SQL Azure, can use this tip to bring themselves up to speed on how to create an account on SQL Azure and connect to the same using SSMS.

create table

Step 2

Open Business Intelligence Development Studio (BIDS), and create a new report project and name it "MyFirstReportProject".

Right click the Reports folder in the solution explorer pane, and select "Add" -> "New Item" and then select "Report".

A blank report should get added to your solution, name this report "MyFirstR2Report".

Step 3

After your report is created, on the right-hand side you should be able to see "Report Data" pane.

Our aim in this exercise is to test connectivity to SQL Azure, so we would not create any Shared Dataset or Shared Datasource. Right-Click DataSources in this "Report Data" pane and select "Add Data Source".

In the Data Source dialog box, there will be an option to select the provider type. Here you can use two options: Microsoft SQL Server and OLE DB. For the sake of this exercise we would use "Microsoft SQL Server" as the type, but those who are curious to use the other one, the connection configurations for both are shown in the figure below.

connection properties

In the connection properties dialog box, make sure that server name is mentioned in the format of .database.windows.net. For SQL Azure we need to use SQL Authentication, and key-in your login credentials for the same. Again the database name would not be populated even if you are able to test your connection successfully, so key-in your database name.

data source properties

After you have configured the connection string using "Microsoft SQL Server" as the type, your dialog box should look as shown in the figure below.

Step 4

Now we need to create a dataset, do the same by right-clicking on the Dataset directory and select "Add Dataset". Name this dataset as "MyDataSet".

Select the datasource which we created in the above set as the datasource for this dataset.

Key-in the query to retrieve records from the "ContactDetails" table which is created from our script.

Click "Refresh Fields", and if everything went fine, you should be able to find all the fields in this table in the "Fields" tab of this dialog box.

visual studio

Step 5

Place a "Table" control on the report, so that we can populate all the records from our dataset into this control.

Drag some of the fields from this dataset on this table control.

Click on the "Preview" tab which would execute your report and you should be able to see the output of your report.

Your report should look like this, after these steps are completed (apart from my image which I have kept to show that this is my report !).

visual studio

Step 6

Now it's time to deploy this report on the report server.

Right-click your solution explorer and select "Properties". Keep the rest of the properties as the default, but key-in your report server URL in the "TargetServerURL" property. If you have installed ReportServer using the default values and location, your report server URL should be something like "http://[machinename]/ReportServer".

After this is done, right-click your solution and select "Deploy" which should deploy this report successfully under normal conditions.

report manager

Step 7

Now its finally time to see this report getting executed from Report Manager.

In a default configuration, the Report Manager URL should look something like "http://[machinename]/Reports".

Open this URL in Internet Explorer, and then you should be able to see the directory MyFirstR2ReportProject and under it there should be a report named "MyFirstReport".

report manager

Click on this report to execute it, and it should work as smoothly with SQL Azure as it works with any locally installed database. Make sure that you do not test on any transaction or data that takes longer than 5 minutes, else there is a chance that SQL Azure would terminate your connection as per the specs of SQL Azure and your report might encounter an error.

Next Steps
  • Download and install SQL Server 2008 R2 Nov CTP along with AdventureWorks 2008 R2 database.
  • Create your SQL Azure account if not created already.
  • Try creating a report that connects to both of these database from a single report using embedded and shared connections.
  • Read these other SQL Azure tips


Last Updated: 2010-02-25


next webcast button


next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, March 22, 2019 - 7:30:33 AM - Boopathi Back To Top

I have created a server using Microsoft Azure. Then I try to find that server in the 'SQL Reporting Services'. It throws the error unable to connect to server.

Could you please provide what are the steps I have to do for reporting configurations.

Thanks in Advance.


Wednesday, March 26, 2014 - 7:54:08 AM - Suthan Back To Top

Hi Siddharth,

 

Need your help. I followed as per your instruction but not sure why I am getting the error when seeing the reports in the browser but I can preview it.

Kindly advice how to resolve it.

Error what I am getting

An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'APLHQSDS'. (rsErrorOpeningConnection)
The report server has encountered a configuration error. (rsServerConfigurationError)


Thursday, August 30, 2012 - 5:47:13 AM - Gaurav Singh Back To Top

HI,

 

Can U send me source code for generateing SSRS Report for Diagnostic Data in Window Azure...?

 

I  Need your Help ... Reply Must

 


Learn more about SQL Server tools