locked
Why is my SSRS Email Subscription not sending Email? RRS feed

  • Question

  • So we recently set-up our SQL Server in order to Email a SSRS subscription report. So we set-up the subscription to email and for some reason we are not getting an email out of it. I am actually trying to email myself to simply test it. The statistics within Report Manager Portal and the [Subscriptions] data rows seem to imply that it did indeed run...just didn't get the email accordingly.

    I am the developer. Soooo I am looking and digging as to what may be causing this from a DBA perspective in regards to setting up the SQL Server correctly and SMTP and other moving pieces that we could look at it to try and determine why the email is not firing.

    Any help or guidance you could provide would be GREATLY appreciated.

    Thanks!

    Monday, August 21, 2017 8:13 PM

All replies

  • Hi,

    Check your log files

    %ProgramFiles%\Microsoft SQL Server\MSSQL.x\Reporting Services\LogFiles

    The SMTP service requires authentication(it is not annonymous enabled), please make sure the SSRS service is running under a valid domain account, and has correct email delivery settings:
    http://msdn.microsoft.com/en-us/library/ms345234.as

    Check with your message team whether you provide right SMTP server details. If your SMTP is fine check whether SQL server  is allowed to relay mails without issue with either network or messaging teams(firewall issues).

    https://technet.microsoft.com/en-us/library/aa995718(v=exchg.65).aspx

    Regards,

    Navin


    Navin.D http://dnavin.wordpress.com




    • Edited by Navind Monday, August 21, 2017 8:23 PM
    • Proposed as answer by Navind Thursday, August 24, 2017 8:14 PM
    Monday, August 21, 2017 8:19 PM
  • If I cannot get this to work, it looks as though I'm going to have to resort to sending a query result set of my [AggregateError] rows via an HTML Table and xp_send_mail.

    Does anyone have some good documentation on this or decent web sites that I can reference on doing this?

    Hopeful for a reply.

    Thanks!

    Monday, August 21, 2017 9:03 PM
  • Hi ITBobbyP,

    To make subscription successful, you need to make sure you have configured correct E-mail Setting(SMTP Server, address and user information) in Reporting Service Configuration Management firstly. You could refer to E-Mail Settings for details.

    Then when you create subscription, you need to make sure the SQL Agent Job works well, check the delivery option in Report Manager is correct(To address). After you create subscription, you could check its result in My Subscription

    If it isn’t be sent, it will show the error information in Result, you also need to check detailed information according to time in trace log(C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\LogFiles)

    In addition, you also need to check wehther there is something wrong in your e-mail box, you could send email to yourself by email box directly instead of create a subscription to yourself to see whether it works well.

    You could refer to Troubleshooting Subscriptions and Monitoring and Troubleshooting Subscriptions for details.

    If you have any question, please feel free to ask.
    Best regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 22, 2017 3:10 AM
  • Hi All,

    I am also facing similar issue. On Report Manager, I have an error message "Failure sending mail: One or more errors occurred." under Result Column. When I checked the Job for this Subscription on SSRS Report Server SQL Agent Job, It says successfully completed. 

    Date 16/09/2019 9:27:00 AM
    Log Job History (913468AB-0B3A-4F31-9B2A-11D45F33FC5F)

    Step ID
    Server ABCD
    Job Name 913468AB-0B3A-4F31-9B2A-11D45F33FC5F
    Step Name
    Duration 00:00:00
    Sql Severity 0
    Sql Message ID 0
    Operator Emailed
    Operator Net sent
    Operator Paged
    Retries Attempted 0

    Message
    The job succeeded.  The Job was invoked by Schedule 108 (Schedule_1).  The last step to run was step 1 (913468AB-0B3A-4F31-9B2A-11D45F33FC5F_step_1).

    Date 16/09/2019 9:27:00 AM
    Log Job History (913468AB-0B3A-4F31-9B2A-11D45F33FC5F)

    Step ID 1
    Server ABCD
    Job Name 913468AB-0B3A-4F31-9B2A-11D45F33FC5F
    Step Name 913468AB-0B3A-4F31-9B2A-11D45F33FC5F_step_1
    Duration 00:00:00
    Sql Severity 0
    Sql Message ID 0
    Operator Emailed
    Operator Net sent
    Operator Paged
    Retries Attempted 0

    Message
    Executed as user: NT AUTHORITY\SYSTEM. The step succeeded.

    Also I check SSRS Error Log File and can't find any Error.

    library!WindowsService_3!b58!09/16/2019-09:27:37:: i INFO: Call to CleanBatch()
    library!WindowsService_3!b58!09/16/2019-09:27:37:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobs, 0 persisted streams, 0 segments, 0 segment mappings, 0 edit sessions.
    library!WindowsService_3!b58!09/16/2019-09:27:37:: i INFO: Call to CleanBatch() ends

    Guys, Please can you help in resolving this..? Is this something to do this Office 365..?


    Thanks Shiven:) If Answer is Helpful, Please Vote


    Friday, September 13, 2019 7:00 AM
  • I checked the Log file ReportingServicesService and not RSPortal located atC:\Program Files\Microsoft Power BI Report Server\PBIRS\LogFiles

    and can see Error Message: What could be reason? 

    emailextension!WindowsService_2!6564!09/18/2019-16:32:27:: e ERROR: Error sending email. Exception: System.AggregateException: One or more errors occurred. ---> System.Net.Mail.SmtpException: Failure sending mail. ---> System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond IP address
       at System.Net.Sockets.Socket.InternalEndConnect(IAsyncResult asyncResult)
       at System.Net.Sockets.Socket.EndConnect(IAsyncResult asyncResult)
       at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Exception& exception)
       --- End of inner exception stack trace ---
       at System.Net.Mail.SmtpConnection.ConnectAndHandshakeAsyncResult.End(IAsyncResult result)
       at System.Net.Mail.SmtpTransport.EndGetConnection(IAsyncResult result)
       at System.Net.Mail.SmtpClient.ConnectCallback(IAsyncResult result)
       --- End of inner exception stack trace ---
       --- End of inner exception stack trace ---
       at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
       at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
       at System.Threading.Tasks.Task.Wait(TimeSpan timeout)
       at Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.Deliver(Notification notification)
    ---> (Inner Exception #0) System.Net.Mail.SmtpException: Failure sending mail. ---> System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond IP address
       at System.Net.Sockets.Socket.InternalEndConnect(IAsyncResult asyncResult)
       at System.Net.Sockets.Socket.EndConnect(IAsyncResult asyncResult)
       at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Exception& exception)
       --- End of inner exception stack trace ---
       at System.Net.Mail.SmtpConnection.ConnectAndHandshakeAsyncResult.End(IAsyncResult result)
       at System.Net.Mail.SmtpTransport.EndGetConnection(IAsyncResult result)
       at System.Net.Mail.SmtpClient.ConnectCallback(IAsyncResult result)
       --- End of inner exception stack trace ---<---
    . Additional Information: SmtpException StatusCode:GeneralFailure
    notification!WindowsService_2!6564!09/18/2019-16:32:27:: e ERROR: Error occurred processing subscription 0bd9c316-7067-4f52-afc2-017ea1ed5e1b: Failure sending mail: One or more errors occurred.

    Thanks Shiven:) If Answer is Helpful, Please Vote

    Wednesday, September 18, 2019 6:48 AM
  • First check if there's some program blocking the port 25

    May be antivirus or Firewall. If yes, open the ports in it

    Next thing to look for is the IP you used within Report Server Configuration manager for SMTP

    If you're using local relay server ensure local IP has not changed 


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, September 18, 2019 7:02 AM