Database Mail and Reporting Services: a high level how to

Preparation:
Set up SMTP Mail account, ensuring that the SQL Server's IP Address is whitelisted against the internal relay if required (this allows emails to be sent internally).
Ensure AWE addressing is enabled.

SP_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
SP_CONFIGURE
GO
SP_CONFIGURE 'awe enabled', 1
GO
SP_CONFIGURE 'show advanced', 0
GO
RECONFIGURE
GO

If necessary, follow the instructions to enable the Lock Pages in Memory Option, according to company security policies, and restart SQL.

Configuration

Easiest to do this using the Database Mail Configuration Wizard. The following steps make more sense if you step through the wizard at the same time.

• Setup Database Mail
• Enable Database Mail
• Create a new profile for SQL Server Mail
• Use the add button to bring up the dialogue to input the account details.
• Add in the SMTP details: your port and SSL settings should be double checked with the sysadmin.
• Chose 'Basic Authentication' to log onto the SMTP server, which uses the account that you've just specified, rahter than the SQL Database Engine or Anonymous Authentication (Anonymous Authentication should be avoided).
• Make this the default mail profile, and either set the privacy for specific accounts, or make it public.

Enable the Database Mails XP Parameter:

SP_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
SP_CONFIGURE
GO
SP_CONFIGURE 'Database Mail XPs', 1
GO
SP_CONFIGURE 'show advanced', 0
GO
RECONFIGURE
GO

Then test that the email is running OK, either by executing the following store procedure, or by right clicking on Database Mail and choosing 'Send Test Email'. Check the results in the Database Mail Log (it's an option in SSMS and can be found in the same spot as the SQL Agent Logs and the SQL Server Logs). Using the Database Mail Log is simpler than using the Database Mail Troubleshooting option (which didn't actually work for me).

USE msdb
GO
EXEC SP_SEND_DBMAIL @profilename = 'ProfileName'
@recipients = 'sweetsql@blogspot.com'
@subject = 'Test SQL Mail'
@body = 'Hurrah, this test has been successful!'
GO

Finally, it's necessary to configure Reporting Services to recognise the existence of the email, so that it can be used for an automatically generated scheduled emailed report. Use the Reporting Services Configuration Manager, add in the details of the mail profile and the smtp server, and save.

The reports that are emailed need to use a data source with saved credentials, rather than a data source which passes on the credentials of the user viewing the report. Happily, parameterised reports can be set up to use a specific set of parameters for emailing purposes: this dialogue can be found when you add an (email) subscription to the report.