SqlDatabaseMail: Unable to create two DB mail profiles #1871

TerminatorShoes · 2023-03-10T13:40:48Z

Problem description

I tried the following configuration code which is unable to create the two profiles declared. The second profile is corrupt with the message “Object reference not set to an object. (DatabaseMailWizard)”, when trying to access it. The second profile is failing and is corrupt.

  if ( $node.qDatabaseMail_enable ) {

 

            SqlConfiguration 'EnableDatabaseMailXPs'

            {

                ServerName     = $Node.NodeName;

                InstanceName   = $node.NLH_SQLInstance;

                OptionName     = 'Database Mail XPs';

                OptionValue    = 1;

                RestartService = $false;

            }

 

            SqlDatabaseMail 'CreateNLHSQLAlertsProfile'

            {

                Ensure         = 'Present';

                ServerName     = $Node.NodeName;

                InstanceName   = $node.NLH_SQLInstance;

                AccountName    = "NLH SQL Alerts";

                ProfileName    = "NLH SQL Alerts";

                EmailAddress   = sqlalerts@northernlight.org;

                ReplyToAddress = noreply@northernlight.org;

                DisplayName    = "NLH SQL Alerts "  + $node.NLH_SQLInstance;

                MailServerName = $Node.MailRelayServer;

                Description    = 'This profile will be used to alert NLH SQL Personnel.';

                LoggingLevel   = 'Extended';

                TcpPort        = 25;

                PsDscRunAsCredential = $SqlNLHsqlAdmin;

            }

 

SqlDatabaseMail 'CreateNLHSQLMaintNotifyProfile'

            {

                Ensure         = 'Present';

                ServerName     = $Node.NodeName;

                InstanceName   = $node.NLH_SQLInstance;

                AccountName    = "NLH SQL Maint Notify ";

                ProfileName    = "NLH SQL Maint Notify ";

                EmailAddress   = sqlmaintnotify@northernlight.org;

                ReplyToAddress = noreply@northernlight.org;

                DisplayName    = "NLH SQL Maint Notify " + $node.NLH_SQLInstance;

                MailServerName = $Node.MailRelayServer;

                Description    = 'This profile will be used to alert SQL Maint Personnel.';

                LoggingLevel   = 'Extended';

                TcpPort        = 25;

                PsDscRunAsCredential = $SqlNLHsqlAdmin;

            }

 

        }

Verbose logs

VERBOSE: [AMSQLDEV02]: LCM:  [ Start  Resource ]  [[SqlConfiguration]EnableDatabaseMailXPs]

VERBOSE: [AMSQLDEV02]: LCM:  [ Start  Test     ]  [[SqlConfiguration]EnableDatabaseMailXPs]

VERBOSE: [AMSQLDEV02]:                            [[SqlConfiguration]EnableDatabaseMailXPs] Found PowerShell module SqlServer already imported in the session. (SQLCOMMON0026)

VERBOSE: [AMSQLDEV02]:                            [[SqlConfiguration]EnableDatabaseMailXPs] Connecting as current user 'NT AUTHORITY\SYSTEM' using integrated security. (SQLCOMMON0054)

VERBOSE: [AMSQLDEV02]:                            [[SqlConfiguration]EnableDatabaseMailXPs] Connected to SQL instance 'AMSQLDEV02\PROJECT3'. (SQLCOMMON0018)

VERBOSE: [AMSQLDEV02]:                            [[SqlConfiguration]EnableDatabaseMailXPs] Configuration option 'Database Mail XPs' has a current value of '1'.

VERBOSE: [AMSQLDEV02]:                            [[SqlConfiguration]EnableDatabaseMailXPs] Configuration option 'Database Mail XPs' is in desired state.

VERBOSE: [AMSQLDEV02]: LCM:  [ End    Test     ]  [[SqlConfiguration]EnableDatabaseMailXPs] True in 0.0470 seconds.

VERBOSE: [AMSQLDEV02]: LCM:  [ End    Resource ]  [[SqlConfiguration]EnableDatabaseMailXPs]

VERBOSE: [AMSQLDEV02]: LCM:  [ Start  Resource ]  [[SqlDatabaseMail]CreateNLHSQLAlertsProfile]

VERBOSE: [AMSQLDEV02]: LCM:  [ Start  Test     ]  [[SqlDatabaseMail]CreateNLHSQLAlertsProfile]

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLAlertsProfile] Connecting to SQL Server instance 'AMSQLDEV02\PROJECT3'.

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLAlertsProfile] Preferred module SqlServer found. (SQLCOMMON0023)

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLAlertsProfile] Importing PowerShell module 'SqlServer' with version '21.1.18256' from path 'C:\Program Files\Wi

ndowsPowerShell\Modules\SqlServer\21.1.18256\SqlServer.psm1'. (SQLCOMMON0025)

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLAlertsProfile] Connecting as current user 'ADMIN\daisanm2' using integrated security. (SQLCOMMON0054)

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLAlertsProfile] Connected to SQL instance 'AMSQLDEV02\PROJECT3'. (SQLCOMMON0018)

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLAlertsProfile] SQL Server Database Mail is enabled. Database Mail XPs are set to 1.

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLAlertsProfile] Account name 'NLH SQL Alerts' was found, returning the current state of the Database Mail config

uration.

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLAlertsProfile] Determines if the Database Mail is in the desired state.

VERBOSE: [AMSQLDEV02]: LCM:  [ End    Test     ]  [[SqlDatabaseMail]CreateNLHSQLAlertsProfile] True in 3.7030 seconds.

VERBOSE: [AMSQLDEV02]: LCM:  [ End    Resource ]  [[SqlDatabaseMail]CreateNLHSQLAlertsProfile]

VERBOSE: [AMSQLDEV02]: LCM:  [ Start  Resource ]  [[SqlDatabaseMail]CreateNLHSQLMaintNotifyProfile]

VERBOSE: [AMSQLDEV02]: LCM:  [ Start  Test     ]  [[SqlDatabaseMail]CreateNLHSQLMaintNotifyProfile]

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLMaintNotifyProfile] Connecting to SQL Server instance 'AMSQLDEV02\PROJECT3'.

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLMaintNotifyProfile] Found PowerShell module SqlServer already imported in the session. (SQLCOMMON0026)

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLMaintNotifyProfile] Connecting as current user 'ADMIN\daisanm2' using integrated security. (SQLCOMMON0054)

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLMaintNotifyProfile] Connected to SQL instance 'AMSQLDEV02\PROJECT3'. (SQLCOMMON0018)

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLMaintNotifyProfile] SQL Server Database Mail is enabled. Database Mail XPs are set to 1.

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLMaintNotifyProfile] Account name 'NLH SQL Maint Notify ' was found, returning the current state of the Database

Mail configuration.

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLMaintNotifyProfile] Determines if the Database Mail is in the desired state.

VERBOSE: [AMSQLDEV02]: LCM:  [ End    Test     ]  [[SqlDatabaseMail]CreateNLHSQLMaintNotifyProfile] False in 0.9220 seconds.

VERBOSE: [AMSQLDEV02]: LCM:  [ End    Resource ]  [[SqlDatabaseMail]CreateNLHSQLMaintNotifyProfile]

DSC configuration

if ( $node.qDatabaseMail_enable ) {

 

            SqlConfiguration 'EnableDatabaseMailXPs'

            {

                ServerName     = $Node.NodeName;

                InstanceName   = $node.NLH_SQLInstance;

                OptionName     = 'Database Mail XPs';

                OptionValue    = 1;

                RestartService = $false;

            }

 

            SqlDatabaseMail 'CreateNLHSQLAlertsProfile'

            {

                Ensure         = 'Present';

                ServerName     = $Node.NodeName;

                InstanceName   = $node.NLH_SQLInstance;

                AccountName    = "NLH SQL Alerts";

                ProfileName    = "NLH SQL Alerts";

                EmailAddress   = sqlalerts@northernlight.org;

                ReplyToAddress = noreply@northernlight.org;

                DisplayName    = "NLH SQL Alerts "  + $node.NLH_SQLInstance;

                MailServerName = $Node.MailRelayServer;

                Description    = 'This profile will be used to alert NLH SQL Personnel.';

                LoggingLevel   = 'Extended';

                TcpPort        = 25;

                PsDscRunAsCredential = $SqlNLHsqlAdmin;

            }

 

SqlDatabaseMail 'CreateNLHSQLMaintNotifyProfile'

            {

                Ensure         = 'Present';

                ServerName     = $Node.NodeName;

                InstanceName   = $node.NLH_SQLInstance;

                AccountName    = "NLH SQL Maint Notify ";

                ProfileName    = "NLH SQL Maint Notify ";

                EmailAddress   = sqlmaintnotify@northernlight.org;

                ReplyToAddress = noreply@northernlight.org;

                DisplayName    = "NLH SQL Maint Notify " + $node.NLH_SQLInstance;

                MailServerName = $Node.MailRelayServer;

                Description    = 'This profile will be used to alert SQL Maint Personnel.';

                LoggingLevel   = 'Extended';

                TcpPort        = 25;

                PsDscRunAsCredential = $SqlNLHsqlAdmin;

            }

 

        }

SQL Server edition and version

Microsoft SQL Server 2019 (RTM-CU18-GDR) (KB5021124) - 15.0.4280.7 (X64)   Jan 23 2023 12:37:13   Copyright (C) 2019 Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

SQL Server PowerShell modules

Import-Module ActiveDirectory;

    Import-DscResource -ModuleName PSDesiredStateConfiguration;

    Import-DscResource -ModuleName SqlServerDsc;

Operating system

Windows Server 2019

PowerShell version

Name                           Value                                                                                                                                                         

----                           -----                                                                                                                                                          

PSVersion                      5.1.17763.3770                                                                                                                                                 

PSEdition                      Desktop                                                                                                                                                       

PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}                                                                                                                                       

BuildVersion                   10.0.17763.3770                                                                                                                                                

CLRVersion                     4.0.30319.42000                                                                                                                                                

WSManStackVersion              3.0                                                                                                                                                           

PSRemotingProtocolVersion      2.3                                                                                                                                                           

SerializationVersion           1.1.0.1

SqlServerDsc version

SqlServerDsc 16.0.0  C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\16.0.0\SqlServerDsc.psd1

The text was updated successfully, but these errors were encountered:

johlju · 2023-03-10T17:38:21Z

Is it possible for you to add the verbose logs that shows the error being thrown.

That configuration could be added to the integration tests to see if it can reproduce the problem. 🤔

TerminatorShoes · 2023-03-10T18:43:44Z

I'm sorry, thought I already included them Here they are.

VERBOSE: [AMSQLDEV02]: LCM:  [ Start  Resource ]  [[SqlConfiguration]EnableDatabaseMailXPs]

VERBOSE: [AMSQLDEV02]: LCM:  [ Start  Test     ]  [[SqlConfiguration]EnableDatabaseMailXPs]

VERBOSE: [AMSQLDEV02]:                            [[SqlConfiguration]EnableDatabaseMailXPs] Found PowerShell module SqlServer already imported in the session. (SQLCOMMON0026)

VERBOSE: [AMSQLDEV02]:                            [[SqlConfiguration]EnableDatabaseMailXPs] Connecting as current user 'NT AUTHORITY\SYSTEM' using integrated security. (SQLCOMMON0054)

VERBOSE: [AMSQLDEV02]:                            [[SqlConfiguration]EnableDatabaseMailXPs] Connected to SQL instance 'AMSQLDEV02\PROJECT3'. (SQLCOMMON0018)

VERBOSE: [AMSQLDEV02]:                            [[SqlConfiguration]EnableDatabaseMailXPs] Configuration option 'Database Mail XPs' has a current value of '1'.

VERBOSE: [AMSQLDEV02]:                            [[SqlConfiguration]EnableDatabaseMailXPs] Configuration option 'Database Mail XPs' is in desired state.

VERBOSE: [AMSQLDEV02]: LCM:  [ End    Test     ]  [[SqlConfiguration]EnableDatabaseMailXPs] True in 0.0470 seconds.

VERBOSE: [AMSQLDEV02]: LCM:  [ End    Resource ]  [[SqlConfiguration]EnableDatabaseMailXPs]

VERBOSE: [AMSQLDEV02]: LCM:  [ Start  Resource ]  [[SqlDatabaseMail]CreateNLHSQLAlertsProfile]

VERBOSE: [AMSQLDEV02]: LCM:  [ Start  Test     ]  [[SqlDatabaseMail]CreateNLHSQLAlertsProfile]

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLAlertsProfile] Connecting to SQL Server instance 'AMSQLDEV02\PROJECT3'.

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLAlertsProfile] Preferred module SqlServer found. (SQLCOMMON0023)

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLAlertsProfile] Importing PowerShell module 'SqlServer' with version '21.1.18256' from path 'C:\Program Files\Wi

ndowsPowerShell\Modules\SqlServer\21.1.18256\SqlServer.psm1'. (SQLCOMMON0025)

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLAlertsProfile] Connecting as current user 'ADMIN\daisanm2' using integrated security. (SQLCOMMON0054)

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLAlertsProfile] Connected to SQL instance 'AMSQLDEV02\PROJECT3'. (SQLCOMMON0018)

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLAlertsProfile] SQL Server Database Mail is enabled. Database Mail XPs are set to 1.

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLAlertsProfile] Account name 'NLH SQL Alerts' was found, returning the current state of the Database Mail config

uration.

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLAlertsProfile] Determines if the Database Mail is in the desired state.

VERBOSE: [AMSQLDEV02]: LCM:  [ End    Test     ]  [[SqlDatabaseMail]CreateNLHSQLAlertsProfile] True in 3.7030 seconds.

VERBOSE: [AMSQLDEV02]: LCM:  [ End    Resource ]  [[SqlDatabaseMail]CreateNLHSQLAlertsProfile]

VERBOSE: [AMSQLDEV02]: LCM:  [ Start  Resource ]  [[SqlDatabaseMail]CreateNLHSQLMaintNotifyProfile]

VERBOSE: [AMSQLDEV02]: LCM:  [ Start  Test     ]  [[SqlDatabaseMail]CreateNLHSQLMaintNotifyProfile]

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLMaintNotifyProfile] Connecting to SQL Server instance 'AMSQLDEV02\PROJECT3'.

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLMaintNotifyProfile] Found PowerShell module SqlServer already imported in the session. (SQLCOMMON0026)

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLMaintNotifyProfile] Connecting as current user 'ADMIN\daisanm2' using integrated security. (SQLCOMMON0054)

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLMaintNotifyProfile] Connected to SQL instance 'AMSQLDEV02\PROJECT3'. (SQLCOMMON0018)

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLMaintNotifyProfile] SQL Server Database Mail is enabled. Database Mail XPs are set to 1.

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLMaintNotifyProfile] Account name 'NLH SQL Maint Notify ' was found, returning the current state of the Database

Mail configuration.

VERBOSE: [AMSQLDEV02]:                            [[SqlDatabaseMail]CreateNLHSQLMaintNotifyProfile] Determines if the Database Mail is in the desired state.

VERBOSE: [AMSQLDEV02]: LCM:  [ End    Test     ]  [[SqlDatabaseMail]CreateNLHSQLMaintNotifyProfile] False in 0.9220 seconds.

VERBOSE: [AMSQLDEV02]: LCM:  [ End    Resource ]  [[SqlDatabaseMail]CreateNLHSQLMaintNotifyProfile]

johlju · 2023-03-10T19:42:11Z

Ah, sorry. I misread. I thought you got the error you mentioned when you run the configuration. Now I understand that the configuration passes without any error.

That is strange. Have you run the same configuration on two different instances to verify it happens every time?

TerminatorShoes · 2023-03-10T19:57:09Z

Thank you. On the very last three lines of the verbose above say:

VERBOSE: [AMSQLDEV02]: [[SqlDatabaseMail]CreateNLHSQLMaintNotifyProfile] Determines if the Database Mail is in the desired state.

VERBOSE: [AMSQLDEV02]: LCM: [ End Test ] [[SqlDatabaseMail]CreateNLHSQLMaintNotifyProfile] False in 0.9220 seconds.

VERBOSE: [AMSQLDEV02]: LCM: [ End Resource ] [[SqlDatabaseMail]CreateNLHSQLMaintNotifyProfile]

So that it shows the failure but continues anyways.

Yes, I've run it a good dozen times, with a failure each time. I even tried pulling the 2nd profile into its own block. The 2nd one is created but is corrupt. Is there anyone that can try to replicate this finding? We have lots of servers that need both profiles.

johlju · 2023-03-10T20:18:06Z

Ah, you mean Test-method returns False when it avoids return True.

I see you have a space at the end of the string in these two properties.

AccountName    = "NLH SQL Maint Notify ";
ProfileName    = "NLH SQL Maint Notify ";

If you change it too "NLH SQL Maint Notify" without a space at the end, does it still fail?

It was a difference from the previous one, so just to make sure it is not the problem.

TerminatorShoes · 2023-03-10T20:38:10Z

I'll give it a try now. Thank you.

TerminatorShoes · 2023-03-10T20:40:16Z

Yes, I just tried it and it failed. :(

johlju · 2023-03-10T20:55:33Z

It was worth a try, just to exclude it.

Suggest extend the integration tests in a PR (Pull Request) to see if it is possible to reproduce the problem using the pipeline.

TerminatorShoes · 2023-03-10T21:15:57Z

I'm not doing pulls just pushes. I'm not setup for pulls. The problem is easily replicated. Do I have any other course of action to fix this?

johlju · 2023-03-11T08:54:36Z

Ah sorry, I meant a GitHub Pull Request - contributing the configuration to the integration tests of this repo.

Adding the configuration to this file: https://github.com/dsccommunity/SqlServerDsc/blob/main/tests/Integration/DSC_SqlDatabaseMail.config.ps1

And testing the outcome in this file: https://github.com/dsccommunity/SqlServerDsc/blob/main/tests/Integration/DSC_SqlDatabaseMail.Integration.Tests.ps1

Similar to how the other exiting configs are tested.

johlju · 2023-04-25T10:35:41Z

Confirmed that Test-method fails with the configuration added to the integration tests in PR #1922.

Test run: https://dev.azure.com/dsccommunity/SqlServerDsc/_build/results?buildId=7880&view=logs&j=7f8ffb2d-a721-50ac-0305-0cc716ec6d31&t=d1c503ce-7ddb-556b-36cb-7351fe3a405c&l=5447

Output:

2023-04-25T07:48:36.4019899Z VERBOSE: Perform operation 'Invoke CimMethod' with following parameters, ''methodName' = TestConfiguration,'className' 
2023-04-25T07:48:36.4020969Z = MSFT_DSCLocalConfigurationManager,'namespaceName' = root/Microsoft/Windows/DesiredStateConfiguration'.
2023-04-25T07:48:36.4112418Z VERBOSE: An LCM method call arrived from computer WIN-3JJ94FIRM5Q with user sid 
2023-04-25T07:48:36.4115616Z S-1-5-21-3804164693-1563980585-975061933-500.
2023-04-25T07:48:36.4174729Z VERBOSE: [WIN-3JJ94FIRM5Q]: LCM:  [ Start  Test     ]
2023-04-25T07:48:36.5224677Z VERBOSE: [WIN-3JJ94FIRM5Q]: LCM:  [ Start  Resource ]  [[SqlDatabaseMail]CreateAlertsProfile]
2023-04-25T07:48:36.5528077Z VERBOSE: [WIN-3JJ94FIRM5Q]: LCM:  [ Start  Test     ]  [[SqlDatabaseMail]CreateAlertsProfile]
2023-04-25T07:48:38.3927810Z VERBOSE: [WIN-3JJ94FIRM5Q]:                            [[SqlDatabaseMail]CreateAlertsProfile] Connecting to SQL Server 
2023-04-25T07:48:38.3934900Z instance 'WIN-3JJ94FIRM5Q\DSCSQLTEST'.
2023-04-25T07:48:41.8161390Z VERBOSE: [WIN-3JJ94FIRM5Q]:                            [[SqlDatabaseMail]CreateAlertsProfile] Connecting as current 
2023-04-25T07:48:41.8162145Z user 'WIN-3JJ94FIRM5Q\SqlInstall' using integrated security. (SQLCOMMON0054)
2023-04-25T07:48:41.8179586Z VERBOSE: [WIN-3JJ94FIRM5Q]:                            [[SqlDatabaseMail]CreateAlertsProfile] Connected to SQL instance
2023-04-25T07:48:41.8180511Z  'WIN-3JJ94FIRM5Q\DSCSQLTEST'. (SQLCOMMON0018)
2023-04-25T07:48:41.8207049Z VERBOSE: [WIN-3JJ94FIRM5Q]:                            [[SqlDatabaseMail]CreateAlertsProfile] SQL Server Database Mail 
2023-04-25T07:48:41.8208046Z is enabled. Database Mail XPs are set to 1.
2023-04-25T07:48:41.8236354Z VERBOSE: [WIN-3JJ94FIRM5Q]:                            [[SqlDatabaseMail]CreateAlertsProfile] Account name 'Company SQL
2023-04-25T07:48:41.8239345Z  Alerts' was found, returning the current state of the Database Mail configuration.
2023-04-25T07:48:41.8259366Z VERBOSE: [WIN-3JJ94FIRM5Q]:                            [[SqlDatabaseMail]CreateAlertsProfile] Determines if the 
2023-04-25T07:48:41.8261053Z Database Mail is in the desired state.
2023-04-25T07:48:41.8340234Z VERBOSE: [WIN-3JJ94FIRM5Q]: LCM:  [ End    Test     ]  [[SqlDatabaseMail]CreateAlertsProfile] True in 5.2850 seconds.
2023-04-25T07:48:41.8370704Z VERBOSE: [WIN-3JJ94FIRM5Q]: LCM:  [ End    Resource ]  [[SqlDatabaseMail]CreateAlertsProfile]
2023-04-25T07:48:41.8399025Z VERBOSE: [WIN-3JJ94FIRM5Q]: LCM:  [ Start  Resource ]  [[SqlDatabaseMail]CreateMaintenanceNotifyProfile]
2023-04-25T07:48:41.8641113Z VERBOSE: [WIN-3JJ94FIRM5Q]: LCM:  [ Start  Test     ]  [[SqlDatabaseMail]CreateMaintenanceNotifyProfile]
2023-04-25T07:48:42.0402881Z VERBOSE: [WIN-3JJ94FIRM5Q]:                            [[SqlDatabaseMail]CreateMaintenanceNotifyProfile] Connecting to 
2023-04-25T07:48:42.0403608Z SQL Server instance 'WIN-3JJ94FIRM5Q\DSCSQLTEST'.
2023-04-25T07:48:42.0411295Z VERBOSE: [WIN-3JJ94FIRM5Q]:                            [[SqlDatabaseMail]CreateMaintenanceNotifyProfile] Connecting as 
2023-04-25T07:48:42.0413269Z current user 'WIN-3JJ94FIRM5Q\SqlInstall' using integrated security. (SQLCOMMON0054)
2023-04-25T07:48:42.0420705Z VERBOSE: [WIN-3JJ94FIRM5Q]:                            [[SqlDatabaseMail]CreateMaintenanceNotifyProfile] Connected to 
2023-04-25T07:48:42.0421376Z SQL instance 'WIN-3JJ94FIRM5Q\DSCSQLTEST'. (SQLCOMMON0018)
2023-04-25T07:48:42.0445467Z VERBOSE: [WIN-3JJ94FIRM5Q]:                            [[SqlDatabaseMail]CreateMaintenanceNotifyProfile] SQL Server 
2023-04-25T07:48:42.0446462Z Database Mail is enabled. Database Mail XPs are set to 1.
2023-04-25T07:48:42.0478206Z VERBOSE: [WIN-3JJ94FIRM5Q]:                            [[SqlDatabaseMail]CreateMaintenanceNotifyProfile] Account name 
2023-04-25T07:48:42.0479448Z 'Company SQL Maintenance Notify' was found, returning the current state of the Database Mail configuration.
2023-04-25T07:48:42.0486744Z VERBOSE: [WIN-3JJ94FIRM5Q]:                            [[SqlDatabaseMail]CreateMaintenanceNotifyProfile] Determines if 
2023-04-25T07:48:42.0487582Z the Database Mail is in the desired state.
2023-04-25T07:48:42.0564481Z VERBOSE: [WIN-3JJ94FIRM5Q]: LCM:  [ End    Test     ]  [[SqlDatabaseMail]CreateMaintenanceNotifyProfile] False in 
2023-04-25T07:48:42.0565351Z 0.1880 seconds.
2023-04-25T07:48:42.0577072Z VERBOSE: [WIN-3JJ94FIRM5Q]: LCM:  [ End    Resource ]  [[SqlDatabaseMail]CreateMaintenanceNotifyProfile]
2023-04-25T07:48:42.0704531Z VERBOSE: [WIN-3JJ94FIRM5Q]: LCM:  [ End    Test     ]     Completed processing test operation. The operation returned 
2023-04-25T07:48:42.0705784Z False.
2023-04-25T07:48:42.0706086Z VERBOSE: [WIN-3JJ94FIRM5Q]: LCM:  [ End    Test     ]    in  5.6590 seconds.
2023-04-25T07:48:42.1016729Z VERBOSE: Operation 'Invoke CimMethod' complete.
2023-04-25T07:48:42.1039666Z VERBOSE: Time taken for configuration job to complete is 5.704 seconds
2023-04-25T07:48:42.4874394Z ##[error]    [-] Should return $true when Test-DscConfiguration is run 5.82s (5.82s|3ms)�[0m
2023-04-25T07:48:42.5306857Z ##[error]     Expected strings to be the same, but they were different.�[0m
2023-04-25T07:48:42.5307194Z ##[error]     Expected length: 4�[0m
2023-04-25T07:48:42.5307355Z ##[error]     Actual length:   5�[0m
2023-04-25T07:48:42.5307559Z ##[error]     Strings differ at index 0.�[0m
2023-04-25T07:48:42.5307927Z ##[error]     Expected: 'True'�[0m
2023-04-25T07:48:42.5308099Z ##[error]     But was:  'False'�[0m
2023-04-25T07:48:42.5308237Z ##[error]                ^�[0m
2023-04-25T07:48:42.5316695Z ##[error]     at Test-DscConfiguration -Verbose | Should -Be 'True', D:\a\1\s\tests\Integration\DSC_SqlDatabaseMail.Integration.Tests.ps1:162�[0m
2023-04-25T07:48:42.5317250Z ##[error]     at Invoke-Assertion, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.psm1:8140�[0m
2023-04-25T07:48:42.5317522Z ##[error]     at Should<End>, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.psm1:8078�[0m
2023-04-25T07:48:42.5317834Z ##[error]     at <ScriptBlock>, D:\a\1\s\tests\Integration\DSC_SqlDatabaseMail.Integration.Tests.ps1:162�[0m
2023-04-25T07:48:42.5318115Z ##[error]     at <ScriptBlock>, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.psm1:1988�[0m
2023-04-25T07:48:42.5318420Z ##[error]     at <ScriptBlock>, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.psm1:1949�[0m
2023-04-25T07:48:42.5318710Z ##[error]     at Invoke-ScriptBlock, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.psm1:2121�[0m
2023-04-25T07:48:42.5319016Z ##[error]     at Invoke-TestItem, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.psm1:1184�[0m
2023-04-25T07:48:42.5319278Z ##[error]     at Invoke-Block, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.psm1:820�[0m
2023-04-25T07:48:42.5319575Z ##[error]     at <ScriptBlock>, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.psm1:878�[0m
2023-04-25T07:48:42.5319831Z ##[error]     at <ScriptBlock>, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.psm1:1988�[0m
2023-04-25T07:48:42.5320132Z ##[error]     at <ScriptBlock>, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.psm1:1949�[0m
2023-04-25T07:48:42.5320396Z ##[error]     at Invoke-ScriptBlock, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.psm1:2124�[0m
2023-04-25T07:48:42.5320694Z ##[error]     at Invoke-Block, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.psm1:925�[0m
2023-04-25T07:48:42.5321030Z ##[error]     at <ScriptBlock>, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.psm1:878�[0m
2023-04-25T07:48:42.5331901Z ##[error]     at <ScriptBlock>, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.psm1:1988�[0m
2023-04-25T07:48:42.5347781Z ##[error]     at <ScriptBlock>, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.psm1:1949�[0m
2023-04-25T07:48:42.5363125Z ##[error]     at Invoke-ScriptBlock, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.psm1:2124�[0m
2023-04-25T07:48:42.5379447Z ##[error]     at Invoke-Block, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.psm1:925�[0m
2023-04-25T07:48:42.5394535Z ##[error]     at <ScriptBlock>, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.psm1:878�[0m
2023-04-25T07:48:42.5410219Z ##[error]     at <ScriptBlock>, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.psm1:1988�[0m
2023-04-25T07:48:42.5425558Z ##[error]     at <ScriptBlock>, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.psm1:1949�[0m
2023-04-25T07:48:42.5442325Z ##[error]     at Invoke-ScriptBlock, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.psm1:2124�[0m
2023-04-25T07:48:42.5458667Z ##[error]     at Invoke-Block, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.psm1:925�[0m
2023-04-25T07:48:42.5476309Z ##[error]     at <ScriptBlock>, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.psm1:1662�[0m
2023-04-25T07:48:42.5499457Z ##[error]     at <ScriptBlock>, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.ps1:3�[0m
2023-04-25T07:48:42.5515370Z ##[error]     at <ScriptBlock>, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.psm1:3172�[0m
2023-04-25T07:48:42.5531030Z ##[error]     at Invoke-InNewScriptScope, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.psm1:3179�[0m
2023-04-25T07:48:42.5546301Z ##[error]     at Run-Test, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.psm1:1665�[0m
2023-04-25T07:48:42.5562066Z ##[error]     at Invoke-Test, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.psm1:2476�[0m
2023-04-25T07:48:42.5577207Z ##[error]     at Invoke-Pester<End>, D:\a\1\s\output\RequiredModules\Pester\5.4.1\Pester.psm1:5284�[0m
2023-04-25T07:48:42.5635435Z ##[error]     at <ScriptBlock>, D:\a\1\s\output\RequiredModules\Sampler\0.116.5\tasks\Invoke-Pester.pester.build.ps1:906�[0m
2023-04-25T07:48:42.5658817Z ##[error]     at *Task, D:\a\1\s\output\RequiredModules\InvokeBuild\5.10.3\Invoke-Build.ps1:590�[0m
2023-04-25T07:48:42.5675183Z ##[error]     at *Task, D:\a\1\s\output\RequiredModules\InvokeBuild\5.10.3\Invoke-Build.ps1:562�[0m
2023-04-25T07:48:42.5690938Z ##[error]     at *Task, D:\a\1\s\output\RequiredModules\InvokeBuild\5.10.3\Invoke-Build.ps1:562�[0m
2023-04-25T07:48:42.5706615Z ##[error]     at <ScriptBlock><End>, D:\a\1\s\output\RequiredModules\InvokeBuild\5.10.3\Invoke-Build.ps1:748�[0m
2023-04-25T07:48:42.5721950Z ##[error]     at <ScriptBlock><Begin>, D:\a\1\s\build.ps1:507�[0m
2023-04-25T07:48:42.5737463Z ##[error]     at <ScriptBlock>, D:\a\_temp\8db17083-42dc-4675-a3e8-422b1c6ec788.ps1:38�[0m
2023-04-25T07:48:42.5753456Z ##[error]     at <ScriptBlock>, <No file>:1�[0m

johlju · 2023-04-25T13:46:57Z

After changing the code to output better verbose messages it seems there is a bug gathering the current state

https://dev.azure.com/dsccommunity/SqlServerDsc/_build/results?buildId=7884&view=logs&j=b737e19f-7a5b-5102-d0bd-68dc73521599&t=7bcc29f5-56f8-577a-d17b-1f044c471130&l=5504

2023-04-25T12:34:43.0351479Z VERBOSE: [WIN-2A8EF7ASEQJ]:                            [[SqlDatabaseMail]CreateMaintenanceNotifyProfile] NOTMATCH: 
2023-04-25T12:34:43.0352187Z Value (type 'System.String') for property 'ProfileName' does not match. Current state is 'Company SQL Alerts' and 
2023-04-25T12:34:43.0354176Z desired state is 'Company SQL Maintenance Notify'. (DRC0021)

TerminatorShoes · 2023-04-25T14:06:01Z

Thank you for the good progress. We appreciate your time.

…

____________________ Alex Moreno, Jr., MCDBA, MA Engr. Mathematics Database Administrator, Data Center Operations | Optum Insight Supporting Northern Light Health O 207.553.3060 ***@***.******@***.***> 43 Whiting Hill Road Brewer, ME 04412 From: Johan Ljunggren ***@***.***> Sent: Tuesday, April 25, 2023 9:47 AM To: dsccommunity/SqlServerDsc ***@***.***> Cc: Moreno Jr, Alejandro ***@***.***>; Author ***@***.***> Subject: Re: [dsccommunity/SqlServerDsc] SqlDatabaseMail: Unable to create two DB mail profiles (Issue #1871) ***************************************************************************************************************** The e-mail below is from an external source. Please do not open attachments or click links from an unknown or suspicious source. ***************************************************************************************************************** After changing the code to output better verbose messages it seems there is a bug gathering the current state https://dev.azure.com/dsccommunity/SqlServerDsc/_build/results?buildId=7884&view=logs&j=b737e19f-7a5b-5102-d0bd-68dc73521599&t=7bcc29f5-56f8-577a-d17b-1f044c471130&l=5504<https://dev.azure.com/dsccommunity/SqlServerDsc/_build/results?buildId=7884&view=logs&j=b737e19f-7a5b-5102-d0bd-68dc73521599&t=7bcc29f5-56f8-577a-d17b-1f044c471130&l=5504> 2023-04-25T12:34:43.0351479Z VERBOSE: [WIN-2A8EF7ASEQJ]: [[SqlDatabaseMail]CreateMaintenanceNotifyProfile] NOTMATCH: 2023-04-25T12:34:43.0352187Z Value (type 'System.String') for property 'ProfileName' does not match. Current state is 'Company SQL Alerts' and 2023-04-25T12:34:43.0354176Z desired state is 'Company SQL Maintenance Notify'. (DRC0021) — Reply to this email directly, view it on GitHub<#1871 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/A6KFGROULWOPJFMGVU7WGF3XC7IVZANCNFSM6AAAAAAVWPGSGU>. You are receiving this because you authored the thread.Message ID: ***@***.******@***.***>>

johlju · 2023-04-25T14:11:11Z

It looks like the bug is here.

SqlServerDsc/source/DSCResources/DSC_SqlDatabaseMail/DSC_SqlDatabaseMail.psm1

Line 142 in 8106945

$mailServer = $databaseMailAccount.MailServers | Select-Object -First 1

And here

SqlServerDsc/source/DSCResources/DSC_SqlDatabaseMail/DSC_SqlDatabaseMail.psm1

Line 148 in 8106945

    
           $returnValue['ProfileName'] = $databaseMail.Profiles | Select-Object -First 1 -ExpandProperty Name

We didn't hit the first issue since the mail server was the same in both resource instances (in the integration test in the PR).

These two rows should not assume that the first value is always the right one. I think the assumption was that the same profile and mail server would always be used. Easy fix I think.

#1922) - SqlDatabaseMail - Update integration tests to verify multiple instances of SqlDatabaseMail in the same configuration (issue #1871). - Changed comparison logic to use `Compare-DscParameterState` (issue #1871). - Return the correct profile name and mail server name from the current state (issue #1871).

johlju · 2023-04-26T05:26:43Z

Thanks for reporting this. Fix will be deploy in a preview shortly (as soon as the pipeline finishes).

johlju added bug The issue is a bug. help wanted The issue is up for grabs for anyone in the community. labels Mar 10, 2023

johlju changed the title ~~SqlDatabaseMail resource Unable to create two DB mail profiles~~ SqlDatabaseMail: Unable to create two DB mail profiles Mar 10, 2023

johlju mentioned this issue Apr 24, 2023

SqlDatabaseMail: Update integration tests to verify multiple instances #1922

Merged

9 tasks

johlju added in progress The issue is being actively worked on by someone. and removed help wanted The issue is up for grabs for anyone in the community. labels Apr 25, 2023

johlju closed this as completed in #1922 Apr 26, 2023

johlju removed the in progress The issue is being actively worked on by someone. label Apr 26, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

SqlDatabaseMail: Unable to create two DB mail profiles #1871

SqlDatabaseMail: Unable to create two DB mail profiles #1871

TerminatorShoes commented Mar 10, 2023 •

edited by johlju

johlju commented Mar 10, 2023

TerminatorShoes commented Mar 10, 2023 •

edited by johlju

johlju commented Mar 10, 2023

TerminatorShoes commented Mar 10, 2023

johlju commented Mar 10, 2023

TerminatorShoes commented Mar 10, 2023

TerminatorShoes commented Mar 10, 2023

johlju commented Mar 10, 2023

TerminatorShoes commented Mar 10, 2023

johlju commented Mar 11, 2023

johlju commented Apr 25, 2023

johlju commented Apr 25, 2023

TerminatorShoes commented Apr 25, 2023 via email

johlju commented Apr 25, 2023

johlju commented Apr 26, 2023

SqlDatabaseMail: Unable to create two DB mail profiles #1871

SqlDatabaseMail: Unable to create two DB mail profiles #1871

Comments

TerminatorShoes commented Mar 10, 2023 • edited by johlju

Problem description

Verbose logs

DSC configuration

Suggested solution

SQL Server edition and version

SQL Server PowerShell modules

Operating system

PowerShell version

SqlServerDsc version

johlju commented Mar 10, 2023

TerminatorShoes commented Mar 10, 2023 • edited by johlju

johlju commented Mar 10, 2023

TerminatorShoes commented Mar 10, 2023

johlju commented Mar 10, 2023

TerminatorShoes commented Mar 10, 2023

TerminatorShoes commented Mar 10, 2023

johlju commented Mar 10, 2023

TerminatorShoes commented Mar 10, 2023

johlju commented Mar 11, 2023

johlju commented Apr 25, 2023

johlju commented Apr 25, 2023

TerminatorShoes commented Apr 25, 2023 via email

johlju commented Apr 25, 2023

johlju commented Apr 26, 2023

TerminatorShoes commented Mar 10, 2023 •

edited by johlju

TerminatorShoes commented Mar 10, 2023 •

edited by johlju