SALES: 1-800-867-1380

Develop with SQL Reporting (Azure SQL Reporting)

Updated: May 9, 2014

ImportantImportant
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.

This article provides getting started information for application developers who integrate reports hosted by SQL Reporting in their applications, as well as develop management tools against SQL Reporting report servers.

Similar to reports deployed to on-premise SQL Server Reporting Services (SSRS) report servers, reports deployed to SQL Reporting report servers can be displayed in Windows Forms or ASP.NET applications using the Visual Studio ReportViewer control. Likewise, SQL Reporting exposes SOAP endpoints that enable programmatic management of the report server.

In This Article

Prerequisites

Review the following topics for requirements, guidelines, compatibility, and unsupported features:

Back to top

Authentication to SQL Reporting

SQL Reporting uses cookie-based authentication. Each cookie expires after one hour. The report server sends a new cookie to the client every half-hour. When applications access a Azure SQL Database report server, the client code must explicitly save new cookies sent by the server and return them with subsequent requests.

Because SQL Reporting implements a custom authentication extension to provide a forms authentication interface, you must accommodate for it when programming against the report server. This custom extension works in the way described in Security Extensions Overview (SQL Server 2008 R2 Reporting Services). In short, you must do the following:

  1. Supply the SQL Reporting credentials in the way supported by the forms authentication extension.

  2. Save the authentication cookie from the authentication attempt with the report server.

Sample reports developed in SQL Server Business Intelligence Development Studio and that use Azure SQL Database databases are included in SQL Reporting Samples.

Back to top

SQL Reporting Reports in ReportViewer Controls

The ReportViewer controls manage the authentication cookie, making your tasks easier. To display reports deployed to a SQL Reporting report server in the ReportViewer controls, you supply the report server URL and the report path as you would for any server report, and implement the IReportServerCredentials interface and use it in ServerReport.ReportServerCredentials.

The following example shows how to implement and use the IReportServerCredentials to access SQL Reporting reports:


/// <summary>
/// Implementation of IReportServerCredentials to supply forms credentials to SQL Reporting using GetFormsCredentials() 
/// </summary>
public class ReportServerCredentials : IReportServerCredentials
{
    public ReportServerCredentials()
    {
    }

    public WindowsIdentity ImpersonationUser
    {
        get
        {
            return null;
        }
    }

    public ICredentials NetworkCredentials
    {
        get
        {
            return null;
        }
    }

    public bool GetFormsCredentials(out Cookie authCookie, out string user, out string password, out string authority)
    {
        authCookie = null;
        user = ConfigurationManager.AppSettings["USERNAME"];
        password = ConfigurationManager.AppSettings["PASSWORD"];
        authority = ConfigurationManager.AppSettings["SERVER_NAME"];
        return true;
    }
}

Then, in the Web.config or App.config file, specify the application settings in the <appSettings> section. In the below example, consider replacing the values for SERVER_NAME, USERNAME, PASSWORD, and REPORT_PATH with appropriate values from your SQL Reporting report server.

<appSettings>
  <add key="SERVER_NAME" value="<INSTANCE_NAME>.report.int.mscds.com" />
  <add key="USERNAME" value="<USERNAME>"/>
  <add key="PASSWORD" value="<PASSWORD>"/>
  <add key="REPORT_PATH" value="<REPORT_PATH>"/>
</appSettings>

The following example shows how to use the IReportServerCredentials to access SQL Reporting reports:

public partial class Default : System.Web.UI.Page
{
    Microsoft.Reporting.WebForms.ReportViewer ReportViewer1 = new ReportViewer();
    protected void Page_Init(object sender, EventArgs e)
    {
         ReportViewer1.ServerReport.ReportServerUrl = new Uri(String.Format("https://{0}/reportserver", ConfigurationManager.AppSettings["SERVER_NAME"]));
         ReportViewer1.ServerReport.ReportPath = ConfigurationManager.AppSettings["REPORT_PATH"];
         ReportViewer1.ServerReport.ReportServerCredentials = new ReportServerCredentials();
    }
}

For more information on the IReportServerCredentials interface, see the corresponding API reference topic in ReportViewer Controls Programming Reference on MSDN. For Windows Forms applications, use the Microsoft.Reporting.Winforms Namespace. For ASP.NET applications, use the Microsoft.Reporting.Webforms Namespace..

See Security Considerations for information on how to secure sensitive data like report server URL, username, and password.

Back to top

Programmatic Access of Reports using SOAP Management Endpoint

The SQL Reporting SOAP API provides several Web service endpoints for developing custom reporting solutions. The management functionality is exposed through the ReportService2005 Namespace and ReportService2010 Namespace endpoints. For the list of unsupported SOAP APIs in SQL Reporting, see Guidelines and Limitations for Azure SQL Reporting.

When accessing the SOAP management endpoint, you use the endpoint’s LogonUser() method to authenticate with the endpoint. You then need to save the authentication cookie returned by the HTTP response and include it in each subsequent operation request. The easiest way to do this is to create a new instance of CookieContainer and assign that to the CookieContainer property of the proxy class before calling LogonUser().

To generate the proxy class

  1. In the browser, go to the path for your endpoint. For example: https://<INSTANCE_NAME>.report.int.mscds.com/ReportServer/reportservice2010.asmx

  2. Type your username and password and click Sign In. A WSDL file will be displayed in the browser.

  3. Download the WSDL file.

  4. Open the Visual Studio Command Prompt and run the wsdl.exe command to generate the proxy class. For example: wsdl /language:CS /n:"ReportServices2010" <WSDL_FILE_PATH>

  5. In Visual Studio, add the generated .cs file to your project.

To authenticate and authorize with the management endpoint

  1. The following code shows how to authenticate and authorize with the ReportingServices2010 management endpoint and perform the ReportingService2010.ListChildren() operation. Note that the CookieContainer property is set to a new instance of the CookieContainer class before the LogonUser() method runs. This ensures that the authentication cookie that is returned by the Web response of LogonUser() is saved and used in later Web service calls.

    ReportingService2010 rs = new ReportingService2010();
    rs.Url = String.Format("https://{0}:443/ReportServer/ReportService2010.asmx", ConfigurationManager.AppSettings["SERVER_NAME"]);
    rs.CookieContainer = new CookieContainer();
    rs.LogonUser(ConfigurationManager.AppSettings["USERNAME"], ConfigurationManager.AppSettings["PASSWORD"], ConfigurationManager.AppSettings["SERVER_NAME"]);
    
    CatalogItem[] items = rs.ListChildren("/", true);
    
  2. Then, in the Web.config or App.config file, specify the application settings in the <appSettings> section. In the below example, consider replacing the values for SERVER_NAME, USERNAME, and PASSWORD with appropriate values from your SQL Reporting report server.

    <appSettings>
      <add key="SERVER_NAME" value="<INSTANCE_NAME>.report.int.mscds.com" />
      <add key="USERNAME" value="<USERNAME>"/>
      <add key="PASSWORD" value="<PASSWORD>"/>
    </appSettings>
    

See Security Considerations for information on how to secure sensitive data like report server URL, username, and password.

Back to top

Security Considerations

Strings such as the report server URL, username, and password, are protected information, and must be stored in the <appSettings> section in the Web.config or App.config file of your application. As a best practice, encrypt this data in your configuration file. The following links are four-part series showing you how to encrypt your data in the <connectionStrings> section. The same steps apply to the <appSettings> section.

Back to top

Next Steps

After you develop your applications, you can choose from one of the following guides as a next step:

Back to top

See Also

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft