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

his 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”. 
   

   

   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.
   

   

   SMTP server properties

4. Under the General tab select your IP Address.
   

   

   IP Address

5. Under the Access tab click on Relay and do as follows.
   

   

   Relay

6. Under the Delivery tab you will get something like this.
   

   

   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".
   

   

   Outbound Security

8. Again under the Delivery tab click on Outbound Connections and do as follows.
   

   

   Outbound Connections

9. Again under the Delivery tab click on Advanced and fill values as below.
   

   

   Advanced

10. Now you are done with the SMTP properties. Now make sure SMTP Server is started.
    

    

    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.
    

    

    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

 http://jaliyaudagedara.blogspot.com/2012/09/configuring-sql-server-2008r2-reporting.html