Category: SSRS

Rant, SQL, SSRS, Windows December 18, 2012

Windows Hotfix KB 2661254 Breaks Reporting Services

I have spent the last 3 weeks trying to troubleshoot an issue with Reporting Services for SQL Server 2008 R2 Service Pack 2 failing to start on a server and have come to discover that a Windows Hotfix is causing the issue.

There is no distinction between trying to install a slipstreamed version of SQL Server 2008 R2 with SP2 or trying to install SQL Server 2008 R2 and then attempting to apply SP2 on top of it, either way if KB 2661254 is installed the Reporting Services service will fail to start. You will not get an error indicating the reason for the failure, just that it failed (way to go with the pertinent error messages there Microsoft).

The Windows hotfix KB 2661254 is an update for the certificate minimum key length to prevent the use of any certificate keys that are less than 1024 bit long. This is a security measure to help prevent brute force attacks against private keys. Why this breaks SSRS I do not know. The patch can be safely applied to systems running SQL Server 2008 R2 SP1.

For now I have passed along word to the sysadmins to not deploy this particular patch to any Windows machine that runs SQL Server and have created a Microsoft Connect item in a hope that they provide resolution to the issue. Please try this in your own test environment, then upvote and mark that you are able to reproduce the problem on Connect.

SSRS April 19, 2012

SSRS–Email Subscriptions Not Being Delivered

If you work with replication much I’m sure you’ve come across the situation whereby somebody created a publication and at some point in the future left the company. Once their AD account had been disabled or removed then the replication jobs, for some reason, would stop functioning and you would have to spend your time trying to figure out why. Eventually you would realize that the jobs had been mysteriously created under the context of that user account and so now longer work. A quick change so that jobs run under SA and everything is all happy happy again.

Well I had something similar happen this week with Reporting Services where all of a sudden email subscriptions were failing and critical reports were not being sent out. This is not the kind of call that you want to get during the night.

Identifying the failure was pretty easy, that was just a case of opening up the SSRS log file (by default found at C:Program FilesMicrosoft SQL ServerMSRS10_50.MSSQLSERVERReporting ServicesLogFiles) and looking through for the error:

notification!WindowsService_463!508!04/19/2012-07:10:06:: e ERROR: Error occured processing subscription 0ceefc23-7857-4fa4-a180-49c1590f00d9: Failure sending mail: The permissions granted to user ‘ <domain><user> ‘ are insufficient for performing this operation.Mail will not be resent.

emailextension!WindowsService_463!508!04/19/2012-07:10:06:: e ERROR: Error sending email. Microsoft.ReportingServices.Diagnostics.Utilities.RSException: The permissions granted to user ‘ <domain><user> ‘ are insufficient for performing this operation. —> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user ‘ <domain><user> ‘ are insufficient for performing this operation.notification!WindowsService_463!508!04/19/2012-07:10:06:: e ERROR: Error occured processing subscription 0ceefc23-7857-4fa4-a180-49c1590f00d9: Failure sending mail: The permissions granted to user ‘ <domain><user> ‘ are insufficient for performing this operation.Mail will not be resent.

Error in hand I went looking at the report in question. It had been created by the user who had left the company and so had two subscriptions. The first subscription was file based. That was having no trouble executing, which was interesting. The other, an email subscription was the one that was failiing.

My belief at this juncture was that the file subscription, which has a user context for execution, was working ok because of that user context and the other was failing because the AD account of the old user was no longer around.

Going back on old knowledge I figured that I could just change the owner of the subscription and everything would be happy again and the reports would flow. I spent a good 30 minutes trying to figure out a way to do that. There’s actually no option, you have to drop and recreate the subscription. For me this would require changes to a lot of subscriptions, with several of them being data driven. Doing this would have taken an age.

Time to see if a meta-data update would help fix the issue.

I started digging through the ReportServer database and was able to come up with a list of subscriptions that were owned by the old user:

SELECT  u.UserName ,
        s.SubscriptionID ,
        Report_OID ,
        Description ,
        LastStatus ,
        EventType ,
        DeliveryExtension
FROM    ReportServer.dbo.Subscriptions s
        INNER JOIN ReportServer.dbo.Users u ON s.OwnerID = u.UserID
        WHERE u.UserName = '<old user>'

With this information in hand I went out and create a new AD account specifically for owning reports that would send emails. After making sure that the account had permissions to access the reports that it would subscribe to I went and updated the subscriptions so that they would run under this new security context:

BEGIN TRAN

BEGIN TRY

    DECLARE @newid UNIQUEIDENTIFIER

    SELECT  @newid = userid

    FROM    ReportServer.dbo.Users

    WHERE   UserName = '<new user>'

    UPDATE  s

    SET     OwnerId = @newid

    FROM    ReportServer.dbo.Subscriptions s

            INNER JOIN dbo.Users u ON s.OwnerID = u.UserID

    WHERE   u.UserName = '<old user>'

END TRY

BEGIN CATCH

    SELECT  ERROR_MESSAGE()

    IF @@TRANCOUNT > 0

        ROLLBACK TRAN;

END CATCH

IF @@TRANCOUNT > 0

    COMMIT TRAN;

This updated 73 reports for me and only took a couple of seconds. Much better than the couple of days it would have taken to try and change every subscription.

Knowing this I’ll be keeping a much close eye on subscription ownership in SSRS going forward.

SQL, SQL 2012, SSRS December 15, 2011

Please Don’t Use Deprecated Data Types

I know that a lot of vendors like to write for the lowest common denominator (i.e. SQL 2000) but really folks it’s gone too far. I’m sick of cracking open vendor code that’s certified for SQL 2008 and seeing things like IMAGE and TEXT data types. Microsoft deprecated these things back when they released SQL 2005 (see http://msdn.microsoft.com/en-US/library/ms143729(v=SQL.90).aspx under Textpointers). Why are you persisting these things six years later?

I bring this up because I’ve come across further egregious usage of these data types in vendor code yet again. The vendor in question? Microsoft.

Yes, that’s right, the folks that deprecated the data type six years ago is still using it to a large extent within the ReportServer and ReportServerTempDB databases that support SQL Server Reporting Services. Seriously Microsoft? Can you please get with the plan and fix this nonsense?

The following query, run against the ReportServer database will show 14 different tables (31 columns) using a variety of NTEXT and IMAGE data types.

select st.name as TableName, t.name as DataType, sc.name as ColumnName 
    from sys.types t 
        inner join sys.columns sc
            on t.system_type_id = sc.system_type_id
        inner join sys.tables st
            on sc.object_id = st.object_id
where 
    t.name in ('image', 'text', 'ntext')
order by 
    st.name, t.name

I have filed a Connect item asking Microsoft to fix this. Please go vote for it at https://connect.microsoft.com/SQLServer/feedback/details/714117/ssrs-using-deprecated-data-types-in-its-databases and help us rid the world of this old stuff.

SSRS, T-SQL Tuesday December 13, 2011

T-SQL Tuesday #25–Did Your RS Report Fail?

Allen White (blog|twitter), in hosting this months T-SQL Tuesday, is looking for tricks to help make you more effective.

I could probably try and figure out lots of things that I mess around with to make myself more effective. For example adding aliases for my servers so that instead of having to go to “PRODSERVER17FML27” I can just go to “FML27”.

Instead of that I thought I’d throw something up real quick that turned up as an issue the other day at work.

We have a bunch of scheduled Reporting Services reports that get emailed to users and files put out on shares. These schedules are actually run as SQL Server Agent jobs. We ran into an issue whereby a report failed over a weekend. We knew nothing about the failure, the first we knew was when the customer started complaining that the report was not available.

Knowing that the schedules are running in SQL Agent I looked for failed jobs. There were none. All the job does is submit the required report into the job queue and then say that it did it’s job. It does not actually monitor successful execution of the report itself. There doesn’t actually appear to be any quick and simple way of doing this. As such I wrote a process that goes out to the Reporting Services database on a daily basis, looks for reports that have failed and then sends me a report to let me know. It’s a good way to get ahead of the customer and find problems before they are reported.

Code below (in this code the default database ReportServer is used for the Reporting Services database)

/* 
Query grabs a list of the failed Reporting Services reports for the last 24 hours.
This list is then sent as an HTML table via email.
Data in email includes:
    Report Name
    Path To Report
    Username Executing Report
    Date/Time When Report Executed
    Parameters Used In Report Execution
*/
 
SET NOCOUNT ON
DECLARE @HTML VARCHAR(MAX)
DECLARE @HTML_Header VARCHAR(MAX)
DECLARE @HTML_Body VARCHAR(MAX)
DECLARE @HTML_Footer VARCHAR(MAX)
DECLARE @COUNT INT
 
--create the table to hold the dataset
DECLARE @Results TABLE
    (
      ID INT IDENTITY(1, 1) ,
      RSName NVARCHAR(850) ,
      RSPath NVARCHAR(850) ,
      UserName NVARCHAR(520) ,
      TimeStart DATETIME ,
      Parameters NVARCHAR(MAX)
    )
 
--Grab the data from the ReportServer database for failed reports
INSERT  INTO @Results
        ( RSName ,
          RSPath ,
          UserName ,
          TimeStart ,
          Parameters
        )
        SELECT  c.Name ,
                c.Path ,
                [UserName] ,
                [TimeStart] ,
                [Parameters]
        FROM    [ReportServer].[dbo].[ExecutionLogStorage] els
                INNER JOIN ReportServer.dbo.Catalog c ON els.ReportID = c.ItemID
        WHERE   status != 'rsSuccess'
                AND TimeStart > DATEADD(hh, -24, GETDATE())
        ORDER BY LogEntryId 
 
 
--create the email
SELECT  @count = MAX(ID)
FROM    @Results
 
IF @count <> 0 
    BEGIN
        SET @HTML_Header = '<html><header></header><body>
        <TABLE>
        <table border="1" cellspacing="1" cellpadding="1"> 
        <TR colspan=2style="color: #A0522D; font-family: Verdana; font-size: 11; font-weight:bold" align=Left>
        <td>Report Name</TD>
        <TD>Report Path</TD>
        <TD>Executed On</TD>
        <TD>Executed By</TD>
        <TD>Parameters</TD>
        </TR>
        '
 
        SET @HTML_FOoter = '</TABLE>
                <BR><BR>
                <FONT FACE="color: #A0522D; font-family: Verdana; font-size: 12";></FONT></body></html>
        '
    END
ELSE 
    BEGIN
        SET @HTML_Header = 'No failed reports in the last 24 hours'
        SET @HTML_Footer = '</TABLE>'
    END
 
SELECT  @HTML_Body = ''
 
WHILE @count > 0 
    BEGIN
        SELECT  @HTML_Body = @html_body + '<TR style="font-size: 10">
                           <TD style="text-align: left;" >' + RSName + '</TD>
                           <TD style="text-align: LEFT;">&nbsp ' + RSPath
                + '</TD>
                           <TD>&nbsp ' + CONVERT(VARCHAR, timestart, 120)
                + '</TD>
                           <TD style="text-align: left;">&nbsp ' + Username
                + '</TD>
                           <TD style="text-align: left;">&nbsp '
                + ISNULL(Parameters, '') + '</TD>
                          
</TR>'
        FROM    @results
        WHERE   ID = @count   
 
        SET @Count = @count - 1
 
    END    
 
SET @HTML = @HTML_Header + @HTML_Body + @HTML_FOoter
 
 
EXEC MSDB.DBO.SP_SEND_DBMAIL @RECIPIENTS = '<Your Email Address>',
    @SUBJECT = 'Failed RS Reports - Last 24 hours', @BODY = @HTML,
    @BODY_FORMAT = 'HTML'
--, @IMPORTANCE = 'HIGH'
GO

SQL, SSRS September 9, 2011

SSRS Validation Of Viewstate MAC Failed

You aren’t likely to see me post much about SSRS, mainly because I use it only on rare ocassions and when I don’t tend to hit blog-worthy things. Today is different though…

We recently deployed a new SSRS farm at the office. The deployment was smooth, the first reports went on without any issues. Everything looked good.

I was setting up a data driven subscription for a report and received the following error:

Validation of viewstate MAC failed. If this application is hosted by a Web Farm or cluster, ensure that <machineKey> configuration specifies the same validationKey and validation algorithm. AutoGenerate cannot be used in a cluster

What the heck?

Searching the web provided a plethora of hits around this, but they were all related to regular ASP.NET and there wasn’t anything in particular around SSRS and this problem.

After digging around I went back to Books Online and started digging through the documentation there with a fine tooth comb, and that’s where I found the solution (see it for yourself at http://msdn.microsoft.com/en-us/library/cc281307(SQL.105).aspx#ViewState )

Due to Reporting Services running in a load balanced configuration it couldn’t autogenerate the key used for validation for the view state. In this case a manual key had to be generated and deployed to each of the SSRS front ends.

I needed a resource to generate a key so I went out to http://aspnetresources.com/tools/machineKey where one click gave me what I needed.

Example key:

I then pasted the key into the ReportManager web.config file on each front end server. After a service restart everything came back up and I was able to deploy the subscription without any further issues.

Moral of the story here: Carefully read Books Online, it’s actually a great reference.

Such fun.