SQL Server Reporting Services
SQL Server Reporting Services
My latest Reporting Services article called Reporting for Duty is in the latest issue of SQL Server Magazine. It should be pretty useful for you if you need to call the SOAP API to generate reports from custom applications.
When I was installing SQL Server 2005 last weekend, one of the first things I wanted to find out was whether I could run SSRS 2000 and SSRS 2005 on the same computer. I seriously doubted it was possible knowing that RS installs a windows service and creates a couple of ASP.NET sites. But after a lot of digging in BOL, I found that you can indeed run both version on the same computer under certain conditions - you have to run Windows Server 2003, SQL Server 2000 has to be the default instance and SQL Server 2005 has to be a named instance. This procedure is from BOL:
-
Verify that SQL Server 2000 Reporting Services is running as the default instance.
-
Run Setup for SQL Server 2005. When you use the SQL Server Installation Wizard, specify a unique instance name on the Instance Name page. When you run Setup from the command prompt, specify a unique instance name by using the instancename option.
-
Choose a files-only installation for the report server. Use the install but do not configure option in the Report Server Installation Options page in the SQL Server Installation Wizard or RSCONFIGURATION=filesonly in a command line installation.
-
Run the Reporting Services Configuration tool to deploy the new SQL Server 2005 instance. Choose a unique virtual directory for the report server and Report Manager. The names cannot be identical to those used in the SQL Server 2000 instance.
The path to the topic in BOL is "ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/4ae50570-7c84-4404-8f4f-f76b646f8518.htm".
I ran into a couple of issues. I was not for whatever reason able to configure a RS database. I tried creating it many times and the configuration tool was showing that it was not able to connect to the report server database. Eventually I got it working after uninstalling and reinstalling SSRS 2005. The other issue was that when I ran rsconfig manually (hoping I would be able to create connection settings this way), the utility was modifying the config file for my SSRS 2000 install. So keep in mind that if you decide to run rsconfig to configure SSRS 2005, you have to refernce the one in the "C:\Program Files\Microsoft SQL Server\90\Tools\Binn" folder because the DOS PATH will likely use the 2000 version since it appears in the PATH before the new one. The chance is that you may never have to touch it since the RS config tool can do the full setup, but it's good to know.
Before you start instaling SSRS 2005, you should backup up both RS 2000 databases, make a copy of the key and the RSReportServer.config file just in case your SSRS 2000 instance gets messed up.
IIS 6.0 allows you to create application pools, then each application pool can be configured to run under a separate identity. When my company deploys web sites, our practice is to create an account for each web application and then create a new application pool to run under that account. This approach however doesn't work with Reporting Services, you might end up getting weird errors like:
"The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database..."
"Key not valid for use in specified state."
This happened to us with our first RS deployment. After many hours of frustration we opened a support call with Microsoft, then it still took some time to finally diagnose the problem. The bottom line is that RS in IIS 6.0 needs to run under the predefined Network Service account, you cannot use a configured account.
If you work with Reporting Services, you probably noticed how long it takes to start Report Manager after a period of inactivity. One of the reasons for this is that it actually needs to initialize and load two applications (RM is an ASP.NET web application and the backend Report Server is a web service). You can keep the application loaded in Windows 2003 Server if you open the application pool seettings and uncheck “Shutdown worker process after being idle for...”. After you do this, any web application that uses the application pool will remain loaded.
We had a strange RS experience at one of our customer sites. It looked like the Report Server windows service started executing ExpireExecutionLogEntries stored procedure in an infinite loop, it was getting kicked off every few milliseconds. The CPU usage was close to 100% for a few hours, affecting the server pretty badly. Stopping the service made it go away, but it resumed when we restarted it. I searched the newsgroups and found a post about the same issue, followed by a response from Microsoft, saying "We have seen the issue and we are looking into the cause". The issue finally went away after rebooting the server. If anybody from the RS teams reads this - I'd be more than happy to help you figure out the cause and collect data from the server if needed.
Last week I talked about setting up your own certificates on RS servers. Today I'll cover a few issues around using SSL with the Report Server web service and the Report Manager application.
There are two ways to enforce SSL requirement with Reporting Services. One option is to configure the Reports web application and the ReportServer web service in IIS Manager console to require SSL. The other option is to modify the value of the SecureConnectionLevel setting in the Report Server configuration file (RSReportServer.config). The SecureConnectionLevel can have 4 different levels - 0, 1, 2 and 3. Depending on the level you choose, SSL will be required for none, some, most or all calls to the RS. The level 3 is the equivalent of using the site properties in IIS Manager to require SSL, it basically enforces SSL for all method calls. Level 0 means no SSL will be required.
The initial RTM release of RS has a few issues that make using SSL with RS complicated. The levels 1 and 2 require only certain methods to use SSL, but the Report Manager app doesn't always get those methods right and it renders some links with http instead of https. If you click on one of those links, you'll get an error telling you that SSL needs to be used. The level 3 works the best - all application links are rendered as https, with the exception of Help. The help link still renders with http instead of https, and you get the same error when you open the link.
My personal opinion is that there is very little reason to use levels 1 or 2, why not just go all the way and make everything secure. You can follow these steps in the RTM version to set up Report Manager so that all links render with https and Help still works:
- In IIS Manager, configure the ReportServer web service to require SSL. Instead of this step you can set the SecureConnectionLevel to 3 in the config file, but I prefer enforcing it in IIS.
- Do the same in the Reports web application setting (Report Manager). After this change all links in RM will be rendered with https, except for Help. We will fix that in the next step
- Expand the Reports site in IIS Manager, open the properties of the EN subfolder and uncheck the SSL setting. The EN folder contains the English help files so deselecting SSL on that folder will allow you to view help without SSL. You will need to modify other folders if you use help other than English.
It appears that all of these issues have been addressed in SP1. I tested all levels without getting any errors. Levels 1 and 2 seem to correctly render links with https or just http as needed. Level 3 renders everything with https. If you manually change the link to http, Report Manager redirects the call to https. You now have two options for fully enforcing SSL - you can either follow the previous steps and configure the folders in IIS, or you can just set SecureConnectionLevel to 3 in the config file.
If your Reporting Services is available over the Internet, you shouldn't even consider putting it out without requiring SSL, otherwise your username and password will be exposed without encryption. That's often easier said than done, especially because a certificate authenticated by a trusted authority is expensive and most companies don't want to purchase a certificate for every development/staging/QA server in their environment. While you can generate your own certificates with a server that has the Certificate Authority services installed, such certificates are not considered fully trusted because they haven't being issued by a trust authority going all the way to “the root”. You might recall that if a browser detects a certificate that's not totally kosher (it could be expired, issued to a different computer or issued by a non-trusted authority), it pops up a dialog and gives you the option to use or not use the certificate.
If you use such certificate with Report Server, it's likely that you will get this error:
The underlying connection was closed: Could not establish trust relationship with remote server.
This error is common if a client application calls a web service via SSL and the certificate is not fully trusted. Even though you generated a certificate on the same network, the full trust is missing because your certificate server is not trusted by a root authority. If you're using a custom client application, you can use the workaround described in the Q823177 article and implement the ICertificatePolicy such that the CheckvalidationResult method always validates the certificate. This workaround doesn't help you though if you're using Report Manager because you can't really modify the source code in the application. But luckily there is another workaround that I discovered after many hours of trial and error - Reporting Services will use a certificate if it was generated on the SAME server where the IIS part of RS is running. So if you run into this problem, install the Certificate Authority component on the IIS server and use a certificate generated by that service.
Next week I'll discuss additional issues you may run into when using SSL with Reporting Services.
Darn, why didn't I think of turning my
blog entry into this
article, like someone else did.
There will be an MSDN webcast next Monday about New Features in Reporting Services Service Pack 2.
A few months ago when I was giving a presentation on Reporting Services someone asked me how to restrict the selection of export formats. Specifically, they wanted to remove XML and CSV formats since the output is not very user friendly and it's only useful in very specific scenarios. At the time I didn't know the answer but I found out since then.
The items in the Export dropdown list can be controlled through the RSReportServer.config file, that's the RS config file stored in the ReportServer folder. First, find the <Render> section. In that section you should see a few rendering extensions, it's pretty easy to figure out what rendering output they map to. Once you find the format you want to hide, add this attribute to the <Extension> node:
Visible="false"
You should be able to see the Visible attribute on the NULL extension, the other extensions probably won't have it because the default value for Visible is true. Once you make the modification, the extension you modified should no longer show in the Export dropdown list. This modification only hides the export option in Report Manager but it still allows exporting into that format either programmatically or through direct URL access. So, you wouldn't really prevent savy users from modifying the URL of another exported report into something like "...Format=XML" and exporting into XML. If you want to disable let's say the XML format all together, comment out the the entire <Extension Name="XML"...> node to un-register the XML rendering extension.
Somebody just reported on the RS mailing list that SQL Server 2005 Express killed his Reporting Services. It might not happen to everybody but it's good to know that this could be an issue. Here is the text of the email:
Microsoft has posted a release of SQL Server 2005 Express Beta (http://lab.msdn.microsoft.com/express/sql/) .
Part of the install is the .NET Framework 2.0. But I install it, the Reporting Services instance on the same system gets corrupted. When I try to go to the http://localhost/reports, I get the error "System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.InvalidOperationException: Method ReportingService.SetExecutionOptions can not be reflected. ---> System.IO.FileLoadException: Unknown error - HRESULT 0x80131047"
Restarting the service did not help. Uninstalling Framework 2.0 resulted in Reporting Services having a corrupted keyset. After restoring the keys using rskeymgmt fixed the problem.
The first Service Pack for SQL Server 2000 Reporting Services is out. There are quite a few functionality enhancements. The most exciting enhancements for us are exporting to Excel 97 and 2000 and improved PDF rendering.
You can download the SP from this link.
I will be presenting “SQL Server 2000 Reporting Services Programming” at the upcoming DevTeach conference in Montreal. On Memorial Day I did a dry run of the session at the SQL Server Montreal User Group meeting. Just as I expected, it was very well received. Reporting Services presentations are an easy sell. So far I did RS presentations to about five different types of audience (developers, DBAs, upper management, midle management, system architects) and it seems like there is something for everyone. What I find very amusing is that the demo that got the best response was not the one that I spent five hours coding. It was something I put together in 15 minutes - simple RSS script executed with the RS script host. The script calls the RS web service, renders a report into Excel format and saves it to a file. You can execute the script from the command line like this:
rs -i "C:\RS Script\MyScript.rss" -s http://myserver/reportserver
Here is the code from MyScript.rss. It renders the Product Line Sales report, it's one of the sample reports that comes with RS:
Public Sub Main()
Dim format as string = "EXCEL"
Dim fileName as String = "C:\RS Script\Product Line Sales.xls"
Dim reportPath as String = "/SampleReports/Product Line Sales"
' Prepare Render arguments
Dim historyID as string = Nothing
Dim deviceInfo as string = Nothing
Dim showHide as string = Nothing
Dim results() as Byte
Dim encoding as string
Dim mimeType as string
Dim warnings() AS Warning = Nothing
Dim reportHistoryParameters() As ParameterValue = Nothing
Dim streamIDs() as string = Nothing
results = rs.Render(reportPath, format, _
Nothing, Nothing, Nothing, _
Nothing, Nothing, encoding, mimeType, _
reportHistoryParameters, warnings, streamIDs)
' Open a file stream and write out the report
Dim stream As FileStream = File.OpenWrite(fileName)
stream.Write(results, 0, results.Length)
stream.Close()
End Sub