sodcalculator

10 Tech How-To

SQL Server Database Mail Setup with Gmail

Guide 10-44718 · Updated · Step by step

This guide walks you through configuring SQL Server Database Mail to send emails through a Gmail account. By the end, you’ll be able to send test emails directly from SQL Server, which is useful for job alerts, error notifications, or automated reports.

What You’ll Need

  • SQL Server with Database Mail feature installed (usually included in Standard and Enterprise editions).
  • SQL Server Management Studio (SSMS) or another query tool with sysadmin permissions.
  • A Gmail account. If you use 2-Step Verification (recommended), you’ll need an App Password instead of your regular password. See Google’s help for creating one.
  • Access to the server’s firewall/network to allow outbound SMTP to smtp.gmail.com:587 (or 465).

Step-by-Step

  1. Enable Database Mail – In SSMS, right-click on “Management” in Object Explorer, select “Database Mail”, then “Configure Database Mail”. If prompted, choose “Yes” to enable the feature. (Alternatively, run sp_configure 'Database Mail XPs', 1; RECONFIGURE;.)
  2. Launch the Configuration Wizard – In the wizard, select “Set up Database Mail by performing the following tasks” and click Next.
  3. Create a Profile – Click “Add” to create a new profile. Give it a name like “GmailProfile” and optionally add a description. Click Next.
  4. Add an SMTP Account – In the “Add Account” dialog, fill in:
    • Account name: e.g., “GmailAccount”
    • Outgoing mail server (SMTP): smtp.gmail.com
    • Port number: 587 (or 465 for SSL)
    • Check “This server requires a secure connection (SSL)”.
    • Authentication: select “Basic authentication”.
    • User name: your full Gmail address (e.g., you@gmail.com)
    • Password: your Gmail password or an App Password if 2-Step Verification is on.

    Click OK, then Next.

  5. Set Profile Security – Choose “Public” profile (allows all users to use it) or “Private” (restrict to specific users). For testing, Public is fine. Set the default profile to “Yes” for the appropriate user(s). Click Next.
  6. Configure System Parameters – Keep the defaults (file size limits, logging level) unless you have specific needs. Click Next, then Finish.
  7. Send a Test Email – In the wizard’s final page, check “Send a test e-mail” and enter your recipient address. Click Finish. If you skip this, you can test later via right-click on Database Mail > “Send Test E-Mail”.
  8. Verify with a Query – After the wizard, run this to check the status:
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'GmailProfile',
        @recipients = 'you@example.com',
        @subject = 'Test from SQL',
        @body = 'Hello, this is a test email from SQL Server.';
    

    Check the mail queue with SELECT * FROM msdb.dbo.sysmail_allitems; to see if it sent successfully.

Common Mistakes

  • Using the wrong password – If you have 2-Step Verification enabled, you must use an App Password, not your normal Gmail password.
  • SSL not enabled – Gmail requires a secure connection. If you don’t check the SSL box, the connection fails.
  • Wrong port – Port 587 is for STARTTLS; port 465 is for SSL. Ensure your firewall allows outbound on the chosen port.
  • Profile not set as default – If you have multiple profiles, you must specify the profile name in sp_send_dbmail or set one as default.
  • Database Mail not enabled – The feature must be turned on; otherwise, the wizard may not appear or sp_send_dbmail fails.

Tips

  • Use an App Password for better security and to avoid issues with Gmail’s security policies.
  • Keep the logging level at “Extended” during initial setup to help troubleshoot, then reduce it to “Normal” for production.
  • Create a dedicated Gmail account for SQL Server alerts to avoid mixing with personal email.
  • Test with a simple email first, then add HTML formatting or attachments once it works.
  • Set up a SQL Server Agent job to send periodic test emails to ensure the configuration remains healthy.

Troubleshooting

  • Email stuck in queue – Check sysmail_allitems and sysmail_mailitems for error messages. Common errors include “The SMTP server requires a secure connection” – fix by enabling SSL.
  • Authentication failed – Verify the Gmail address and password. If using an App Password, ensure it’s typed correctly (spaces are allowed).
  • Connection timed out – Test connectivity from the SQL Server machine: telnet smtp.gmail.com 587. If it fails, check firewall rules.
  • Gmail blocks the sign-in – Google may block less secure apps. Use an App Password and ensure “Allow less secure apps” is off (it’s off by default).
  • Database Mail not installed – If the wizard doesn’t appear, you may need to install the feature via SQL Server Setup or run the sp_configure command.

Frequently Asked Questions

Can I use a regular Gmail password?

Yes, if 2-Step Verification is off, but it’s less secure. Google may also block the sign-in if it detects unusual activity.

What if I don’t want to use Gmail?

You can use any SMTP server (e.g., Outlook, SendGrid). The steps are the same; just change the server name, port, and credentials.

How do I send HTML emails?

Set @body_format = 'HTML' in sp_send_dbmail and include HTML tags in the body.

Can I send attachments?

Yes, use the @file_attachments parameter, e.g., @file_attachments = 'C:\temp\report.pdf'. Ensure SQL Server has access to the file path.

Why does my email go to spam?

Gmail may flag automated emails. Use a clear subject, avoid links, and consider using a dedicated account. You can also add the sender to your contacts.

Is there a limit on emails per day?

Gmail has sending limits (around 500 per day for free accounts). For higher volumes, consider a professional SMTP service.

Related

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →