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.). )
Answer
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.