32

I have VS2015 with SSDT installed, along with SSMS and the SqlServer PowerShell module (which includes the invoke-sqlcmd comand), and yet If I try to execute a query against an Azure SQL Data Warehouse like so:

invoke-sqlcmd  -Query "Select top 5 * from customer"  -ConnectionString "Server=tcp:my.database.windows.net,1433;Database=Customer;  Authentication=Active Directory Integrated; Encrypt=True; "

I get the following error:

invoke-sqlcmd : Unable to load adalsql.dll (Authentication=ActiveDirectoryIntegrated). Error code: 0x2. For more information, see
http://go.microsoft.com/fwlink/?LinkID=513072
At line:1 char:1
+ invoke-sqlcmd  -Query "Select top 5 * from vwOffer"  -ConnectionStrin ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
    + FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

invoke-sqlcmd :
At line:1 char:1
+ invoke-sqlcmd  -Query "Select top 5 * from vwOffer"  -ConnectionStrin ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ParserError: (:) [Invoke-Sqlcmd], ParserException
    + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptComman

If I try and install adalsql.dll directly, I get a message stating that A higher version already exists and I can see the both versions of the dll can be found here:

 C:\Windows\SysWOW64\adalsql.dll
 C:\Windows\System32\adalsql.dll

and yet, invoke-sqlcmd cant find it. Any idea how to either (A) register the existing dll so invoke-sqlcmd can find it or (B) uninstall it so that it can be re-installed?

Incidentally, I am able to use Active Directory Authenticatoin with the 32-bit SQLCMD.exe, so I know the 32 bit dll is working fine. It's just the 64 bit dll that isn't loading properly...

Jthorpe
  • 9,756
  • 2
  • 49
  • 64
  • I have the same problem. Just now I uninstalled Active Directory Authentication Library for SQL Server from appwiz.cpl and reinstalled, and it works? Here is the installer I used: https://www.microsoft.com/en-us/download/confirmation.aspx?id=48742 – Bill Sambrone Aug 08 '17 at 21:47
  • If you post an answer with the above and include uninstall via `settings` > `Add or Remove Programs` I'll accept it as the answer. I struggled all morning trying to figure out how to re-install it... – Jthorpe Aug 08 '17 at 22:27

5 Answers5

49

So, this problem vexed me as well. I'm unclear as to how it happened, but maybe it's just coincidence that it happened when I installed the latest version of SSMS. My fix was to:

  1. Navigate to Add or Remove Programs

  2. In the little search window type sql, or just go find: "Active Directory Authentication Library for SQL Server".

Note: This will not be in Windows' installed programs list if you have installed Visual Studio 2019. You can run the same installer by invoking MsiExec.exe /I{6BF11ECE-3CE8-4FBA-991A-1F55AA6BE5BF} from a command prompt.

  1. Uninstall that little guy

  2. Navigate here and download the latest ADAL library (pick x64):
    https://www.microsoft.com/en-us/download/confirmation.aspx?id=48742

  3. Just for kicks, reboot

  4. Your stuff should now properly load adalsql.dll!

Update #3, 2020-07-13: Installing the older version works, but is vulnerable to being clobbered by the SSMS and VS2019 installers. The reason the new version of ADAL doesn't work is because its installer fails to add a registry entry pointing to the 64-bit version of adal.dll. I added an answer below which includes the missing registry value, which you can add yourself. Once that's done, you should never need to do this dance again. - @Tullo_x86

Tullo_x86
  • 2,573
  • 25
  • 27
Bill Sambrone
  • 4,334
  • 4
  • 48
  • 70
  • I just ran into the same issue running a Powershell script using Azure Active Directory to add user permissions to a number of SQL Databases. Bill's suggestion id the job, so +1. – jl. Aug 18 '17 at 14:07
  • 13
    Every bloody update of SSMS I need to come back to this answer, as every time they break it! I wish I could upvote more than once – trailmax May 18 '18 at 11:22
  • FYI I was getting this with 14.x of ADAL. Installing 13.x as recommended resolved. Didn't need to reboot. – w00ngy Dec 06 '18 at 19:41
  • 2
    I found that a simple "repair" fixed the same issue, without a reboot. Lucky. – Eric Patrick Feb 02 '19 at 17:15
  • 1
    This is no longer visible after you install Visual Studio 2019. The entry under "Apps & Features" is removed, even if you still have VS2017 installed. – Tullo_x86 May 31 '19 at 16:46
  • You can, however, uninstall the VS2019 version by running `MsiExec.exe /I{6BF11ECE-3CE8-4FBA-991A-1F55AA6BE5BF}` from a command prompt, and selecting Uninstall from the wizard. – Tullo_x86 May 31 '19 at 20:16
  • Update: I've posted an answer which allows you to be immune from SSMS and VS clobbering the old version of adalsql.dll by adding the ADAL installer's missing registry entry: https://stackoverflow.com/a/62883624/130200 – Tullo_x86 Jul 13 '20 at 20:19
16

The bewilderingly simple solution to this annoying problem

  1. Ensure you actually have ADAL installed (the existence of C:\Windows\system32\adal.dll is plenty)

  2. Paste this into a .reg file:

     Windows Registry Editor Version 5.00

 [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSADALSQL]
 "TargetDir"="C:\\Windows\\System32\\adal.dll"

  3. Double-click the .reg file to install that value

You're done. Go celebrate!

Explain WTF is going on. Why this is necessary?

This error happens because the .NET adapter for SQL Server can't find adal.dll. But why is this? It's installed, right?

Well, the installer for the latest version of ADAL (packaged with VS 2019 and the latest version of SQL Server Management Studio) adds a registry entry for the 32-bit version of the DLL (at C:\Windows\SysWOW64\adal.dll), but does not add the entry for the 64-bit version (which gets installed to C:\Windows\system32\adal.dll). If you build and run your application in 32-bit mode, you'll never see this bug manifest.

This is simply a bug in the ADAL installer. Presumably it wasn't detected by Microsoft because both Visual Studio and SSMS are 32-bit applications and thus don't go looking for the 64-bit version.

Tullo_x86
  • 2,573
  • 25
  • 27
  • 1
    Thanks, this resolved an annoying issue for me. I wish I could upvote about a dozen more times. – Brad Crandell Sep 25 '20 at 18:31
  • After the dozenth time getting blindsided by this — and a cumulative workday spent dealing with its fallout — I remembered seeing a blog post referencing registry keys for ADAL, and went searching for it. What a surprise to find that the installer just _forgot_! – Tullo_x86 Oct 08 '20 at 17:39
2

In my case culprit was VS 2019. It has newer version of ADALSQL package. For me it was at path "C:\ProgramData\Microsoft\VisualStudio\Packages\sql_adalsql,version=16.0.61903.25110,chip=x64,language=en-US", which I uninstalled and installed desired version to resolve the issue.

Hope this helps!

kiran
  • 53
  • 4
  • The easy way to remove the VS2019 version is to use Windows Installer to start the install wizard using its GUID reference, and selecting Uninstall. This is no longer visible after you install Visual Studio 2019. The entry under "Apps & Features" is removed, even if you still have VS2017 installed. In a command prompt, run `MsiExec.exe /I{6BF11ECE-3CE8-4FBA-991A-1F55AA6BE5BF}`. – Tullo_x86 May 31 '19 at 20:17
2

In my case, add/remove programs didn't show any installed instances of "Active Directory Authentication Library". Yet, trying to install the library would fail saying a newer version was installed.

I un-wedged myself by opening the registry editor (regedt32) and using the Find feature from

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer

looking for adalsql.

That got a hit with containing keys

DisplayName: Active Directory Authentication Library for SQL Server
DisplayVersion: 15.0.1300.359

along with, importantly, a path to the associated installer. For me it was something like c:\windows\installer\4c334521.msi. I ran that msi and was finally able to "uninstall". After that, running the installer from the 'library' link above finally worked.

Tommy
  • 21
  • 3
-1

Or you if planned to upgrade SSMS in the time being, simply uninstall "Active Directory Authentication Library for SQL Server", and repair SSMS.

Next time you upgrade SSMS it won't broke anything. SSMS bring his own adalsql.dll, and know how to upgrade it when you upgrade SSMS. But if you had installed adalsql with something else (or using https://www.microsoft.com/en-us/download/confirmation.aspx?id=48742 ), SSMS is somehow broken.

petoulachi
  • 9
  • 2