How to send an email message using Microsoft Exchange Server from Sql Server 2005/2008?

Question

I know how to send mail using an SMTP server specified in a mail profiler in the Database Mail of Sql Server.

You would do it like so:

EXEC msdb.dbo.sp_send_dbmail  @profile_name = 'profile',
                              @recipients = @email,
                              @body = @body,
                              @body_format = 'HTML',
                              @subject = @subject

But I don't know how to send mail using Microsoft Exchange.

Update 1. I believe that the main problem of my not being able to send mail through Exchange is that it's using the NTLM-based authentication. And I most certainly don't want my Sql Server running using my domain user credentials.

Update 2. Thank you for your suggestions. Here's the catch. I don't have any control whatsoever over the corporate Exchange Server in question. So the only course of action would be to try to impersonate me on the Exchange endpoint somehow. Luckily, I can communicate with the Exchange via telnet ip-of-the-exchange-server 25.

Answer 1

Exchange is just another SMTP server. You point your profile to the specifics of your server, and you're done. I do it every day in my environment. Is SMTP disabled for some reason on your Exchange instance?

Answer 2

• Use your Exchange Server's IP as the STMP server in SQL Server and
• configure Exchange Server's SMTP connector to allow relaying for the IP of your SQL Server.

Follow the link for instructions on how to do this with Exchange Server 2003 and 2007.

Regarding your Update: If you do it like described above (allow relaying), you don't need any credentials. Every process on your SQL Server's machine will be allowed to send mail through your Exchange Server, without any authorization.

Here are some general hints to debug an SMTP connection. To test this configuration, you could open a command line on your SQL Server and use telnet to create an SMTP session (you have to type the lines that do not begin with a number):

C:\>telnet ip-of-your-exchange-server 25
220 your.domain Microsoft ESMTP MAIL Service, Version: 6.0.3790.4675 ready at  Thu, 2 Sep 2010 16:19:33 +0200
EHLO heinzi
250-your.domain Hello [192.168.3.1]
250-TURN
[...]
250-XEXCH50
250 OK
MAIL FROM:<[email protected]>
250 2.1.0 [email protected] OK
RCPT TO:<[email protected]>

After the last line, your receive either of two answers. This one means that everything is fine:

250 2.1.5 [email protected]

This one (or a similar one) means that you have still not configured your Exchange Server correctly:

554 5.7.1 <[email protected]>: Relay access denied