Thursday, September 27, 2012

Configuring SQL Server 2008R2 Reporting Services Email Delivery with Gmail SMTP

This is how you can configure SQL Server 2008R2 Reporting Services email with Gmail SMTP server. Please note that my operating system is Windows Server 2008R2.
  1. Go to Server Manager and Add feature “SMTP Server”.
    Untitled1
    Add Feature SMTP Server
  2. Go to Administrative Tools and open Internet Information Services (IIS) 6.0 Manager.
  3. Right Click on SMTP Virtual Server and select Properties.
    Untitled2
    SMTP server properties
  4. Under the General tab select your IP Address.
    Untitled3
    IP Address
  5. Under the Access tab click on Relay and do as follows.
    Untitled4
    Relay
  6. Under the Delivery tab you will get something like this.
    Untitled5
    Delivery Tab
  7. Now under the Delivery tab click on Outbound Security and type your gmail email address and the password. Remember to tick on "TLS encryption".
    Untitled6
    Outbound Security
  8. Again under the Delivery tab click on Outbound Connections and do as follows.
    Untitled7
    Outbound Connections
  9. Again under the Delivery tab click on Advanced and fill values as below.
    Untitled8
    Advanced
  10. Now you are done with the SMTP properties. Now make sure SMTP Server is started.
    Untitled9
    Start SMTP Server
  11. Go to Reporting Services Configuration Manager under Configuration Tools in All Program's Microsoft SQL Server 2008 R2. As Sender address, type your gmail address and as SMTP Server, type your IP address.
    Untitled10
    Reporting Services Configuration Manager
  12. Go to "C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer" and open "rsreportserver.config" file. Make sure your Report Manager URL is inside UrlRoot tags and Report Server Email Extension tag is filled as below with your details. If you find your tags empty, make a backup of "rsreportserver.config" file and fill the tag values as below.
            <UrlRoot>http://jaliya-pc/ReportServer</UrlRoot>
------------------------------------------------------------------------------------------------
            <Extension Name="Report Server Email" Type="Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider,ReportingServicesEmailDeliveryProvider">
                <MaxRetries>3</MaxRetries>
                <SecondsBeforeRetry>900</SecondsBeforeRetry>
                <Configuration>
                    <RSEmailDPConfiguration>
                        <SMTPServer>192.168.10.107</SMTPServer>
                        <SMTPServerPort>
                        </SMTPServerPort>
                        <SMTPAccountName>
                        </SMTPAccountName>
                        <SMTPConnectionTimeout>
                        </SMTPConnectionTimeout>
                        <SMTPServerPickupDirectory>
                        </SMTPServerPickupDirectory>
                        <SMTPUseSSL>
                        </SMTPUseSSL>
                        <SendUsing>2</SendUsing>
                        <SMTPAuthenticate>
                        </SMTPAuthenticate>
                        <From>jaliya.udagedara@gmail.com</From>
                        <EmbeddedRenderFormats>
                            <RenderingExtension>PDF</RenderingExtension>
                        </EmbeddedRenderFormats>
                        <PrivilegedUserRenderFormats>
                        </PrivilegedUserRenderFormats>
                        <ExcludedRenderFormats>
                            <RenderingExtension>HTMLOWC</RenderingExtension>
                            <RenderingExtension>NULL</RenderingExtension>
                            <RenderingExtension>RGDI</RenderingExtension>
                        </ExcludedRenderFormats>
                        <SendEmailToUserAlias>True</SendEmailToUserAlias>
                        <DefaultHostName>
                        </DefaultHostName>
                        <PermittedHosts>
                        </PermittedHosts>
                    </RSEmailDPConfiguration>
                </Configuration>
            </Extension>
------------------------------------------------------------------------------------------------
That's it. Now you can test your email delivery by creating a Data-driven Subscription. You can find the Reporting services log file under the following location. It will contain all the logs related to this email sending.

"C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles"

I am hoping to write a post about programmatically creating a Data-driven Subscription in a coming post and hope this helps.

Happy Coding.

Regards,
Jaliya

6 comments:

  1. Email delivery rates can vary dramatically depending on your email configuration, the type of software you are using, and the servers that you are sending your mail from. Below are 5 tips to help you improve your email delivery rates and get more of your messages in front of your recipients.

    Email Delivery Service

    ReplyDelete
  2. Hi Jaliya,
    Your post is exactly what i am looking for on setting up SSRS using gmail. I had followed and reports under my subscriptions are showing status as Mail sent. Unfortunately the recipient did not receive any email. I had checked all the settings and it looks fine.
    Even the logfile does not have any error.

    Is there any other area i can check for problems?

    May

    ReplyDelete
    Replies
    1. Hi May,

      I believe your recipient's mail server must be blocking report server emails.

      Happy Coding.

      Regards,
      Jaliya

      Delete
  3. excelente es lo que necesitaba 10 puntos

    ReplyDelete
  4. Truly helpful but can you give windows 7 procedure?

    ReplyDelete
  5. Thank you for your sharing. I can send report on SSRS via Email.

    ReplyDelete