Set up data sources for SQL Reporting reports
Updated: May 9, 2014
|SQL Reporting will discontinue service on October 31, 2014. See this FAQ for details. For reporting on Microsoft Azure, visit Virtual Machines on WindowsAzure.com.|
In SQL Reporting, a data source defines a connection to the database providing the data. A data source definition specifies the database name, credential type, and credentials. The credentials must be a valid database user of the SQL Database service. A data source can be either embedded in a report, or specified as a shared data source that you manage separately in the management portal.
We recommend that you create shared data sources for reports hosted on Azure. Shared data sources can be updated through the management portal. In contrast, if you use an embedded data source, any changes to the credentials or server location must be made inside the report, and then republished to Azure.
Supported data sources include SQL Database from any Azure data center, as long as SQL Database and SQL Reporting are under the same subscription. You cannot retrieve data from an on-premises database on your corporate network, nor can you connect to a relational database in a virtual machine in Azure. SQL Reporting only connects to SQL Database in the cloud. External data access, such as a data connection to a database behind a firewall on a corporate network, is not supported.
A single report can use SQL Database data from multiple data centers as long as the data is contained by a report object. For example, a single report might have two tables. One table shows data from one data center; the second table shows data from another data center. Neither SQL Reporting nor Reporting Services provide data integration from multiple sources at the object level. If you want to join data from multiple sources, you must integrate the data before providing it to the report.
If the ability to preview reports in Report Designer in SQL Server Data Tools is a business requirement, you must add the IP address of the client computer to the firewall exception list for each database that is a report data source. You can edit the firewall configuration to allow access to server. The allowed services option for Azure Services is enabled by default. You need only turn it on, if it was explicitly turned off earlier.
Using reporting features such as shared data sources or shared data sets simplify credential management.
Change credentials, credential type, or choose a different database.