Utilizing smtp4dev with SQL Server Database Mail for Testing

SQL Server has database mail, a solution which would allow us to send e-mail from our database server. It allows us to send either e-mail in html or text format to users. It’s great because we can use it to send query results to users (or ourselves). We can also configure SQL Server Agent to use database mail to send alerts to operator.

Database mail utilizes SMTP server to send the e-mails. In some cases, we might not have a readily available SMTP server in our environment (like in my home lab environment) or we might be block from using the SMTP server from our development environment for security reason. In those situations, I’ve been using smtp4dev. It is a neat little utility that would allow you to have a dummy SMTP server on your local computer. It is lightweight and also no installation is needed. When we run the utility, it would sit on the system tray and listen to port 25 (default SMTP port). It will receive the e-mail that get send to its way, without sending the e-mail to its final destination (the e-mail recipients). It is pretty handy when we need to run some tests.

Setting up database mail to work with smtp4dev is pretty simple.

• Download smtp4dev from http://smtp4dev.codeplex.com.
• Extract the file and save it on your system (assuming that it is your development system and it has the SQL Server).
• Run smtp4dev. Notes: If you have Windows Firewall turned on, you might get a “Windows Security Alert” that ask you if you want to allow smtp4dev to communicate with either the private network, public network or both. Since normally I run smtp4dev on my development system with also has SQL Server that I want to have database mail turn on, I would just hit the cancel button.
• If database mail in SQL Server has not been enable, we can enable it by using the following SQL script:

exec sp_configure 'show advanced', 1;
go
reconfigure;
go
exec sp_configure 'Database Mail XPs', 1;
go
reconfigure;
go

• We then need to set up database mail profile, account and then associate the profile with the account in SQL Server. To do that, we can use the following script (based on the “Database Mail Simple Configuration Template”):

DECLARE @profile_name sysname,
        @account_name sysname,
        @SMTP_servername sysname,
        @email_address NVARCHAR(128),
        @display_name NVARCHAR(128),
        @error_display NVARCHAR(500);

-- Profile name. Replace with the name for your profile
        SET @profile_name = 'SMTP4Dev';

-- Account information. Replace with the information for your account.

        SET @account_name = 'SMTP4Dev Account';
        SET @SMTP_servername = 'localhost';            --- Since the smtp4dev is on local system. 
        SET @email_address = 'john.doe@email.com';
        SET @display_name = 'John Doe';

-- Verify the specified account and profile do not already exist.
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @profile_name)
BEGIN
    SET @error_display = 'The specified Database Mail profile (' + @profile_name + ') already exists.';
    RAISERROR(@error_display, 16, 1);
    GOTO done;
END;

IF EXISTS (SELECT * FROM msdb.dbo.sysmail_account WHERE name = @account_name )
BEGIN
    SET @error_display = 'The specified Database Mail account (' + @account_name + ') already exists.';
    RAISERROR(@error_display, 16, 1) ;
    GOTO done;
END;

-- Start a transaction before adding the account and the profile
BEGIN TRANSACTION ;

DECLARE @rv INT;

-- Add the account
EXECUTE @rv=msdb.dbo.sysmail_add_account_sp
    @account_name = @account_name,
    @email_address = @email_address,
    @display_name = @display_name,
    @mailserver_name = @SMTP_servername;

IF @rv<>0
BEGIN
    SET @error_display = 'Failed to create the specified Database Mail account (' + @account_name + ').';
    RAISERROR(@error_display, 16, 1) ;
    GOTO done;
END

-- Add the profile
EXECUTE @rv=msdb.dbo.sysmail_add_profile_sp
    @profile_name = @profile_name ;

IF @rv<>0
BEGIN
    SET @error_display = 'Failed to create the specified Database Mail profile (' + @profile_name + ').';
    RAISERROR(@error_display, 16, 1);
    ROLLBACK TRANSACTION;
    GOTO done;
END;

-- Associate the account with the profile.
EXECUTE @rv=msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = @profile_name,
    @account_name = @account_name,
    @sequence_number = 1 ;

IF @rv<>0
BEGIN
    SET @error_display = 'Failed to associate the speficied profile with the specified account (' + @account_name + ').';
    RAISERROR(@error_display, 16, 1) ;
    ROLLBACK TRANSACTION;
    GOTO done;
END;

COMMIT TRANSACTION;

done:

GO

• Now the moment of truth, we can test to see if it works by trying to send an e-mail using sp_send_dbmail

exec msdb..sp_send_dbmail @profile_name = 'SMTP4Dev',
    @recipients = 'jane.doe@email.com',
    @subject = 'Test E-mail',
    @body = 'This is a test email.';

If everything works as plan hopefully on your tray notification, you would see the following message:

And if you open smtp4dev, you should see the following:

To view the e-mail, you can click on the “View” or “Inspect” button.

Some caveats that I’ve noticed:

• I have Microsoft Office 2013 installed on my system, which includes Outlook 2013. For some reason, when I click the “View” button, I would get an error message “The attempted operation failed. An object could not be found.” and it just would not open the e-mail (if I try to open it using Outlook 2013). It is ok if I use Windows Live Mail 2012. I would normally use the “Inspect” button instead.
• If you exit out smtp4dev, the e-mails that you have on the Messages tab will get deleted.

Cleaning Up

If you want to remove the database mail from your SQL Server, first you want to remove the profile association with account, the profile and the account. You can do that by using the following script:

DECLARE @profile_name sysname,
        @account_name sysname,
        @error_display nvarchar(500);

-- Profile name. Replace with the name for your profile
        SET @profile_name = 'SMTP4Dev';

-- Account information. Replace with the information for your account.
        SET @account_name = 'SMTP4Dev Account';

IF NOT EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @profile_name)
BEGIN
    SET @error_display = 'The specified Database Mail profile (' + @profile_name + ') does not exists.';
    RAISERROR(@error_display, 16, 1);
    GOTO done;
END

IF NOT EXISTS (SELECT * FROM msdb.dbo.sysmail_account WHERE name = @account_name)
BEGIN
    SET @error_display = 'The specified Database Mail account (' + @account_name + ') does not exists.';
    RAISERROR(@error_display, 16, 1);
    GOTO done;
END

-- Start a transaction before deleting the profile account, profile and account.
BEGIN TRANSACTION ;

DECLARE @rv INT;

EXEC @rv=msdb.dbo.sysmail_delete_profileaccount_sp 
    @profile_name = @profile_name, 
    @account_name = @account_name;

IF @rv<>0
BEGIN
    SET @error_display = 'Failed to delete the profile with the specified account (' + @account_name + ').';
    RAISERROR(@error_display, 16, 1) ;
    ROLLBACK TRANSACTION;
    GOTO done;
END;

EXEC @rv=msdb.dbo.sysmail_delete_profile_sp
    @profile_name = @profile_name;

IF @rv<>0
BEGIN
    SET @error_display = 'Failed to delete the database mail profile (' + @profile_name + ').';
    RAISERROR(@error_display, 16, 1) ;
    ROLLBACK TRANSACTION;
    GOTO done;
END;

EXEC @rv=msdb.dbo.sysmail_delete_account_sp
    @account_name = @account_name;
IF @rv<>0
BEGIN
    SET @error_display = 'Failed to delete the database mail account (' + @account_name + ').';
    RAISERROR(@error_display, 16, 1) ;
    ROLLBACK TRANSACTION;
    GOTO done;
END;

COMMIT TRANSACTION;

done:

GO

Then after that, you can disabled the database mail by using the following script:

exec sp_configure 'Database Mail XPs', 0;
go
reconfigure;
go
exec sp_configure 'show advanced', 0
go
reconfigure;
go