Microsoft SQL Server 2008 Reporting Services: Deploying Reports to a Server

Configuring Data Source Properties

You will likely design and test reports in BI Dev Studio by using a local data source that contains a representative subset of production data. By limiting the data used for report development, you can avoid long waits for report execution. On the server, however, you want reports to retrieve data from the production data sources. You can maintain one data source for your development environment and another for your server environment. The OverwriteDataSources property for the project controls whether the data source in your project gets copied to the server. By default, deployment copies the data source file the first time, but subsequent deployments ignore the data source file, allowing you to change the properties on the server without affecting the data source on your development computer.

Prompting the User for Credentials

The simplest type of security to implement is Windows integrated security, which passes the user’s credentials to the data source, but you might have data sources that don’t use Microsoft Windows credentials for authentication. For these situations, you can prompt the user for credentials, which then pass to the data provider for authentication at the data source. You should use this authentication method only for reports that the user can access on demand because the report cannot execute without user input.

In this procedure, you create a report-specific data source to prompt the user for credentials before executing the report.

Configure prompted credentials

1. In Internet Explorer, open the Sales Summary report that you created earlier in this chapter.

   • Native mode: In Report Manager, click the Home link at the top of the window, click the For Review link, and then click the Sales Summary link.

   • Integrated mode: In the address bar, type http://<servername>/sites/ssrs/ReportsLibrary/For Review (replacing <servername> with the name of your server), or if viewing a report, click the Reports Library link at the top of the window and then click the Sales Summary link.

   A message displays explaining that the current data source connection information has been deleted. Remember that this report was uploaded directly to the report server and independently of any shared data source described in the report definition. You must either associate the report with shared data sources on the report server, or configure a report-specific data source. In this procedure, you create a report-specific data source to learn the proper steps.

2. Open the data source properties page for the report.

   • Native mode: Above the report, click the Properties tab, and then click the Data Sources link. A message displays explaining that the data source reference is no longer valid.

   • Integrated mode: At the top of the window, click the For Review link, point to the Sales Summary report, and click the Edit button to the right of the report title, select Manage Data Sources, and click the AdventureWorksDW2008 link. A message displays explaining that the linked data source could not be found.

3. Create a report-specific data source to connect to the AdventureWorksDW2008 data source.

   • Native mode: Select A Custom Data Source, and then, in the Connection String text box, type Data Source=localhost;Initial Catalog=AdventureWorksDW2008;.

   The default authentication when creating a new data source on the report server is Credentials Supplied By The User Running The Report, as shown here.

   

   • Integrated mode: Select Custom Data Source, and then, in the Connection String box, type Data Source=localhost;Initial Catalog=AdventureWorksDW2008;, and then select Prompt For Credentials.

   Before the user can view the report, the user must enter a user name and password. You can change the prompt that is displayed to the user in the Display The Following Text To Prompt User For A User Name And Password text box in native mode, or in the Provide Instructions Or Example box in integrated mode.

   Important

   

   If the user must supply a Windows user account, be sure to select the Use As Windows Credentials When Connecting To The Data Source check box in native mode, or select Use As Windows Credentials in integrated mode.

4. Apply the change to the data source properties.

   • Native mode: Click Apply.

   • Integrated mode: Click OK, and then click Close.

5. View the report.

   • Native mode: Above the data source properties, click the View tab. The prompt for credentials displays, as shown here. Type your user name and password, and then click View Report to display the report.

     

   • Integrated mode: Click the Sales Summary link. The prompt for credentials displays. Type your user name and password, and then click Apply to display the report.

   Important

   

   If you have configured Windows authentication for your SQL Server database engine, which is the default, be sure to include the domain name with your user name in the Log In Name box, using the Domain\User format.

Using Stored Credentials

Several execution methods require the data source to use stored credentials when the report runs on a scheduled basis or produces a report snapshot. The stored credentials are stored in the ReportServer database using reversible encryption. The account used for stored credentials must be granted Read permission on the source database.

In this procedure, you configure a shared data source to use stored credentials.

Configure stored credentials

1. In Internet Explorer, open the AdventureWorksDW2008 data source that was deployed with your report project earlier in this chapter.

   Important

   

   If you deployed the report model project in Chapter 9, you also see the Adventure Works DW2008 data source for the report model in the Data Sources folder. Be sure to select the correct data source.

   • Native mode: In Report Manager, click the Home link at the top of the window, click the Data Sources link, and then click the AdventureWorksDW2008 link.

   • Integrated mode: Type http://<servername>/sites/ssrs/data connections (replacing <servername> with the name of your server), and then click the AdventureWorksDW2008 link.

2. Configure the stored credentials option.

   • Native mode: Select Credentials Stored Securely In The Report Server, type your user name and password in the applicable text boxes, select the Use As Windows Credentials When Connecting To The Data Source check box if you are using Windows authentication in your SQL Server database, and then click Apply. The updated properties page for the data source is shown here.

     

     NOTE

     

     In this example, the server name in the User Name string is fictitious and is included for illustration purposes only.

   • Integrated mode: Select Stored Credentials, type your user name and password in the applicable boxes, select the User As Windows Credentials check box if you are using Windows authentication in your SQL Server database, and click OK.

     Important

     

     If you type an incorrect user name and password for a data source’s stored credentials, no error message displays. You must test a report using this data source to confirm the user name and password are correct, which you do in the next procedure.