DataChomp

Chomping At The Bits

SQL Report Server Outbound Email Configuration

So you get your handy dandy SQL Server Report Server all set up for email and are looking good then you go to have it email to an outbound address.  All of a sudden you get the ‘The e-mail address of one or more recipients is not valid.’   While there could be a couple reasons for this issue, the main two I run into is SMTP authentication and user aliases.  The fix to both of these involves updating the ReportServer.Config file usually found in the filesystem with a folder structure resembling – “c:program filesmicrosoft SQL ServerMSRS10.MSSQLSERVER”

You want to pop it open and then search for “SMTPServer” which will take you to something that looks like this:

<RSEmailDPConfiguration>
<SMTPServer>email.example.com</SMTPServer>
<SMTPServerPort></SMTPServerPort>
<SMTPAccountName></SMTPAccountName>
<SMTPConnectionTimeout></SMTPConnectionTimeout>
<SMTPServerPickupDirectory></SMTPServerPickupDirectory>
<SMTPUseSSL></SMTPUseSSL>
<SendUsing>2</SendUsing>
<SMTPAuthenticate>0</SMTPAuthenticate>
<From>MrMail@example.com</From>
<EmbeddedRenderFormats><RenderingExtension>MHTML</RenderingExtension></EmbeddedRenderFormats>
<PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>
<ExcludedRenderFormats><RenderingExtension>HTMLOWC</RenderingExtension>
<RenderingExtension>NULL</RenderingExtension><RenderingExtension>RGDI</RenderingExtension>
</ExcludedRenderFormats>
<SendEmailToUserAlias>True</SendEmailToUserAlias>
<DefaultHostName>Example.com</DefaultHostName>
<PermittedHosts></PermittedHosts>
</RSEmailDPConfiguration>

The two fields you want to focus on are:

<SMTPAuthenticate>0</SMTPAuthenticate> and <SendEmailToUserAlias>True</SendEmailToUserAlias>

You will want to change these to

<SMTPAuthenticate>2</SMTPAuthenticate> and <SendEmailToUserAlias>False</SendEmailToUserAlias>

This will let use the report servers service credentials to authenticate to the SMTP server. In addition, Report Server start using the actual email address instead of trying to turn everything into an alias depending on what you have populated for DefaultHostName.

This will let use the report servers service credentials to authenticate to the SMTP server. In addition, Report Server start using the actual email address instead of trying to turn everything into an alias depending on what you have populated for DefaultHostName.

For more information about the report server configuration settings,  see here: http://msdn.microsoft.com/en-us/library/ms157273.aspx

This post also makes the assumption you do SMTP authenticate for outbound emails…. If you are unsure, then double check that you are not currently operating an open relay email server.