Configuring E-mail

First things first. Expand the Management node of your SQL Server installation, as shown in Figure 4-2. You should see Database Mail.

Figure 4-2. Location of Database Mail in SSMS

Right-click and select Configure Database Mail. You should see the information that is displayed in Figure 4-3.

Figure 4-3. Database Main Configuration Wizard

This is a great little wizard that walks you through how to step up your database mail. Once this is set up, I guarantee that you will start looking for reasons to employ database mail in your daily workflow. What purpose could this possibly lend to you? Think about how convenient it would be if you were to receive an e-mail after a task has run, or after the database has been backed up or restored successfully. What about when you are expecting an e-mail to say that your primary database was backed up, but you never receive that e-mail? That could be a trigger for bad things afoot. At least you will have sort of a heads up that something is wrong, as opposed to being blindsided by angry users or managers. This is a great tool for managing messaging as a database administrator, and has a role to play in nearly every application.

You can click the check box to not show this in the future, if you would like. I usually do. When you’re ready, click Next to continue.

The next screen that comes up, as shown in Figure 4-4, allows you to select what you want to do: set up a new account, manage accounts, or change parameters.

Figure 4-4. Select Configuration Task

Since we are setting up a new user, keep the default value of “Set up Database Mail…” selected and choose Next.

If you get an alert saying that the Database Mail feature is not available, select Yes to enable it. You shouldn’t have to install anything, considering that a full SQL Server install was done.

You now are taken to the New Profile screen, which looks like Figure 4-5.

Figure 4-5. New Profile (initial interface)

This is where most of the work is done. Enter a profile name and description in the boxes. These help you to differentiate your accounts. I entered the values shown in Figure 4-6, but you can enter whatever you want.

Figure 4-6. New Profile, nearly complete

When you’re satisfied with the values you’ve entered, press the Add button on the right side of the interface. This allows you to add a new profile for database mail.

Yet another interface magically appears with the information shown in Figure 4-7. I filled it in so you can get an idea of what you need to enter. You may need to get some of this information from your system administrator.

Figure 4-7. New Database Mail Account settings

The fields Account Name and Description are just placeholders, like in the previous screen. The outgoing mail information obviously has to be 100% correct or it will fail. The rest should be fairly obvious. Once you get everything filled in, click OK to be taken back to the previous interface, where you should now see the Account Name and E-mail Address in the grid area, as shown in Figure 4-8.

Figure 4-8. SMTP Accounts

When you’re ready to move on, choose Next.

You are shown a screen titled Manage Profile Security that has two tabs: Public Profiles and Private Profiles. We are going to deal specifically with the Public Profile for this exercise, as shown in Figure 4-9.

Figure 4-9. New Database Mail Account settings

What this means is that the database mail service is looking for the security of the object entered. You may recognize Profile Name because that’s what was entered on the previous screen. I originally thought that this may have been pulling off of Active Directory or something, but in a distributed database system, you may not have an account on Active Directory, but this would still work. Therefore, it had to be a value that was previously entered into the interface by the user… in this case, you. You can see how it appears in Figure 4-10. If you like, press Back and change the Profile Name at the top of the screen, then press Next to see that the name is changed. Pretty cool! You need to select the check box in the Public column of this interface to enable it; otherwise, any future setting changes you make will be applied globally. Also, pull down the Default Profile menu and select Yes. In this case, you just want to edit this particular user. The completed interface is shown in Figure 4-10.

Figure 4-10. Manage Profile Security

Click Next when you’re ready.

Oh look, Figure 4-11 shows another interface. Just for clarification, I use the term interface a lot. If that seems confusing, then you can always substitute “interface” for whatever term you have deemed to be what you call a change in on-screen appearance presented to the user in which a prompt or decision is displayed. I remember going through beginner Visual Basic 6 courses way back in the Stone Age, and my professor called them “interfaces.” It just kind of stuck.

Figure 4-11. Configure System Parameters

This interface is what you see now. All of these fields are editable, so proceed at your own risk. You are free to make whatever choice you want here, or keep the defaults. Whatever your environment or security principles call for is what should be enforced here. For example, I would not recommend changing the Prohibited Attachment File Extensions. Those extensions listed are basically all of the current file extensions that can be accessed by SQL Server and can also be executed by the operating system. They can’t necessarily be produced by SQL Server, but they can be added as attachments.

The following are the available options on this screen:

• Account Retry Attempts: Number of retry attempts for a mail server to send e-mail.

• Account Retry Delay (seconds): Delay between attempts for a mail server to send e-mail in seconds.

• Maximum File Size (Bytes): Maximum file size in bytes for an attachment for a mail server to send e-mail.

• Prohibited Attachment File Extensions: Prohibited file extensions for a mail server to send e-mail.

• Database Mail Executable Minimum Lifetime (seconds): Minimum lifetime for Database Mail executable in seconds.

• Logging Level: Determines which events are written to the Database Mail event log.

The definitions for those options are straight from the interface shown in Figure 4-11. An interesting thing to note here: if you were to click the Reset All button, you will see that the value for Account Retry Delay changes to 5000 instead of 60. So instead of waiting 1 minute, the default is to wait for over an hour just to retry sending mail. Think about what happens in the event of using e-mail to report a catastrophic failure. Would you rather know right away, or over an hour after the event?

I’m going to select the default options for this interface and choose Next to proceed. You will see next an interface in Figure 4-12 that is titled Complete the Wizard, with a brief summary of the settings chosen. Let’s look at those next.

Figure 4-12. Complete the Wizard

From top to bottom, we see all the information that we previously entered.

It tells us we created a new account and profile. It tells us we added that new account to that new profile. Think of the profile like a security container of sorts; to edit the account, you have to edit the profile. Finally, it tells us that we set the profile as public.

Looks like we’re ready to implement this, right? Click Finish to find out. Figure 4-13 shows the result you should get.

Figure 4-13. Configuring…

Woohoo! I always love seeing all those green check boxes! If you didn’t get that, go back to the beginning of this chapter and start again.

Sending a Test E-mail

If you did get those green check boxes, congratulations! Let’s move on to implementing database mail as a part of the database maintenance plan. Remember, the purpose of using e-mail is to …? That’s right, to keep us database administrators informed of any potential issues with the database. In this case, specifically, we will utilize database mail to send us a status report when a query runs successfully.

Right-click Database Mail again and select Send Test E-Mail. You will get a screen pop-up with information in it, as shown in Figure 4-14.

Figure 4-14. Send Test E-Mail

Fill out the To box with a valid e-mail address, and click Send Test E-Mail. It takes a second to get there, but it should show up. If it doesn’t, then something was set up incorrectly and you probably need to go back to the beginning and figure out what you may have missed.

Enabling the Mail Profile

So now our database can send mail, right? You’re going to hate me, but… yes and no. You see, SQL Server has this thing about security principles, and just because one user or login has permission to do something does not mean that all users and logins have permission to do that same thing. For this reason, it is important to bring up that, without an extra step, database mail will likely never run on its own.

Yes, the database has just sent the test mail. But remember this: whatever you do when you’re logged in to SQL Server is done under the context of your login. If you have been granted permission to do something, guess what: you can do it. Consequently, if you haven’t been granted that permission, you are not allowed to do it. For the most part, regular SQL Server logins (especially those that log in with the sa account) have permission to send mail by default. Interestingly enough, the SQL Server Agent does not, by default, have permission to send mail. Isn’t that odd?

There are probably good reasons why sending mail isn’t enabled by default, but I honestly can’t think of any good ones, so I will punt to Microsoft on this one.

A key point that I can’t stress enough is to make sure that you enable the mail profile in SQL Server Agent. Begin by right-clicking SQL Server Agent and choosing Properties, as shown in Figure 4-15.

Figure 4-15. SQL Server Agent properties

Once that opens, on the left, you see that you have options where you can choose to go to different areas of the configuration of SQL Server Agent. Do not change anything in here yet. Seriously, you will regret it.

Click the Alert System option. An interface comes up, as shown in Figure 4-16.

Figure 4-16. SQL Server Agent Properties (initial interface)

Those sneaky little Microsoft developers! Note that top check box next to “Enable mail profile” is unchecked. That is literally the only thing stopping you from sending automated e-mail from SQL Server. Select that check box, make sure that Database Mail is selected, and choose the mail profile we set up earlier. If you have previously defined an operator, you can set them up here as the fail-safe operator; so check the check box near the bottom next to Enable fail-safe operator and choose your operator. If you don’t have an operator defined yet, we will do that shortly, and then you can skip back here and add it to these properties. Be sure to select the E-mail option also, so that the person is notified by e-mail. The completed interface should resemble Figure 4-17.

Figure 4-17. SQL Server Agent Properties (completed interface)

When that’s all done, click OK, right-click SQL Server Agent, and select Restart. A prompt will pop up asking if you want to restart, so say Yes. That bounces the service and enables the database mail subsystem within SQL Server Agent.

Trust me. This is what will stop you later. When you get done with this chapter, go back and disable the mail profile and watch what happens. No e-mail!

Let’s take a second and review what we’ve done for this exercise so far.

What did we want to do? We wanted to create a SQL Server Agent job that sends e-mail when a SQL query is run, and returns the status of that query in the e-mail.

Did we accomplish that successfully? Why or why not? Not yet. We haven’t written the query or completed the SQL Server Agent job.

What is the next step? The next step is to write a simple query, and then integrate the query and the database mail into the SQL Server Agent job for testing.

Now we have a roadmap. First, write the query. Second, move the query and the mail into the steps of the job and begin testing.

Creating an Example Table

If you have a query that you would like to use, go right ahead. For the purposes of this exercise, if you don’t have any tables yet and you want to run an example, just execute the following two queries. The first query creates the table; the second populates it with data.

CREATE TABLE [dbo].[Users](
        [uid] [int] IDENTITY(1,1) NOT NULL,
        [userid] [varchar](10) NOT NULL,
        [lastname] [varchar](20) NOT NULL,
        [firstname] [varchar](20) NOT NULL,
        [email] [varchar](100) NOT NULL,
        [phone] [varchar](25) NOT NULL,
        [admin] [bit] NOT NULL
) ON [PRIMARY]

INSERT INTO [dbo].[Users]([userid],[lastname],[firstname],[email],[phone],[admin])
VALUES ('beardbr1','Beard','Bradley','[email protected]','555-555-5555',1);
GO

You can enter as much data as you want into the table. I would put more than one row in the table though. This information is just a placeholder so that you can see what to enter. The last field, admin, is to determine whether a user is an admin level user or not. 0 is no, 1 is yes. Implementing this in an application is up to you or the software developer.

Keep in mind that the preceding queries are not what we are going to use in the example SQL Server Agent job. They are just to set up a table with data for us to query.

Next, let’s write the actual query that is going to go in the job. Looking at the columns in our data, it seems pretty cut and dried. How about a nice little report on all of the data, sorted by last name? Sounds like a plan. This is a very easy query to write.

Writing a Query for the Job

We have an example table and some data in place. Now we can write query against that data, and then schedule execution of the query through a SQL Agent job.

Here is the example query that we’ll execute:

SELECT [userid],[firstname] + ' ' + [lastname] AS uname,[email],[phone],[admin]
FROM [dbo].[Users]
ORDER BY [lastname];

Let’s just go over the query really quickly, just to be sure that everyone understands what is going on. I am getting the concatenation of the firstname and lastname fields as a new field name called uname. This is specifically so that I don’t return two columns for each name, with the last name and the first name separated. I would rather have them returned in a nice, readable format; hence, concatenate them together and call it a day.

So now we have the database mail setup correctly and we have a functional query to return our data. All that is left is for us to put them together in a SQL Server Agent job.

Creating a SQL Agent Job

In SQL Server Management Studio, expand SQL Server Agent (at the very bottom), right-click the Jobs folder and select New Job…. The first thing to notice on the General tab is that the Owner box isn’t necessarily your logged in user name, but usually your Windows account name (your first and last name, usually, if using Windows Authentication). You need to change this to your actual Windows username, as shown in Figure 4-18, which is probably the owner of the database anyway.

Figure 4-18. New Job (initial interface)

You need to add a name for the job, so just enter Email User Information in the Name field.

Change the Category to Database Maintenance and add a simple description. It should be something that is easily identifiable. You don’t want to write War and Peace here, but you also don’t want to write “does stuff” either. Short but concise is the key.

Your screen should now resemble Figure 4-19.

Figure 4-19. New Job (updated interface)

Make sure that the Enabled check box is checked; otherwise, the job is not enabled and it will not run as expected.

SQL Server Agent Options

Pay attention to the menu options on the left of the interface. These are the different options that you have to set in the job, as you have seen in earlier chapters. Let’s take a look at those options now. Don’t make any changes yet, just familiarize yourself with the options:

• General: This is the option that you are currently on. It lets you set the general options, hence the name.

• Steps: This option lets you define the steps that the job will take. You can also switch up the order of the steps, or define a different starting step. As always, once you add a step, you can always go back and edit or delete it.

• Schedules: This option lets you define a schedule to run the job. It can be set up almost any way you can imagine, so spend some time in this area to make sure that you got it right.

• Alerts: This option lets you choose from a wide variety of alerts that SQL Server maintains. Click the Add button at the bottom of the screen. When you first enter this option, you are on the General tab of the New Alert window.

Alerts are further broken down, as follows:

• SQL Server event alert: Choose the database to target and the severity or error number to look for. You can also set an alert to be raised whenever specific text is returned from any message, such as “error” or “failure”, for example.

• SQL Server performance condition alert: This one is fun. You can choose the Object, Counter, and Instance to target here. For example, if you wanted to see the number of transactions as of the time that the job runs, you would choose Databases as the Object, Active Transactions for Counter, and your database name for the Instance. Change the value to “Alert if counter” to “rises above” and 0, and you will have a handy dandy performance monitor.

• WMI event alert: This area uses little-known WQL, or Windows Management Instrumentation (WMI) Query Language, to monitor SQL Server events. I think it may be beyond the scope of this book, since we can do what we need with the other two areas.

Next is the Response tab. Here is where you define what job you want to execute. You also have the option to Notify Operators by selecting the check box and choosing New Operator or choosing a predefined operator. Simply fill out the interface as needed and you are done! SQL Server makes this very easy to manage, don’t you think? Select the E-mail check box and you will be all set up. It is important to mention here that this is not what we set up earlier. This isn’t the database mail portion of the job; this pertains only to this particular Alerts area. We will get to the database mail portion next.

Steps Tab

Go back to the Steps tab. It should be blank, meaning that there are no steps listed yet. On this Steps tab, there is a button labeled New at the bottom of the screen. Click this to continue.

General

You should see the interface shown in Figure 4-20 open. Notice that you are currently on the General tab on this screen.

Figure 4-20. New Job Step, General option (initial interface)

This is what you see at this point. Enter Run SQL Query in the “Step name” box.

Coincidentally, the Transact-SQL Script (T-SQL) option is already selected for us. There are other options here that you can choose, but we will stick with this one for now.

There is a drop-down here labeled “Run as”. You can leave it at the default (which is blank). This box is for if you have set up a proxy account, and would like the proxy account to execute this step instead of the SQL Server Agent account. The reason for this is that the current context of a running job is always going to be SQL Server Agent, but the current step can be executed by a different user. You can set a different user for a particular task, which I will show you next, but this can stay at the default.

Select your database name from the Database drop-down menu; master is currently selected.

The Command box is where you want to paste the SQL query we wrote earlier, so go ahead and put that in there. You can press Parse, if you would like. The interface should now look like Figure 4-21.

Figure 4-21. New Job Step, General option (updated interface)

When you’re ready, click the Advanced tab to continue.

Advanced

The purpose of this screen is to set up the advanced options for this step. Initially, it should look like Figure 4-22.

Figure 4-22. New Job Step, Advanced option (initial interface)

Note that there is a “Run as user” box. We just did that, didn’t we? Well, yes and no. The correct option for this field is your current Windows login (if you have Windows Authentication and it is a user on the database), or the database owner (dbo) account. The reason is because this step needs to run under the context of the user account so that it can run. Typically, you can use dbo for just about anything, since it will most like be the account that owns the database and can therefore perform most of the necessary operations without any additional privileges.

If you want to log what happens, this is the place to do it. You have an option for On Success and On Failure, with retry and interval options. If you have chosen logging for this area, then go ahead and set it here.

You can also choose to output the SQL script, append the SQL script to an existing file, log to a table (you need to have the table already set up), and include the output in step history. Really, the only one that I want to see is the step output in history, so go ahead and select this option, as shown in Figure 4-23.

Figure 4-23. New Job Step, Advanced option (updated interface)

Now that this is all done, go ahead and click OK to move to the next area shown in Figure 4-24.

Figure 4-24. New Job Step, Advanced option (updated interface)

Note that after you clicked OK, you were brought back to the Job Properties window and Steps tab, and you can now see the newly entered step in there. Good job!

It looks good so far. Note that the Start Step option is set to the only value available, which is what we just entered. You now have the option to Insert, Edit, or Delete steps. Proceed carefully because it is not recoverable if you accidentally delete something.

Schedules Tab

Here is where you set the schedule. This is the part where you need to determine how often you want the job to run. You can set it for just about any value, just remember that you will greatly diminish your system resources if you constantly have a job cranking along. That’s not really adding anything for you, is it? Remember, the purpose of this maintenance plan is to make your life as a database administrator easier, not more complicated. If this complicates your life, you’re probably doing it wrong. Take the time to reexamine the requirements of the job, and implement a solution from there.

There are two options initially on the Schedules tab, as shown in Figure 4-25.

Figure 4-25. Job Properties, Schedules tab

Those options are New and Pick. New lets you create an entirely new schedule from scratch. Pick lets you pick from a previously existing schedule.

Pretty straightforward. You can go over the predefined schedules here, or create your own suited to your specific needs. For the purpose of this exercise, I have chosen Pick and I am going with the option CollectorSchedule_Every_6h. This plan will run all day, every day, every 6 hours, until the end of time. Choosing the option and clicking OK shows the option on the screen, as shown in Figure 4-26.

Figure 4-26. Job Properties, Schedules tab (updated)

So that’s all set up. Moving right along!

Alerts Tab

Next is the Alerts tab. There is a ton of good information in here, and we will go over all of the menu options in detail. Let’s look at a general overview of what happens after you press Add. You can always refer to the preceding information for a briefer introduction, because this is going to be a while.

The first thing you notice is that there are three tabs: General, Response, and Options.

Response

When you first click the menu option, you see the screen shown in Figure 4-27.

Figure 4-27. New Alert

If you have already saved the job, you will see that the Execute Job check box is defaulted to on. That’s because it is a part of this job, like it or not. If you haven’t, it won’t be assigned to a job yet, but will still be disabled. You also have the ability to notify operators, though, and this is key. For this step, to notify operators of the event being targeted, this must be checked and a user must be selected. You can check the Notify Operators box and click New Operator to add a new operator to the Operator area of SQL Server Agent. An example of this is shown in Figure 4-28.

Figure 4-28. New Operator

Click OK at this screen. Notice that if you were to expand your server name, followed by SQL Server Agent, and then Operators, you would see the operator you specified in this area. Also note that you cannot delete an operator from this interface (within the Job Properties). If you need to delete an operator, it will need to be done from SSMS in the Operators section.

If you’ve already added an operator, we see that this operator is in the interface with a set of check boxes next to the name. The values are E-mail, Pager, and Net Send. If you’ve set a value for these options, then select them here (e-mail, at least). If you like, click View Operator and notice that there is another option on the left called History. Clicking that option reveals a new interface, as shown in Figure 4-29.

Figure 4-29. New Operator, History tab

Once there is data available for this job, that is, after it runs, then there will be data here. Since it has never run, it has no data. Click OK to go back to the New Alert interface.

You should now see where you left off; the Execute job option is disabled and no operators are selected, as shown in Figure 4-30.

Figure 4-30. New Alert, Response tab

Next, click the Options tab on the left. You are presented with the interface shown in Figure 4-31.

Figure 4-31. New Alert, Options tab (initial interface)

This gives you the option to include the alert error text in e-mail, pager, or net send. You can specify any additional information here as well by entering it into the textbox. You can delay responses here as well. What is a delay response? Simply put, it’s a sleep command for any additional occurrences of the same event within the specified time frame. Let’s say that you didn’t set this correctly and had the defaults set as we have them now. What that implies is that for every single targeted event that hits the database engine, an e-mail is created and sent to the operator listed. You can imagine that this would generate a ton of e-mail, and you would be correct. Setting a delay between responses—a time frame that SQL Server waits to respond to the next same event—takes care of this.

What does all this mean? Here is a good way to look at it.

When the specified alert happens, I want to notify this person via e-mail. I also want to add the text “I hope it’s not a permanent error!” to the e-mail, so I’m going to add it here. I don’t want to know about every single error of this type, just that this error occurred. I’m going to set a delay for this, so that even if the same event occurs that would normally trigger this event, I don’t want the event to trigger this same job.

See how it works? An updated interface with suggested values is shown in Figure 4-32.

Figure 4-32. New Alert, Options tab (updated interface)

Now we can see that we are going to wait at least 5 minutes between e-mails.

We don’t want to actually use these values, so just press Cancel to return to the Job Properties page. You should see a blank Alerts screen; we didn’t want to set up any alerts here, remember. We did set up an operator, which is all we really needed out of this area. Yes, there are a few other ways to do it, but this way showed you around this area.

Notifications

Next is the Notifications tab, as shown in Figure 4-33. Notice that none of the options is checked and the drop-down values are defaulted.

Figure 4-33. Job Properties, Notifications tab

This is where we actually get to define the database mail option we set up earlier. The following are the available contact options:

• E-mail

• Page

• Net Send

• Write to the Windows Application event log

• Automatically delete job

Any or all of these can be selected so that the users that need to be notified can be. I would usually suggest setting up a catchall e-mail for database issues, but that would ultimately depend on your setup and any security constraints. In any event, there really is no reason for this area to be left blank (besides e-mail being selected, since that’s the point of this). For example, why wouldn’t you want the job status to be written to the Windows event log?

This screen itself is pretty self-explanatory, as shown in Figure 4-34 with my recommended options and values.

Figure 4-34. Job Properties, Notifications tab (updated)

Note how my account name is in the e-mail field? That’s because it was populated there from the Database Mail work we did earlier. Also notice that there are three different options for the drop-down menus: When the job succeeds, When the job fails, and When the job completes. Figure 4-35 illustrates this.

Figure 4-35. Job status meanings

What do these mean? What is the difference between them and which should I choose? The following heuristics will help answer these questions:

• If you want to know when a job completes successfully, choose “When the job succeeds”

• If you want to know when a job fails, choose “When the job fails”

• If you want to know that a job either succeeded or failed, choose “When the job completes”

That is literally the only difference between the options. The “completes” option is a catchall, where “succeeds” and “fails” are contingent on the pass or fail status of the job. Think of “completes” as an AND clause, and both “succeeds” and “fails” as OR clauses.

Targets

You can leave this screen defaulted to “Target local server”. Click OK when you’re done. The SQL Server Agent job is now set up!

Test the new job to make sure it works. Right-click the job and choose Start Job at Step… to start it. Figure 4-36 shows what you should see at this point.

Figure 4-36. Success!

Success! Now, check your e-mail. Hey, look at that! You should have received an e-mail similar to what is shown in Figure 4-37.

Figure 4-37. E-mail received

There’s something else that I need to add at this point… I set up the in an environment that did not have any contingencies for outside servers, so getting the connectivity was a piece of cake. In the real world, it will likely be much more complex, so I would suggest making sure that all your ducks are in a row before you start, so to speak. I went back and tried to get this to work using Gmail, and it effectively blew up in my face. There were a lot of settings that were specific to how Gmail operates that had to be set on Gmail’s side. It is assumed that you will be working in a corporate environment of some type, and will have access to the SMTP information you need from a system or mail administrator. I will include a short and sweet guide to connecting to Gmail and using their SMTP service though.

Setting Up a Profile

Double-click Database Mail from inside the Management folder in SSMS. You will see the interface shown in Figure 4-38.

Figure 4-38. Select Configuration Task

This is the generic beginning screen, so choose the default option selected and click Next.

When you see the screen in Figure 4-39, enter Gmail Profile as the profile name, and something short and sweet for the description.

Figure 4-39. New Profile

Once you get that, click Add… to continue.

You should now see what is shown in Figure 4-40. Notice that our old profile is there. That’s the other profile, not the new Gmail one.

Figure 4-40. Add Account to Profile

Since we don’t want to use this old profile because we are setting up a new profile, click the New Account… button. That will bring up the interface that you see in Figure 4-41.

Figure 4-41. New Database Mail Account

This is exactly as we entered earlier, but now let’s update that for the Gmail-specific settings. Complete your selections as shown in Figure 4-42.

Figure 4-42. New Database Mail Account (updated)

The important bits are that the SSL check box needs to be selected and your account information needs to be correct, obviously. The SMTP server for Gmail is smtp.gmail.com and the port number is 25. Click OK when you’re done. You will see the screen shown in Figure 4-43.

Figure 4-43. New Profile

Go ahead and click Next here. You will see the Manage Profile Security screen, as shown in Figure 4-44.

Figure 4-44. Manage Profile Security

Note that I have selected the Public check box. I also changed the Default Profile value to Yes. This is a pull-down menu, so pull it down and change the value. You don’t need to go into the Private Profiles section, so don’t worry about that. Click Next when you are ready to move on. You will see what is shown in Figure 4-45.

Figure 4-45. Configure System Parameters

This is the screen where you define the system parameters. Remember setting these options before? They’re the same now, so leave them alone and click Next.

You will then see the Complete the Wizard screen, as shown in Figure 4-46.

Figure 4-46. Complete the Wizard

These settings are what we’ve done to this new profile, so go ahead and click Finish when you’re done. You should see the green check boxes next, as shown in Figure 4-47.

Figure 4-47. Configuring…

Testing E-mail Configuration

At this point, the first part is done. It probably won’t work just yet. To test if it does, you can either manually send a test e-mail by right-clicking Database Mail and choosing Send Test E-Mail… or by using the following script.

EXEC msdb.dbo.sp_send_dbmail
@profile_name='Gmail Profile',
@recipients = '[email protected]',
@subject='This is only a test. We control the horizontal. We control the vertical.',
@body='Testing the Gmail profile'

This will send e-mail also. Either way you want to do it is fine. After you run it, check the Database Mail Log by right-clicking Database Mail and choosing View Database Mail Log. You will then see the screen shown in Figure 4-48.

Figure 4-48. Log File Viewer

See that error? Let’s look at that: “The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2015-11-13T16:35:55). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.5.1 Authentication Required. Learn more at).”

Interesting! The important part here is (and I can’t stress this enough): do not change anything yet. Trust me. SQL Server is configured correctly. You have to finish setting up Google before anything is going to work.

Now, let’s look at Google’s settings.

Allowing Access to Google’s SMTP Server

The first thing that we need to do is allow SQL Server to access Gmail’s servers. This seems like it would be a trivial task, and it kind of is, but you need to change a few things on Google’s side.

First off, log in to Google from your browser and go to https://myaccount.google.com/security . This is Google’s main security page. You can handle just about any Google task from right here.

Once you get to this page, you want to look for a section labeled “Allow less secure apps”. This is set to OFF by default. Just click the button and change it to ON, as shown in Figure 4-49.

Figure 4-49. Allow less secure apps

Once that’s done, go back to SSMS and check the Database Mail Log again. I only see one more error in there, which happened about 1 second after the first one. Remember how we set the interval to 5 minutes? We have to wait that amount of time now to see if it worked or not.

I don’t see anything yet, so there must be something else! Let’s see… we’ve set up the Database Mail task correctly (trust me), we’ve enabled less secure apps to interact with Google… what else is there?

Enabling POP E-mail

What else is there? An obscure setting in the bowels of Gmail that must be enabled, that’s what. Open your Gmail account and go to Settings. Then go to Forwarding and POP/IMAP. You should see the screen shown in Figure 4-50.

Figure 4-50. Gmail settings

See that bit there about POP? That’s the stuff we need to look at next. Pop quiz: What is the difference between POP and SMTP? POP receives e-mail, whereas SMTP sends e-mail. So my settings say that POP is disabled, meaning I can’t receive e-mail over POP. Change this to “Enable POP for all mail” and click Save Settings at the bottom of the screen. It takes a second to save, and then you go back to your inbox.

Go back to SSMS and send another test e-mail using the earlier script. Check your Gmail inbox. You should see what is shown in Figure 4-51.

Figure 4-51. Gmail received

We can send test e-mails to Gmail now!

If you still have trouble connecting to Gmail from here, go back over this section again. I’ve read that the SSL port might need to be changed to 587 from 25, but my profile is set to 25 and it works just fine.