August 13, 2008 at 10:19 am
The basic problem I have is we went gung-ho here and ported a couple of databases over from 2000 to 2005 just because they passed the upgrade advisor tests and now we can't resolve names to email addresses.
For example, in the past if SQL Mail tried to send a mail to 'js41' - my login in active directory and outlook - SQL Mail resolved it because it used the outlook client.
Database Mail - which we've moved to - uses SMTP and therefore doesn't resolve the email address, so the mail doesn't get sent.
Is there a way to configure database mail such that if you try to send an email to a network log in it somehow queries active directory to get the email address before it's sent? I'm wondering whether i'm just being stupid and this is easy, or we've got to program any routines that do that to use active directory to resolve that address ourselves.
Ideally I'd like to just be able to configure database mail to talk to active directory and that's that. Is that possible?
Thanks! 🙂
August 13, 2008 at 10:27 am
i don't know about configuring dbmail to query the ad, but you can wrap a proc around dbmail and have the proc query the ad. in other words, instead of calling msdb..sp_send_dbmail, call dbo.myproc instead, where dbo.myproc does something like:
SELECT @ADEmail = email FROM OPENQUERY(LinkedServerToAD, 'SELECT email FROM ''LDAP://ADServer/DC=name,DC=com'' WHERE objectCategory = ''Person'' AND objectClass = ''user'' and userPrincipal = ''SomeUserName'''')'
or something along those lines (i may have too many quotes, not enough quotes, etc). i use something like this all the time, and it will return a max of 1000 rows, but if you're just looking for 1 row, it should work.
anyway, once @ADEmail has the email address, call sp_send_dbmail with @ADEmail as the recipient.
August 13, 2008 at 11:10 am
Your mail admins should be able to add additional SMTP addresses which correspond to your user accounts.
K. Brian Kelley
@kbriankelley
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply