none
Specifying a username and password for SMTP

    Question

  • How do I specify the SMTP username and password for reporting services?  It seems like I can specify one for SQLmail, but not reporting services?  How the heck is anyone going to use the email feature of reporting services without being able to specify smtp credentials or making their mail server an open relay for spam??

    Wednesday, November 09, 2005 3:51 AM

All replies

  • Here you go..

    Configuring a Report Server for E-Mail Delivery

    Reporting Services includes an e-mail delivery extension so that you can distribute reports through an existing e-mail server. Before it can be used, you must configure the report server to make e-mail delivery available to your users. Report server e-mail delivery is implemented on Collaboration Data Objects (CDO), and requires a Simple Mail Transport Protocol (SMTP) server. The e-mail delivery extension does not provide support for digitally signing or encrypting outgoing mail messages.

    To configure a report server to use the SMTP server, you must provide the following:

    Item Description
    SMTP server address Specifies the UNC name of the server. Include the domain name if necessary.

    This value is specified in the SMTPServer configuration setting.

    A valid e-mail account Specifies an existing e-mail account on that server that can be used to generate e-mail messages.

    This value is specified in the From configuration setting.

    You can specify additional settings to modify how e-mail delivery is used in your installation. E-mail delivery settings are stored in configuration files. Values can be specified during setup, or by editing the rsreportserver configuration file after setup is complete.

    Report Server E-Mail is the default delivery extension. The default delivery extension is specified through the DefaultDeliveryExtension setting in rswebapplication.config. For more information about e-mail delivery of reports, see E-Mail Delivery in Reporting Services.

    RSReportServer Configuration File Settings

    The following settings are in the rsreportserver.config configuration file. The descriptions include information from CDO for Windows 2000 product documentation. For more information, see the CDO documentation on MSDN.

    Setting Description Values
    SMTPServer Specifies a string value indicating the address of the mail server. An alternative way to specify the SMTP server is through an IP address. An IP address, a DNS resolvable name such as 177.177.0.2, or the name of any computer hosting the SMTP service.

    This value is required.

    SMTPServerPort Specifies an integer value indicating the port on which the SMTP service uses to detect incoming connections. Port 25 is typically used to send e-mail.  
    SMTPAccountName Contains a string value that assigns Outlook® Express account name. You can set this value if your SMTP server is configured to use it in some way; otherwise you can leave it blank. Use From to specify an e-mail account used to send reports.  
    SMTPConnectionTimeout Specifies an integer value indicating the number of seconds to wait for a valid socket connection with the SMTP service before timing out. The default is 30 seconds, but this value is ignored if SendUsing is set to 2.  
    SMTPServerPickupDirectory Specifies a string value indicating the pickup directory for the local SMTP service.  
    SMTPUseSSL Specifies a Boolean value that can be set to use Secure Sockets Layer (SSL) when sending an SMTP message over the network. This setting can be used when the SendUsing element is set to 2.  
    SendUsing Specifies which method to use for sending messages.

    To send report subscriptions through e-mail using a local POP3 server hosted on a Windows 2003 server, you must set the value to 1.

    Valid integers

    1=sends a message using the local SMTP service pickup directory.

    2=sends the message using the network SMTP service.

    SMTPAuthenticate Specifies an integer value that indicates the kind of authentication to use when sending messages to an SMTP service over a TCP/IP connection. 0=no authentication.

    1=basic authentication. Credentials are passed in clear text using either sendusername and senduserpassword, or postusername and postuserpassword fields.

    2= NTML (NT LanMan) authentication. The security context of the current process is used to authenticate the service.

    From Specifies an e-mail address from which reports are sent in the format abc@host.xyz. The address appears on the From line of an outgoing e-mail message. This value is required. It must be a valid e-mail account on the SMTP server.
    EmbeddedRenderFormats Specifies the rendering format used to encapsulate a report within the body of an e-mail message. Images within the report are subsequently embedded within the report. Valid values are MHTML and HTML4.0.
    PrivilegedUserRenderFormats Specifies rendering formats that a user can select from for a report subscription when subscribing is enabled through the "Manage all subscriptions" task If this value is not set, all render formats that are not purposely excluded are available to use.
    ExcludedRenderFormats Purposely excludes formats that do not work well with e-mail delivery. HTMLOWC
    SendEmailToUserAlias This value works with DefaultHostName.

    When SendEmailToUserAlias is set to true, users who define individual subscriptions are automatically specified as recipients of the report. The To field is hidden. If this value is false, the To field is visible. Set this value to true if you want maximum control over report distribution.

    true=The e-mail address of the user creating the subscription is used. This is the default value.

    false=Any e-mail address can be specified.

    DefaultHostName This value works with SendEmailToUserAlias.

    Specifies a string value indicating the host name to append to the user alias when the SendEmailToUserAlias is set to true.

    The value is a DNS name or IP address.
    PermittedHosts Limits report distribution by explicitly specifying which hosts can receive e-mail delivery. Only e-mail accounts defined for the host are valid recipients. Include the value DefaultHostName in PermittedHosts. Value must be one or more DSN names or IP addresses. By default, this value is not set.

    If the value is not set, there are no restrictions on who can receive e-mailed reports.


    Wednesday, November 09, 2005 4:40 PM
  • I too am facing the same problem and the above information does not help. Here is my problem:

     

    I need to send emails using a remote SMTP server. I want to use the smtp server of my email provider, which is outside my local network. The SMTP server takes a username and password.

     

    I read in another post in this forum that Reporting service cannot do this. It said, that the only workaround for this is to ask reporting service to generate emails into a specific directory. Then configure windows 2003 SMTP service to pickup emails from this folder and forward it to my SMTP server (using my smtp username and password).

     

    Can some one comment on this approach? i could not find a way to configure the windows 2003 smtp server to do it. has some one already done it?

     

     

    Thursday, April 26, 2007 7:10 AM
  • Jacob,

     

    Here is what I have found.  Once you install the SMTP Service, the pickup folder defaults to C:\Inetpub\mailroot\pickup.  You also need to create a Smart Host Authentication to forward the emails on I believe.  I can't really test much of this because I don't have a SMTP Server that requires authentication to send through that I can use.  You do need to change your RSReportServer.config file, and there is a link below to that.

     

     

    RSReportServer Configuration File

    IIS Insider: Smart Host Authentication

    SMTP Forward to IMail  (scroll way down to the answer)

     

    I hope this helps some.

     

    EDIT:  I added the below Extension which should do the dropping to your pickup folder:

     

    Code Snippet

    <Extension Name="Report Server Email" Type="Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider,ReportingServicesEmailDeliveryProvider">

    <MaxRetries>3</MaxRetries>

    <SecondsBeforeRetry>900</SecondsBeforeRetry>

    <Configuration>

    <RSEmailDPConfiguration>

    <SMTPServer></SMTPServer>

    <SMTPServerPort></SMTPServerPort>

    <SMTPAccountName></SMTPAccountName>

    <SMTPConnectionTimeout></SMTPConnectionTimeout>

    <SMTPServerPickupDirectory>c:\inetpub\mailroot\pickup</SMTPServerPickupDirectory>

    <SMTPUseSSL></SMTPUseSSL>

    <SendUsing>1</SendUsing>

    <SMTPAuthenticate></SMTPAuthenticate>

    <From>noreply@domain.com</From>

    <EmbeddedRenderFormats>

    <RenderingExtension>MHTML</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>

     

     

    Also Look at:

     

    http://www.tech-faq.com/understanding-and-managing-smtp-virtual-servers.shtml

    Thursday, March 13, 2008 7:21 PM
  • Jonathan,

    The Issue that I and (and many people that I know) is that, the SMTP server is not local. The problem is about using a remote SMTP server. For example, I need to send my emails using the SMTP server: smtp.dotnetquest.com which takes a username and password (Authenticated SMTP). The report server configuration files do not have a settting that can take a username and password for this purpose.

     

    I heard that the Email delivery extension does not support remote SMTP servers (outside the local network). Is that correct? If yes, is this feature expected in the near future? I could not find any documentation about that.

     

    thanks

    Jacob

     

    Friday, March 14, 2008 2:19 AM
  • Jacob,

     

    I searched for a bit on this, and played with a number of things even after I made my post.  SQL 2008 Feb CTP doesn't support what you need based on the limited documentation, and the config files.  The mechanism for sending email is to either use a internal SMTP server, or to set the XML Node <SendUsing>1</SendUsingwhich according to the BOL will allow you to configure the path to the Pickup folder for the SMTP IIS Service running on the local server.  If you configure that correctly, it should pickup from this folder, and then using the Smart host, with your authentication, forward the email to the external SMTP Server.  I unfortunately don't have any method of testing this end to end as I don't have an authenticating SMTP Server to forward out to.

     

     

    Friday, March 14, 2008 2:26 AM
  • With the internal SMTP SERVER (local network) it is working well. We have been using it for quite a long time. The problem is with a few customers who do not have a local SMTP server.

     

    Friday, March 14, 2008 2:35 AM
  • Jacob,

     

    On the SQL Server itself, you would install the SMTP Services from Windows 2003.  You can do this by running the Configure my Server wizard.  I know, that this is definately not in the best practices, but from what I can find, once you install the SMTP Server, you configure it to use a Smart Host, either by name or IP Address, with a username and password and the local SMTP Services will use that server as a forwarding point for all of the mail.  Then you just need to change the config file for the RS and have it drop the subscriptions in the Pickup folder for the SMTP Services.  When the SMTP Services poll this folder again, it should forward that email out to the external SMTP server for actual sending/delivery.

     

     

    BTW, once installed, the SMTP Services will be in the IIS Administration Snap in.

    Friday, March 14, 2008 2:42 AM
  • Currently a local smtp server (Windows SMTP Service) is used to deliver emails. Was looking for a way to work without having an SMTP server installed locally. I just added a feature request at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=332979.

    Please vote, if you think this feature is important.

     

    thanks Jonathan.

    Friday, March 14, 2008 2:59 AM
  • Jacob,

     

    Vote Added.  I was surprised to find this kind of shortfall in subscription delivery in SSRS, especially in 2008 this far into the game I expected that someone would have come across this previously.

     

    In thinking about this, if you had a secondary 2003 server, I guess you could configure it and allow the RS to drop the files in its pickup folder over a UNC.  Of course if they already have SMTP and SQL running together, then this is likely not an option.

     

    Friday, March 14, 2008 3:07 AM
  • I'm have this issue too and I wanted to vote for this.  When I go to the link above I see the issue. However once I log in the issue is not longer available to be voted on.  I get the following error:

     

    Page Not Found

    The content that you requested cannot be found or you do not have permission to view it.

    If you believe you have reached this page in error, click the Help link at the top of the page to report the issue and include this ID in your e-mail: db093024-34ee-4892-b02d-91a5a1156f6d

    Microsoft Connect welcome page.

     

     

    If I search for the error message, it does not exist! Go Figure!  I guess they MS does nto want to fix a flaw in their software.

     

    Sunday, March 29, 2009 9:50 PM
  • Thanks for filing the request. We will consider this in a future release of RS.
    Sunday, March 29, 2009 10:40 PM
  • Thanks for filing the request. We will consider this in a future release of RS.

    So what does this mean? Where can I send other to vote on this issue. I know of at least two other companies that are having this issue. So…
    Monday, March 30, 2009 2:09 AM
  • Vote here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=332979

    -Lukasz


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, April 01, 2009 4:20 PM
  • Vote here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=332979

    -Lukasz


    This posting is provided "AS IS" with no warranties, and confers no rights.

    You missed the point.. As soon as you log in the issue is gone and you get a 404 error message. So, How can you vote for something that is clearly locked out when you login to the Connect site? Again this the error message I get once I login!

     

    Page Not Found

    The content that you requested cannot be found or you do not have permission to view it.

    If you believe you have reached this page in error, click the Help link at the top of the page to report the issue and include this ID in your e-mail: 981a1067-5d7b-492f-b360-6d438f03f8e0

    Microsoft Connect welcome page.

    Wednesday, April 01, 2009 5:22 PM
  • Johnathan,

    thank you for your suggestion.  This totaly resolved my problem.  The trick is to get the local SMTP configured with the smart host:

    1) Install SMTP server in Add/Remove programs / App Server / IIS
    In IIS Manager:
    2) add the domain (as a remote type) you will be sending as such as yourwebsite.com
    3) Under Default SMTP Virtual Server properties / delivery tab / advaced button, add your remote smtp server in the smart host field.
    4) Under Default SMTP Virtual Server properties / delivery tab / Outbound security button, choose Basic Authentication, user name = SMTP user name, password = SMTP password

    Then modifing the report config file like u have outlined in your answer.  Thank you again.
    Thursday, April 23, 2009 10:11 PM
  • Jonathan, It is working using this solution.

    Thanks

    Bruno Santos
    Bruno Santos
    Thursday, January 28, 2010 12:07 PM