All posts by Sam

Enabling Network Trace in Reporting Services

I’m trying to set up reporting services 2005 to use ssl with a 2008 instance also on the same machine also using ssl.  I’m trying to use the simple method of changing the ssl port of the 2005 site.  The website’s ssl certificate seems valid when I go to the /Reports directory, but a call is going bad somewhere inside.

The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.

I was directed to activate network tracing for the .net application as outlined here:

After figuring out what version of .net the application is using, which can be found in the IIS settings for the site, activating was as simple as moving the proper xml into my web.config file for the SSRS web application.

I also added this code to direct the trace output:

Now I’m seeing  the network.log file in the reportmanager directory.  I also see some calls happening to the webservice which don’t include the 444 port number which would be necessary to make the ssl connection.

Upgrading SQL Reporting 2005

This weekend I’ll be upgrading a copy of our 2005 instance to 2008.  Upgrading the copy will let us fall back to the old version if needed.  (update: and yes, we had to, see notes below).

I opted to create a new instance on another server – apart from our clustered SQL engine. Running IIS on a SQL server is not recommended and you can run into some memory issues with IIS and SQL 2005 reports. It’s also a security issue, serving pages from your database server.

My plan was to install some 2008 components on the server – analysis, database, integration. Then I installed 2005 reporting and created a copy of the ReportServer database on this report server.

When it came time to upgrade the 2005 instance, the upgrade would not proceed. There were no components detected by the wizard to upgrade. So I uninstalled the 2008 database, moved the database to our new 2008 DB cluster and the upgrade worked. Then I cleared everything off and reinstalled 2005 with all the services. I patched it up and once more, restored the ReportServer (and of course ReportServerTempDB) to my 2008 cluster.

A quick note – if you’re planning on hosting more than one SSRS database on a server and need to change one of the database set names, you’ll need to update all the stored procedures in the ReportServer database to point to the properly named TempDB. You can script out all the procedures in ALTER form in SSMS and then run a find/replace on ReportServerTempDB. Perhaps it should be a best practice when installing a report server not to give the database the default name – use some kind of numbering scheme, so we don’t have to resort to hacky solutions when things go bad and you may have to consolidate some apps onto one server.

Another note – I also tried to attach the 2005 report server database to a 2008 report instance – it didn’t automatically upgrade it, just got an error.

Another challenge is managing the subscription jobs when setting up a new server. I wrote a script which would find all the jobs with a category of reportserver and generate some scripts to enable, disable and delete the jobs. This let me set up the new production server and then disable all the jobs until the cutover – otherwise we’re going to get duplicate reports emailed to people.  The script is below – set your output to text and run the output.

USE msdb
SELECT ‘EXEC msdb.dbo.SP_UPDATE_JOB @job_id = ”’ + CONVERT(VARCHAR(100),j.job_id) + ”’  , @enabled = 0′ FROM dbo.sysjobs_view j INNER JOIN dbo.syscategories c
ON j.category_id = c.category_id
WHERE c.NAME = ‘Report Server’
USE msdb
SELECT ‘EXEC msdb.dbo.SP_UPDATE_JOB @job_id = ”’ + CONVERT(VARCHAR(100),j.job_id) + ”’  , @enabled = 1′ FROM dbo.sysjobs_view j INNER JOIN dbo.syscategories c
ON j.category_id = c.category_id
WHERE c.NAME = ‘Report Server’
USE msdb
SELECT ‘EXEC msdb.dbo.SP_DELETE_JOB @job_id = ”’ + CONVERT(VARCHAR(100),j.job_id) + ”’ ‘ FROM dbo.sysjobs_view j INNER JOIN dbo.syscategories c
ON j.category_id = c.category_id
WHERE c.NAME = ‘Report Server’

Important notes from this install

If you want to upgrade 2005, don’t put 2008 on the box first. I’ve had it work once and fail twice.

Stopping and starting the report service in the report configuration manager will sometimes fix things.

Make sure the ssl certificate friendly name is the same as the url.  Getting 2005 set up on server 2008 needed this.

Installing reporting services 2005 on IIS 7 takes a little additional config.  You’ll need to enable some features in IIS7 and make sure to run SSRS in a classic .net app pool.

Be sure to assign the ssl certificate to the web site, just as you would in IIS6.  If the check mark to require SSL is grayed out in SSRS configuration manager, you probably haven’t done this.

Make sure your sever can ping the url you are planning on using and that you have IIS running if you are planning on using ssl in 2008 – it is a dependency.  If you are adding ssl bindings in the 2008 SSRS config tool and they are successful, but not showing up, then something has been hosed.  This can be fixed by using the HttpSysConfig.exe tool to delete the http.sys bindings and then deleting the entries for those URLs from the report server config file.  I had this issue during our first attempt and couldn’t figure it out during our allotted down time.

The new reporting server is on a VMWare virtual 2008 server – love it!

Phew!!  Hopefully this weekend everything works out this weekend – I’m sick of Reporting Services.

HTTP 404 Not Found with Reporting Services and Windows 7 (Server 2008)

IIS reserves port 80. SQL 2008 reporting services is now using it’s own ‘web server’ – HTTP.sys.  In reporting services configuration manager change the Report Manager URL advanced settings.  In the ‘Multiple Identities for Report Manager’, edit the entry and change the TCP Port to not be 80.  Common alternates are 8080 or 8000.


Need to find out who is in a global group?  Where I work, they don’t install management tools on our computers if you’re not in the security or server group.  Sysinternals to the rescue.

We keep our users and group in a container named _users. I just navigate to that node and search the container for whatever group I’m looking for.  The group has a members property you can double-click to view.

SQL Browser

Did you know you can start the SQL Browser service from the command line and watch as the UDP requests on 1434 fly in?

Your location of the file may vary, but you just use the -c switch like so:

c:\program files\microsoft sql server\90\shared>sqlbrowser.exe -c

Cluster Diagnostics

Installing SQL 2008 on my existing 2005 cluster seemed to have broken my clustered 2005 SSIS service.  It stopped starting successfully around the time of the install and the instance started using the SSIS 10 service – although I didn’t know this.

I found out by using a tool called clusterdiag.exe from microsoft.

Instruction on usage and obtainage here:

I tried to start the SSIS service and then fired up this program.  I opened the cluster folder in the WINDOWS directory on my server directly by opening \\server01\c$\windows\cluster in offline mode.  There was an error creating some sort of tree view, but everything seemed to work well enough.

I opened the Test and Cluster logs folder and found cluster.log within it.  After a double-click I saw a color coded display of the log.  I found the error messages and googled the error codes.  The service did not exist.  That led me to find that it had been replaced by the 2008/10 install.  I reran the 2005 install and ended up with 2 SSIS versions.

After a thorough read of the clusterdiag.exe page I found that you can highlight an error code and go to TOOLS > FIND WIN32 ERROR. You get a handy definition of the error, which my brain can understand.

I believe my install went wrong when I upgraded a report server from 2005 to 2008 and must have also upgraded the shared components.

Some Transaction Log Smarts

Paul Randal writes about and demonstrates the workings of the transaction log.

Some reasons why your transaction log will not or is not shrinking.

A query to see why your log cannot be reused and is stopping the function of your database

SELECT name,log_reuse_wait_desc
FROM MASTER.sys.databases

The meanings of log_reuse_wait_desc are shown in the msdn article linked to above.

And finally, some steps for what to do when your log is full.

Making backups for non-production usage

Last night while dealing with a full transaction log, it was requested that I restore a database.  I generated the restore statement to a certain  point in time and reviewed the script.  The backup file was being retrieved from a non-standard location and when I went there, the backup file was not present!  I tracked down a job on the dev server which was backing up this DB, restoring it to dev and then deleting the file!  The result of this is that we effectively had no recovery available for the database since that backup was deleted.  How did this happen and how can we avoid this?

It’s a frequent task in SQL to create a back for a developer to use in staging, development or on their desktop.  If they can do with last nights data, you can just give them a copy of the previous day’s backup.  If they need something current, you’ll need to make another backup.  You’ll probably run a statement like this:


This file has now become a vital part of your recovery for this database.   To recover anything done past this point using transaction log backups made in the future, you’ll need this file.  So you can’t just drag this file off to another server or share.

If you want to move the file around or delete it, use this command: 


You can back up straight to a network share on another machine and save some time copying files around.  This doesn’t disturb your chain of backups and log backups and lets you do as you please with the file. 

More information on log sequence numbers and how they work can be found here .

Renewing SSL Certificates for SQL Reporting

Once a year, our certificates expire for ssl on reporting services.  These can be replaced with zero down time in reporting services.
1.    Every year, the certificate for reporting services must be renewed.
2.    Obtain certificate .pfx file and password from web infrastructure team.
3.    Connect remotely to the reporting server.
4.    Open Control Panel > Administrative Tools > Public Key Management
5.    Open Certificates (Local Computer) > Personal
6.    All Tasks > Import
7.    Let the wizard determine the store.
8.    Open IIS > Web Sites > Default WebSite > Directory Security Tab > Server Certificate
9.    Replace the current certificate
10.    Pick new cert from the store
11.    View the website and click the lock to the right of the url bar in IE.  Click the ‘view certificate’ button and ensure the date has been changed to the new certificate effective/expiry dates.

As you can see, we didn’t restart the website and you’re using the new cert.