MicrosoftВ® SQL Server(TM) 2005 Reporting Services Step by Step (Step by Step (Microsoft))

Now that you know how to create folders, it is time to put some content in those folders. You do this by moving reports and their supporting files from the development environment to the Report Server. This can be done using a number of different methods. We look at two of those methods now: using the Report Designer and using the Report Manager.

Deploying Reports Using the Report Designer

The most common method of moving reports to the Report Server is by using the Report Designer. Once you are satisfied with a report you developed, you can make it available to your users without leaving the development environment. This capability to create, preview, and deploy a report from a single authoring tool is a real plus.

Deploying Reports in the Chapter09 Project Using the Report Designer

Let’s try deploying the report project from Chapter 9. To do so, follow these steps:

1. Start Visual Studio or the Business Intelligence Development Studio and open the Chapter09 project.

2. Select Project | Chapter09 Properties from the Main menu. The Chapter09 Property Pages dialog box appears.

3. Type Galactic Delivery Services/Chapter 09 for TargetReportFolder. This is the folder into which the report is going to be deployed.

4. Type http://ComputerName/ReportServer for TargetServerURL, where ComputerName is the name of the computer where the Report Server is installed. You should replace http: with https: if you are using a secure connection. You can use localhost in place of the computer name if the Report Server is installed on the same computer you are using to run Visual Studio (see Figure 10–3).

   Figure 10–3: The Chapter09 Property Pages dialog box

5. Click OK to exit the Chapter09 Property Pages dialog box.

6. Right-click the Chapter09 project entry in the Solution Explorer and select Deploy from the Context menu.

7. The Report Designer builds all the reports in the project, and then deploys all the reports, along with their supporting files, to the Report Server. (During the build process, the Report Designer checks each report for any errors that would prevent it from executing properly on the Report Server.) The results of the build and deploy are shown in the Output window.

8. Open the Report Manager in your browser. Click the Galactic Delivery Services folder to view its content. You see that Visual Studio created a new folder in the Galactic Delivery Services folder called Chapter 09.

9. Click the Chapter 09 folder to view its content. All the items in the Chapter09 project—three reports and a shared data source—were deployed.

10. Click the RenderingTest report. You see the HTML version of the RenderingTest report.

Working Through the Web Service

When the Report Designer deploys reports, it works through the Reporting Services web service. The Report Manager web application provides a human interface to Reporting Services. The web service provides an interface for other programs to communicate with Reporting Services. Because the Report Designer falls into the latter of these two categories, it uses the web service to deploy reports.

The web service has a different URL than the Report Manager. You must enter the URL for the web service and not the Report Manager in the Properties Pages dialog box for the deployment to work properly. The default URL for the web service is shown in Step 4 in the previous section.

Creating Folders While Deploying

In Steps 2 through 5, you entered information into properties of the Chapter09 project. These values tell the Report Designer where to put the reports and supporting items when the project is deployed. In this case, you instructed the Report Designer to put our reports and shared data source in the Chapter 09 folder within the Galactic Delivery Services folder.

You created the Galactic Delivery Services folder in the previous section. You did not create the Chapter 09 folder. Instead, the Report Designer created that folder for us as it deployed the items in the project. In fact, the Report Designer creates folders for any path you specify.

Deploying a Single Report

In Step 6, you used the project’s Context menu to deploy all the items in the project. Alternatively, you could have right-clicked a report and selected Deploy from the report’s Context menu. However, this would have deployed only this report, not the entire project.

On some occasions, you might want to deploy a single report rather than the entire project. At times, one report is going to be completed and ready for deployment, while the other reports in the project are still under construction. At other times, one report will be revised after the entire project has already been deployed. In these situations, it is only necessary to redeploy the single revised report.

Deploying Shared Data Sources

Even when a single report is deployed, any shared data sources used by that report are automatically deployed along with it. This only makes sense. A report that requires shared data sources does not do much if those shared data sources are not present.

If you look back at Figure 10–3, you notice an OverwriteDataSources item in the dialog box. This controls whether a shared data source that has been deployed to the Report Server is overwritten by subsequent deployments. In most cases, shared data sources do not change, so they do not need to be overwritten. For this reason, OverwriteDataSources is set to False, meaning do not overwrite existing data sources.

Aside from saving unnecessary effort, not overwriting data sources also helps out in another way. Consider the environment shown in Figure 10–4. In this environment, reports are developed in Visual Studio using a shared data source that points to a development database server. Once the first version of the report is completed, it is deployed to a production Report Server, as shown in Figure 10–5. As soon as the deployment is complete, the shared data source on the production Report Server needs to be changed to point to the production database server. This is shown in Figure 10–6.

Figure 10–4: A report and a shared data source ready to deploy

Figure 10–5: Deploying the report and shared data source

Figure 10–6: Modifying the shared data source to point to the production database server

Now, as time has passed, a new version of the report (version 2) is created in the development environment. This time, when version 2 of the report is deployed to the production Report Server, the shared data source already exists there.

If OverwriteDataSources is set to True, the data source from the development environment would overwrite the data source in the production environment, and we would be back to the situation in Figure 10–5. With this setting, we would have to redirect the shared data source each time a report is deployed.

To avoid this, OverwriteDataSources is set to False. Now when version 2 of the report (and subsequent versions) is deployed to the production Report Server, the shared data source is not overwritten. It remains pointing to the production database server. This is shown in Figure 10–7. We have saved a bit of extra effort with each deployment.

Figure 10–7: A subsequent deployment with OverwriteDataSources set to False

As you will see throughout this chapter, folders are used to organize reports on the Report Server and help manage security for those reports. You can, if you are managing your Report Server properly, have reports deployed in a number of different folders. A number of these reports use the same database as the source for their data. Rather than having a number of shared data sources scattered throughout the folders on your Report Server, it makes more sense to have the reports all reference a single data source or set of data sources stored in one central folder. This is accomplished through the use of the TargetDataSourceFolder.

Just as the TargetReportFolder property enables you to specify a path to a Report Server folder where a report is to be deployed, the TargetDataSourceFolder property, on the Property Pages dialog box, lets you specify a path to a Report Server folder where the shared data source should be deployed or found, if it already exists there. If no folder path is specified, the TargetDataSourceFolder defaults to the TargetReportFolder. The OverwriteDataSources flag applies whether the shared data source is being deployed to the TargetReportFolder or the TargetDataSourceFolder.

Additional Properties in the Property Pages Dialog Box

If you look back at Figure 10–3, you can see a couple additional items in the Property Pages dialog box that we have not discussed. We look at those two items now.

Maintaining Multiple Configurations   At the top of the dialog box is the Configuration drop-down list. This drop-down list enables you to maintain several different deployment configurations for the same project. Each configuration has its own values for TargetDataSourceFolder, TargetReportFolder, TargetServerURL, and the other settings in the dialog box.

This is useful if you need to deploy the reports in a project to more than one Report Server. Perhaps you have the Report Server loaded on your own PC for your own testing, a development Report Server where the report undergoes quality assurance testing, and a production Report Server where the report is to be made available to the end users. You can enter the properties for deploying to the Report Server on your PC in the DebugLocal configuration, the properties for deploying to the development Report Server in the Debug configuration, and the properties for deploying to the production Report Server in the Production configuration.

You can then easily switch between deploying to each of these Report Servers as new versions of your reports go from your own testing to quality assurance testing and are then made available to the users. You can change the configuration you are using for deployment through the Solution Configuration drop-down list in the Report Designer toolbar, as shown in Figure 10–8.

Figure 10–8: The Solution Configuration drop-down list

Running a Report Project   The final item we want to look at in the Project Property Pages dialog box is StartItem, which is used when running your report project. Use the StartItem drop-down list to select which report from your project should be executed when you run the project. The report selected as the start item is displayed in a browser window in HTML format.

When you run a report project, you deploy all the reports, shared data sources, and other supporting information in the project to the target server and target folders in your active configuration. Once the deployment is complete, the report specified as the start item is executed in a browser window. You can then debug this report, making sure it looks correct and functions properly in HTML format. You can run the project by clicking the Start button on the toolbar (to the left of the Solution Configuration drop-down list) or by selecting any of the following items from the Debug menu (or by pressing any of the shortcut keys that correspond to these menu items):

• Start Debugging

• Start Without Debugging

• Step Into

• Step Over

There is no such thing as stepping into or over a report. These menu items simply run the project. The report selected as the start item is executed in a browser window from start to finish.

Uploading Reports Using Report Manager

Another common method of moving a report to the Report Server is by using the Report Manager. This is known as uploading the report. Deploying reports from the Report Designer can be thought of as pushing the reports from the development environment to the Report Server, whereas uploading reports from the Report Manager can be thought of as pulling the reports from the development environment to the Report Server.

You may need to use the Report Manager upload in situations where your report authors do not have rights to deploy reports on the Report Server. The report authors create their reports and test them within the Report Designer. When a report is completed, the report author can place the RDL file for the report in a shared directory or send it as an e-mail attachment to the Report Server administrator. The Report Server administrator can upload the RDL file to a quality assurance Report Server and test the report for clarity, accuracy, and proper use of database resources. Once the report has passed this review, the Report Server administrator can upload the report to the production Report Server.

Uploading Reports in the Chapter06 Project Using the Report Manager

Let’s try uploading some of the reports from the Chapter06 report project:

1. Open the Report Manager in your browser. Click the Galactic Delivery Services folder to view its content.

2. Create a new folder called Chapter 06.

3. Select the new folder to view its contents.

4. Click the Upload File button in the toolbar on the Contents tab. The Upload File page appears, as shown in Figure 10–9.

   Figure 10–9: The Upload File page

5. Click Browse. The Choose file dialog box appears.

6. Navigate to the folder where you created your solution for Chapter 6. If this folder is in the default location, you can find it under the following path:

   My Documents\Visual Studio 2005\Projects\MSSQLRS\Chapter06

7. Select the Nametags report (Nametags.rdl) and click Open to exit the Choose file dialog box.

8. Click OK to upload the file.

9. The Nametags report has been uploaded to the Chapter 06 folder.

10. Click the Nametags report to execute it. You see an error similar to the one in Figure 10–10. You received this because, unlike the deployment from the Report Designer, the upload in Report Manager did not bring the shared data source along with the report.

    Figure 10–10: The Reporting Services Error page

11. Click the link to the Chapter 06 folder at the top of the page.

Creating a Shared Data Source in the Report Manager

To get the Nametags report functioning, you need to provide it with a shared data source. One way to do this is to create a new shared data source using the Report Manager. Follow these steps:

1. Click the New Data Source button in the toolbar on the Contents tab. The New Data Source page for a shared data source appears, as shown in Figure 10–11.

   Figure 10–11: The New Data Source page

2. Type Galactic for Name.

3. Type Connection to the Galactic Database for Description.

4. Make sure Microsoft SQL Server is selected in Connection type. Other options here are OLE DB, Microsoft SQL Server Analysis Services, Oracle, ODBC, and XML.

5. Type data source=(local);initial catalog=Galactic for Connection String. If the Galactic database is not on the Report Server, but is on a different computer, put the name of that computer in place of (local) in the connection string.

   Note

   Do not include the parentheses if you use a computer name in place of (local).

6. Select the option Credentials Stored Securely in the Report Server.

7. Type GalacticReporting for User Name.

8. Type gds for Password.

9. Click OK to save the data source and return to the Chapter 06 folder.

10. Click the Nametags report to execute it. You receive the same error message page because we have not yet told the report to use our new data source.

11. Select the Properties tab. The properties page for the Nametags report appears.

12. Click the Data Sources link on the left side of the screen. The Data Sources page for an individual report appears.

13. A shared data source should be selected. Click Browse. The Select a Shared Data Source page appears.

14. Expand each folder in the tree view under Location until you can see the Galactic shared data source in the Chapter 06 folder. Click the Galactic shared data source. The path to the Galactic shared data source is filled in Location. (You can also type this path into Location if you do not want to use the tree view.)

15. Click OK to exit the Select a Shared Data Source page.

16. Click Apply at the bottom of the page.

    Note

    It is easy to forget to click Apply when making changes to a report’s data sources. If you do not click Apply, none of your changes are saved. This can lead to confusion, frustration, and wasted troubleshooting time. At least, that is what I have been told.

17. Select the View tab to view the report. The report now generates using the new shared data source. (A red X is where the GDS logo should be. We deal with this in the section “Uploading External Report Images.”)

18. Once the report has completed generating, click the Chapter 06 link at the top of the page.

Hiding an Item

Figure 10–12 shows the list view of the Chapter 06 folder. The Galactic shared data source appears in the left column. Shared data sources have a cylinder and four arrows icon. The Nametags report appears in the right column. Reports have an icon showing a piece of paper with a bulleted list.

Figure 10–12: The Chapter 06 folder list view

When users are browsing through folders to find a report, you may not want other items, such as shared data sources, cluttering things up. It makes more sense to have the shared data sources where the reports can use them, but out of sight of the users. Fortunately, Report Manager provides a way to do just that:

1. Click the Galactic data source. The Data Source Properties page appears.

2. Check the Hide in List View check box.

3. Click Apply to save this change.

4. Click the Chapter 06 link at the top of the page.

The Galactic data source is no longer visible in the list view. You can use this same technique to hide reports you do not want to have generally available to users browsing through the folders.

If you do need to edit the Galactic data source, you can view it by using the detail view of the folder. Follow these steps:

1. Click the Show Details button in the toolbar on the Contents tab. The Galactic data source is now visible in this detail view, as shown in Figure 10–13. By default, the detail view is in alphabetical order by name.

   Figure 10–13: The Chapter 06 folder detail view

2. Click the Type column heading. The detail view is now sorted by type in ascending order. (In an ascending sort by type, the reports are at the top of the list, with supporting items, such as shared data sources, at the bottom.) Note, the downward, black arrow is now next to the Type column heading on your screen.

3. Click the Type column heading again. The detail view is now sorted by type in descending order. Now the black arrow is pointing upward next to the column heading.

   Note

   The name of the sort order (ascending or descending) and the direction of the black arrow may seem opposite to one another. Remember this: in an ascending sort, you move from smaller values (A, B, C…) to larger values (…X, Y, Z). When you move through the list in the direction of the arrow, you also move from smaller values to larger values.

4. Click the Modified Date column heading. The detail view is sorted by modified date in ascending order. You can sort the detail view by Type, Name, Description, Modified Date, Modified By, or When Run, in either ascending or descending order.

5. Click the Hide Details button in the toolbar on the Contents tab. You are back to the list view.

Connect Using Options

When you are accessing data from a server-based database, such as SQL Server or Oracle, you need to provide some type of credentials, usually a user name and password, to show you have rights to access the data. Keeping these credentials secure is an important concern. The shared data sources created on the Report Server provide several methods for specifying these credentials.

When entering the connection string into a shared data source, it is best not to include the credentials in the connection string itself. The connection string is displayed as plain text to anyone who views the Data Source Properties page. To better protect password information, always enter the credential information under one of the Connect Using options described here.

Credentials Supplied by the User   The first Connect Using option is to have the user enter the credentials required by the data source each time the report is run. This is the “Credentials supplied by the user running the report” option. You can specify the prompt to be presented each time the user must enter these credentials. If the Use as Windows Credentials When Connecting to the Data Source check box is checked, the user name and password entered by the user are treated as a Windows login. This means the user name and password provide database access using Windows Integrated security. If this check box is not checked, the user name and password are treated as a database login.

Having the user enter the credentials each time the report is run is the most secure option. No login information is stored with the data source, but most users are not pleased with a system where they must enter login information each time they run a report. This option may be appropriate when your organization’s security policy forbids storing login information in any way. In most other cases, the other Connect Using options provide a better solution.

Credentials Stored in the Report Server   The next option enables you to have the user name and password stored in the Report Catalog on the Report Server. This is the “Credentials stored securely in the report server” option. The user name and password entered with this option are encrypted when they are stored in the Report Catalog. Also, the password is not displayed to the user in the Data Source Properties page.

This Connect Using option is convenient for the user because they do not need to remember and enter credentials to run reports using this data source. It also provides the required security for most situations through the measures noted in the previous paragraph.

As with the first Connect Using option, there is a Use as Windows Credentials When Connecting to the Data Source check box here as well. If this check box is checked, the user name and password stored in the Report Catalog are treated as a Windows login. If this check box is not checked, the user name and password are treated as a database login.

The second check box under this Connect Using option is Impersonate the Authenticated User After a Connection Has Been Made to the Data Source. If this check box is checked, the data source can use these credentials to impersonate this user. Not all database servers support this type of delegation of credentials. Consult the documentation for your specific database server for more information.

Integrated Security   If you are not comfortable with storing credentials in the Report Catalog, but you do not want your users entering credentials every time a report is run, integrated security may be the solution for you. The Windows integrated security option does not require the user to enter credentials. Instead, it takes the Windows login credentials that let the user access the Report Manager and passes them along to the database server. Your database server, of course, needs to be set up to accept these credentials.

Integrated security always works when the data source exists on the same server as the Report Server. It may run into problems, however, if the data source is on another server. The problems are caused by the way integrated security works between servers.

For a better understanding of the problems with integrated security, let’s look at an example of the way integrated security works. The user logs in to their computer. This computer knows everything about this user because the original authentication occurred here.

When the user accesses the Report Manager application, the user’s credentials are passed from the original computer to the computer hosting the Report Server. However, using standard Windows security, not everything about this login is passed to the Report Server computer—only enough to authenticate the user is passed. Some sensitive information does not make this hop across the network.

When the user runs a report with a data source using integrated security, the Report Server must pass on the credentials to the database server. However, the Report Server does not have the complete credentials to pass along. In fact, it does not know enough about the user to successfully authenticate them on the database server. The authentication on the database server fails. Using standard Windows security, integrated security only works across one hop, from the original authenticating computer to a second computer. In the case of the Report Manager, this is the hop from the user’s computer to the Report Server.

To get integrated security to work across more than one hop, your Windows domain must use a special kind of security known as Kerberos, which allows authentication across multiple hops. Using Kerberos security, integrated security works across any number of servers in the network.

Credentials Not Required   The final Connect Using option is for data sources that do not require any authentication. This option would be used for connection to some Access databases, FoxPro databases, and others that do not require any login or password. This option could also be used if you insist, despite prior warnings here, on putting your credentials right in the connection string.

Uploading Other Items Using Report Manager

In addition to reports and shared data sources, other items can be uploaded to Report Server folders. External images needed as part of the reports can be uploaded, for example, as well as documentation and other supporting materials.

Uploading External Report Images

If you look closely at the Nametags report when it comes up in Report Manager, you notice this report has a problem. The GDS logo that should appear in the lower-left corner of each nametag is missing. You see the broken-link X symbol instead of the GDS logo.

This image was stored as an external image in the Chapter06 project. We need to upload this image to the Report Server. Once the image is uploaded into the same folder as the report, the report can find it. Here are the steps to follow to do this:

1. Return to the Chapter 06 folder in the Report Manager.

2. Click Upload File in the Contents tab toolbar. The Upload File page appears.

3. Click Browse. The Choose File dialog box appears.

4. Navigate to the folder containing the Chapter06 project. Select the gds.gif file and click Open to exit the Choose File dialog box.

5. Leave the name as gds.gif. The image needs to keep this name, so it can be found by the report. Click OK to upload this file.

6. Click the Nametags report to execute it. If the broken-link X is still visible, click the Refresh Report button in the Report Viewer toolbar, as shown in Figure 10–14.

   Figure 10–14: The Refresh Report button in the Report Viewer toolbar

   Note

   When you need to have Report Manager refresh a report, always use the Refresh Report button in the Report Viewer toolbar. Do not use the browser’s Refresh button. The browser’s Refresh button causes the page to be refreshed, but it does not cause the report to be reexecuted.

7. Click the link for the Chapter 06 folder.

8. The entry for the gds.gif image shows in the list view of the Chapter 06 folder. As with the Galactic shared data source, you probably don’t want entries for supporting resources cluttering up your list view. Click the entry for gds.gif. The gds.gif image is displayed.

9. Click the Properties tab.

10. Check the Hide in List View check box.

11. Click Apply.

12. Click the link for the Chapter 06 folder.

Uploading Supporting Materials

In some cases, you need to provide your users with documentation on one or more reports in the form of either a text file, or a Word or HTML document. Supporting materials may also be created in other applications. For example, you may have a PowerPoint presentation or a Visio diagram that aids in the interpretation and understanding of a set of reports. These materials can be uploaded as a folder item just like report files.

A text file or an HTML document can be displayed right in the browser without any additional software. For other types of documents, if the appropriate application is installed on the user’s computer, the documents can be viewed right in the browser as well. These documents can also be downloaded and saved to the user’s computer, if desired.

Now, we’ll create a simple text document, and then upload it to the Chapter 06 folder:

1. Open Notepad or another text editor.

2. Type the following in the text editor:

   The items in this folder are for the GDS Conference.

3. Save this as ReportReadMe.txt in a temporary location on your computer.

4. Return to your browser with the Report Manager viewing the Chapter 06 folder. Click Upload File in the Contents tab toolbar. The Upload File page appears.

5. Click Browse. The Choose File dialog box appears.

6. Navigate to the ReportReadMe.txt file and click Open to exit the Choose File dialog box.

7. Click OK to upload this file.

8. Select the ReportReadMe.txt entry in the Chapter 06 folder. You see the contents of the text file displayed within the Report Manager.

9. Click the link for the Chapter 06 folder.

10. Let’s add a second line to our text file. Open the ReportReadMe.txt file in your text editor and add the following as a second line:

    These items were created for the GDS Art Department.

11. Save the changes and close your text editor.

12. Return to your browser with the Report Manager viewing the Chapter 06 folder. Click Upload File in the Contents tab toolbar. The Upload File page appears.

13. Click Browse. The Choose File dialog box appears.

14. Navigate to the ReportReadMe.txt file and click Open to exit the Choose File dialog box.

15. Check the Overwrite Item If It Exists check box. If you fail to check this check box, the new version of the text file does not overwrite the older version on the Report Server.

16. Click OK to upload this file.

17. Select the ReportReadMe.txt entry in the Chapter 06 folder. You see the new version of the text file.

18. Click the Properties tab.

19. Type The purpose of these reports…for the description.

20. Click Apply to save your changes.

21. Click the link for the Chapter 06 folder. The description shows up under the entry for ReportReadMe.txt.

22. Let’s make another change to our text file and look at another way to overwrite an entry on the Report Server. Open the ReportReadMe.txt file in your text editor and add the following as a third line:

    These items were created for all billing contacts.

23. Save the changes and close your text editor.

24. Return to your browser with the Report Manager viewing the Chapter 06 folder. Select the ReportReadMe.txt entry.

25. Click the Properties tab.

26. Click Replace.

27. Click Browse. The Choose File dialog box appears.

28. Navigate to the ReportReadMe.txt file and click Open to exit the Choose File dialog box.

29. Click OK to upload this file.

30. Click the View tab. You see the latest version of the text file.

31. Click the link for the Chapter 06 folder.

32. Delete the ReportReadMe.txt file on your computer.

Uploading Reports Using .NET Assemblies

In addition to external images, reports can also reference .NET assemblies. You saw this in the Weather Report and the Delivery Analysis Report created in Chapter 8. Let’s look at the steps necessary to move these reports to the Report Server.

Copying the .NET Assembly to the Report Server

For a report to access a .NET assembly, it must be in the application folder of the Report Server. No fancy deployment, upload, or installation routine is required here. Simply copy the assembly’s DLL file to the appropriate directory. We can give this a try using the Weather Report and its .NET assembly, WeatherInfo.dll, as well as the

Delivery Analysis Report and its .NET assembly, ReportUtil.dll. Here are the steps to follow:

1. Locate the WeatherInfo.dll and ReportUtil.dll files. You also need the ES folder that contains the Spanish version of the ReportUtil.dll. This Spanish version is called ReportUtil.resources.dll. (The folder name, ES, is the two-letter code for Español.) If you do not have them anywhere else, they should be in the Public Assemblies folder on your development computer. The default path for the Public Assemblies folder is

   C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PublicAssemblies

2. Copy these files and the ES folder.

3. Paste the files and the ES folder into the Report Server application folder on the computer acting as your Report Server computer. You may receive a warning because a folder called ES already exists. Click Yes to continue. The default path for the Report Server application folder is

   C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ ReportServer\bin

Code Access Security

Because Reporting Services is a .NET application, it uses code access security to determine what execution permissions are possessed by each assembly. A code access group associates assemblies with specific permissions. The criteria for membership in a code access group are determined by a security class, and the permissions are determined by named permission sets.

Figure 10–15 provides an illustration of code access security. A .NET assembly or web service can gain entry into a code access group only if it matches the criteria specified by the security class. Once the .NET assembly or web service is allowed into a code access group, it can use the named permission set associated with that code access group to gain rights. These rights allow the .NET assembly or web service to perform tasks on a computer. Full trust rights and execution rights are the two types of rights we use with the Weather report. A number of different types of rights, however, can be included in a named permission set.

Figure 10–15: Code access security

Code access groups can be nested one inside another. A .NET assembly or web service can be allowed into a parent group and gain its permissions; then it can try to gain membership in child code access groups to accumulate additional rights. A code access group can be a first match code group, where a .NET assembly or web service can only gain membership in one code access group—the first one it matches. Or, a code access group can be a union code group, where a .NET assembly or web service is allowed to gain membership in a number of code access groups, joining together the permissions from each group.

For our Weather report to execute properly, we will have to create a code access group that provides permissions to the WeatherInfo.dll assembly. Also, we will have to create a second code access group to provide permissions to the web service that we are using to get our weather information. Even though this web service is not executing on our server, our WeatherInfo.dll assembly is executing some of its methods, so it needs to have permission to execute.

Security Classes   A security class describes the conditions a .NET assembly or web service needs to meet to get into a code access group. We use two different types of security classes with the Weather report. The UrlMembershipCondition security class is used with the web service, and the StrongNameMembershipCondition security class is used with the WeatherInfo.dll and ReportUtil.dll assemblies.

The UrlMembershipCondition security class says that any assembly or web service being executed from a specified URL is to be included in a particular code access group. The URL that must be matched is listed in each code access group using the UrlMembershipCondition security class. For example, the GDSServer code access group may use UrlMembershipCondition and give http://GDSServer/* as the URL that must be matched. Any web service running on the GDSServer would be included in this code access group.

The StrongNameMembershipCondition security class uses the strong name associated with an assembly to identify it. The strong name, which is a long string of hexadecimal digits that uniquely identifies an assembly, is assigned to the assembly when it is created. The StrongNameMembershipCondition security class is a good way to ensure that only the intended assembly is allowed into your code access group.

You see a couple of other security classes in the Report Server security configuration. The AllMembershipCondition security class allows in all .NET assemblies and web services. The ZoneMembershipCondition security class allows in .NET assemblies and web services that originate in a particular zone. Some sample zones are MyComputer, intranet, and Internet.

Named Permission Sets   Named permission sets group together the permissions to be assigned by code access groups. The security configuration used by the Report Server contains three named permission sets. The Nothing permission, which grants no rights, is used to initially take away all rights from a .NET assembly or web service before specific rights are added back by subsequent code access groups. This ensures each .NET assembly or web service has only the rights it should have.

The Execution permission grants execution rights to a .NET assembly or web service. This means the .NET assembly or web service can be run. The .NET assembly or web service does not, however, have rights to access any protected resources, such as the file system, the registry, or the Internet.

The FullTrust permission grants the .NET assembly or web service access to everything. This includes access to all the protected resources. FullTrust permission should only be granted to .NET assemblies and web services that you trust not to mess up your computer!

Modifying the Report Server’s Security Configuration

Now that you have a basic understanding of code access security, we can modify the Report Server’s security configuration to allow the WeatherInfo.dll and the ReportUtil.dll to run.

We need to make some additions to the Report Server’s security configuration to provide our custom assemblies with the rights they need to execute. The security configuration for the Report Server is in the rssrvpolicy.config file. The default path for this file is

C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ ReportServer

This file contains the code-access security information in an XML structure.

The XML structure in the rssrvpolicy.config file can be divided into three sections: Security Classes, Named Permission Sets, and Code Groups. We only need to make changes to the Code Groups section of the document. Here are the steps to follow:

1. Open the rssrvpolicy.config file in Notepad or another text editor.

2. Scroll down until you locate the Code Group portion of the document. The Code Group portion of the document starts on the line after the closing XML tag for the named permission sets:

   </NamedPermissionSets>

3. The first code group is the parent code group, which makes use of the AllMembershipCondition to assign the Nothing permission to all .NET assemblies and web services. We add a new child code group right beneath this. Insert this new code group as shown here (add the lines shown in bold):

   . . . <CodeGroup version="1" PermissionSetName="Nothing"> <IMembershipCondition version="1" /> <CodeGroup version="1" PermissionSetName="Execution" Name="WeatherWebServiceCodeGroup" Description="Code group for the Weather Web Service"> <IMembershipCondition version="1" Url="http://live.capescience.com/*" /> </CodeGroup> <CodeGroup version="1" PermissionSetName="Execution" Name="Report_Expressions_Default_Permissions" Description="This code group grants default permissions for code in report expressions and Code element. "> . . .

4. Another parent code group uses ZoneMembershipCondition to assign Execution permissions to all .NET assemblies and web services in the MyComputer zone. We add a new child code group right beneath this. Insert this new code group as shown here (add the lines shown in bold). Note, the Description and PublicKeyBlob should each be entered on one line.

   . . . <CodeGroup version="1" PermissionSetName="Execution" Description="This code group grants MyComputer code Execution permission."> <IMembershipCondition version="1" Zone="MyComputer"/> <CodeGroup version="1" PennissionSetName="FullTrust" Name="MSSQLRSCodeGroup" Description="Code group for the MS SQL RS Book Custom Assemblies"> <IMembershipCondition version="1" PublicKeyBlob="0024000004800000940000000602000000 240000525341310004000001000100B9F7 4F2D5B0AAD33AA619B00D7BB8B0F767839 3A0F4CD586C9036D72455F8D1E85BF635C 9FB1DA9817DD0F751DCEE77D9A47959E87 28028B9B6CC7C25EB1E59CB3DE01BB516D 46FC6AC6AF27AA6E71B65F6AB91B957688 6F2EF39417F17B567AD200E151FC744C6D A72FF5882461E6CA786EB2997FA968302B 7B2F24BDBFF7A5" /> </CodeGroup> <CodeGroup c1ass="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="Microsoft_Strong_Name" Description="This code group grants code signed with the Microsoft strong name full trust."> <IMembershipCondition version="1" PublicKeyBlob="0024000004800000940000000602000000 24000052534131000400000100010007D1 FA57C4AED9F0A32E84AA0FAEFD0DE9E8FD 6AEC8F87FB03766C834C99921EB23BE79A D9D5DCC1DD9AD236132102900B723CF980 957FC4E177108FC607774F29E8320E92EA 05ECE4E821C0A5EFE8F1645C4C0C93C1AB 99285D622CAA652C1DFAD63D745D6F2DE5 F17E5EAF0FC4963D261C8A12436518206D C093344D5AD293" /> </CodeGroup> . . .

5. Save the modified file and exit your text editor.

   Note

   Looking at the rssrvpolicy.config file, you can see that expressions written within a report are granted Execute permissions. Because the WeatherInfo.GetWeather method is called from a report expression, by default, it should only be able to get Execute permissions. .NET Security says a process cannot get rights that exceed the rights granted to processes further up the stack. The GetWeather method needs FullTrust rights to make the web service call. The GetWeather method uses a special process to assert that it needs to exceed the rights of the calling process and gain FullTrust rights. If you downloaded the source code for the WeatherInfo.dll, you can look to see how the assert is accomplished.

Uploading the Report

You are now ready to upload the Weather report. Complete the following steps using the Report Manager:

1. Create a folder called Chapter 08 in the Galactic Delivery Services folder.

2. Open the Chapter 08 folder and upload the WeatherReport.rdl file from the Chapter08 project folder.

3. Click the report WeatherReport to execute it. The report produces an error because the shared data source does not exist.

4. Click the Properties tab. The properties page for WeatherReport appears.

5. Click the Data Sources link on the left side of the screen. The Data Sources page for an individual report appears.

6. A shared data source should be selected. Click Browse. The Select a Shared Data Source page appears.

7. Rather than create another shared data source, we are going to use the existing shared data source in the Chapter 06 folder. Click Browse. Expand each folder in the tree view under Location until you can see the Galactic shared data source in the Chapter 06 folder. Click the Galactic shared data source.

8. Click OK to exit the Select a Shared Data Source page.

9. Click Apply at the bottom of the page.

10. Select the View tab to view the report. Select one or more planets and click View Report. The report now generates. (Remember, the .NET assembly calls a web service, so it requires an Internet connection.)

Try the Deploy One More Time

This last report upload required us to manually point the report to a shared data source in a different folder. This is because we do not want to have a shared data source in every report folder. If we had numerous shared data sources spread across a number of report folders, this would defeat much of the purpose of having shared data sources. When the database server name changes or the login credentials need to be updated, we would still have a major headache.

Instead, we want to have just one shared data source for each unique connection needed by our reports. This small group of shared data sources should be placed in one central location. That still leaves us with the task of manually pointing each report at the central group of shared data sources after each report upload.

You may recall there was a property on the report project’s Property Pages dialog box specifying the folder path where the shared data source is to be deployed. Let’s try deploying the Delivery Analysis Report from the Report Designer and see if this property can help us avoid all of the manual updating. Try the following:

1. Open the Chapter08 project in Visual Studio or the Business Intelligence Development Studio.

2. From the Main menu, select Project | Chapter08 Properties. The Chapter08 Property Pages dialog box appears.

3. Enter /Galactic Delivery Services/Chapter 06/ for TargetDataSourceFolder.

4. Enter /Galactic Delivery Services/Chapter 08/ for TargetReportFolder.

5. Enter http://ComputerName/ReportServer for TargetServerURL. Substitute the appropriate value for ComputerName as you did earlier in this chapter.

6. Click OK to close the Chapter08 Property Pages dialog box.

7. Select Save All from the toolbar.

8. Right-click the entry for the Delivery Analysis report in the Solution Explorer window and select Deploy from the Context menu.

9. Notice in the Output window that the Report Designer attempted to deploy the shared data source from this project along with the report. This did not work because there is already a shared data source with the same name in the Chapter 06 folder and the OverwriteDataSources property is set to False. What, you may ask, did we accomplish by putting a path in TargetDataSourceFolder? In addition to trying to deploy the shared data source to a specific folder, this process also instructs the deployed report to look in that same folder for the data sources it needs.

10. Switch to the browser and navigate to the Chapter 08 folder.

11. Execute the Delivery Analysis report.

12. Select a number of service types from the drop-down list and click View Report. The report displays using the shared data source found in the Chapter 06 folder.

A Look at Localization

You may recall we used the ReportUtil.dll assembly to present the report labels in both English and Spanish. (If you do not recall this, look at the instructions for this report in Chapter 8.) We passed the User!Language parameter to the LocalizedString method to retrieve a report label in the appropriate language. The User!Language parameter contains the language setting for the application requesting the report. When we are using the Report Manager, the browser is that application.

Let’s try changing the language setting of the browser and see if our localization works the way it should. (The following directions apply to Internet Explorer.)

1. Select Tools | Internet Options from Internet Explorer’s Main menu. The Internet Options dialog box appears.

2. Click Languages. The Language Preference dialog box appears.

3. If an entry for Spanish (Mexico) [es-mx] is not in the Language list, click Add. The Add Language dialog box appears.

4. Highlight Spanish (Mexico) [es-mx] in the Language list and click OK to exit the Add Language dialog box.

5. Highlight Spanish (Mexico) [es-mx] in the Language list and click Move Up as many times as necessary to move the Spanish entry to the top of the list.

6. Click OK to exit the Language Preference dialog box. Click OK to exit the Internet Options dialog box.

7. Click the link for the Chapter 08 folder, and then re-execute the Delivery Analysis report. The User!Language parameter now has a value of es-mx because you set the primary language of your browser to Spanish (Mexico). Because of this, the title of the report and the column headings are now Spanish.

8. Use the Language Preference dialog box to remove the Spanish entry, if you created it in Steps 3 and 4. Make sure you return the correct language to the top of the Language list.

The ReportUtil.dll assembly has resource files for English and Spanish. English is the default language. If the parameter passed to the LocalizedString method is any of the cultural variations of Spanish, the method uses the Spanish resource file to look up the text for the report title or a column heading. If anything else is passed to the LocalizedString method, the English resource file is used.

Modifying Reports from the Report Server

In addition to uploading a report definition to the Report Server, it is also possible to download a report definition, modify it, and send your modifications back to the Report Server as an update. You only need to do this if you do not have a copy of the RDL file for a report that is on the Report Server and needs to be modified. If you already have the report in a report project, you can edit that report using the Report Designer, and then redeploy it.

Downloading a Report Definition

For this example, imagine we do not have the RDL file for the SubReportTest report and need to make a change to the report. The first task we need to complete is to download this report’s RDL file from the Report Server to our local computer. Follow these steps:

1. Open the Report Manager in your browser and navigate to the Chapter 09 folder.

2. In the previous section, when we wanted to view the Properties tab for a report, we first executed that report. Now, we use the Show Details button to get at the Properties tab another way. Click the Show Details button in the Contents tab toolbar. The detail view of the folder’s contents appears.

3. Click the icon in the Edit column next to the SubReportTest report. The Properties tab for the SubReportTest report appears.

4. There is a Report Definition section on this page just above the buttons at the bottom. Click the Edit link in the Report Definition section. This causes the Report Manager to download a copy of the SubReportTest.rdl file, so you can edit it. The File Download dialog box appears.

5. Click Save. The Save As dialog box appears.

6. Browse to an appropriate temporary location on your computer. Leave the filename as SubReportTest.rdl. Click Save to exit the Save As dialog box. The file is downloaded and saved in the specified location.

Editing the Report Definition

We now have the report definition file for the SubReportTest report moved from the Report Server to our local computer. However, an RDL file by itself is not useful. To edit it, we have to place it in a report project. Again, remember, for this example, we are imagining we do not already have the SubReportTest report in a report project. Here are the steps to follow:

1. Start Visual Studio or the Business Intelligence Development Studio.

2. Create a new report project in the MSSQLRS folder called EditSubReportTest. (Do not use the Report Wizard.)

3. Create a shared data source called Galactic for the Galactic database using GalacticReporting for the user name and gds for the password.

4. Right-click the Reports entry in the Solution Explorer and select Add | Add Existing Item from the Context menu. The Add Existing Item dialog box appears.

5. Navigate to the location where you stored the SubReportTest.rdl file in the previous section. Select the SubReportTest.rdl file and click Open to exit the Add Existing Item dialog box.

6. Double-click the SubReportTest report to open it for editing. (If you encounter an error while trying to edit this report, save the project, close the Report Designer, restart it again, and reopen the EditSubReportTest project.)

7. On the Data tab, add the PurchaseDate to the output.

8. On the Layout tab, put the PurchaseDate in a text box to the right of the SerialNumber. Set the Format property for this text box to MM/dd/yyyy.

9. Use the Preview tab to make sure your changes were made properly.

10. Click Save All in the toolbar.

11. Close the Report Designer.

Uploading the Modified Report Definition

Now that the report definition changes are completed, we are ready to upload the modified report:

1. Return to the Report Manager. If you are not already there, navigate to the Properties tab for the SubReportTest report.

2. Click the Update link in the Report Definition section of the page. The Import Report page appears.

3. Click Browse. The Choose File dialog box appears.

4. Navigate to the EditSubReportTest folder to find the updated version of the SubReportTest.rdl file.

   Note

   Do not select the copy of SubReportTest.rdl you originally downloaded. The modified version is in the folder with the EditSubReportTest report project.

5. Select SubReportTest.rdl and click Open to exit the Choose File dialog box.

6. Click OK to upload the file.

7. Click the View tab to view the report, and then click the Report Refresh button in the Report Viewer toolbar. The purchase date is now shown for each transport.