PowerShell - Fix Unable to load adalsql.dll exception

I have quite a few databases in Azure and use PowerShell from time to time to connect and execute queries on them. This is pretty straight forward and normally works as intended. However, sometimes I get the following exception:

Unable to load adalsql.dll (Authentication=ActiveDirectoryPassword). Error code: 0x2. For more information, see http://go.microsoft.com/fwlink/?LinkID=513072

This usually happens if you have SSMS installed on the same machine you're running the PowerShell code from. It seems that the version of the adalsql.dll that ships and installs with SSMS doesn't seem to work properly. What is more annoying is that this breaks every time you update SSMS!

To fix this you need to replace the adalsql.dll with a working version. Follow the steps below (taken from https://stackoverflow.com/questions/45578395/unable-to-load-adalsql-dll-error-when-calling-invoke-sqlcmd):

1. Navigate to Add or Remove Programs
2. Search for "Active Directory Authentication Library for SQL Server"
3. Uninstall the program
4. Navigate to the following page and download the latest ADAL library (ENU\x64\adalsql.msi)
5. Open a new PowerShell console and you should be able to connect to Azure SQL!

I just installed Visual Studio 2019 and it seems that it now ships with a new dll named adal.dll which replaces the adalsql.dll. Annoyingly, this also breaks the SQL PowerShell connection. If you follow the above steps you'll run into an issue that the "Active Directory Authentication Library for SQL Server" doesn't appear in the program list to uninstall and if you try and install the latest ADAL library you will be presented with the following error:

A higher version already exists

To fix this, perform the following steps:

1. Download the ADAL msi linked here (choose ENU\x64\adalsql.msi)
2. Extract the adalsql.dll from the msi (taken from ghacks):
   1. Open console as administrator (Windows + R keys > Type: cmd > Press Ctrl + Shift + Enter keys)
   2. Type: msiexec.exe /a "[Path to downloaded file]\adalsql.msi"/qb TARGETDIR="[Export directory]"
   3. Open the newly exported directory
   4. Copy the [Export directory]\Windows\SysWOW64\adalsql.dll to C:\Windows\SysWOW64 folder.
   5. Repeat for the System32 folder.
3. Go ahead and open a new PowerShell console and attempt to connect an Azure SQL database. If it works, you can skip the rest of the steps.
4. If you still can't connect you may need to update your registry keys (taken from winterdom):
5. Open regedit (Windows + R keys> Type: regedit > Press Enter)
6. Check if you have the following keys:

Windows Registry Editor Version 5.00

\[HKEY\_LOCAL\_MACHINE\\SOFTWARE\\Microsoft\\MSADALSQL\]
 "TargetDir"="C:\\\\Windows\\\\system32\\\\adalsql.dll"

\[HKEY\_LOCAL\_MACHINE\\SOFTWARE\\WOW6432Node\\Microsoft\\MSADALSQL\]
"TargetDir"="C:\\\\WINDOWS\\\\system32\\\\adalsql.dll"

1. If you don't have these entries or they are different then create a new text document and copy and paste the above text into it.
2. Save the file and rename the extension to *.reg.
3. Double click the file to install the registry keys.
4. Open a new PowerShell console and try to connect to an Azure SQL instance. You should now be able to connect as normal.

You should have the TargetDir string value pointing to the adalsql.dll file location