Setting up Database Mail for SQL Server with SSMS

By:   |   Updated: 2024-05-22   |   Comments (36)   |   Related: 1 | 2 | 3 | > Database Mail


Problem

SQL Server allows you to do many things including automating tasks and sending notifications. You can configure SQL Server to use Database Mail to send emails from SQL Server and include content in the email body as well as send attachments. In this article, we look at how to setup Database Mail using SQL Server Management Studio.

Solution

There are two ways that you can setup Database Mail, either by using the stored procedures that are included with SQL Server or by using SQL Server Management Studio (SSMS). For this exercise we will walk through how to setup Database Mail by using the SSMS.

To setup Database Mail, connect to your server and expand the Management node of the tree and then right click on "Database Mail".

configure database mail from ssms

Then select "Configure Database Mail' and you will get the following welcome screen and then click "Next".

database mail wizard

The following screen will appear and select "Set up Database Mail by performing..." and click "Next".

database mail select configuration task

If Database Mail has not been enabled, you will get this following screen. Just click "Yes" to enable it. If it has already been enabled this screen will not appear.

enable database mail features

Enter in a name for the Profile and also a description and click "Add..."

database mail new profile

The following screen will appear. Fill out the details for your mail account that will be used to send out email from SQL Server. When you are done click "OK". You can send email using SSL or not, so configure accordingly. There are also several options for SMTP authentication. In this example, we will just use "Basic authentication".

"database mail new database mail account

After you click "OK" you will be brought back to this screen and the SMTP details will now show for the account you just setup. Click "Next" to continue.

database mail new profile

On the next screen you will see the name of the profile that you just setup. Click on the checkbox to allow this to be a Public profile and also select "Yes" for the default profile and then click "Next".

database mail manage profile security

The following screen has some additional parameters that can be set to control how mail is sent. You can make changes or leave the defaults. When you are done click "Next".

database mail configure system parameters

A summary screen will appear that shows you all of the options that were selected. If everything is correct click "Finish" or click "Back" to go back and make changes.

database mail complete the wizard

When you click "'Finish" the next screen will appear that shows you the status of installing Database Mail. When this has finished just click "Close" to close this screen.

database mail configuration success

To test Database Mail, right click on Database Mail and select "Send Test E-Mail".

database mail send test email

Fill in a "To:" email address and change the body of the email if you want and then click "Send Test E-Mail".

database mail send test email

After you have sent the email you will get this message box to confirm if the email was received or not. If it was, you can click "OK" to close the screen or click "Troubleshoot" which will launch the help information to see what the issue may be and how it can be resolved.

database mail check test email send

That's all there is to it. As I mentioned before this can also be setup by using stored procedures. To look at this approach take a look at this article Database Mail in SQL Server.

Next Steps
  • Setting up Database Mail is not that complicated and can be very useful.
  • After you setup Database Mail don't forget to setup your operators, alerts and SQL Agent alert settings


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2024-05-22

Comments For This Article




Tuesday, May 28, 2024 - 12:50:11 PM - Greg Robidoux Back To Top (92273)
Hi Steven,

here are the scripts. We will create an article for this on the site.

EXEC msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Database Mail'
,@description = 'mail from SQL Server';

EXEC msdb.dbo.sysmail_add_account_sp
@account_name = 'test'
,@description = 'test email' -- optional
,@email_address = '[email protected]'
,@display_name = 'MSSQLTips' -- optional
,@replyto_address = '[email protected]' -- optional
,@mailserver_name = 'mail.mssqltips.com'
,@mailserver_type = 'SMTP' -- optional SMTP is default
,@port = 25
-- basic authentication
,@username = '[email protected]'
,@password = 'test123';

EXEC sysmail_help_account_sp; -- use output from this for @account_id below
EXEC sysmail_help_profile_sp; -- use output from this for @profile_id below

EXEC msdb.dbo.sysmail_add_profileaccount_sp
@profile_id = 1
,@account_id = 1
,@sequence_number = 1;

EXEC sp_configure 'show advanced options', '1';
RECONFIGURE
GO
EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE
GO

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Database Mail'
,@recipients = '[email protected]'
,@subject = 'Test'
,@body = 'Test email body';

Tuesday, May 28, 2024 - 10:37:30 AM - Steven Pearl Back To Top (92271)
What is the option to script DB Mail in SQL Express since that functionality is not part of Express?

Friday, April 3, 2020 - 1:17:58 PM - Greg Robidoux Back To Top (85268)

Hi Sanket,

Are you able to send any emails or do all emails you send get this error message?

Also, did you do any searches on the error message you got?

-Greg

Friday, April 3, 2020 - 11:28:42 AM - Sanket Back To Top (85266)

Hi,

I have configured Database mail but getting below error.

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2020-04-03T11:22:49). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Invalid credentials for relay [104.130.34.118]. The IP address you've).  )

Please advise.

Thanks,

Sanket

Tuesday, March 17, 2020 - 8:52:41 PM - Rick Back To Top (85133)

Very helpful.  Should have been all I needed.  However, at least with SQLServer 2016, I found I needed to also enable .NET 3.5 for it to work.  Also, I found that requiring SSL and using port 587 was causing it to fail; I needed to revert to port 25 and no SSL (as in your example).

Monday, September 16, 2019 - 4:42:52 PM - Lakshay Arora Back To Top (82474)

Hi Greg,

Thanks for your reply. however, I already tried all these steps. I am still facing the issue.

Monday, September 16, 2019 - 9:06:07 AM - Greg Robidoux Back To Top (82467)

Hi Lakshay

Take a look at this to see if this helps: https://www.mssqltips.com/sqlservertip/2578/setup-sql-server-database-mail-to-use-a-gmail-hotmail-yahoo-or-aol-account/

-Greg

Sunday, September 15, 2019 - 5:00:44 PM - Lakshay Arora Back To Top (82462)

Hi Greg,

Thanks for the great artile. I really appreciate it. I followed your article and set up the database mail. However, when I send mail to my gmail accounr, it shows error as " mail not sent. mail failure". I tried many options like enabling IMAP on gmail, checked the roles and logins. Still unsuccessful.

Error message:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2018-09-10T11:56:38). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it IPAddress:25).

Could you please help me out in this scenario. You can send me the solution on mail.

Friday, March 22, 2019 - 12:36:40 PM - Greg Robidoux Back To Top (79374)

Hi Matteo,

take a look at this post to see if this helps: https://www.sqlservercentral.com/Forums/Topic686387-359-1.aspx

Thanks
Greg

Friday, March 22, 2019 - 9:02:25 AM - Matteo Santucci Back To Top (79370)

Hello Greg

When using "msdb.dbo.sp_send_dbmail", to send an email, what is the minimum permissions that need to be added to the db account running the sp to allow it to send email? I have tried a variety of settings but sysadmin is the only thing I have tried that works, which is of course not a desirable state...

Tuesday, September 11, 2018 - 7:24:35 AM - Greg Robidoux Back To Top (77501)

Hi Singh,

It looks like a permission issue.  Can you try with another mail account and see if that works?

-Greg

Tuesday, September 11, 2018 - 6:37:54 AM - Singh Back To Top (77499)

Hi Greg,

I followed all the steps but I'm not able to send any email from database... How do I know if the SMTP has picked my email or not? Because if I drop a text file in SMTP pick up folder it works fine(http://www.vsysad.com/2012/04/setup-and-configure-smtp-server-on-windows-server-2008-r2/) but on database it fails.

Or Is there any permission issue?

Error message: The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2018-09-11T06:28:55). Exception Message: Cannot send mails to mail server. (Failure sending mail.).
)

OR

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2018-09-10T11:56:38). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it IPAddress:25).
)

Thanks

Tuesday, June 5, 2018 - 11:05:19 AM - Perry Back To Top (76133)

Hi Greg,

 

Yes the simple test works. But the problem actually is when I am using a variable for 'like' comparison. 

 

declare @test varchar(20) = '%Perry%';
declare @query varchar(max);

 

set @query = 'SELECT id, name from master.sys.objects where name like @test'

this errors out

set @query = 'SELECT id, name from master.sys.objects where name like ' + @test

this errors out too

so what I did was add another single quote using ascii code.

set @query = 'SELECT id, name from master.sys.objects where name like ' + char(39) + @test + char(39)

and this now works.

 

Thanks,

Perry

Tuesday, June 5, 2018 - 8:00:57 AM - Greg Robidoux Back To Top (76131)

Perry,

Did you try to just do a simple test like the example I provided?  If so, did that work for you?

-Greg

Monday, June 4, 2018 - 7:02:15 PM - Perry Back To Top (76126)

Hi Greg,

 

Tried that but still get an error. 

 

Thanks for the help.

Monday, June 4, 2018 - 3:18:38 PM - Greg Robidoux Back To Top (76125)

Hi Perry,

did you save the query into a variable and then use that to send the email?  Something like this.

 

declare @test varchar(max) = 'SELECT top 10 * from '
declare @test2 varchar(max) = 'master.sys.objects'
declare @test3 varchar(max)
set @test3 = @test + @test2

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'test',  
    @recipients = '[email protected]',  
    @query = @test3,
    @subject = 'test'

Monday, June 4, 2018 - 11:36:05 AM - Perry Back To Top (76120)

Hi Greg,

Can you help me with this SQL? This SQL works in the visual studio and also in server management studio but when I am setting it via the database mail I am getting error.

 

select @qry='

SELECT DISTINCT ABP.ProjectID, ABP.ProjectNumber, ABP.ProjectName, 

ABP.ProjectJobSiteAddress1, ABP.ProjectJobSiteAddress2, 

ABP.ProjectJobSiteCity, ABP.ProjectJobSiteState, 

ABP.ProjectJobSiteZipCode, ABP.ProjectLocation, ABP.ProjectJobCountyID, 

convert(varchar, ABP.FirstAdvBidDate, 101) as FirstAdvBidDate, 

convert(varchar, ABP.ContractAwardDate, 101) as ContractAwardDate, 

convert(varchar, ABP.ProjectStartDate, 101) as ProjectStartDate, 

convert(varchar, ABP.ProjectEndDate, 101) as ProjectEndDate, 

ABP.ContractorLicenseNumber, ABP.ContractAwardAmount, ABP.AwardingBodyID, 

ABP.ContractNumber , ABA.AwardingBodyName 

FROM AwardingBody_Projects ABP INNER JOIN AwardingBody_Agency ABA 

ON ABA.AwardingBodyName like ' + @AwardingBodyName 

+ ' AND ABA.AwardingBodyID = ABP.AwardingBodyID 

INNER JOIN AwardingBody_ProjectContractors ABPC 

ON (ABPC.Status = ' + @status1 + ' or ABPC.Status = ' + @status2 + ' ) 

AND ABPC.AwardingBodyID = ABP.AwardingBodyID 

AND ABPC.ContractNumber = ABP.ContractNumber 

AND ABPC.ProjectId = ABP.ProjectId 

AND ABPC.ProjectNumber = ABP.ProjectNumber  

AND ABPC.ContractorTypeID = ' + @ContractorTypeId 

+ ' AND ABPC.DeleteStatus = ' + @deleteStatus 

+ ' AND (ABPC.PWCRNumber IN (

SELECT CONVERT(varchar,PWCR.REGISTRATION_NUMBER) 

FROM PWCR_Contractor_vw PWCR 

WHERE  PWCR.LEGAL_NAME like ' + @ContractorName  

+ ' )  

OR ABPC.ContractorLicenseNumber IN (

SELECT CR.CON_ID 

FROM Contractor_ref CR 

WHERE  CR.CON_COMPANY_NAME like ' + @ContractorName 

+ ' ) 

WHERE ABP.ProjectName like ' + @ProjectName  

+ ' ORDER BY ABP.ProjectName'

 

This is the error I am getting.

Msg 22050, Level 16, State 1, Line 0

Error formatting query, probably invalid parameters

Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 504

Query execution failed: Msg 156, Level 15, State 1, Server OAK01SQL, Line 13

Incorrect syntax near the keyword 'AND'.

Msg 102, Level 15, State 1, Server OAK01SQL, Line 23

Incorrect syntax near ')'.

Msg 102, Level 15, State 1, Server OAK01SQL, Line 27

Incorrect syntax near ')'.

 

Thank you,

Perry

Friday, June 1, 2018 - 7:46:18 AM - Greg Robidoux Back To Top (76070)

Perry,

take a look at this tip: https://www.mssqltips.com/sqlservertip/2347/send-email-in-a-tabular-format-using-sql-server-database-mail/

-Greg

Thursday, May 31, 2018 - 5:42:48 PM - Perry Back To Top (76068)

NVM, I changed my separator to TAB and it is now displayed properly in the CSV file. Thank you.

Thursday, May 31, 2018 - 5:33:40 PM - Perry Back To Top (76067)

Hi Greg

Finally we were able to resolve the missing stored procedure. It was created on my different account.

 

I was able to send a sample query of a table using the separator ','. The CSV attachment is like a text file pasted into the CSV and unreadable. Is there a way to format the query result into columns just like excel?

Thank you,

Perry

Thursday, May 31, 2018 - 8:11:28 AM - Greg Robidoux Back To Top (76058)

Hi Perry,

not sure what that is not working.  Here is a link to an article that shows to do what you say: https://stackoverflow.com/questions/11666277/how-to-grant-sendmail-permission-to-sql-server-user

The one thing that is weird is that it says the stored procedure cannot be found.  I would think you would get a permissions error not a problem with the procedure not being found.  Can you check with your admin to make sure the procedure exists and it wasn't renamed.

-Greg

Wednesday, May 30, 2018 - 12:36:56 PM - Perry Back To Top (76053)

Hi Greg,

Great post. But how do I add database mail on a specific database/view? The admin setup database mail and added me to "DatabaseMailUserRole".

When I tried to use the "msdb.dbo.sp_send_dbmail" in a query, it says it cannot find the stored procedure.

Thank you.

Thursday, January 11, 2018 - 4:25:26 PM - Greg Robidoux Back To Top (74944)

Hi Clemens,

to be honest I have no idea why one would work and the other does not work. My guess is that it has something to do with the way Microsoft has setup those mail services.

-Greg

Wednesday, January 10, 2018 - 12:29:49 PM - clemens Back To Top (74938)

 Thanks finaly it functions.

But:

It works with a free live.com email adress but i didn't succseed with our company office365 account

Do you have an idee what whe do wrong?

Thanks

C.

 

Thursday, January 4, 2018 - 8:58:40 PM - Natanael Back To Top (74787)

Funciono correctamente!!!

It worked correctly!!!

Thursday, December 14, 2017 - 9:54:43 AM - Susan Qi Back To Top (74006)

Excellent post, it works for sql server 2017. Thank you.

Thursday, August 31, 2017 - 12:25:21 PM - Greg Robidoux Back To Top (65667)

What version and edition of SQL Server are you using?

Thursday, August 31, 2017 - 10:38:07 AM - Haim Rubin Back To Top (65660)

Hi

Is the mailer is not installed on the machine. How do I add it

Thanks

 

 

Wednesday, June 24, 2015 - 12:33:52 PM - margarett hance Back To Top (38022)

Great TIp - easy to follow  Database EMAIL

Friday, January 30, 2015 - 11:32:59 AM - Andy Bartholomew Back To Top (36118)

This was great, thanks!

Friday, April 12, 2013 - 7:02:24 AM - Dinesh Vishe Back To Top (23325)

hi,

 

select 

 

*from msdb.dbo.sysmail_profile => Profile creation details

 

select 

 

*from msdb.dbo.

sysmail_faileditems=> failed mail

 

 

select

 

 

*from msdb.dbo.sysmail_profileaccount => Profile Modify user

select

 

 

*from msdb.dbo.

 

sysmail_sentitems => send mai details

 

 

select

 

*from msdb.dbo.

sysmail_event_log => All event details.

 

Tuesday, April 2, 2013 - 5:57:31 AM - suresh y Back To Top (23109)

HI GREG,

HOW R U 

I AM SURESH.  WE ARE GIVING BEAUTIFUL TIPS FROM U R MSSQLTIPS. I AM FOLLOWING DAILY  YOUR BLOG PLEASE SOME MORE

SUGGESTIONS.  I AM DOUBTFUL "SQL MEMORY ARCHITECTURE'' I AM ASKED SO MANY AUTHORS BUT I AM NOT GETTING FULL FLDGE

KNOWLDGE ABT THAT TOPIC. LOT OF CONFUSION 32 BIT ARCHITECTURE AND 64 BIT ARCHITECTUR, AWE TOPIC ,BUFFERPOOL AND

DATA CACHE,CHECHPOINT,DIRTY PAGES

 

Friday, November 9, 2012 - 1:03:12 PM - Jacz Down Back To Top (20276)

Where accept any certificate as they use a self-signed?

Sorry for my English..

 

 

Friday, October 26, 2012 - 12:37:39 PM - anwar ahmed Back To Top (20106)

Great help

Tuesday, August 21, 2012 - 1:25:46 PM - sai Back To Top (19148)

*Very good article

Monday, July 2, 2012 - 3:43:28 AM - john the rule Back To Top (18291)

Very good guidelines with  wizard














get free sql tips
agree to terms