locked
SQL Server 2008 Database Mail RRS feed

  • Question

  • I am familiar with SQL Server, but not extremely knowledgeable about in depth stuff - so somewhat of a SQL newbie.

    I am in the process of trying to get Database Mail setup on my server.  I have gone through the Database Mail Configuration wizard to create a mail profile.  When I try to send a test message, the following error is entered into the sysmail_log table:

    The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2010-07-07T09:58:08). Exception Message: Cannot send mails to mail server. (Failure sending mail.). )

     

    Here is some info regarding my setup:

    I am using Windows Authentication to login to Management Studio.
    The Database Mail XPs is at 1.
    My account is part of the sysadmin role, so I think it should have rights to the DatabaseMailUserRole

     

    I believe that I do not have something else setup correctly.  I am reading online all the stuff I can find, but cannot seem to find a solution.  One thing I am not sure about is the stuff I read tells me to make sure the user who is sending the mail needs to be a part of the DatabaseMailUserRole in msdb database.  How do I tell which user it is trying to use to send the mail?  Is it the user I am logged in as?

    The users and roles are confusing me because they exist at multiple levels so I am not sure exactly where I need to have where.

    Thanks for any help.

    Wednesday, July 7, 2010 2:16 PM

Answers

  • Hi,

    According to your error message, I think it is an authentication issue or a port issue. When we use the Database Mail Configuration Wizard or the Database Mail stored procedures to create Database Mail accounts, please make sure the system parameters are valid.

    1.    Is your server name correct?

    2.    Is the port number opened?

    3.    Is the port number blocked by firewall?

    4.    Are Basic authentication settings correct? 

    You can run a query on the sysmail_allitems system view to check if the message was sent successfully.

    For more information, please see the following links:

    sysmail_allitems

    Troubleshooting Database Mail: Profile not valid

    How to create Database Mail Accounts 

      How do I tell which user it is trying to use to send the mail?  Is it the user I am logged in as? 


    When the login wants to execute sp_send_dbmail, this login must be a member of the DatabaseMailUserRole database role in the msdb database. If we run a SQL Agent Job to execute sp_send_dbmail, the login is a SQL Server Agent fixed database role too.

    For more information, please see

    sp_send_dbmail (Transact-SQL)

    Thanks,

    Ai-Hua Qiu

    • Proposed as answer by Danny Zhang Friday, July 9, 2010 4:42 PM
    • Marked as answer by KJian_ Tuesday, July 20, 2010 8:00 AM
    Friday, July 9, 2010 4:36 PM