SelfService Active Directory Powershell script

Hi everybody,

We have a need for self service portal in our IT department. We arranged after work shifts, and if I’m not near my VPN connected PC, I needed to unlock or reset user passwords, or give users the ability to have their mangers do that for them.

I needed unlock or password reset solution for users, that will trigger a script on mail recieve. So I searched for simillar powershell script, and I found it DeployHappiness AD Self Service Portal.
This blog show us how to send SMS to mail, but our provider does not have this kind of service.

Still, this gave me a logic of things, and how to connect ot Exhange via API on specific mailbox, and query that mailbox for specific mails.

Then I needed to make sure that only users from our Organization can request, and that the person requesting is direct or indirect manager of the user.

Example:

  • IT Admins
    • John
      • Dave
      • Julia
        • Peter
        • Sandra
      • Steve
    • Angela
      • David
      • Jeremy
        • Marco

IT admins can request Unlock or Reset for everybody
John can request Unlock or Reset for his group (Dave,Julia,Peter,Sandra), but not for himself or Angela’s group.
Angela can request Unlock or Reset for hers group (David,Jeremy,Marco), but not for herself or John’s group.
Julia can request Unlock or Reset for hers group (Peter,Sandra), but not for herself or Dave and Steve.
Jeremy can request Unlock or Reset for Marco, but not for himself or David.

(Hope you got it) Smiješak

 

Next on Prerequisites:

  • Create a user with mail adress
  • Create Recieve connector on Exchange for server that will host the script
  • On server that will host a script install Feature: Active Directory module for Window Powershell
  • Setup Active Directory Users Manager on Users Organization Tab (example)
  • Define Mail Subject and Body field (For me Subject = User for Unlock or rest, Body = what to do Unlock user or Reset password)
  • Create taks scheduler (run as user to which you send mail requests), triggers set to 5 minutes (more or less is up to you)

Script:
Explanations are in my script commented.

<#

Script for SelService AD


This script enables IT to unlock or reset passwords to AD users. Mangers can also request for theirs employees.


        Author: Luka Gros

        Website: blog.lukagros.com

        Email: luka@lukagros.com

        Date created: 20.Octobar.2015

        Last modified: 20.Octobar.2015

        Version: 1.1


    .LINK


        http://blog.lukagros.com

        https://twitter.com/lukagros


#>

####Parts of Script from http://deployhappiness.com/reset-user-passwords-with-ad-self-service-portal/#######

##############################################################Config##############################################################

$SmtpServer = "10.10.10.1"

$ResetEmail = "Reset Notification <reset.password@domain.com>"

$Username = "DOMAIN\user"

$Password = "Passw0rd"

$MailServer = "https://mail.domain.com/ews/exchange.asmx"

$ExchangeVersion = "Exchange2013" ##"Exchange2010_sp1"

####ALERT MAIL####################

$LoggingUser = "InformationMail@domain.com"

####NEW USER RESET PASSWORD######

$NewPassword = "Passw0rd" #You can also find function that creates random password, but we use ours default. And be careful of character length in GPO


#######Download for API assembley file is here: http://www.microsoft.com/en-us/download/details.aspx?id=35371 (There is 2.2 that works better with Exchange 2013)

[Reflection.Assembly]::LoadFile("C:\Program Files\Microsoft\Exchange\Web Services\2.2\Microsoft.Exchange.WebServices.dll")



##############################################################Config##############################################################


###############################Function that checks if requester is manager to user###############################

#######http://www.lazywinadmin.com/2014/10/powershell-who-reports-to-whom-active.html########

function Get-ADdirectReports

{

    PARAM ($SamAccountName)

    Get-Aduser -identity $SamAccountName -Properties directreports | %{

        $_.directreports | ForEach-Object -Process {

            # Output the current Object information

            Get-ADUser -identity $Psitem -Properties mail,manager | Select-Object -Property Name, SamAccountName, Mail, @{ L = "Manager"; E = { (Get-Aduser -iden $psitem.manager).samaccountname } }


            # Find the DirectReports of the current item ($PSItem / $_)

            Get-ADdirectReports -SamAccountName $PSItem

        }

    }

}#CLOSE SECTION function Get-ADdirectReports


###############################Connect to Exchange mailbox###############################

 $email = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService([Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2013)

 $email.Credentials = New-Object Net.NetworkCredential($Username, $Password)

 $uri=[system.URI] $MailServer

 $email.Url = $uri

 $inbox = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($email,[Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Inbox)

###############################Connect to Exchange mailbox###############################


###############################Check if there are unread mails###############################

if ($inbox.UnreadCount -gt 0)

 {

        $PropertySet = new-object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.BasePropertySet]::FirstClassProperties)

        $PropertySet.RequestedBodyType = [Microsoft.Exchange.WebServices.Data.BodyType]::Text;

        # Set search criteria - unread only

        $SearchForUnRead = New-object Microsoft.Exchange.WebServices.Data.SearchFilter+IsEqualTo([Microsoft.Exchange.WebServices.Data.EmailMessageSchema]::IsRead, $false)

        $items = $inbox.FindItems($SearchForUnRead,10)  #return only 10 unread mail items


        Import-Module -Name ActiveDirectory


 ###############################CHECK IF UNREAD ITEMS - IF NOT END###############################

 foreach ($item in $items.Items)

 {

  # load the property set to allow us to view the body

 $item.load($PropertySet)


    #######Get Subject and Body fields###

        $getsubjecttext = $item.subject

        $getsubjecttext = $getsubjecttext+"*" #add right wildcard

        $getbodytext = $item.body

        $getsubjectuser = Get-ADUser -Filter {DisplayName -like $getsubjecttext -and employeetype -eq'1'} -Properties UserPrincipalName,SamAccountName,cn,DisplayName,mail

        If ($getsubjectuser -eq $null) #If IT cannot be found by full name, try by SamAccountName

            {

            $getsubjectuser = Get-ADUser -Filter {SamAccountName -like $getsubjecttext -and employeetype -eq'1'} -Properties UserPrincipalName,SamAccountName,cn,DisplayName,mail

            }


        $managerEmployeOK = '0' ##Reset Manager Flag


        #######Get-requestere###

        $address = $item.From.address

        $user = Get-ADUser -Filter {UserPrincipalName -eq $address} -Properties UserPrincipalName,SamAccountName,cn


  ###Reset Unlock nad Reset flags for every mail####

  $UnlockAccount = '0'

  $ResetpwdAccount = '0'

#######IF Keywords in body

  if (($item.body.text -Like "Unlock account*") -and $getsubjectuser -ne $null) #THIS IS STRICT OR PEOPLE WILL WRITE RUBBISH

        {

            $UnlockAccount = '1'

        }


  if ($item.body.text -Like "Reset Password" -and $getsubjectuser -ne $null -and $UnlockAccount -eq '0') #THIS IS STRICT OR PEOPLE WILL WRITE RUBBISH

        {

            $ResetpwdAccount = '1'

        }


#################################If requester is found GO - IF NOT do nothing#######################################

if($user -ne $null)

{

<# If user is under requester set flag $managerEmployeOK=1, if not send mail NO RIGHTS

#>

    $usersToDo = Get-ADDirectReports $user.SamAccountName

    foreach ($Name in $usersToDo)

    {

        if ($Name.SamAccountName -eq $getsubjectuser.SamAccountName)

            {

                #Write-Output $Name.SamAccountName

                $managerEmployeOK = '1'

            }

    }

####Allow IT admins######

If ($user.SamAccountName -eq 'userAdmin1' -or $user.SamAccountName -eq 'userAdmin2') ##You can write if userAdmin is in some AdminGroup that can send requests

    {

    $managerEmployeOK = '1'

    }

##################################


IF ($managerEmployeOK -eq 1)

{

###UNLOCK SECTION################################################################################

 if($UnlockAccount -eq '1')

    {

            ##The line below is commented for e-mail testing, no command is issued

            ###Unlock-ADAccount -identity $getsubjectuser.samaccountname


            ##Send MAIL TO $LoggingUser = "InformationMail@domain.com"###################################

            $body="

                <p style='font-family:arial'>User Account " + $getsubjectuser.cn + " (" + $getsubjectuser.SamAccountName + ") has been unlocked. Request was from: $address</p>

                <p>&nbsp;

                <p style='font-family:arial;font-size:12px;color:red'>Please do not respond to this automatic e-mail!</p>

                "

            send-mailmessage -to $LoggingUser -from $ResetEmail -subject "Action: User unlock requested!" -body $Body  -SmtpServer $SmtpServer -BodyAsHtml -Encoding UTF8


            ##Send MAIL TO Requester###################################

            $body="

                <p style='font-family:arial'>HI,</p>

                <p style='font-family:arial'>User " + $getsubjectuser.cn + " (" + $getsubjectuser.SamAccountName + ") has been unlocked.</p>

                <p>&nbsp;

                 <p style='font-family:arial;font-size:12px;color:red'>Please do not respond to this automatic e-mail!</p>

                "

            send-mailmessage -to $item.From.address -from $ResetEmail -subject "User is unlocked!" -body $Body -SmtpServer $SmtpServer -BodyAsHtml -Encoding UTF8


            ##SEND MAIL TO unlocked user###################################

                $body="

                <p style='font-family:arial'>Hi " + $getsubjectuser.cn + ",</p>

                <p style='font-family:arial'>User " + $user.cn + " (" + $item.From.address + ") has requested unlocking of your account.</p>

                <p style='font-family:arial'>Your account <b>" + $getsubjectuser.SamAccountName +"</b> is now unlocked</p>

                <p>&nbsp;

                 <p style='font-family:arial;font-size:12px;color:red'>Please do not respond to this automatic e-mail!</p>

                "

            send-mailmessage -to $getsubjectuser.mail -from $ResetEmail -subject "Your account has been unlocked!" -body $body -SmtpServer $SmtpServer -BodyAsHtml -Encoding UTF8

            $Unlock = $True



    }#UNLOCK CLOSE SECTION


###RESET SECTION################################################################################

 if($ResetpwdAccount -eq '1')

    {

                <#The line below is commented for e-mail testing, no command is issued

                ###Set-ADAccountPassword -identity $user.samaccountname -Reset -NewPassword (ConvertTo-SecureString -AsPlainText $NewPassword -Force) #Set users new password

                ###Unlock-ADAccount -identity $user.samaccountname #Probably user locked, so unlock

###Set-ADUser -Identity $user.samaccountname -ChangePasswordAtLogon $true #Set must change password on next logon

                #>

                ##If the password was reseted in the last 10 minutes do nothing

                $PasswordAge = (Get-ADUser $getsubjectuser -Properties PasswordLastSet | Select PasswordLastSet)

                if ((Get-Date).AddMinutes(-10) -ge $PasswordAge.PasswordLastSet)

                 {


                ##Send MAIL TO $LoggingUser = "InformationMail@domain.com"###################################

                    $body="

                        <p style='font-family:arial'>Request for password reset for user " + $getsubjectuser.cn + " (" + $getsubjectuser.SamAccountName + "). Request was from: $address</p>

                        <p>&nbsp;

                        <p style='font-family:arial;font-size:12px;color:red'>Please do not respond to this automatic e-mail!</p>

                      "

                    send-mailmessage -to $LoggingUser -from $ResetEmail -subject "Action: User password reset requested!" -body $Body  -SmtpServer $SmtpServer -BodyAsHtml -Encoding UTF8


                    ##Send MAIL TO Requester###################################

                    $body="

                        <p style='font-family:arial'>Hi,</p>

                        <p style='font-family:arial'>Password reset for user" + $getsubjectuser.cn + " (" + $getsubjectuser.SamAccountName + ").</p>

                        <p style='font-family:arial'>New password is : <b>" + $NewPassword + "</b></p> ##If user cannot read their mail, then their manager can let them now

                        <p style='font-family:arial'>User can now try to logon with his/hers new password, but password must be changed on first logon!</p>

                        <p>&nbsp;

                        <p style='font-family:arial;font-size:12px;color:red'>Please do not respond to this automatic e-mail!</p>

                        "

                    send-mailmessage -to $item.From.address -from $ResetEmail -subject "Reset user password complete!" -body $Body -SmtpServer $SmtpServer -BodyAsHtml -Encoding UTF8


                    ##SEND MAIL TO user###################################

                    $body="

                        <p style='font-family:arial'>Hi " + $getsubjectuser.cn + ",</p>

                        <p style='font-family:arial'>User " + $user.cn + " (" + $item.From.address + ") requested your password to be changed. Your new password is: <b>" + $NewPassword +"</b></p>

                        <p>&nbsp;

                        <p style='font-family:arial;font-size:12px;color:red'>Please do not respond to this automatic e-mail!</p>

                           "

                    send-mailmessage -to $getsubjectuser.mail -from $ResetEmail -subject "Your password has been reseted!" -body $body -SmtpServer $SmtpServer -BodyAsHtml -Encoding UTF8

                } #CLOSE SECTION when was password last set

    }#CLOSE RESET SECITON


 ###Command or user unknown################################################################################

 if (($UnlockAccount -eq '0' -and $ResetpwdAccount -eq '0') -or ($getsubjectuser -eq $null))

    {

                        ##Send MAIL TO $LoggingUser = "InformationMail@domain.com"###################################

                        $body="

                        <p style='font-family:arial'>There was request " + $getsubjecttext +"; with body " + $getbodytext +"</p>

                        <p style='font-family:arial'>Request was  from: $address</p>

                        <p>&nbsp;

                        <p style='font-family:arial;font-size:12px;color:red'>Please do not respond to this automatic e-mail!</p>

                        "

                        send-mailmessage -to $LoggingUser -from $ResetEmail -subject "Action:request error" -body $Body  -SmtpServer $SmtpServer -BodyAsHtml -Encoding UTF8


                        ##Send MAIL TO Requester###################################

                        ##Don't forget here to write the instructions how the mail was supose to look

                        $body ="

                        <p style='font-family:arial'>Hi " + $user.cn + ",</p>

                        <p style='font-family:arial'>YOur mail, with title <b>" + $item.subject + "</b> is unknown.</p>

                        <p style='font-family:arial'>Please write correct user FirsName and Surname, or loginname.</p>

                        <p style='font-family:arial'>Keywords:</p>

                        <ul style='font-family:arial'>

                        <li><b>Unlock</b> - unlocks</li>

                        <li><b>Reset</b> - password reset</li>

                        </ul>

                        <p>&nbsp;

                        <p style='font-family:arial;font-size:12px;color:red'>Please do not respond to this automatic e-mail!</p>

                        "

                        send-mailmessage -to $item.From.address -from $ResetEmail -subject "Mail command unknown" -body $Body -SmtpServer $SmtpServer -BodyAsHtml -Encoding UTF8

      }#CLOSE SECTION Command or user unknown

} #CLOSE SECTION managerEmployeOK

else #If not manager

{

                        ##Send MAIL TO $LoggingUser = "InformationMail@domain.com"###################################

                        $body="

                        <p style='font-family:arial'>Stigao je mail sa naslovom <b>" + $getsubjecttext +"</b></p>

                        <p style='font-family:arial'>i tekstom <b>" + $getbodytext +"</b></p>

                        <p style='font-family:arial'>Zahtjev je došao sa adrese: $address</p>

                        <p>&nbsp;

                        <p style='font-family:arial;font-size:12px;color:red'>Please do not respond to this automatic e-mail!</p>

                        "

                        send-mailmessage -to $LoggingUser -from $ResetEmail -subject "Action:manager error" -body $Body  -SmtpServer $SmtpServer -BodyAsHtml -Encoding UTF8


                        ##Send MAIL TO Requester###################################

                        $body ="

                        <p style='font-family:arial'>Hi " + $user.cn + ",</p>

                        <p style='font-family:arial'>According to our specifications, you are not manager to user " + $getsubjectuser.cn + " (" + $getsubjectuser.SamAccountName + "). </p>

                        <p style='font-family:arial'>Request denied!</p>

                        <p>&nbsp;

                        <p style='font-family:arial;font-size:12px;color:red'>Please do not respond to this automatic e-mail!</p>

                        "

                        send-mailmessage -to $item.From.address -from $ResetEmail -subject "Manager request error" -body $Body -SmtpServer $SmtpServer -BodyAsHtml -Encoding UTF8

}#CLOSE SECTION if not manager

##Set mail read

$item.Isread = $true

$item.Update([Microsoft.Exchange.WebServices.Data.ConflictResolutionMode]::AlwaysOverwrite)


 }#CLOSE SECTION Requester OK

 } #CLOSE SECTION foreach ($item in $items.Items)

 } #CLOSE SECTION ($inbox.UnreadCount -gt 0)

 

So , this would be it.

Active Directory User Auditing – Simple

For my Audit report, I had to create an audit list about creating,deleting,disabling,enabling,locking, unlocking my AD users.

Since we haven’t had a SCOM on our premises, I hade to come up with something that will trigger an e-mail to me with an event.
First of all you have to enable user auditing on Default GPO.

image

After that, I connected to my primary domain controller and created an Powershell script (with a little help from http://powershell.com/cs/) which sends an e-mail in HTML form to me, with some parameters.
First of all, it creates HTML file with a table, which then populates from Security Event under Event ID 4740. After it populates HTML file, this table sets as an body, and sends it to email addresses.

   1: #$DC = "DCServerName" 
   2: $Report= "C:\Admin\lockedaccount\locked.html" 

   3: $HTML=@" 

   4: <title>Account locked out Report</title> 

   5: <style> 

   6: BODY{background-color :#FFFFF} 

   7: TABLE{Border-width:thin;border-style: solid;border-color:Black;border-collapse: collapse;} 

   8: TH{border-width: 1px;padding: 1px;border-style: solid;border-color: black;background-color: ThreeDShadow} 

   9: TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color: Transparent} 

  10: H2{color: #457dcf;font-family: Arial, Helvetica, sans-serif;font-size: medium; margin-left: 40px; 

  11: </style> 

  12: "@ 

  13: $Account_Name = @{n='Account name';e={$_.ReplacementStrings[-1]}} 

  14: $Account_domain = @{n='Account Domain';e={$_.ReplacementStrings[-2]}} 

  15: $Caller_Computer_Name = @{n='Caller Computer Name';e={$_.ReplacementStrings[-1]}} 

  16: $event= Get-EventLog -LogName Security -ComputerName $DC -InstanceId 4740 -Newest 1 | 

  17:    Select TimeGenerated,ReplacementStrings,"Account name","Account Domain","Caller Computer Name" | 

  18:    % { 

  19:      New-Object PSObject -Property @{ 

  20:       "Account name" = $_.ReplacementStrings[-7] 

  21:       "Account Domain" = $_.ReplacementStrings[5] 

  22:       "Caller Computer Name" = $_.ReplacementStrings[1] 

  23:       Date = $_.TimeGenerated 

  24:     } 

  25:    } 

  26:   $event | ConvertTo-Html -Property "Account name","Account Domain","Caller Computer Name",Date -head $HTML -body  "<H2> User is locked in the Active Directory</H2>"| 

  27:      Out-File $Report -Append 

  28: $MailBody= Get-Content $Report 

  29: $MailSubject= "User Account locked out" 

  30: $SmtpClient = New-Object system.net.mail.smtpClient 

  31: $SmtpClient.host = "mail.uniqa.hr" 

  32: $MailMessage = New-Object system.net.mail.mailmessage 

  33: $MailMessage.from = “AccountLockout@test.com” 

  34: $MailMessage.To.add(“itsupport@test.com) 

  35: $MailMessage.Subject = $MailSubject 

  36: $MailMessage.IsBodyHtml = 1 

  37: $MailMessage.Body = $MailBody 

  38: $SmtpClient.Send($MailMessage) 

  39: del C:\Admin\lockedaccount\locked.html 

After creating this powershell script, the next step is to create an Event Trigger which will send this e-mail.
This is done through Task Scheduler.

image

image
This trigger works when Event with 4740 ID is generated in Security Event Viewer.

image

-command "& 'C:\Admin\lockedaccount\account_locked_out.ps1' "

The final result is this:

image

image

Now, you can do this with Unlock account 4767, or Disable account 4725 or deleted 4726etc.
I found out this site with lists of Event IDs : link

Good Luck

SQL Server Reporting Service (SSRS) – Use email friendly name

I had my SSRS running smoothly, but there was one thing bothering me. That was email sender friendly name. In RS Configuration Manager you can setup e-mail settings, and enter senders e-mail address.
But every time I got an email with report, the sent from stated sql@sqlreports.com. But I need an friendly name.
So, I just added “SQL REPORTS” sql@sqreports.com in RSCM and it worked! IT also works with MSSQL 2012 server.

image

Mail merge from SQL Database mail

Few months ago, I was given a task to send some notifications via circular letter. The best thing is to send it by MS WORD.
image
Unfortunately, after sending this, the attachment could not be read on the recipients side. Me and my colleague could not understand, is it because WORD or attachment or in the end mail server. Because we where on tight schedule, I came up with idea to send circular letter through SQL Database mail.
After importing the table of recipients, I have created an simple WHILE loop to call sp_send_dbmail.

It can be sent in plain text or HTML.
For this I have create a three users me, John Doe, Jane Doe.

This is T-SQL for loop plain text
/*****************************************PLAIN TEXT********************************************************/
use msdb
SET QUOTED_IDENTIFIER ON
GO

declare @Title varchar(20)
declare @Name varchar (200)
declare @email varchar(50)
declare @option varchar (600)
declare @attachment varchar(100)
declare @start int =(select min(id) from test.dbo.circular)
declare @end int =(select max(id) from test.dbo.circular)
declare @bodyrun varchar(2048)

while @start<=@end
begin

set @title=(select title from test.dbo.circular where id=@start)
set @Name=(select name from test.dbo.circular where id=@start)
set @email=(select email from test.dbo.circular where id=@start)
set @option=ISNULL(‘You have also selected an option "’+(select [option] from test.dbo.circular where id=@start)+’"’,”)
set @bodyrun =
”+@Title+’
‘+@Name+’

We must inform you that you have signed a contract with TESTFirm.

‘+@option+’

Best Regards

Somebody’

exec sp_send_dbmail
@profile_name =  ‘SarumanMails’,
@recipients = @email,
@from_address =  ‘sql@sql.com’ ,
@reply_to =  ‘sql@sql.com’,
@subject =’Contract and options’ ,
@body =  @bodyrun,
–@body_format=’HTML’,
@file_attachments =N’c:\test\contract.pdf’ –this is on server side

set @start=@start+1
end
/*****************************************PLAIN TEXT********************************************************/

/*****************************************HTMLTEXT********************************************************/

use msdb
SET QUOTED_IDENTIFIER ON
GO

declare @Title varchar(20)
declare @Name varchar (200)
declare @email varchar(50)
declare @option varchar (600)
declare @attachment varchar(100)
declare @start int =(select min(id) from test.dbo.circular)
declare @end int =(select max(id) from test.dbo.circular)
declare @bodyrun varchar(2048)

while @start<=@end
begin

set @title=(select title from test.dbo.circular where id=@start)
set @Name=(select name from test.dbo.circular where id=@start)
set @email=(select email from test.dbo.circular where id=@start)
set @option=ISNULL(‘You have also selected an option "’+(select [option] from test.dbo.circular where id=@start)+’"’,”)
set @bodyrun =
‘<font size="3" face="Arial" color="black">
<p><big>’+@Title+'</p>
<p>’+@Name+'</big></p>
<p> </p>
<p> </p>
<p>We must inform you that you have signed a contract with TESTFirm.</p>
<p> </p>
<p>’+@option+'</p>
<BR>&nbsp;<BR>
<p></p>
<p>Best Regards</p>
<p></p>
<p>Somebody</p> </font>’

exec sp_send_dbmail
@profile_name =  ‘SarumanMails’,
@recipients = @email,
@from_address =  ‘sql@sql.com’ ,
@reply_to =  ‘sql@sql.com’,
@subject =’Contract and options’ ,
@body =  @bodyrun,
@body_format=’HTML’,
@file_attachments =N’c:\test\contract.pdf’ –this is on server side

set @start=@start+1
end
/*****************************************HTMLTEXT********************************************************/

I received three mails:
image

The result of plain text mail:
image 

The result of HTML mail:
image

So, that’s about it.

Good Luck

E-mails in MSSQL– How to setup Database Mail

MSSQL has a service called Database mail and it is used from MSSQL 2005 till today. This service is predecessor of SQL Mail.
I will write about Database mail, and how to set it up.
First of all, you have to open a port 25 (SMTP), so you can send mail from server to the “world”.
Second, we must enable Database Mail

sp_CONFIGURE ‘show advanced’, 1
GO
RECONFIGURE
GO
sp_CONFIGURE ‘Database Mail XPs’, 1
GO
RECONFIGURE
GO

After that, we must give permission of sending mail to SQL Agents service account. My Agent is running under domain service account. Add that user to DatabaseMailUserRole in msdb database
image

image

After adding the user to msdb database, next we must create mail profile.
image
image
image

if your mail server requires authentication, enter it in SMTP authentication, if not leave it blank.
image

image
 
Leave it blank, because you setup an account in msdb database.
image
Set Database mail system parameters.
image 

Last, but not least you have to enable sending alerts from SQL Agent.
image

Now, test email settings
USE msdb
GO
EXEC sp_send_dbmail @profile_name=’SQL TEST’,
@recipients=’test@sql.com’,
@subject=TEST,
@body=’Body, Successfully sent an e-mail’

Database Mail, of course saves all outgoing e-mails.
You can check it in msdb database, in tables
sysmail_allitems
sysmail_sentitems
sysmail_unsentitems
sysmail_faileditems
And of course you can check the log in table sysmail_log.
image

Good Luck