Database mail passwords aren't synced

[mrdenny]

Verified issue does not already exist?

Yes

What error did you receive?

SQL Mail was unable to send email until the password was reentered

Steps to Reproduce

# provide your command(s) executed pertaining to dbatools
# please include variable values (redacted or fake if needed) for reference

Start-SqlMigration -source db1 -destination db2 -exclude databases

Are you running the latest release?

Yes

Other details or mentions

The password in this case was 30+ characters. As soon as the password was entered in again email started being sent.

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe)

PowerShell Host Version

Name Value

---

PSVersion 5.1.17763.2183
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.17763.2183
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

SQL Server Edition and Build number

12.0.6164.21

[wsmelton]

Password length would not apply here as we do not have access to copy the password with SMO. We do a call to the Script() method for the profiles and accounts which is generating the SQL code to recreate it. The SMTP authentication section of the account is not accessible in SMO, but either way it would still not output the password value.

The output of the Script method in SMO will not output the password assigned to the mail account. A migration process would have to include going into SSMS to actually update that as SMO does not offer the option that I'm aware.

[potatoqualitee]

This might be possible, but would possibly require decryption like with Linked Servers and Credentials. I wanted to explore it in the past but never got around to it bc i fear the math. Perhaps next time I get some motivation I'll take a look and explore, let's keep this one open.

[andreasjordan]

Let me remove the "long" from the title, as I think no passwords are synced. And I will change it from a bug to a feature request.

[potatoqualitee]

Agreed, perhaps an immediate solution to this particular issue is documentation. I still have no idea how to decrypt these passwords as I've yet to see it mentioned elsewhere.

[tkwj]

This is a bit of both bug and feature request. I’ve experienced this several times and found a “broken” mail profile/account after migrations. As a workaround I’d typically run an Export-DbaCredential to get the cleartext passwords, and then enter them manually.
In a run of Start-SqlMigration with a basic authentication SMTP account, you end up with two credentials on the destination for every mail credential in the source. The first is the original one from the source’s mail server that comes from Copy-DbaCredential. The second one that is created after Copy-DbaDbMail, which currently gets created with a blank password, due to the behavior of the SMO MailServer.Script() method. Also, as mentioned users do tend to expect the mail account to work after migration, and it will not, without extra steps, hence the discussion here about documentation.
If you’d like to consider implementing the password copy, I’ve got a PR here for your review. This does not address the duplicate credential from Start-SqlMigration, but it does address the password in the new one which is the main issue.

[potatoqualitee]

Sweet, i'd totally copy that password. Looking forward to that merge 👍🏼