SSDT 發布失敗，因為“System.DirectoryServices”驗證失敗

將現有數據庫 (SQL2012) 導入到 VS2017 中的 SSDT 項目，並在嘗試將其部署到我的本地實例 (SQL2016) 時收到錯誤消息：

為程序集“System.DirectoryServices”創建程序集失敗，因為程序集“System.DirectoryServices”驗證失敗。

當指向它時，我在 SSMS 中的數據庫中創建程序集沒有問題：

C:\Windows\Microsoft.NET\assembly\GAC_MSIL\System.DirectoryServices\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.DirectoryServices.dll

在我的 SSDT 項目中，我將目標框架設置為 SQLCLR 下的 .NET Framework 4。

對 System.DirectoryServices 的引用將 Generate Sql Script 和 Model Aware 選項設置為 true，並將權限設置為 Unsafe。

數據庫設置還啟用了可信賴選項。

select * from sys.dm_clr_properties在我的實例上執行返回：

directory   C:\Windows\Microsoft.NET\Framework64\v4.0.30319\
version v4.0.30319 
state   CLR is initialized

我需要進行哪些更改才能使我的項目成功發布？

完整的錯誤資訊如下。

Creating [System.DirectoryServices]...
Warning: The Microsoft .NET Framework assembly 'system.directoryservices, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.
(221,1): SQL72014: .Net SqlClient Data Provider: Msg 6218, Level 16, State 2, Line 1 CREATE ASSEMBLY for assembly 'System.DirectoryServices' failed because assembly 'System.DirectoryServices' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
[ : System.DirectoryServices.ActiveDirectorySecurity::PurgeAccessRules][mdToken=0x6000009][offset 0x00000000] Code size is zero.
[ : System.DirectoryServices.ActiveDirectorySecurity::PurgeAuditRules][mdToken=0x6000010][offset 0x00000000] Code size is zero.
[ : System.DirectoryServices.ActiveDirectorySecurity::get_AccessRightType][mdToken=0x6000015][offset 0x00000000] Code size is zero.
[ : System.DirectoryServices.ActiveDirectorySecurity::get_AccessRuleType][mdToken=0x6000016][offset 0x00000000] Code size is zero.
[ : System.DirectoryServices.ActiveDirectorySecurity::get_AuditRuleType][mdToken=0x6000017][offset 0x00000000] Code size is zero.
[ : System.DirectoryServices.ActiveDirectorySecurity::ModifyAccessRule][mdToken=0x6000008][offset 0x00000000] Code size is zero.
[ : System.DirectoryServices.ActiveDirectorySecurity::ModifyAuditRule][mdToken=0x600000f][offset 0x00000000] Code size is zero.
[ : System.DirectoryServices.ActiveDirectorySecurity::AccessRuleFactory][mdToken=0x6000011][offset 0x00000000] Code size is zero.
[ : System.DirectoryServices.ActiveDirectorySecurity::AuditRuleFactory][mdToken=0x6000013][offset 0x00000000] Code size is zero.
[ : System.DirectoryServices.ActiveDirectorySecurity::AccessRuleFactory][mdToken=0x6000012][offset 0x00000000] Code size is zero.
[ : System.DirectoryServices.ActiveDirectorySecurity::AuditRuleFactory][mdToken=0x6000014][offset 0x00000000] Code size is zero.
[ : System.DirectoryServices.ActiveDirectorySecurity::.ctor][mdToken=0x6000001][offset 0x00000000] Code size is zero.
[ : System.DirectoryServices.ActiveDirectorySecurity::AddAccessRule][mdToken=0x6000002][offset 0x00000000] Code size is zero.
[ : System.DirectoryServices.ActiveDirectorySecurit...
(221,0): SQL72045: Script execution error.  The executed script:
CREATE ASSEMBLY [System.DirectoryServices]
   AUTHORIZATION [dbo]
   FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300A1E3A14B0000000000000000E00022200B01080000E801000008000000000000C205020000200000002002000000AC65002000000002000004000000000000000400000000000000006002000002000062580200030040850000100000100000000010000010000000000000100000000000000000000000700502004F000000002002006C040000000000000000000000F2010070170000004002000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000038E601000020000000E8010000020000000000000000000000000000200000602E727372630000006C040000002002000006000000EA0100000000000000000000000000400000402E72656C6F6300000C00000000400200
An error occurred while the batch was being executed.

我不確定這種行為是 SSDT 想要的還是對以前版本的更改，但正在發生的事情是“參考程序集”正在被拉入。參考程序集不是實際的程序集，因此它們不會起作用導入 SQL Server。這裡令人沮喪的是，即使您在添加引用時使用“瀏覽”並選擇C:\WINDOWS\Microsoft.NET\Framework64\v4.0.30319\System.DirectoryServices.dll然後Model Aware在其屬性下選擇“true”， SSDT 仍將拉入參考程序集（114 kb）而不是您指向的 DLL（即 413 kb）。更具體地說，它將從與項目屬性中設置的“目標框架版本”對應的文件夾中提取參考程序集（例如 4.6.1 ==C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework.NETFramework\v4.6.1）。如果重要的話，我將 Visual Studio 2015 和 SSDT 用於 Management Studio 2017（即 v14）。

然而，雖然不理想，但我可以通過將 DLL 複製到非系統文件夾來實現此功能。我將C:\WINDOWS\Microsoft.NET\Framework64\v4.0.30319**中的完整System.DirectoryServices.dll**複製到我的項目文件夾中，然後我添加了一個“現有項”以指向 DLL（在我的項目文件夾中） ，它的“建構操作”為，然後將引用屬性設置為： Owner = 、 Permission Set =和 Model Aware = 。之後它起作用了。None``dbo``UNSAFE``True

如果這個 DLL 有任何依賴項，那麼我想我也需要將它們複製到我的項目中。但我不是 100% 確定那部分。

我想我們可以將此體驗添加到不使用不受支持的 .NET Framework 庫和/或 SSDT 的原因列表中；-)

引用自：https://dba.stackexchange.com/questions/177972