Delivering Subscriptions Outside of the Ogranization

Recently I faced an issue with reporting services 2005 when attempting to deliver subscriptions to addresses outside of the organization. Internal addresses received the email based subscription deliveries
without any questions. I must have checked and re-checked the settings using RSConfigTool about million times, looking for anything I might have overlooked. The error message I received was, “The e-mail address of one or more recipients is not valid”. After some research (which lead me to a lot of dead ended forums) I read the phrase “email relay”, that’s when the gears started spinning.

I realized that the issue had nothing to do with the configuration of SQL Server Reporting Services; rather, the SMTP server! In order for the messages to be delivered outside of the organization the Reporting Services Server needed to be authorized so-to-speak. Unfortunately I don’t have access to Exchange 2003 so I cannot provide screen shots, but for 2007 all you need to do is add the server’s IP Address to the SMTP server’s receiver group in the HUB Transport configs.

Then to test your subscription without tweaking the schedule execution time just run the SQL job! To find out the name of the job use the attached sql script. If you have a named instance append $instancename to all three of ReportServer occurrences (i.e. ReportServer$InstanceName) for MSSQL 2005. I believe for MSSQL 2008 you would append _InstanceName (i.e. ReportServer_InstanceName), but I am not certain. You should get the results similar to the screen shot attached.

SELECT	
	sj.[name] AS [Job Name],
	c.[Name] AS [Report Name],
	c.[Path],
	su.Description,
	su.EventType,
	su.LastStatus,
	su.LastRunTime

FROM msdb..sysjobs AS sj 
JOIN ReportServer..ReportSchedule AS rs ON sj.[name] = CAST(rs.ScheduleID AS NVARCHAR(128)) 
JOIN ReportServer..Subscriptions AS su ON rs.SubscriptionID = su.SubscriptionID 
JOIN ReportServer..[Catalog] c ON su.Report_OID = c.ItemID
 
/*
USAGE:

USE [msdb]
EXEC sp_start_job @job_name = 'AF015D8B-D80D-4D2A-9808-CD1D519B3332'

NOTE:
If using a named instance use ReportServer$Instance_Name for 2005
For 2008 I believe you only need to change the $ to _ when using 2008 (i.e. ReportServer_Instance_Name)
*/

Advertisements

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