February 25, 2010 at 9:30 am
All,
Have created a new report in sql server 2005 reporting services that needs to be emailed on a regular basis to someone else. I've shared the data source (there will eventually be more than one report) using credentials stored securely in the report server and username as domain\username. I've set up a database mail profile, and enabled sending email in sql agent. I've started the Messenger service in Admin Tools/Services. In Reporting Services Configuration the Email settings are set to an admin account as the Sender, SMTP as the method, and the smtp server name. And I still keep getting the message "Failure sending mail; The Transport failed to connect to the server".
Is there anywhere that I can find a-z list of what needs to be done, configured, set up to be able to successfully set up a report subscription? And even when you complete the 'subscription' page on report manager, how does it know which database mail profile to use (if there were more than one)?
Please help!
Jules
February 25, 2010 at 1:15 pm
OK it sounds like you have things setup correctly, or at least as far as I can tell. I believe you only need to setup the mail information in the Reporting Services Configuration. You are continuing to get a connection error however. This leads me to believe that something is blocking you communication from the SSRS box to the SMTP server. Make certain the name or IP of the SMTP server is correct.
Are you actually able to make SMTP connections from the machine to the SMTP server? Many firewalls, anti-virus, and other anti-malware programs block this by default. One easy way to check would be to try to open a smtp connection from the server directly.
Open a command prompt and type telnet YourSmtpServerNameorIP 25
Most A/V applications will start yelling once they block something, so be on the lookout for that and review your logs as they may contain a clue to your problem.
-Luke.
February 26, 2010 at 2:19 am
Hi Luke
Thanks very much for your advice. I did the telnet thing using the server name I used when setting up the database mail and it worked fine. So knowing I had the right name, I changed the server name stored in the reporting services configuration to match and hey presto - all worked! So I'm blaming someone (anyone) who is no longer here who set up reporting services in the first place. Can't believe it was something so basic.
Do you know if I even need to set up a database mail account to do anything in reporting services?
Thanks again,
Jules
February 26, 2010 at 6:04 am
I'm fairly certain that you don't need Database Mail to send Mail for Subscriptions. I don't have it setup on one of my reporting servers and it sends mail just fine. I believe the Rendering engine for SSRS handles creating the message and then just hands it off to the SMTP server.
For what it's worth it sounds like someone gave you an IP for your SMTP server instead of the DNS name. I typically recommend people stick with DNS names or better still DNS aliases so that you can change the hardware behind the scenes but don't have to reconfigure the numerous mail server settings on your servers/switches or anything else that sends mail. For example if you host your own DNS internally and you have the ability to create SMTP.MyDomain and point that at the IP of your SMTP server, you cna change the servername and as long as it keeps the same IP you don't have to change anything. If you do need to change the IP you ponly have to change it once, in the DNS record.
-Luke.
February 26, 2010 at 6:11 am
It's occurred to me that perhaps the database mail is used for email reports from, e.g. maintenance plans. Not email reports from report server! The ssrs reports definitely come from the account set up in reports configuration. I'll set one up to test that theory out.
I'm doing a test install of sql server 2008 at the moment, so any handy hints n tips you have for that, please feel free!
J.
February 26, 2010 at 6:18 am
yes database mail is what you need to use to send reports from Maintenance plans and other such portions of SQL Server.
The SSRS settings only send mail from SSRS.
As for setting up 2008 I'd say hit up the 2008 BOL via MSDN. I'm pretty sure they have a how to: step by step to do the install.
-Luke.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply