If you have used Powershell and SQL Server Management Objects (SMO) then you know how much time and work the combination can save. There are also numerous articles and blogs with great examples to help with unfamiliar classes, properties and methods of the SMO assembly. One of the areas without complete examples, or at least none that I could find, is configuring a profile for Database Mail. With the examples from this article you can quickly configure Database Mail end to end, without all the clicking it takes when configuring it through the SSMS interface.

The script that follows is not written as a function since I don't configure Database Mail on a daily basis. The explanation will, hopefully, be detailed enough for you to understand how to modify it to fit your needs and environment. Testing has been done against SQL 2005 Enterprise edition, SQL 2008 Standard edition and SQL 2008 R2 Enterprise edition. Be sure to read the security notes at the end of this article.

The Script

First the complete script for those who just want an example of using the SMO objects to configure Database Mail. The complete script is also included in the Resources section at the bottom of the page. An explanation of each step of the script follows this section.

# Step 1 - Set variables for mail options. 
  $sqlServer = 'YourServerName'
  $accountName = 'dbMailDefaultAcct'
  $accountDescription = 'Default dbMail Account'
  $originatingAddress = "$sqlServer@yourDomain.com"
  $replyToAddress = 'DO_NOT_REPLY@yourDomain.com'
  $smtpServer = 'smtpServer.yourDomain.com'
  $profileName = 'dbMailDefaultProfile'
  $profileDescription = 'Default dbMail profile'
# Step 2 - Load the SMO assembly and create the server object, connecting to the server.
  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
  $server = New-Object 'Microsoft.SqlServer.Management.SMO.Server' ($sqlServer)
# Step 3 - Configure the SQL Server to enable Database Mail.
  $server.Configuration.DatabaseMailEnabled.ConfigValue = 1
  $server.Configuration.Alter()
# Step 4 - Alter mail system parameters if desired, this is an optional step.
  $server.Mail.ConfigurationValues.Item('LoggingLevel').Value = 1
  $server.Mail.ConfigurationValues.Item('LoggingLevel').Alter()
# Step 5 - Create the mail account. 
# ArgumentList contains the mail service, account name, description, 
# display name and email address.
  $account = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Mail.MailAccount `
   -Argumentlist $server.Mail, $accountName, $accountDescription, $sqlServer, $originatingAddress 
  $account.ReplyToAddress = $replyToAddress 
  $account.Create()
# Step 6 - Set the mail server now that the account is created.
  $account.MailServers.Item($sqlServer).Rename($smtpServer)
  $account.Alter()
# Step 7 - Create a public default profile. 
# ArgumentList contains the mail service, profile name and description.
  $mailProfile = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Mail.MailProfile ` 
   -ArgumentList $server.Mail, $profileName, $profileDescription
  $mailProfile.Create()
# Step 8 - Associate the account to the profile and set the profile to public
  $mailProfile.AddAccount($accountName, 0)
  $mailProfile.AddPrincipal('public', 1)
  $mailProfile.Alter()
# Step 9 - Configure the SQL Agent to use dbMail.
  $server.JobServer.AgentMailType = 'DatabaseMail'
  $server.JobServer.DatabaseMailProfile = $profileName
  $server.JobServer.Alter()

What Is Happening in the Script

Now to walk through the script section by section and explain what it does. Please note that comments in the script are not commented on in this explanation.

# Step 1 - Set variables for mail options. 
  $sqlServer = 'YourServerName'
  $accountName = 'dbMailDefaultAcct'
  $accountDescription = 'Default dbMail Account'
  $originatingAddress = "$sqlServer@yourDomain.com"
  $replyToAddress = 'DO_NOT_REPLY@yourDomain.com'
  $smtpServer = 'smtpServer.yourDomain.com'
  $profileName = 'dbMailDefaultProfile'
  $profileDescription = 'Default dbMail profile

Step 1 - Set variables for mail options. Set string variables here for the target SQL Server, mail account name, account description, originating email address, reply-to email address, SMTP server, profile name and profile description. You will need to adjust them for your environment. You could also set these as parameters to the script or just skip them entirely and replace the variables where they are used in the script with quoted values.

# Step 2 - Load the SMO assembly and create the server object, connecting to the server.
  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
  $server = New-Object 'Microsoft.SqlServer.Management.SMO.Server' ($sqlServer)

The next two lines in Step 2 are familiar if you've done anything with Powershell and SMO. The first loads the SMO assembly and the second creates the SQL Server object.

# Step 3 - Configure the SQL Server to enable Database Mail.
  $server.Configuration.DatabaseMailEnabled.ConfigValue = 1
  $server.Configuration.Alter()

With the $server object created Step 3 enables Database Mail and commits the change. This is similar to using the sp_configure 'Database Mail XPs', 1 and recompile commands.

# Step 4 - Alter mail system parameters if desired, this is an optional step.
  $server.Mail.ConfigurationValues.Item('LoggingLevel').Value = 1
  $server.Mail.ConfigurationValues.Item('LoggingLevel').Alter()

The two lines in Step 4 change the mail system parameter for the logging level from Extended (value of 2, the default) to Normal (value 1). Other options that could be modified, with their default values, are listed in the following table. Altering the mail system parameters is optional.

# Step 5 - Create the mail account. 
# ArgumentList contains the mail service, account name, description, 
# display name and email address.
  $account = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Mail.MailAccount `
     -Argumentlist $server.Mail, $accountName, $accountDescription, $sqlServer, $originatingAddress 
  $account.ReplyToAddress = $replyToAddress 
  $account.Create()

Step 5 creates a MailAccount object with five argument values. First, the mail service ($server.Mail), second the account name ($accountName), third the account description ($accountDescription), fourth the display name ($sqlServer) or the target server name in this example. The final value is the email address ($originatingAddress). Notice the back-tick (grave accent) for line continuation at the end of the "$account =" line. If you're just copying the code from the initial, complete list watch for that wherever you paste the copy. The back-tick doesn't always translate from HTML to editors well. The next line sets the email Reply To address ($replyToAddress). The Reply To address is optional if you don't mind out of office notices and replies from "those special users" going to your SQL Server. Finally the create() method creates the account in the SQL Server.

# Step 6 - Set the mail server now that the account is created.
  $account.MailServers.Item($sqlServer).Rename($smtpServer)
  $account.Alter()

Now that the account is created these two lines in Step 6 set the SMTP Server name value for the account and put it into effect.

# Step 7 - Create a public default profile. 
# ArgumentList contains the mail service, profile name and description.
  $mailProfile = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Mail.MailProfile ` 
     -ArgumentList $server.Mail, $profileName, $profileDescription
  $mailProfile.Create()

In Step 7 the first two lines (that back-tick thing again) create the mail profile object. The argument list contains three values. They are, the mail service ($server.Mail), the profile name ($profileName) and a description ($profileDescription). The third line creates the profile in SQL Server.

# Step 8 - Associate the account to the profile and set the profile to public
  $mailProfile.AddAccount($accountName, 0)
  $mailProfile.AddPrincipal('public', 1)
  $mailProfile.Alter()

This Step associates the mail account created earlier with newly created profile, sets the profile security as the default public profile and commits the changes.

# Step 9 - Configure the SQL Agent to use dbMail.
  $server.JobServer.AgentMailType = 'DatabaseMail'
  $server.JobServer.DatabaseMailProfile = $profileName
  $server.JobServer.Alter()

Finally Step 9 configures the SQL Agent properties for its alert system to use Database Mail and sets the mail profile to the newly created profile. Don't forget to restart the SQL Agent service for this change to take effect. That's it, we're done.

To close

Powershell and SMO make many DBA tasks easy to automate, even those tasks we only do occasionally. Configuring Database Mail, which could take a few dozen clicks in SSMS (maybe a slight exaggeration but not by much), takes only a few lines of script and seconds to complete with Powershell and SMO.

Security notes

1. This script configures a Public, Default profile. While that may be acceptable from a security standpoint in my environment it might not be in yours. Adjust as appropriate or required.
2. The script in this article is provided with no support or warranties. You should not trust it until you understand what it does and how it could affect your environment. Always check scripts for suitability on a non-production server before applying to your production environment.