SSIS发送邮件任务执行失败求助：SMTP认证错误排查

Got it, let's tackle this authentication error you're hitting with your SSIS Send Mail Task and Office 365 SMTP. I’ve dealt with this exact headache before, so here are the most common fixes to get your emails sending again:

Key Issues & Solutions #

1. Ditch Windows Authentication for Basic Authentication #

Office 365’s SMTP service does not support Windows Authentication for most modern tenant setups—this is almost certainly the root cause here. Here’s how to fix it:

• Open your SMTP Connection Manager in SSIS
• Uncheck the "Windows Authentication" box
• Select "Basic Authentication"
• Enter your full Office 365 email address (e.g., john.doe@yourdomain.com) as the username
• For the password:
  • If your account uses Multi-Factor Authentication (MFA), you can’t use your regular login password—you need to generate an App Password from your Microsoft Account settings.
  • If MFA is disabled, use your regular account password.

2. Verify SMTP Port & SSL Settings #

Double-check these critical settings to match Office 365’s requirements:

• SMTP Server: server-smtp.office365.com (you already have this right)
• Port: 587 (this is the standard for TLS-encrypted SMTP with O365; avoid 465 unless specifically required)
• Ensure "Enable SSL" is checked (you already did this, but it’s worth confirming)

3. Enable SMTP Auth for Your O365 Account #

Many Office 365 tenants disable SMTP client authentication by default. You’ll need to turn this on for your email account:

• Via Exchange Admin Center:
  1. Go to the Exchange Admin Center > Recipients > Mailboxes
  2. Select your mailbox > Click "Mailbox features"
  3. Under "Email apps", find "SMTP AUTH" and toggle it to "Enabled"
• Via PowerShell (if you have admin rights):
  Run this command, replacing user@yourdomain.com with your email:

  Set-CASMailbox -Identity "user@yourdomain.com" -SmtpClientAuthenticationDisabled $false
  

4. Test the SMTP Connection Outside SSIS #

To rule out SSIS-specific issues, test the SMTP setup with a quick PowerShell script. If this works, the problem is in your SSIS configuration; if not, it’s an O365 setting issue:

$smtpServer = "server-smtp.office365.com"
$smtpPort = 587
$smtpUser = "your-email@yourdomain.com"
$smtpPass = "your-app-password-or-regular-password"
$to = "recipient@yourdomain.com"
$from = "your-email@yourdomain.com"
$subject = "SSIS SMTP Test"
$body = "This is a test email to verify SMTP connectivity."

$smtpClient = New-Object System.Net.Mail.SmtpClient($smtpServer, $smtpPort)
$smtpClient.EnableSsl = $true
$smtpClient.Credentials = New-Object System.Net.NetworkCredential($smtpUser, $smtpPass)

try {
    $smtpClient.Send($from, $to, $subject, $body)
    Write-Host "Email sent successfully!"
} catch {
    Write-Host "Error sending email: $_"
}

5. Check SSIS Execution Context #

If you’re running the package via SQL Server Agent:

• Ensure the Agent service account or proxy account has permissions to access the SMTP server (though authentication is the bigger issue here)
• If using Basic Authentication in the SMTP manager, make sure the password is saved correctly (check the "Save password" box when configuring the connection)

Final Notes #

The 5.7.57 error almost always boils down to one of these: using Windows Auth with O365, not using an app password for MFA accounts, or SMTP Auth being disabled on the mailbox. Start with switching to Basic Authentication—this is the most common fix.

内容的提问来源于stack exchange，提问作者Sriram