Problem:
Recently our Data Driven Subscriptions started failing with the following error message (as in SSRS Error Logs found in Program Files\Microsoft SQL Server\MSRS10.SqlServer\Logfiles\….).
library!WindowsService_0!960!01/07/2011-14:17:59:: e ERROR: Error processing data driven subscription: Microsoft.ReportingServices.Diagnostics.Utilities.UnknownUserNameException: The user or group name ‘<UserName>’ is not recognized at Microsoft.ReportingServices.Library.Native.NameToSid(String name) at Microsoft.ReportingServices.Library.ActiveSubscription.CreateDataDrivenNotification(SubscriptionImpl subscription, Guid activationID, Settings settings, ParamValues paramValues) at Microsoft.ReportingServices.Library.DataDrivenSubscriptionHandler.DataDrivenSubscriptionWorker(Object o)
<UserName> mentioned above was the username of one employee who recently left our organization.
Diagnosis:
I had no idea what was going on and the first task was to identify which assembly contained this class – Microsoft.ReportingServices.Library.ActiveSubscription. After loading few assemblies from ReportServer bin folder (<Drive>:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin) to see which assembly contained these classes, the assembly containing the calls was – ReportingServicesServer.dll. This assembly is used by the SSRS Windows Service (ReportingServices.exe) during subscription delivery.
Next step is to generate the code of this assembly and see what is happening within these method calls. I have used Reflector and File Disassembler (Reflector AddIn) to generate the code of this assembly and the code which causes the problems is as below:
public void CreateDataDrivenNotification(SubscriptionImpl subscription, Guid activationID, Settings settings, ParamValues paramValues)
{
using (InstrumentedSqlCommand command = base.NewStandardSqlCommand(“CreateDataDrivenNotification”))
{
command.Parameters.AddWithValue(“@SubscriptionID”, subscription.ID); command.Parameters.AddWithValue(“@ActiveationID”, activationID); command.Parameters.AddWithValue(“@ReportID”, subscription.ReportID); command.AddParameter(“@Locale”, SqlDbType.NVarChar, subscription.m_subscriptionCulture); command.AddParameter(“@ExtensionSettings”, SqlDbType.NText, ParameterValueOrFieldReference.ThisArrayToXml(settings.ToSoapParameterValueArray())); command.AddParameter(“@Parameters”, SqlDbType.NText, paramValues.ToXml(false)); command.Parameters.AddWithValue(“@LastRunTime”, subscription.m_lastRunTime); command.AddParameter(“@DeliveryExtension”, SqlDbType.NVarChar, subscription.DeliveryExtension); command.Parameters.AddWithValue(“@OwnerSid”, Microsoft.ReportingServices.Library.Global.NameToSid(subscription.Owner)).SqlDbType = SqlDbType.VarBinary; command.AddParameter(“@OwnerName”, SqlDbType.NVarChar, subscription.Owner.UserName); command.Parameters.AddWithValue(“@OwnerAuthType”, (int) subscription.Owner.AuthenticationType); command.Parameters.AddWithValue(“@Version”, subscription.m_version); command.ExecuteNonQuery(); } }
In this code there is a call to a method, NameToSid which tries to get Security Identifier of a User Name, which owns the Subscription. In this case, since the employee left the company, his user id was deleted. Since the User ID is not there anymore, an exception is thrown.
Fix:
Subscription related data is stored in ReportServer.dbo.Subscription table.This UserId is set as owner of the subscription in ReportServer.dbo.Subscriptions Owner_ID column (Refers to ReportServer.dbo.Users.UserID). Now the solution is to update the OwnerId field in Subscriptions table to another user [presumably a service account or the username of a person who will never leave the company ;-)].
In this case we chose the latter. We used the following SQL to update the subscription ownership is below:
UPDATE ReportServer.dbo.Subscriptions SET OwnerID = (SELECT UserID FROM ReportServer.dbo.Users WHERE UserName ='<Service Account UserName>’) WHERE OwnerID = (SELECT UserID FROM ReportServer.dbo.Users WHERE UserName = ‘<Deleted UserName>’)
Since our automated deployment (to deploy subscriptions) was done using the credentials of this user, his userid was set as Subscription owner.
Googling for Change SSRS Subscription owner, brings this URL..
http://blogs.msdn.com/b/miah/archive/2008/07/10/tip-change-the-owner-of-report-server-subscription.aspx
Hmmm. If at all I knew what the problem was in the first place …:(
easybi 