9

I am using MS SQL Server 2008 R2 SP2 Express edition, 64 bit. I do not have access to the Database Mail setup wizard, but the Binn directory contains DatabaseMail.exe (v10.50.16600.1) and DatabaseMailengine.dll.

I found several references to "Cannot send mails to mail server", but nothing useful with the annoyingly generic "Failure sending mail" message.

My specific question is where can I find out what that error message means/what causes it? If some kind soul would care to hazard a guess about what is going on, or not going on, I would appreciate it.

Setup info from the database follows:

EXECUTE sp_configure 'show advanced'

name                    minimum maximum config_value    run_value
show advanced options   0       1       1               1

EXECUTE sp_configure 'Database Mail XPs'

name                minimum maximum config_value    run_value
Database Mail XPs   0       1       1               1

sysmail_server

account_id  servertype  servername      port username           credential_id   use_default_credentials enable_ssl  flags   timeout last_mod_datetime       last_mod_user
1           SMTP        smtp.gmail.com  465  sqlmail@domain.com 65536           0                       1           0       NULL    2015-11-23 09:34:26.913 WSCORP\username

sys.credentials

credential_id   name                                 credential_identity create_date             modify_date             target_type target_id
65536           F05ADE33-6AA8-45FC-8FF5-A7631831ECB1 sqlmail@domain.com  2015-11-23 09:34:26.913 2015-11-23 09:34:26.913 NULL        NULL

EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole'

DbRole               MemberName      MemberSID
DatabaseMailUserRole WSCORP\username 0x010500000000000515000000F483990DEADF2220C7DB306E6B040000

EXEC msdb.dbo.sysmail_help_principalprofile_sp

principal_id principal_name  profile_id profile_name is_default
27           WSCORP\username 1          Default      1

EXECUTE dbo.sysmail_start_sp

Status
STARTED

sysmail_account

account_id name    description email_address      display_name         replyto_address  last_mod_datetime       last_mod_user
1          SQLMail NULL        sqlmail@domain.com SQLExpresspay12 Mail user@domain.com  2015-11-23 09:34:26.843 WSCORP\username

sysmail_profile

profile_id name    description     last_mod_datetime       last_mod_user
1          Default Default profile 2015-11-23 09:56:58.947 WSCORP\username

sysmail_profileaccount

profile_id  account_id  sequence_number last_mod_datetime       last_mod_user
1           1           1               2015-11-23 10:02:29.867 WSCORP\username

sysmail_servertype

servertype is_incoming  is_outgoing last_mod_datetime       last_mod_user
SMTP       0            1           2010-04-02 17:36:17.340 sa

sysmail_mailitems

mailitem_id profile_id  recipients      copy_recipients blind_copy_recipients subject      from_address reply_to body                                       body_format importance  sensitivity file_attachments attachment_encoding query  execute_query_database  attach_query_result_as_file query_result_header query_result_width  query_result_separator  exclude_query_output append_query_error send_request_date       send_request_user sent_account_id   sent_status sent_date               last_mod_date       last_mod_user
4           1           user@domain.com NULL            NULL                  Test message NULL         NULL     This is the body of the test message.      TEXT        NORMAL      NORMAL      NULL             MIME                NULL   NULL                    0                           1                   256                 0                       0                    0                  2015-11-24 08:23:59.493 WSCORP\username   NULL              2           11/1124/20158 8:25:20   11/24/2015 08:25:20 sa
                                                                                                                   Congratulations, Database Mail Received
                                                                                                                   By you Successfully.

sysmail_log

log_id  event_type  log_date                description                                             process_id  mailitem_id account_id last_mod_date            last_mod_user
13      1           2015-11-24 08:23:59.787 DatabaseMail process is started                         7324        NULL        NULL       2015-11-24 08:23:59.787  NT AUTHORITY\NETWORK SERVICE
14      1           2015-11-24 08:24:10.270 The mail queue was started by login "WSCORP\username".  NULL        NULL        NULL       2015-11-24 08:24:10.270  WSCORP\username
15      3           2015-11-24 08:24:10.297 The mail could not be sent to the recipients because of 7324        4           NULL       2015-11-24 08:24:10.297  sa
                                             the mail server failure. (Sending Mail using Account 1
                                              (2015-11-24T08:24:10). Exception Message: Cannot send
                                               mails to mail server. (Failure sending mail.). )
WeststarEric
  • 321
  • 1
  • 2
  • 12

1 Answers1

12

Google wanted me to use the TLS port (587) even though, as far as I can tell, there was no way to tell the SQL server to use TLS. So specifying SSL and using the TLS port worked.

WeststarEric
  • 321
  • 1
  • 2
  • 12
  • Can you please elaborate your answer @WeststarEric? I am having the same problem as yours but couldn't find out the possible solution. May be your trick can ease my way. Thanks – user3673503 Dec 28 '15 at 05:43
  • 2
    This page https://support.google.com/a/answer/176600?hl=en says to use port 465 for SSL or port 587 for TLS. As you can see from my sysmail_server record above, I used port 465 and set the "enable_ssl" flag. The answer was to leave that flag set, but change the port to 587. – WeststarEric Dec 29 '15 at 15:08
  • Does it still work in 2020? I get error: `(The SMTP server requires a secure connection or the client was not authenticated. The server response was 5.7.0 Authentication required. Learn more at).` – Adam Feb 10 '20 at 21:47
  • Sadly, this database is no longer in use, so I don't know whether it would still work. Also our mail server is not functioning. Things are not going well. – WeststarEric Feb 11 '20 at 22:57
  • 2
    fyi... DatabaseMail.exe is a .NET Framework program using the [System.Net.Mail.SmtpClient class](https://learn.microsoft.com/en-us/dotnet/api/system.net.mail.smtpclient) to do mail transmission. The `enable_ssl` column drives the [EnableSsl property](https://learn.microsoft.com/en-us/dotnet/api/system.net.mail.smtpclient.enablessl) on the class which determines whether or not it uses `STARTSSL` (aka. Explicit SSL) on either port 25 or port 587. The SmtpClient class does not support Implicit SSL on port 465. – AlwaysLearning Jul 30 '21 at 05:15
  • Most email servers require TLS1.2. Not sure if this patch applies to SQL Server Express edition. I used this to patch the Enterprise edition: https://support.microsoft.com/en-us/topic/kb3135244-tls-1-2-support-for-microsoft-sql-server-e4472ef8-90a9-13c1-e4d8-44aad198cdbe – James L. Aug 11 '23 at 23:26