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.