I've been using the Get-MSSQLLinkPasswords Powershell script for a long time and it has been a huge help. The script decrypts the passwords for all linked servers on a particular Windows server and shows them to you in plain text. This functionality is even incorporated into the dbatools Copy-SqlLinkedServer script.
There are security caveats for running the script as show here
The script must be run locally on the MSSQL server (as DPAPI requires
access to the local machine key). The user executing the script must
also have sysadmin access to all the database instances (for the DAC
connection) and local admin privileges on the Windows server (to
access the entropy bytes in registry). In addition, if UAC is enabled,
the script must be ran as an administrator.
This script has worked flawlessly until recent upgrades to SQL Server 2016. I was wondering if anyone else has encountered any issues with this script under SQL Server 2016. Perhaps some security model has changed in SQL Server 2016 and this script is unable to decrypt the passwords now.
As time permits, I will try to see if the dbatools Copy-SqlLinkedServer script still works on 2016.
Best Answer
I've tested dbatools' Copy-SqlLinkedServer from SQL Server 2016 to SQL Server 2016 and SQL Server 2014 to SQL Server 2016.
All linked servers and their respective logins were successfully migrated.
I checked both the permissions and ensured the same login was being used. What versions are to you migrating from? What error do you get?
(I am the creator of dbatools).