Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL server "Cannot send mails to mail server" "Failure sending mail"

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  [email protected] 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 [email protected]  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        [email protected] SQLExpresspay12 Mail [email protected]  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           [email protected] 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.). )
like image 820
WeststarEric Avatar asked Nov 24 '15 21:11

WeststarEric


1 Answers

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.

like image 102
WeststarEric Avatar answered Nov 11 '22 16:11

WeststarEric