October 1, 2007 at 7:11 am
Hi,
I am trying to set up Database Mail for my first time, in SQL Server 2005. It's probably worth noting at the beginning of this post that SQL Server 2005 is not a tool that I use to do anything particularly complex with usually - I have a few stored procedures that I use to load data, and I occasionally run simple queries, and that's about it.
I decided to set up Database Mail so that I could receive an email notifying me when one of my jobs had finished, rather than having to keep an eye on the Job Activity Monitor.
I have been using SQL Server Help, and have got so far as making sure that Database Mail is enabled, active, and that I am a member of the DataBaseMailUserRole. I have used the Database Mail Configuration Wizard to set up a Database Mail profile and account. For the Server field in the Wizard, I went into my email application (LOTUS NOTES), and went to Database, then Properties, then picked up the text that was listed under 'Server', which was in the format AWNDMail01/Office Location/Company Name. (Omitting some detail here so that I don't get in trouble at work!)
When I then try to send a test email, I get the following error message:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2007-10-01T12:26:04). Exception Message: Could not connect to mail server. (No such host is known). )
I'm hoping that someone might be able to help me figure out what this error message means? At this stage, I'm thinking it's either:
a) That I haven't entered the correct server information into the Configuration Wizard
b) After some reading of various forums, it would seem that other people were getting the same error as a result of Port 25 being blocked on their firewall. I have checked my Windows Firewall settings, and Windows Firewall seems to be turned off on my local machine. I believe that the IT department at my company would control this sort of stuff themselves (not locally on each PC), as we have extremely restricted access to the internet (no personal email etc), and many of our ingoing and outgoing emails get quarantined.
Any help would be great appreciated.
Thanks,
Emily.
October 1, 2007 at 7:32 am
SQL Mail is not looking for the Lotus Notes mail server, it is looking for an SMTP server. If your lotus notes server is configured as an SMTP relay point (which it typically would be), you should just need to enter the name of the server, or the IP address. Usually SMTP servers are left configured as the default port 25, so it should be easy to set up.
If you have a network admin in charge of your lotus notes server, tell tham that you need to be able to send an SMTP message and ask which server and port you should be using.
October 1, 2007 at 7:37 am
Thanks for the explanation Michael.
It may take a few days to get my request understood and turned around, will get back to you on how it goes.
October 1, 2007 at 8:42 am
Since it is using SMTP, you may run into some other issues. I do not know what security is in Lotus Notes these days (luckily I don't have to deal with it), but most mail servers restrict SMTP relaying to prevent hackers from using your mail server to send spam.
Exchange, for instance, is most often configured to not allow an SMTP relay except from specific IP addresses, so you may need to have your SQL Server's IP address configured in the allow relay list. It is also common to only allow SMTP relay's to email addresses in the global address list, so if you try to SMTP to an email outside of your company you may have issues.
October 1, 2007 at 8:43 am
As an aside, if you have not used Service Broker for anything, you are now. The new mail procedures use service broker. Look at the procedure text to see what they are doing - it is a good way to get a start at understanding the service broker architecture.
October 1, 2007 at 9:04 am
Thanks for all of the advice Michael 🙂
I've managed to get ahold of the IP address and Port, and I have entered the relevant IP address and Port into the Server Name & Port fields in the Configuration Wizard.
I now get a new (and equally mysterious) error message in the sysmail event log:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2007-10-01T15:51:34). Exception Message: Could not connect to mail server. (An established connection was aborted by the software in your host machine). )
Do you think then the next step is to contact IT again and ask them to add SQL Servers IP address to the list allowed to relay?
Thanks again,
Emily.
October 1, 2007 at 9:08 am
You can build your case w/ IT by confirming its an SMTP issue - i.e. RDP into the MSSQL server and attempt to send mail via telnet (http://www.amset.info/exchange/telnet-test.asp).
Tommy
Follow @sqlscribeOctober 1, 2007 at 9:48 am
That's a great document link - start there.
October 2, 2007 at 3:10 am
Hi Tommy/Michael,
Have sat down this morning and ran through the first couple of steps of the SMTP Telnet Test.
When I run Step 4 (open external-ip 25), I get the following message:
...Could not open connection to the host, on port 25: Connect failed
Does this indicate that it is an SMTP issue? Do I now ask IT to add SQL Servers IP address to the list allowed to relay..?
Thanks so much to both of you for your help so far.
October 2, 2007 at 7:16 am
Hi Emily,
Is there an FW between the MSSQL server and the SMTP relay? If this is the case, can you confirm w/ IT that TCP 25 is open between the two? It is also possible that the SMTP virtual server is restricted to a range of IP addresses.
Tommy
Follow @sqlscribeOctober 3, 2007 at 3:56 am
Hi,
Not sure about the firewall. I think I'm going to have to get in touch with IT to see if they can understand what is going on (guaranteed to be lengthy and painful!).
Is there any other way of setting up a notification to myself when one of my jobs have finished, rather than having to keep an eye on the Job Activity Monitor?
Thanks,
Emily.
October 3, 2007 at 7:58 am
Good luck with IT 🙂
I've always used a query similar to the one below to check for job failures in the morning. You can get creative and throw this in reporting services (if you have an instance of RS available to you).
select
Left(@@servername,20) Server,
left(j.name,50) Job
from msdb.dbo.sysjobs j (nolock)
join msdb.dbo.sysjobhistory h (nolock)
on j.job_id = h.job_id
left join msdb.dbo.sysjobhistory h2 (nolock)
on j.job_id = h2.job_id and h2.step_name='(Job outcome)' and h2.message like 'the job succeeded%' and h.instance_id<h2.instance_id
where h.run_date >= convert(char(8),dateadd(day,-1,getdate()),112)
and h.step_name='(Job outcome)'
and h.message not like 'the job succeeded%'
and h2.instance_id is null
group by j.name
order by j.name
Tommy
Follow @sqlscribeDecember 26, 2007 at 1:50 pm
If this isn't helpful, I apologize. I had the same problem and here is what I did ...
If it doesn't help you, maybe it will be pertinent in someone else's situation:
Here is what I did to resolve it:
1. I made sure the port is open by telnet - at dos prompt: telnet 25
If it works, then your error is not a problem with the smtp server.
2. I made sure no one else had attempted to set up a default profile:
SELECT * FROM msdb.dbo.sysmail_profile
In my case the DBA prior to me had set up a bad profile, which never worked. I deleted it. VOILA!
My database mail started working immediately.
Good luck!
😎
January 3, 2008 at 11:05 am
I had yet another issue with database mail. Where the above steps didn't work.
Apparently, when you drop and recreate a new profile, the registry doesn't always get changed to reflect the new profile.
Step 1. select * from msdb.dbo.sysmail_profile
Step 2. regedit
Step 3. verify that the DatabaseMailProfile key name is exactly the same as step 1 (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\SQLServerAgent)
Step 4. if not then change it
Step 5. YOU HAVE TO RESTART THE SQLAGENT
Hope this helps someone, I've been fighting this for a while!
😎
January 4, 2008 at 9:40 am
Tommy Bollhofer (10/2/2007)
Hi Emily,Is there an FW between the MSSQL server and the SMTP relay? If this is the case, can you confirm w/ IT that TCP 25 is open between the two?
I think mcAffee blocks port 25 by default. I dont remember exactly, but I had to do something to mcaffee to allow database mail to work.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply