search

dsccommunity / SqlServerDsc

This module contains DSC resources for deployment and configuration of Microsoft SQL Server.
MIT License
359 stars 224 forks source link

SqlAgentOperator - Doesn't update email addresses #1990

Closed quillypowers closed 8 months ago

quillypowers commented 8 months ago

Problem description

When I modify email addresses correlating to an operator, they don't update. The operator is just marked as present.

Verbose logs

VERBOSE: [S150RCMCDB1-CI]: LCM:  [ Start  Resource ]  [[SqlAgentOperator]AppTeam]
VERBOSE: [S150RCMCDB1-CI]: LCM:  [ Start  Test     ]  [[SqlAgentOperator]AppTeam]
VERBOSE: [S150RCMCDB1-CI]:                            [[SqlAgentOperator]AppTeam] Checking if SQL Agent Operator 'AppTeam' is present or absent.
VERBOSE: [S150RCMCDB1-CI]:                            [[SqlAgentOperator]AppTeam] Connecting as current user 'Dom\DscSvcAct' using integrated security. (SQLCOMMON0054)
VERBOSE: [S150RCMCDB1-CI]:                            [[SqlAgentOperator]AppTeam] Connected to SQL instance 'S150RCMCDB1-CI'. (SQLCOMMON0018)
VERBOSE: [S150RCMCDB1-CI]:                            [[SqlAgentOperator]AppTeam] Getting SQL Agent Operators.
VERBOSE: [S150RCMCDB1-CI]:                            [[SqlAgentOperator]AppTeam] SQL Agent Operator 'AppTeam' is present.
VERBOSE: [S150RCMCDB1-CI]: LCM:  [ End    Test     ]  [[SqlAgentOperator]AppTeam]  in 0.4850 seconds.
VERBOSE: [S150RCMCDB1-CI]: LCM:  [ Skip   Set      ]  [[SqlAgentOperator]AppTeam]
VERBOSE: [S150RCMCDB1-CI]: LCM:  [ End    Resource ]  [[SqlAgentOperator]AppTeam]

DSC configuration

SqlAgentOperator AppTeam
{
    Ensure               = "Present"
    Name                 = "AppTeam"
    InstanceName         = "MSSQLSERVER"
    EmailAddress         = "UpdatedEmail1@list.com;UpdatedEmail2@list.com"
    PsDscRunAsCredential = $RunAs
}

Suggested solution

Debug below condition

elseif ($PSBoundParameters.ContainsKey('EmailAddress') -and $getTargetResourceResult.EmailAddress -ne $EmailAddress)
            {
                Write-Verbose -Message (
                    $script:localizedData.SqlAgentOperatorExistsButEmailWrong `
                        -f $Name, $getTargetResourceResult.EmailAddress, $EmailAddress
                )

                $isOperatorInDesiredState = $false
            }

SQL Server edition and version

Microsoft SQL Server 2016 (SP3-GDR) (KB5029186) - 13.0.6435.1 (X64)   Jul 30 2023 19:53:42   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor)

SQL Server PowerShell modules

Name        Version    Path                                                                                     
----        -------    ----                                                                                     
AzureRM.Sql 4.4.0      C:\Program Files\WindowsPowerShell\Modules\AzureRM.Sql\4.4.0\AzureRM.Sql.psd1            
sqlserver   21.1.18256 C:\Program Files\WindowsPowerShell\Modules\sqlserver\sqlserver.psd1                      
SQLPS       1.0        C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1

Operating system

OsName               : Microsoft Windows Server 2019 Datacenter
OsOperatingSystemSKU : DatacenterServerEdition
OsArchitecture       : 64-bit
WindowsVersion       : 1809
WindowsBuildLabEx    : 17763.1.amd64fre.rs5_release.180914-1434
OsLanguage           : en-US
OsMuiLanguages       : {en-US}

PowerShell version

Name                           Value                                                                                                                                     
----                           -----                                                                                                                                     
PSVersion                      5.1.17763.5202                                                                                                                            
PSEdition                      Desktop                                                                                                                                   
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}                                                                                                                   
BuildVersion                   10.0.17763.5202                                                                                                                           
CLRVersion                     4.0.30319.42000                                                                                                                           
WSManStackVersion              3.0                                                                                                                                       
PSRemotingProtocolVersion      2.3                                                                                                                                       
SerializationVersion           1.1.0.1

SqlServerDsc version

Name         Version Path                                                                            
----         ------- ----                                                                            
SqlServerDsc 16.5.0  C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\16.5.0\SqlServerDsc.psd1
johlju commented 8 months ago

The resource does not take a string array for email adress, just a string (one e-mail address). If you only use one e-mail address do you get the same result?

quillypowers commented 8 months ago

The resource does not take a string array for email adress, just a string (one e-mail address). If you only use one e-mail address do you get the same result?

I've used it from the beginning passing in multiple email addresses. One address serves no purpose for how I'm using it. I don't have a test implementation I can mess with at the moment to toggle to one address though but will circle back. Thanks.

quillypowers commented 8 months ago

The resource does not take a string array for email adress, just a string (one e-mail address). If you only use one e-mail address do you get the same result?

I've used it from the beginning passing in multiple email addresses. One address serves no purpose for how I'm using it. I don't have a test implementation I can mess with at the moment to toggle to one address though but will circle back. Thanks.

Ah but I am doing it as a single string with multiple addresses embedded. I botched that in my example and will update, thanks.

johlju commented 8 months ago

If this worked before, what are you doing differently now? What is the length of the string of the e-mail addresses you try to add? There is probably a limit of the length set by SQL Server.

I can't reproduce. The integration tests passes.

 Context When using configuration DSC_SqlAgentOperator_Change_MultipleEmailAddresses_Config
VERBOSE: Perform operation 'Invoke CimMethod' with following parameters, ''methodName' = 
SendConfigurationApply,'className' = MSFT_DSCLocalConfigurationManager,'namespaceName' = 
root/Microsoft/Windows/DesiredStateConfiguration'.
VERBOSE: An LCM method call arrived from computer fv-az688-456 with user sid 
S-1-5-21-1597464496-2812813532-1165135672-500.
VERBOSE: [fv-az688-456]: LCM:  [ Start  Set      ]
VERBOSE: [fv-az688-456]: LCM:  [ Start  Resource ]  [[SqlAgentOperator]Integration_Test]
VERBOSE: [fv-az688-456]: LCM:  [ Start  Test     ]  [[SqlAgentOperator]Integration_Test]
VERBOSE: [fv-az688-456]:                            [[SqlAgentOperator]Integration_Test] Checking if SQL Agent Operator
 'MyOperator' is present or absent.
VERBOSE: [fv-az688-456]:                            [[SqlAgentOperator]Integration_Test] Connecting as current user 
'fv-az688-456\SqlInstall' using integrated security. (SQLCOMMON0054)
VERBOSE: [fv-az688-456]:                            [[SqlAgentOperator]Integration_Test] Connected to SQL instance 
'fv-az688-456\DSCSQLTEST'. (SQLCOMMON0018)
VERBOSE: [fv-az688-456]:                            [[SqlAgentOperator]Integration_Test] Getting SQL Agent Operators.
VERBOSE: [fv-az688-456]:                            [[SqlAgentOperator]Integration_Test] SQL Agent Operator 
'MyOperator' is present.
VERBOSE: [fv-az688-456]:                            [[SqlAgentOperator]Integration_Test] SQL Agent Operator 
'MyOperator' exists but has the wrong email address. Email address is currently 'newemail1@company.local' and should be
 updated to 'newemail1@company.local;newemail2@company.local'.
VERBOSE: [fv-az688-456]: LCM:  [ End    Test     ]  [[SqlAgentOperator]Integration_Test]  in 3.4840 seconds.
VERBOSE: [fv-az688-456]: LCM:  [ Start  Set      ]  [[SqlAgentOperator]Integration_Test]
VERBOSE: [fv-az688-456]:                            [[SqlAgentOperator]Integration_Test] Connecting as current user 
'fv-az688-456\SqlInstall' using integrated security. (SQLCOMMON0054)
VERBOSE: [fv-az688-456]:                            [[SqlAgentOperator]Integration_Test] Connected to SQL instance 
'fv-az688-456\DSCSQLTEST'. (SQLCOMMON0018)
VERBOSE: [fv-az688-456]:                            [[SqlAgentOperator]Integration_Test] Updating email address to 
'newemail1@company.local;newemail2@company.local' for SQL Agent Operator 'MyOperator'.
VERBOSE: [fv-az688-456]: LCM:  [ End    Set      ]  [[SqlAgentOperator]Integration_Test]  in 0.2810 seconds.
VERBOSE: [fv-az688-456]: LCM:  [ End    Resource ]  [[SqlAgentOperator]Integration_Test]
VERBOSE: [fv-az688-456]: LCM:  [ End    Set      ]
VERBOSE: [fv-az688-456]: LCM:  [ End    Set      ]    in  3.9580 seconds.
VERBOSE: Operation 'Invoke CimMethod' complete.
VERBOSE: Time taken for configuration job to complete is 3.988 seconds
quillypowers commented 8 months ago

It works to initially setup an operator with a list of email addresses, it does not update the operator if a changed list of email addresses (again, just a string with semicolons separating) is applied, which the documentation indicates it does. All I did differently was change the list of email addresses and rerun my code then check sql server. No updates and per my verbose output, I never see "updating email address" like you have. Since updating wasn't working, when I had to modify them I just blew out the entire operator by toggling ensure to absent and recreating with the updated email address string.

johlju commented 8 months ago

Since it not possible to reproduce you have to debug it at your end to see why the Test-TargetResource function does not return $false. When it return $false then LCM should run Set-TargetResource which seem never happens in your case.

quillypowers commented 8 months ago

Ok, I guess closing/ignoring.