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