Tech4Him – Technology with Integrity

As we have posted about before, we are getting close to our upgrade and consolidation from SQL Server 2000 and 2005 environments into a single SQL Server 2008 environment. In preparation for this we found ourselves also needing to change our SMTP settings for SSRS to a non-default setup. Hopefully this might save you a few minutes if you find yourself in a similar situation.

Does your mail delivery look like this?

In our case we are actually blocking port 25 and our external SMTP servers use a set of alternative ports along with SSL. In SSRS 2005 nor 2008, the Reporting Services configuration tool does not offer options for modify the SMTP port nor the use of SSL. As such, we quickly found the following two articles from Microsoft which show how to address these requirements.

Configuring a Report Server for E-Mail Delivery SSRS 2008

Configuring a Report Server for E-Mail Delivery

As of this writing, it looks like both 2005 and 2008 do not yet support authenticated external SMTP services requiring a username and password. There is a feature request here that we did find with a suggested workaround to implement your own delivery provider.

So here are the basics.

SSRS 2005

Simply find the RSreportserver.config file and edit the SMTP configuration section as needed.

[Please note that SSRS 2005 does not support encrypted emails.]

<RSEmailDPConfiguration>
     <SMTPServer>mySMTPServer.Adventure-Works.com</SMTPServer>
     <SMTPServerPort></SMTPServerPort>
     <SMTPAccountName></SMTPAccountName>
     <SMTPConnectionTimeout></SMTPConnectionTimeout>

     <SMTPServerPickupDirectory></SMTPServerPickupDirectory>
     <SMTPUseSSL></SMTPUseSSL>
     <SendUsing>2</SendUsing>
     <SMTPAuthenticate></SMTPAuthenticate>

     <From>my-rs-email-account@Adventure-Works.com</From>
     <EmbeddedRenderFormats>
          <RenderingExtension>MHTML</RenderingExtension>
     </EmbeddedRenderFormats>
     <PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>

     <ExcludedRenderFormats>
          <RenderingExtension>HTMLOWC</RenderingExtension>
          <RenderingExtension>NULL</RenderingExtension>
     </ExcludedRenderFormats>
     <SendEmailToUserAlias>True</SendEmailToUserAlias>

     <DefaultHostName></DefaultHostName>
     <PermittedHosts>
          <HostName>Adventure-Works.com</HostName>
          <HostName>hotmail.com</HostName>

     </PermittedHosts>
</RSEmailDPConfiguration>

SSRS 2008

<RSEmailDPConfiguration>
     <SMTPServer>mySMTPServer.Adventure-Works.com</SMTPServer>
     <SMTPServerPort></SMTPServerPort>
     <SMTPAccountName></SMTPAccountName>
     <SMTPConnectionTimeout></SMTPConnectionTimeout>

     <SMTPServerPickupDirectory></SMTPServerPickupDirectory>
     <SMTPUseSSL></SMTPUseSSL>
     <SendUsing>2</SendUsing>
     <SMTPAuthenticate></SMTPAuthenticate>

     <From>my-rs-email-account@Adventure-Works.com</From>
     <EmbeddedRenderFormats>
          <RenderingExtension>MHTML</RenderingExtension>
     </EmbeddedRenderFormats>
     <PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>

     <ExcludedRenderFormats>
          <RenderingExtension>HTMLOWC</RenderingExtension>
          <RenderingExtension>NULL</RenderingExtension>
     </ExcludedRenderFormats>
     <SendEmailToUserAlias>True</SendEmailToUserAlias>

     <DefaultHostName></DefaultHostName>
     <PermittedHosts>
          <HostName>Adventure-Works.com</HostName>
          <HostName>hotmail.com</HostName>

     </PermittedHosts>
</RSEmailDPConfiguration>

For our case, our particular attention was focused on the following elements:

• SMTPServer
• SMTPServerPort
• SMTPUseSSL

SMTPServer was set to point at the FQDN of our SMTP server. Example: mail.example.com

SMTPServerPort was set to our non-standard SMTP port. Example: 444

SMTPUseSSL needed to be enabled so we set the value to True.

Popularity: 65% [?]