2

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.

  • Where your upgrades in place upgrades or migration to new servers, when you moved to SQL Server 2016? – Shawn Melton Nov 12 '16 at 20:40
  • @ShawnMelton - Actually, it's still working - I did some more testing and realized my original powershell script was actually piping the output to a text file, so I 'thought' the script was broken - it still works (thankfully, because I just used it yesterday successfully) - thanks for the reply though. – Scott Hodgin Nov 12 '16 at 20:46
5

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).

  • That is very interesting - and you were able to right-click all of your copied linked servers and 'Test Connection' successfully without any other changes? Weird! Alas, I was able to copy the linked servers from one 2016 server to another server using the dbatools script (without any visible errors), but the passwords for the individual users IN the linked servers did not get migrated over and that's where the problem lies. Hmmm, I wonder what the difference is between what I'm doing and what you did?? – Scott Hodgin Nov 8 '16 at 10:09
  • After you indicated you were successful, i went back and did some more testing and realized that the base powerscript I had been using was actually piping the output to a text file, so I thought there was no output. – Scott Hodgin Nov 12 '16 at 20:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.