1

I am trying to send the result of a SQL query through email using SQL Server 2014. The problem is that the e-mails are getting queued, but are not delivered to the recipient. There are some issues with the connectivity to the server. The description I am getting is:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2017-04-05T16:05:09). Exception Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 74.125.130.109:25).

My code is:

EXECUTE msdb.dbo.sysmail_add_account_sp  
     @account_name = 'MIS_Automation_Project',  
     @description = 'Mail account for office files.',  
     @email_address = 'my_email_address',  
     @display_name = 'MIS_Automation',  
     @mailserver_name = 'smtp.gmail.com' ;  

-- Create a Database Mail profile  
EXECUTE msdb.dbo.sysmail_add_profile_sp  
    @profile_name = 'MIS_Automation',  
    @description = 'Profile used for mis automation project' ;

-- Add the account to the profile  
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp  
    @profile_name = 'MIS_Automation',  
    @account_name = 'MIS_Automation_Project',  
    @sequence_number =1 ;

-- Grant access to the profile to the DBMailUsers role  
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp  
    @profile_name = 'MIS_Automation',  
    @principal_name = 'guest',  
    @is_default = 1 ; 

DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)

SET @xml = CAST(( SELECT [clno] AS 'td','',[clname] AS 'td','',
[cladd] AS 'td'
FROM  Client  
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='<html><body><H3>Client Information</H3>
<table border = 1> 
<tr>
<th> Client No </th> <th> Client Name </th> <th> Client Address </th>
</tr>'  

SET @body = @body + @xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'MIS_Automation', -- replace with your SQL Database Mail Profile 
    @body = @body,
    @body_format ='HTML',
    @recipients = 'recipient', -- replace with your email address
    @subject = 'E-mail in Tabular Format' ;

How can I resolve this issue?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
aneri
  • 11
  • 1
  • 2

1 Answers1

2

My best guess is you have used wrong authentication method to connect to google SMTP server (error message indicates you try to connect on port 25 but google uses secured SSL port 465 as far as I remember). Moreover there is no credentials passed neither thus it tries to use anonymous authentication which I think wouldn't work with gmail neither).

So for the troubleshooting you can start from some simple verification:

Please connect to your SQL Server via SSMS and navigate to Management -> Database Mail -> Configure Database Mail -> Manage Database Mail accounts and profiles -> View, change or delete an existing account and verify settings there. You should have SSL enabled (with port 465 specified) and basic authentication.

My other thought is that firewall blocks the connection so it would be second point worth to be verified.

If it still doesn't work following article might be useful: https://technet.microsoft.com/en-us/library/ms187540%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

GrzegorzO
  • 121
  • 3