During my 13-year career, I’ve been involved in some pretty obscure software issues. One particular nightmare that springs to mind involved SQL Server Reporting Services 2014 (SSRS), which had our developers scratching their heads for days!
During a deployment window, we found it increasingly difficult to deploy new/updated SSRS reports onto our client’s system. At the time, we had chosen to do this via Report Manager, and due to the sheer number of reports, it was taking up to 5 minutes simply to load the page.
The amount of snapshots made accessing the Report Manager very slow.
Side note – There are better ways to deploy SSRS reports. Go take a look at Cláudio Silva’s session at SQL Bits 17 as he talks about using PowerShell instead.
Accessing the Report Manager caused issues for the end users whenever we deployed any changes. Every time the Report Manager page was accessed, the procedure was causing SQL Server to run into performance issues, slowing down other queries that were being executed – generally giving the end user a poor experience. Digging around, we found that the Report Manager page was executing a built-in stored procedure called FindObjectsNonRecursive (found in the ReportServer database).
Now, this next part was the crucial find – FindObjectsNonRecursive was showing to be triggering the PREEMPTIVE_OS_LOOKUPACCOUNTSID wait type (for an introduction to SQL Server Waits, see this article from my good friend and colleague, Shaun Austin).
As described on SQLSkills.com, this wait type is used when an execution thread calls the Windows LookupAccountSid function. This function looks up the security identifier (SID) that is passed in to determine whether it is a local account, local domain account or within the trusted domain account. It returns either true or false.
From reviewing FindObjectsNonRecursive, we could see that it was referencing a table called Users. Our instinct was to see what records are in there, so running the query below returned all the users that have been set up to access Reporting Services:
SELECT U.Username, U.UserId FROM ReportServer.dbo.[Users] AS U;
Looking at the list of users, something struck us – the users ClarkKent and MACHINE_B\BruceWayne were not on the domain, as illustrated by their username. We subsequently discovered that this SSRS server had been migrated from a different environment, on a different domain, and these user accounts were the original accounts used to execute the reports! When SSRS was migrated to the new environment, the Users table and these records came with it!
So why is that relevant? Well going back to FindObjectsNonRecursive, we noticed that it contains 2 calls to a scalar-function called SUSER_SNAME() as shown below:
CREATE PROCEDURE [dbo].[FindObjectsNonRecursive] @Path nvarchar (425), @AuthType int AS SELECT C.Type, C.PolicyID, SD.NtSecDescPrimary, C.Name, C.Path, C.ItemID, DATALENGTH( C.Content ) AS [Size], C.Description, C.CreationDate, C.ModifiedDate, SUSER_SNAME(CU.Sid), CU.[UserName], SUSER_SNAME(MU.Sid), MU.[UserName], C.MimeType, C.ExecutionTime, C.Hidden, C.SubType, C.ComponentID FROM Catalog AS C INNER JOIN Catalog AS P ON C.ParentID = P.ItemID INNER JOIN Users AS CU ON C.CreatedByID = CU.UserID INNER JOIN Users AS MU ON C.ModifiedByID = MU.UserID LEFT OUTER JOIN SecData SD ON C.PolicyID = SD.PolicyID AND SD.AuthType = @AuthType WHERE P.Path = @Path
SUSER_SNAME() essentially tries to verify if the SID passed in is valid against the domain. Since ClarkKent and MACHINE_B\BruceWayne had been linked to 11 million report snapshots, the Catalog table within the ReportServer database contained references to the invalid user accounts, and the stored procedure was having to check the user account twice, to see if they were valid or not. Most of these records were not valid, which was causing the stored procedure call to take up to 5 minutes.
Once we’d figured that out, the solution was pretty simple. Either update the ReportServer.dbo.Catalog table directly to reference a valid user account for the CreatedBy and ModifiedBy date columns, or write a simple script to do it via the Reporting Services Web Service (we chose the latter)*.
This solved our problem – the Report Manager was loading within a matter of seconds rather than minutes. In turn, deployments became much faster, the impact on users dropped significantly and customers were happy!
What did we learn? Moving services across domains comes with all sorts of gotchas – this is one of them! If you’re doing something similar, consider any references to user context that may be stored within the applications. Not doing so may affect the application in not-so-obvious ways!
*Note that direct database updates may put SSRS into an unsupported state with Microsoft, so tread carefully if you choose this approach.
Thanks for reading!