Setting up automated notifications in SQL Server can be a lifesaver for DBAs and developers alike. Whether it’s alerting you to a failed job or sending out scheduled reports, Database Mail is the go-to feature.In this guide, I’ll walk you through the configuration process step-by-step. Let’s dive in.
Step 1: Access the Database Mail Configuration Wizard.
- Connect to SQL Server: Launch SQL Server Management Studio (SSMS) and connect to the appropriate server instance.
- Navigate to Management: In the Object Explorer, expand the Management node.
- Launch the Wizard: Locate Database Mail, right-click it, and select Configure Database Mail.
Step 02: Navigating the Setup Wizard.
Once you've launched the configuration tool, the Welcome screen will appear. This window provides a brief overview of what the wizard can do, such as managing accounts and profiles.
Simply click the Next button to move forward and start defining your configuration.
Step 03: Choosing the Configuration Type.
On the
Select Configuration Task screen, you'll see a few different options. Since we are starting from scratch, select the first option:
“Set up Database Mail by performing the following tasks.” Once you’ve made that selection, click
Next.
Step 04: Enabling the Feature and Creating Your Profile.
If this is a fresh SQL Server installation, the wizard might throw a quick pop-up letting you know that the Database Mail feature is currently disabled. Don't sweat it—this is just a standard security precaution. Simply click Yes to enable it and keep the momentum going.
Once enabled, you’ll be greeted by the New Profile window. Here’s what you need to do:
- Profile Name: Give your profile a clear, recognizable name (e.g., Admin_Alerts or System_Notifications).
- Description: While optional, it’s a total pro-move to add a quick note about what this profile is for.
- Add SMTP Account: Now for the important part—click the Add... button on the right side to start linking your actual email server settings to this profile.
Step 05: Configuring Your SMTP Settings.
This is the "meat" of the configuration where we link SQL Server to your actual email provider. In the New Database Mail Account window, you’ll need to fill in the following details:
1. Account Identity
- Account Name: Give this specific account a name.
- Description: Briefly describe what this account does (e.g., "Main Gmail SMTP Account").
2. Outgoing Mail Server (SMTP)
Here’s where you plug in the technical specs:
- Email Address: The email address you’ll be sending from.
- Display Name: What you want people to see in their inbox (e.g., SQL-Alert-Service).
- Reply Email: Optional, but handy if you want replies sent to a specific person.
- Server Name: The SMTP server address (e.g., smtp.gmail.com or mail.yourdomain.com).
- Port Number: This depends on your provider, but it’s usually 25, 465, or 587.
3. SMTP Authentication
Most modern mail servers require security, so under the SMTP Authentication section:
- Select the Basic Authentication radio button.
- Username: Enter your full email address.
- Password: Enter your email password (or an "App Password" if you’re using MFA).
Click OK (or Add) to save the configuration.
Step 06
Then window will appear. it shows your created profile and click Next.
Step 07
Then another window will , the Configure System Parameters description is shown.
Step 08
Then Click Next. It will show confirmation about your profile , so click Finish.
Step 09
Now SQL server was configured email service and click close .
Step 10
Run following SQL Query
sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO
How to test SQL Sever mail
Then again right click on Database Mail and select Send Test Email. Then select Database mail profile and enter receiver mail address , mail subject.
How to view Database Mail Log
Right click on the Database mail and select View Database Mail Log
