Allen White

Creating Database Mail Accounts in SMO and Working Around the MailServer Bug

I'm working on automating all my server maintenance processes, and decided that I'd build an application to build my maintenance processes that can be run any time, and it would build the jobs correctly based on the existing databases. When I set up a new server I just have to run the program and my standard maintenance jobs are automatically built.

The first thing I needed to do was set up Database Mail consistently on all the servers so I'm notified if any problems occur in any of the jobs. SMO provides objects for that so it's logical to use SMO to build an application to create the Database Mail account and profile needed for the notifications.

First we need to add the Smo.Mail namespace to the application:

Imports Microsoft.SqlServer.Management.Smo.Mail

Next, we need to connect to the server, define our Database Mail objects, and if the account we want to use already exists, drop it, so we know we're creating it based on our current standards.

        ' Connect to the server
        Dim srvMgmtServer As Server
        srvMgmtServer = New Server("MyServer")
        Dim srvConn As ServerConnection
        srvConn = srvMgmtServer.ConnectionContext
        srvConn.LoginSecure = True
 
        Dim mscMailSrvColl As MailServerCollection
        Dim msMailSrv As MailServer
        Dim objMail As SqlMail
        Dim objMailAcct As MailAccount
 
        objMail = srvMgmtServer.Mail
        objMailAcct = objMail.Accounts("sqldba")
        If Not objMailAcct Is Nothing Then
            objMailAcct.Drop()
        End If

Now we want to create the MailAccount object.

        objMailAcct = New MailAccount(objMail, "sqldba")
        objMailAcct.Description = "Database Administrator Email"
        objMailAcct.DisplayName = "Database Administrator"
        objMailAcct.EmailAddress = "sqldba@example.com"
        objMailAcct.ReplyToAddress = "sqldba@example.com"

At this point we should be able to create a new MailServer object to add to the MailServers collection of the MailAccount object, but there's a design bug in SMO and they left out the New() method for the MailServer object. I exchanged a couple of emails with Michiel Wories about this, and he filed a bug after verifying the bug. I submitted Feedback on the bug, but figured out a workaround. If I just execute the Create() method at this point, a MailServer object will be created in the collection, using the current server name as the default name of the MailServer object. By using the Rename() method I was able to set the MailServer object to the SMTP server I wanted to use. Here's the code:

        objMailAcct.Create()
 
        mscMailSrvColl = objMailAcct.MailServers
 
        msMailSrv = mscMailSrvColl.Item(0)
        msMailSrv.Rename("smtpsrv.example.com")
        msMailSrv.Port = 25
        msMailSrv.EnableSsl = False
        msMailSrv.UserName = ""
        msMailSrv.Alter()
        objMailAcct.Alter()

The last thing I need to do is to set up a profile. The sp_send_dbmail stored procedure takes a profile name as its way of connecting with a mail server, so we need to create the profile. Here's the code for that:

        objMailProfileColl = objMail.Profiles
        objMailProfile = objMailProfileColl.Item("DBAMail")
        If Not objMailProfile Is Nothing Then
            objMailProfile.Drop()
        End If
 
        objMailProfile = New MailProfile(objMail, "DBAMail", "Database Administrator Mail Profile")
        objMailProfile.Create()
        objMailProfile.AddAccount("sqldba", 1)
        objMailProfile.Alter()

In another post I'll share the code that creates the maintenance jobs. If you get a chance, check out the Feedback using the link I provided and vote for it so it'll be addressed in the next Service Pack.

Allen