Create a shared data source (Azure SQL Reporting)
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.|
A shared data source is data connection information that you manage independently from the reports that reference it. Connection information consists of a SQL database that is available to you via your Azure subscription, a credential type, and if using stored credentials, the user name and password of a database user.
|To save steps, create a shared data source first, and then reference it in the reports you create. Defining the shared data source on the server ensures that the report is bound to the data source right from the start.|
To create shared data sources on SQL Reporting, use the management portal. You can also create a shared data source in SQL Server Data Tools as part of a report project. When the project is deployed to SQL Reporting, the shared data source is uploaded to the reporting service along with reports and other items in the project.
It is recommended that you use shared data sources instead of embedded data sources because the shared data sources can be updated independently, which makes account and password changes easier to manage. In contrast, you cannot edit embedded data sources on the SQL Reporting report server; instead, you must update reports on premises and then deploy or upload them to SQL Reporting again.
Best practice is to create a small number of SQL database logins (for example, one per department), and then use report parameters to vary report data for each user group.
Create one or more database logins in SQL Database, and then create a database user based on the login, with a minimum of read permissions on the database. For instructions, see Managing Databases and Logins in Azure SQL Database.
Open a Web browser, and launch the Azure Platform Management Portal by specifying the appropriate credentials. Open the SQL Reporting server on which you want to create a new shared data source.
Click Items at the top of the page. If you do not want the new data source in the root folder of the report server, navigate to the folder in which you want to create the data source. Folders can be one level deep. As such, all new and existing folders will be visible in the Items page.
Click Add at the bottom of the page, and then click Create Data Source.
Type a name in the Name box. Optionally, type a description in Description. Names of data sources in a folder must be unique.
Choose the SQL Database to use in this data source. Although the database can be from any data center, it must be hosted on a SQL Database instance created under the same subscription as SQL Reporting. Only those databases that are valid for the subscription appear in the list.
Click the arrow on the page to specify how the data source connects to the Azure SQL Database database. You can use either of the following approaches:
Prompt for credentials to prompt users to provide a user name and password when they run the report. Optionally, update the default prompt text: Type the user name and password to use in order to access the data source:.
Credentials stored securely in the report server to provide a user name and a password that is stored on the report server, separately from the report.
SQL Database uses database authentication over HTTPS. Encryption is built into every connection, so no additional configuration is required on your part.
- Prompt for credentials to prompt users to provide a user name and password when they run the report. Optionally, update the default prompt text: Type the user name and password to use in order to access the data source:.
Click the checkmark to save your changes.
In contrast with an on-premises report server, you might notice that Test Connection and Enable Data Source are not included in the Create data source page in the management portal. There is no workaround for this issue.
The data source will be saved even if the information is invalid, so be sure to test the shared data source in a report to verify it is working properly. For more information about using a shared data source in a report, see Configure a Report to use a SQL Reporting Data Source in Report Builder.
After the shared data source is created, you can set permissions on the item to control further updates to the information it contains. Restricting access to the item has no effect on a user's ability to use data from the connection. For example, if Sue is denied access to the item, she cannot view, edit or delete connection information, but she still sees the data in any report that uses the shared data source.