SQL Server has a feature called Database Mail that works very well for this purpose, but it can be a bit difficult to configure correctly, especially if you try to do it manually. I will show several ways to make use of Database Mail in future posts, but in this post I will show how to get it configured correctly. The necessary steps are:
- Enable Database Mail and SQL Agent: This sets the features so they can be used, as they are generally disabled by default.
- Setup Database Mail
- Create a Database Mail profile: A profile is a collection of mail accounts, and it must exist before we can add an account to it.
- Create a Database Mail account: This is the account we are going to use for sending email; it contains all the information about the SMTP server and the address to be used as the sender.
- Add the account to the profile: The account cannot be used unless it is a member of a profile.
- Grant the DBMailUsers role access to the profile: This provides the security right that is needed and also makes the profile the default.
- Send a test message: At this point, Database Mail should be ready to send a message and this will confirm that it works properly. Depending on your mail client, you may need to refresh your InBox view or give it a command to fetch new mail to avoid waiting for the standard interval for checking if new mail has arrived.
- Configure SQL Agent to use the profile and operator: Without this step, SQL Agent cannot use Database Mail. Unfortunately, it requires that the SQL Agent service be restarted. I didn't add the restart to the script as it is likely that security would prevent it from working in many environments. You can use SSMS (right-click on SQL Agent in Object Explorer and select Restart), SQL Server Configuration Manager, or the Windows Services Control Panel applet to restart the service manually.
/* Configure Database Mail.sql Purpose: Configures Database Mail and sends a test message Author: Mark Freeman Last Edited: 2011-11-20 Instructions: Update the values in the block of SET statements as appropriate, then execute. References: Adapted from: http://www.kannade.com/tech/database-mail-and-sql-agent-mail-setup-using-script-t30.html and http://www.mssqltips.com/sqlservertip/1736/sql-server-database-mail-and-sql-agent-mail-setup-by-using-a-script/ Compatibility: SQL Server 2005 and newer License: This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License. http://creativecommons.org/licenses/by-nc-sa/3.0/ */ SET NOCOUNT ON USE [master] DECLARE @ProfileName SYSNAME, @SmtpServer SYSNAME, @OperatorName SYSNAME, @ToEmail SYSNAME, @RecipientDomainName SYSNAME, @SenderDomainName SYSNAME, @ReplyToEmail SYSNAME, @SmtpUserName NVARCHAR(128), @SmtpPassword NVARCHAR(128), @SmtpPort INT, @SmtpUseDefaultCredentials BIT, @SmtpEnableSsl BIT; -- Please update the values in the section below as needed: SET @ProfileName = 'MyProfile'; /* This could be your company name, domain name, or something generic such as 'Database Mail Profile' */ SET @SmtpServer = 'smtp.mydomain.com'; /* Usually something like 'smtp.mydomain.com' */ SET @SmtpPort = 25; /* This is the standard port, but yours may differ */ SET @RecipientDomainName = 'mydomain.com'; /* This may differ from the domain of the sender if, for example, you arehaving servers at other companies send you emails */ SET @SenderDomainName = @RecipientDomainName; /* This is usually the domain of the SMTP server */ SET @SmtpUserName = NULL; /* Only needed if your SMTP server requires authentication */ SET @SmtpPassword = NULL; /* Only needed if your SMTP server requires authentication */ SET @SmtpUseDefaultCredentials = 0 /* Change to 1 to use the credentials of the SQL Server Database Engine and ignore the user name and password settings */ SET @SmtpEnableSsl = 0; /* Change to 1 if your SMTP server uses SSL encryption */ SET @ToEmail = 'dba' + @RecipientDomainName; SET @ReplyToEmail = 'sqlserver' + @SenderDomainName; -- Please update the values in the section above as needed -- Enable Database Mail and SQL Agent EXEC sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'Database Mail XPs', 1; EXEC sp_configure 'Agent XPs',1; EXEC sp_configure 'show advanced options', 0; RECONFIGURE WITH OVERRIDE; -- Setup Database Mail IF EXISTS(SELECT * FROM msdb.dbo.sysmail_profile) PRINT 'Database Mail already configured'; ELSE BEGIN DECLARE @FromEmail SYSNAME, @FromName SYSNAME; SELECT @FromEmail = REPLACE(@@SERVERNAME,'\','_') + @SenderDomainName, @FromName = 'SQL Server ' + @@SERVICENAME; -- Create a Database Mail profile EXEC msdb.dbo.sysmail_add_profile_sp @profile_name = @ProfileName ,@description = 'Mail profile setup for email from this SQL Server'; -- Create a Database Mail account, assuming Windows Authentication EXEC msdb.dbo.sysmail_add_account_sp @account_name = @ToEmail ,@description = 'Mail account for use by database processes' ,@email_address = @FromEmail ,@replyto_address = @ReplyToEmail ,@display_name = @FromName ,@mailserver_name = @SmtpServer ,@mailserver_type = 'SMTP' ,@port = @SmtpPort ,@username = @SmtpUserName ,@password = @SmtpPassword ,@use_default_credentials = @SmtpUseDefaultCredentials ,@enable_ssl = @SmtpEnableSsl; -- Add the account to the profile EXEC msdb.dbo.sysmail_add_profileaccount_sp @profile_name = @ProfileName ,@Account_name = @ToEmail ,@sequence_number = 1; -- Grant access to the profile to the DBMailUsers role EXEC msdb.dbo.sysmail_add_principalprofile_sp @profile_name = @ProfileName ,@is_default = 1; END -- Send a test message DECLARE @MessageText VARCHAR(100); SET @MessageText = 'Test from SQL Server ' + @@SERVICENAME; EXEC msdb..sp_send_dbmail @profile_name = @ProfileName ,@recipients = @ToEmail ,@subject = @MessageText ,@body = @MessageText; -- Create operator IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysoperators WHERE name = @ToEmail) EXEC msdb.dbo.sp_add_operator @name = @ToEmail ,@enabled = 1 ,@email_address = @ToEmail; EXEC master.dbo.sp_MSsetalertinfo @failsafeoperator = @ToEmail; -- Configure SQL Agent to use the profile and operator USE [msdb]; EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1; EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE' ,N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent' ,N'UseDatabaseMail' ,N'REG_DWORD' ,1; EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE' ,N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent' ,N'DatabaseMailProfile' ,N'REG_SZ' ,@ProfileName; PRINT '**** You must manually restart the Service: SQL Server Agent (' + @@SERVICENAME + ')';