Microsoft.ReportingServices.Diagnostics.Utilities.UnknownUserNameException: The user or group name is not recognized.


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 …:(

Advertisements

About EasyBIJegan

Business Intelligence Developer
This entry was posted in SSRS. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s