Office365 – Email Connectivity Testing through Mozilla Thunderbird

Background

Earlier this weekend got an email that analysts were no longer receiving emails that we had subscribed them to via Microsoft Reporting Services.

Troubleshooting

Log Files

Checked the various log files ( Reporting Services, IIS SMTP).

BTW, we are using IIS SMTP as a Smarthost Relay.

The logs were not illuminating.

 

Mozilla Thunderbird

Tried the same configuration with slight variations.  Actually many more times than I can remember.  And, definitely much more than I will admit.

 

Google

That chick named Google is your friend and so looked for how to configure email clients to use Office 365.

Found some good leads, but nothing worked out.

Configuration

Process

Here is the process we undertook to configure Mozilla Thunderbird to use Microsoft Office 365.

  1. Launch Mozilla Thunderbird
  2. Choose the Account
    • Right click on the Account you have selected
    • And, from the dropdown menu, choose “Settings
  3. Account Settings
    • The “Account Settings” window appears
    • At the bottom of the “Account Settings” window we will observe the “Account Actions” panel
    • One of the choices available in the “Account Actions” panel is the “Add Mail Account” option
    • Please choose the “Add Mail Account” option
  4. “Mail Account” Setup
    • The “Mail Account Setup” window appears
    • Initial Screen
      • It is prefilled with your current system’s full name
      • Please enter your full email address
      • And, password
      • Once entered, please press the Continue button
    • Suggested Configuration
      • Based on the email address entered, Mozilla retrieves the domain name
      • The registered DNS Provider for the domain name is contacted
      • And, asked for MX record
      • Using the MX record, the servers registered for Mail are then contacted
      • If the Mail providers are able to provide mail registration data, communication is started with them
    • Manual Configuration
      • Please click on the manual config button to review or adjust configuration data

Images

Mail Account Setup
Access Account Configuration

Account Settings

Account Settings

Mail Account Setup – 01

Mail Account Setup – 02

Mail Account Setup – Looking up Configuration – Email Provider

Mail Account Setup – Looking up Configuration – Email Provider – Configuration found in Mozilla ISP database

Mail Account Setup – Manual Configuration

Configuration Results

Flow Protocol Server hostname Port SSL Authentication
Incoming IMAP imap-mail.outlook.com 993 SSL/TLS Normal password
Outgoing SMTP smtp-mail.outlook.com 587 STARTTLS Normal password

 

Network Monitoring

Microsoft Network Monitor

Once we were satisfied that we were able to establish communication between our host and the Mail Server, we used Microsoft Network Monitor to review the traffic.

Here is the filters we employed and what we captured via “Microsoft Network Monitor“.

Filter

Textual

ProcessName.Contains(“thunder”)

Image

Network Conversations

 

References

  1. Microsoft
    • Technet
      • Network Monitor Conversation Filtering
        Link

Summary

A good solution was so far away until we discovered that Mozilla Thunderbird is able to communicate with the Provider and request configuration data.

SQL Server – Scripting Database Mail Configurations

Background

Need to make sure that a couple of SQL Server Instances have the same configuration.

 

Sample Code

Sought out sample code on the Net and found this one:

Mohammad Abdul Majeed
Script to Script out EXISTING database mail settings
Link

Code

Outline

What are we scripting…

  1. Database Mail Components
    • Mail Profile
    • Mail Account
    • Mail Profile & Account binding

 

Stored Procedures

email.sp_DatabaseMailSetupScript

Script


use master
go

/*

    email.sp_scriptDatabaseMailSetup

*/

if schema_id('email') is null
begin

    exec('create schema [email] authorization [dbo]');

end
go

if object_id('[email].[sp_scriptDatabaseMailSetup]') is not null
begin

    drop procedure [email].[sp_scriptDatabaseMailSetup]

end
go

if object_id('[email].[sp_DatabaseMailSetupScript]') is null
begin

    exec('create procedure [email].[sp_DatabaseMailSetupScript] as print ''shell'' ')

end
go

alter procedure [email].[sp_DatabaseMailSetupScript] 
(
      @profile  sysname = null
    , @account  sysname = null
)
as
begin

    /*

        Referenced Work

        a) Mohammad Abdul Majeed
           Script to Scipt out EXISTING database mail settings
           https://gallery.technet.microsoft.com/scriptcenter/Script-to-Scipt-out-14a19eda

    */

    set nocount on;
    set XACT_ABORT on;

    declare @CHAR_TAB               varchar(10)
    declare @CHAR_SPACE             varchar(10)
    declare @CHAR_COMMA             varchar(10)
    declare @CHAR_NEWLINE           varchar(10)
    declare @CHAR_NULL              varchar(10)
    declare @CHAR_QUOTE             varchar(10)
    declare @CHAR_ZERO              varchar(10)
    declare @CHAR_COMMENT           varchar(10)
    
    declare @MAIL_PROFILE_CREATE        nvarchar(600)
    declare @MAIL_ACCOUNT_CREATE        nvarchar(4000)
    declare @MAIL_PROFILE_ACCOUNT_BIND  nvarchar(600)

    declare @CHAR_BEGIN             varchar(10)
    declare @CHAR_END               varchar(10)

    set @CHAR_TAB = char(9);
    set @CHAR_NEWLINE = char(13) + char(10);

    set @CHAR_SPACE = ' ';
    set @CHAR_COMMA = ',';
    set @CHAR_NULL = ' null';
    set @CHAR_QUOTE = ''''
    set @CHAR_ZERO = 0
    set @CHAR_COMMENT = '--';

    set @CHAR_BEGIN = 'begin';
    set @CHAR_END = 'end';

    /*
        exec msdb.dbo.sysmail_help_profile_sp
    */
    set @MAIL_PROFILE_CREATE 
            = 'if not exists ( SELECT * FROM msdb.dbo.sysmail_profile WHERE name = ''%s'' ) '
                + @CHAR_BEGIN   
                + @CHAR_TAB
                + ' EXECUTE msdb.dbo.sysmail_add_profile_sp '   
                + @CHAR_TAB + @CHAR_TAB + '   @profile_name = ''%s'' '  
                + @CHAR_TAB + @CHAR_TAB + ' , @description = ''%s'' '   
                --+ @CHAR_TAB
                + @CHAR_END             

    -- use [msdb]; exec sp_help 'dbo.sysmail_add_account_sp' 
    -- exec [msdb].dbo.sysmail_help_account_sp
    set @MAIL_ACCOUNT_CREATE 
            = 'if not exists ( SELECT * FROM msdb.dbo.sysmail_account WHERE name = ''%s'' ) '
                + @CHAR_BEGIN       
                + @CHAR_NEWLINE 
                + ' EXECUTE msdb.dbo.sysmail_add_account_sp '   
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_SPACE + '@account_name = ''%s'' '   
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@email_address = ''%s'' '  
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@display_name  = ''%s'' '  
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@replyto_address  = ''%s'' '
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@description  = ''%s'' '   
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@mailserver_name  = ''%s'' '       
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@mailserver_type  = ''%s'' '   

                /*
                    Error: 50000, Severity: -1, State: 1. (Params:). 
                    The error is printed in terse mode because there was error during formatting. 
                    Tracing, ETW, notifications etc are skipped.  
                */
                --+ @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@port  = ''%s'' '        
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@port  = %d '  
            
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@username  = %s '  
            
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@use_default_credentials  = %d '   
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@enable_ssl  = %d '    

                /*
                    + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@account_id = %d ' 

                */

                + @CHAR_NEWLINE 
            
                + @CHAR_END 

    -- exec msdb..sysmail_configure_sp
    set @MAIL_PROFILE_ACCOUNT_BIND 
            = 'if not exists '
                + ' ( '
                + ' SELECT * '
                + ' FROM   msdb.dbo.sysmail_profileaccount tblSMPA '
                + ' inner join  msdb.dbo.sysmail_profile tblSMP '
                + '       on tblSMPA.[profile_id] = tblSMP.[profile_id] '
                + ' inner join msdb.dbo.sysmail_account tblSMA '
                + '       on tblSMPA.[account_id] = tblSMA.[account_id] '
                + ' WHERE tblSMP.[name] = ''%s'' '
                + ' and   tblSMA.[name] = ''%s'' '
                + ' ) '
                + @CHAR_BEGIN   
                + @CHAR_TAB
                + ' EXECUTE  msdb.dbo.sysmail_add_profileaccount_sp  '  
                + @CHAR_TAB + @CHAR_TAB + '   @profile_name = ''%s'' '  
                + @CHAR_TAB + @CHAR_TAB + ' , @account_name = ''%s'' '  
                + @CHAR_TAB + @CHAR_TAB + ' , @sequence_number = %d '   
                --+ @CHAR_TAB
                + @CHAR_END     

    SELECT 
               = 'Mail Profile'
            , [profileID] = tblSMP.[profile_id]
            , [profileName] = tblSMP.[name]
            , [description] = tblSMP.[description]
            , [profileModifiedBy] = tblSMP.[last_mod_user]
            , [profileModifyDate] = tblSMP.[last_mod_datetime]
            , [script] = FORMATMESSAGE
                            (
                                  @MAIL_PROFILE_CREATE
                                , tblSMP.[name] -- profile name
                                , tblSMP.[name]  -- profile name
                                , isNull(tblSMP.[description], '') -- profile description
                            )

    FROM   msdb.dbo.sysmail_profile tblSMP

    where  tblSMP.[name] = case

                                when ( @profile is null) then tblSMP.[name]
                                when ( @profile = '') then tblSMP.[name]
                                else @profile
                           end       

    -- select * from msdb.dbo.sysmail_server 
    SELECT 
               = 'Mail Account'
            , [accountID] = tblSMA.[account_id]

            , [account] = tblSMA.[name]
            , [emailAddress] = tblSMA.[email_address]

            , [displayName] = tblSMA.[display_name]
            , [replyToAddress] = tblSMA.[replyto_address]

            , [description] = tblSMA.[description]

            , [mailServerName] = tblSMS.[servername]
            , [mailServerType] = tblSMS.servertype
            , [mailServerPort] = tblSMS.[port]

            , [useDefaultCredentials] = tblSMS.use_default_credentials  
            

            , [accountModifiedBy] = tblSMA.[last_mod_user]
            , [accountModifyDate] = tblSMA.[last_mod_datetime]

            , [script] = FORMATMESSAGE
                            (
                                  @MAIL_ACCOUNT_CREATE
                                , tblSMA.[name] -- account name
                                , tblSMA.[name]  -- account name
                                , isNull(tblSMA.[email_address], @CHAR_SPACE) 
                                , isNull(tblSMA.[display_name], @CHAR_SPACE) 
                                , isNull(tblSMA.[replyto_address], @CHAR_SPACE) 
                                , isNull(tblSMA.[description], @CHAR_SPACE) 
                                , isNull(tblSMS.servername, @CHAR_SPACE) 
                                , isNull(tblSMS.servertype, @CHAR_SPACE)
                                , isNull(tblSMS.[port], @CHAR_SPACE)
                             
                                , case
                                    when tblSMS.[username] is null then @CHAR_NULL
                                    else @CHAR_QUOTE + tblSMS.[username] + @CHAR_QUOTE
                                  end

                                  /*
                                    Msg 2748, Level 16, State 1, Line 132
                                    Cannot specify bit data type (parameter 11) as a substitution parameter.
                                  */
                                , case 
                                    when tblSMS.[use_default_credentials] is null then @CHAR_ZERO
                                    else cast(tblSMS.[use_default_credentials] as tinyint)
                                  end
                                    
                                , case

                                    when tblSMS.[enable_ssl] is null then @CHAR_ZERO
                                    else cast(tblSMS.[enable_ssl] as tinyint)
                            
                                  end       

                                /*

                                    , case

                                        when tblSMA.[account_id] is null then @CHAR_ZERO
                                        else tblSMA.[account_id]
                            
                                      end                               

                                */
                                 
                            )

    FROM   msdb.dbo.sysmail_account tblSMA

    LEFT OUTER JOIN msdb.dbo.sysmail_server tblSMS
     
            ON tblSMA.account_id = tblSMS.account_id 

    where  tblSMA.[name] = case

                                when ( @account is null) then tblSMA.[name]
                                when ( @account = '') then tblSMA.[name]
                                else @account
                           end  
                            
    SELECT 
               = 'Mail Profile Account Binding'
            , [profileID] = tblMPA.profile_id
            , [profile] = tblSMP.[name]

            , [accountID] = tblMPA.account_id
            , [account] = tblSMA.[name] 

            , [sequenceNumber] = tblMPA.[sequence_number]
            , [script] = FORMATMESSAGE
                            (
                                 @MAIL_PROFILE_ACCOUNT_BIND

                                , tblSMP.[name] -- profile name
                                , tblSMA.[name] -- account name

                                , tblSMP.[name] -- profile name
                                , tblSMA.[name] -- account name
                             

                                  /*
                                    Msg 2748, Level 16, State 1, Line 132
                                    Cannot specify bit data type (parameter 11) as a substitution parameter.
                                  */
                                , case 
                                    when tblMPA.sequence_number is null then @CHAR_ZERO
                                    else cast(tblMPA.sequence_number as tinyint)
                                  end
                                 
                            )

    FROM msdb.dbo.sysmail_profileaccount tblMPA 
    
    INNER JOIN msdb.dbo.sysmail_profile tblSMP 
        ON tblMPA.profile_id = tblSMP.profile_id 
    
    INNER JOIN msdb.dbo.sysmail_account tblSMA
        ON tblMPA.account_id = tblSMA.account_id

    where  tblSMP.[name] = case

                                when ( @profile is null) then tblSMP.[name]
                                when ( @profile = '') then tblSMP.[name]
                                else @profile
                           end  

    and  tblSMA.[name] = case

                                when ( @account is null) then tblSMA.[name]
                                when ( @account = '') then tblSMA.[name]
                                else @account
                           end  

end
go


 

Invoke



declare @profile sysname
declare @account sysname
 
exec [email].[sp_DatabaseMailSetupScript] 
          @profile = @profile
        , @account = @account


Output

 

Source Control

Github

  1. Project
    • DanielAdeniji/SQLServerDatabaseMailConfiguration
      Link
  2. Files
    • email.sp_DatabaseMailSetupScript.sql
      Link

 

Dedicated

Dedicated to Mohammad Abdul Majeed

SQL Server Agent – Message – “Failed to notify…”

Background

I have been tending to a job that often fails, and wanted to add email notification, for the last couple of weeks.

It is bad that enough that the job itself fails, but worse no email.

 

Monday Morning

Monday Morning is often a good day of the week to make use of another carefree weekend and see where the error lies.

Error Message

Image

 

Textual

Message
The job succeeded. The Job was invoked by Schedule 25 (Every Day). The last step to run was step 12 (Say Completed). NOTE: Failed to notify ‘Daniel Adeniji’ via email.

 

TroubleShooting

SQL Server Agent Error Log

here is what is logged in log file for SQL Server Agent.

Please note we are referencing SQL Server Agent log files, and not SQL Server Engine.

Image

Textual

[476] Database Mail is not enabled for agent notifications. Cannot send e-mail

 

Remediate

Access SQL Server Agent Properties and set SQL Mail…

GUI

Unset

When Unset

Here is how things look before configuring SQL Server Agent Mail Profile

 

Set

Us setting SQL Server Agent Mail Profile

Script

Read SQL Server Agent Properties

Code


declare @useDatabasemail     int
declare @DatabaseMailProfile nvarchar(255)
declare @AgentMailType       int
declare @ServiceStartMode    int
declare @ServiceAccount      nvarchar(512)
declare @AgtGroup            nvarchar(512)

exec master.dbo.xp_instance_regread 
          N'HKEY_LOCAL_MACHINE'
        , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
        , N'UseDatabaseMail'
        , @param = @useDatabasemail OUT
        , @no_output = N'no_output'

exec master.dbo.xp_instance_regread 
          N'HKEY_LOCAL_MACHINE'
        , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
        , N'DatabaseMailProfile'
        , @param = @DatabaseMailProfile OUT
        , @no_output = N'no_output'

exec master.dbo.xp_instance_regread 
          N'HKEY_LOCAL_MACHINE'
        , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
        , N'UseDatabaseMail'
        , @param = @AgentMailType OUT
        , @no_output = N'no_output'



--NOTE: When setting the the services start value, 2 == auto-start, 3 == Don't auto-start
EXEC master.sys.xp_instance_regread 
          'HKEY_LOCAL_MACHINE'
        , 'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT'
        , N'Start'
        , @ServiceStartMode OUTPUT



--declare @ServiceAccount nvarchar(512)
EXEC master.sys.xp_instance_regread
           'HKEY_LOCAL_MACHINE'
         , 'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT'
         , N'ObjectName'
         , @ServiceAccount OUTPUT


exec master.dbo.xp_instance_regread 
              N'HKEY_LOCAL_MACHINE'
            , N'SOFTWARE\Microsoft\MSSQLServer\Setup'
            , N'AGTGroup'
            , @AgtGroup OUTPUT

select 
          [@use_databasemail] = @useDatabasemail
        , [@DatabaseMailProfile] = @DatabaseMailProfile      
        , [@AgentMailType] = @AgentMailType
        , [@ServiceStartMode] = @ServiceStartMode
        -- NOTE: When setting the the services start value, 2 == auto-start, 3 == Don't auto-start
        , [@ServiceStartModeLiteral] 
                = case  
                        when @ServiceStartMode=2  then 'Auto Start'
                        when @ServiceStartMode=3  then 'Don''t Auto Start'
                  end   
        , [@ServiceAccount] = @ServiceAccount
        --, [@AgtGroup] = @AgtGroup

Output

Set SQL Server Agent Properties

Code


USE [msdb]
GO

set nocount on
set XACT_ABORT on
go

declare @sqlInstance                sysname
declare @databaseMailProfileDesired sysname

declare @FORMAT_MESSAGE_SET         nvarchar(600)
declare @message                    nvarchar(600)

declare @databaseMailProfile nvarchar(255)
declare @AgentMailType       int
declare @ServiceStartMode    int
declare @ServiceAccount      nvarchar(512)
declare @AgtGroup            nvarchar(512)

declare @commit              bit

set @commit =1
set @sqlInstance = cast(SERVERPROPERTY('servername') as sysname)
set @databaseMailProfileDesired = N'DBAMail'

set @FORMAT_MESSAGE_SET = 'On %s, setting database mail profile to %s'

/*
    Access Registry (SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent') 
        a) Read Database Mail Profile
*/
exec master.dbo.xp_instance_regread 
          N'HKEY_LOCAL_MACHINE'
        , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
        , N'DatabaseMailProfile'
        , @param = @databaseMailProfile OUT
        , @no_output = N'no_output'

print '@DatabaseMailProfile :' + isNull(@DatabaseMailProfile, '')

begin tran
    

    if  
        ( @DatabaseMailProfile is null )
    begin

        exec master.dbo.xp_sprintf      
                  @message output
                , @FORMAT_MESSAGE_SET
                , @sqlInstance
                , @databaseMailProfileDesired

        print @message

        /*
            Set SQL Sql Server Agent Properties
        */
        EXEC msdb.dbo.sp_set_sqlagent_properties 
                  @email_save_in_sent_folder=1
                , @databasemail_profile=@databaseMailProfileDesired
                , @use_databasemail=1
                , @alert_replace_runtime_tokens=1


    end

while (@@TRANCOUNT > 0)
begin

    if (@commit = 1)
    begin

        print 'commit tran'

        commit tran

    end
    else
    begin

        print 'rollback tran'

        rollback tran

    end
end

GO

Output

Reset SQL Server Agent Properties

To revert changes, please run the script below.


USE [msdb]
GO

EXEC msdb.dbo.sp_set_sqlagent_properties
          @email_profile=N'' 
        , @databasemail_profile=N''
        , @use_databasemail  =0
GO


Listening

Listening to …

Jazmine Sullivan – In Love With Another Man
Link

 

SQL Server – Mail – Granting users access to mail profile

Background

Post setting up a critical SQL Server Agent Job, wanted to setup email alert upon completion; good or bad let someone know.

 

Error

My first attempt led here….

Image

Textual

Msg 14636, Level 16, State 1, Procedure sp_send_dbmail, Line 112 [Batch Start Line 8]
No global profile is configured. Specify a profile name in the @profile_name parameter.


Remediation

Public Profile

Grant all MSDB users permission to database profile.

GUI

Outline

  1. Click on Object Explorer
  2. Choose the the Sql Instance
  3. Navigate to Management \ Database Mail
  4. Right click on Management \ Database Mail and from the drop-down menu, choose Properties
  5. In the “Manage Profile Security” window
    • Keep focused on the “Public Profiles” tab
    • Place a checkmark on all profiles that you will like to make public
  6. In the “Select Configuration Task” window choose “Manage Profile Security”
  7. In the “Manage Profile Security” window, place a checkmark on all profiles that you will like to make public
  8. In the “Complete the Wizard” window, if there are profiles that were previously not public, they are displayed and we are given an opportunity to effect the change

Images

Object Explorer

Database Mail Configuration Wizard – Select Configuration Task

Database Mail Configuration Wizard – Manage Profile Security – Public Profiles – Viewing

We create public profiles by checking the public checkbox on the profile.

Database Mail Configuration Wizard – Manage Profile Security – Public Profiles – Configuring

SQL

SQL – Syntax


EXEC msdb.dbo.sysmail_add_principalprofile_sp
        @principal_name=N'guest'
      , @profile_name=[profile_name]
      , @is_default=0

SQL – Sample


EXEC msdb.dbo.sysmail_add_principalprofile_sp
        @principal_name=N'guest'
      , @profile_name=N'SQL Server Notification Service'
      , @is_default=0

 

Private Profile

Grant specific MSDB user permission to database profile.

Outline

  1. Click on Object Explorer
  2. Choose the the Sql Instance
  3. Navigate to Management \ Database Mail
  4. Right click on Management \ Database Mail and from the drop-down menu, choose Properties
  5. In the “Select Configuration Task” window choose “Manage Profile Security”
  6. In the “Manage Profile Security” window
    • Access the “Private Profiles” tab
    • Place a checkmark on all profiles that you will like to make public
  7. In the “Complete the Wizard” window, if there are profiles that were previously not public, they are displayed and we are given an opportunity to effect the change

 

GUI

Images

Object Explorer

Database Mail Configuration Wizard – Manage Profile Security – Private Profiles – Viewing

We create private profiles by accessing the “Private Profiles” tab, choosing the msdb user that we will like augmented, and checking each private profiles we will like to grant access to.

SQL – Syntax


EXEC msdb.dbo.sysmail_add_principalprofile_sp
        @principal_name=[account]
      , @profile_name=[profile_name]
      , @is_default=0

SQL – Sample


EXEC msdb.dbo.sysmail_add_principalprofile_sp 
          @principal_name=N'NT SERVICE\ReportServer'
        , @profile_name=N'SQL Server Notification Service'
        , @is_default=0

Review Mail Profile User Security Permisions

Issue SQL to check the msdb.dbo.sysmail_principalprofile table.

This table binds each principal to registered profile.


select 
		  [profile]
			= tblSMP.[name]

                , [profileID]
			= tblSMPP.profile_id

		, [principalID]	
			= tblSMPP.principal_sid

		, [principal] 
			= tblSDP.[name]
			
		, [isDefault]
			 = tblSMPP.is_default

		, [modifiedBy] 
			= tblSMPP.last_mod_user

		, [modifiedOn] 
			= convert
				(
					  varchar(30)
					, tblSMPP.last_mod_datetime
					, 100
				)

from  [msdb].[dbo].[sysmail_profile] tblSMP

inner join [msdb].[dbo].[sysmail_principalprofile] tblSMPP

	on tblSMP.[profile_id] = tblSMPP.profile_id

inner join sys.database_principals tblSDP

	on tblSMPP.principal_sid = tblSDP.[sid]

Image

 

References

  1. Microsoft
    • Database Mail Stored Procedures ( Transact-SQL )
    • Add Principal Profile
      • sysmail-add-principalprofile-sp ( Transact-SQL )
    • Update Principal Profile
      • sysmail_update_principalprofile_sp ( Transact-SQL )
  2.  SQLServerCentral.com
    • Home»SQL Server 2008»SQL Server 2008 – General»Converting User SID Binary to String TSQL
      Link

 

 

Sql Server – SQL Server Agent – Optimizing DB Traffic

Background

In our last post, we spoke about how MSDB is responsible for some of our Single use queries.

Let us dig more in by identifying some of those queries and see if we can change, modify, or reduce their frequency.

 

MSDB – Queries

We have identified a couple of queries and here they are:

  1. msdb.dbo.sp_sqlagent_get_perf_counters
  2. msdb.dbo.sysmail_help_profile_*

 

SQL – msdb.dbo.sp_sqlagent_get_perf_counters

SQL Server Profiler

SQL Server Profiler – Captured

Image

sqlserverprofiler-20160908-0334pm

 

Textual

  1. Text Data :- EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
  2. Application Name: – SQLAgent – Alert Engine

 

SQL Server Profiler – Explanation

  1. SQL Server Agent issues “EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters”
    • Every 20 seconds

 

Remediation

Steps

  1. Script out all current SQL Server Agent Alerts
  2. Remove all the alerts
    • Please keep in mind that it is not enough to disable them
Before

sqlserveragentalerts-20160908-0254pm

 

SQL Server Agent – Mail

SQL Server Profiler

Image

sqlserverprofiler-20160908-0426pm

Textual


exec msdb.dbo.sysmail_help_profile_sp @profile_name=N'Local Relay Server'
exec msdb.dbo.sysmail_help_profileaccount_sp @profile_id=1
exec msdb.dbo.sysmail_help_admin_account_sp @account_id=1

SQL Server Profiler – Explanation

  1. SQL Server Agent issues “EXECUTE msdb.dbo.sysmail_help*” commands
    • Every 5 minutes

Remediation

Steps

  1. Access SQL Server Agent Properties
  2. Access the “Alert System” Tab
  3. Disable “Enable Email profile”
Before

alertsystem-before

 

After
alertsystem-after

 

SQL Server Profiler ( After)

Image

sqlserverprofiler-20160908-0437pm

Textual


exec msdb.dbo.sysmail_help_profile_sp @profile_name=N''

SQL Server Profiler – Explanation

  1. SQL Server Agent issues “EXECUTE msdb.dbo.sysmail_help_profile_sp” commands
    • Every 5 minutes
    • Captured Output
      • Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42 profile name is not valid 

Summary

If you are not using SQL Server Agent for alerts and email notification services, you might be able to get away with removing alerts and disabling the email profile setup for SQL Server Agent.

Again, please help in mind we are specifically targeting a v2005 SQL instance.