sql-server - 调用 `Invoke-sqlcmd`时无法加载adalsql.dll错误

Question

我有 VS2015 SSDT已安装，以及 SSMS和 SqlServer PowerShell 模块(其中包括 invoke-sqlcmd 命令)，但如果我尝试对 Azure SQL 数据仓库执行查询，如下所示:

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

我收到以下错误:

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

如果我尝试安装adalsql.dll直接，我收到一条消息，指出更高版本已经存在，我可以看到两个版本的 dll 都可以在这里找到:

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

但是，invoke-sqlcmd 找不到它。知道如何 (A) 注册现有 dll 以便 invoke-sqlcmd 可以找到它或 (B) 卸载它以便可以重新安装它？

顺便说一句，我能够将 Active Directory Authenticatoin 与 32 位 SQLCMD.exe 一起使用，因此我知道 32 位 dll 工作正常。这只是 64 位 dll 未正确加载...

Answer 1

所以，这个问题也困扰着我。我不清楚它是如何发生的，但也许这只是巧合，当我安装最新版本的 SSMS 时，它就发生了。我的解决方法是:

1. 导航至添加或删除程序

2. 在小搜索窗口中输入 sql，或者直接查找:“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.

卸载那个小家伙

导航至此处并下载最新的 ADAL 库(选择 x64):
https://www.microsoft.com/en-us/download/confirmation.aspx?id=48742

只是为了好玩，重新启动

您的内容现在应该可以正确加载 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