Post reply

Problem with DatabaseMail

  • April 8, 2014 at 4:54 am

    #287324

    Hi all

    We've just moved our database to a new server, and we're having problems getting databaseMail to work.

    Essentially, when we try to send a message by using sp_send_dmail, the return message is that the mail is queued. If we look in the queue using sysmail_allitems, we can see the message there with a status of Unsent. However, if we locate and manually run DatabaseMail.exe by double-clicking on it, the mail is sent successfully.

    We don't get any errors, and we've tried all that we can from scouring the web for answers, including

    1. Enabling dbmail by using sp_configure 'Database Mail XPs',1

    2. Enabling service broker by using ALTER DATABASE [MSDB] SET ENABLE_BROKER WITH NO_WAIT

    3. Configuring a mail profile and the SMTP account (exactly as per the previous server's settings)

    As I said, it works if we manually double-click on databasemail.exe, which would imply that the settings are all correct, but if we don't double-click, the mail simply sits in the queue.

    Can anyone provide any advice?

    Many thanks

    Andrew

  • April 8, 2014 at 5:42 am

    #1704094

    Looks like there might be permission issue. What error message you are getting when sending mail fails?

    DatabaseMail.exe is external programme and use windows authentication. Refer following link which might help you:

    http://technet.microsoft.com/en-us/library/ms190466.aspx

    HTH

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • April 8, 2014 at 10:47 am

    #1704207

    Hi

    Thanks for the reply.

    Unfortunately (or not!) there's no error when we try to send mail - it simply says "Item queued" (or words to that effect - I'm not in front of the server right now).

    I did wonder about permissions, so I (for temporary testing purposes only!) added "Everyone" to "Full permissions" for the databasemail.exe file, but that seemed to have no impact. I didn't change permissions on the containing folder - I guess I could try that...

    Thanks

    Andrew

  • April 8, 2014 at 12:35 pm

    #1704267

    andrew.richards 92695 (4/8/2014)

    Hi

    Thanks for the reply.

    Unfortunately (or not!) there's no error when we try to send mail - it simply says "Item queued" (or words to that effect - I'm not in front of the server right now).

    I did wonder about permissions, so I (for temporary testing purposes only!) added "Everyone" to "Full permissions" for the databasemail.exe file, but that seemed to have no impact. I didn't change permissions on the containing folder - I guess I could try that...

    Thanks

    Andrew

    Could you see if you can find any error messages in the Database Mail Log ?

    --

    SQLBuddy

  • April 9, 2014 at 3:46 am

    #1704424

    Hi all

    Thanks for your help.

    I looked in the Windows Application event log and there were various errors along the lines of "Database Engine Instance=ID30851;Mail PID=13148;Error Message:The update to the database failed. Reason: The EXECUTE permission was denied on the object 'sysmail_logmailevent_sp', database 'msdb', schema 'dbo'."

    Having done quite a lot of reading around, it seemed that this is a permissions issue, and various posts suggested that SQLAgent Loginneeds to be set up as sysadmin (which had no effect) and also that the dbmail process may be running as MSSQLServer, which would then also need to be sysadmin.

    I made the MSSQLServer login a member of sysadmin and this fixed the issue - dbmail now works.

    However, I'm braced for a slew of people telling me that this is a REALLY bad idea for security reasons, so if anyone can provide guidance on what the better solution should be (what membership MSSQLServer should have, for example, or how I get DBMail to run as something other than MSSQLServer maybe?) then that guidance would be gratefully received.

    Thanks a lot

    Andrew

  • April 9, 2014 at 8:36 am

    #1704543

    Before you made it a sysadmin, was the login you were using for database mail a member of the DatabaseMailUserRole database role in msdb? Do this then remove it from the sysadmin server role and try it again.

    If it still fails, check your mail profiles (under Configure Database Mail > Manage Profile Security). Sysadmins have access to all mail profiles, but other logins don't usually. Grant access to a private profile to the login in question, then try again. Hopefully it will work.

  • April 9, 2014 at 8:36 am

    #1704544

    (Dup.)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply