1

Imported an existing database (SQL2012) to an SSDT project in VS2017 and when trying to deploy it to my local instance (SQL2016) I get the error message:

CREATE ASSEMBLY for assembly 'System.DirectoryServices' failed because assembly 'System.DirectoryServices' failed verification.

I've no issue creating the Assembly in my database in SSMS when pointing it to:

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

In my SSDT project I have the target framework set to .NET Framework 4 under SQLCLR.

The reference to System.DirectoryServices has Generate Sql Script and Model Aware options set to true and permission set to Unsafe.

Database settings also has the trustworthy option enabled.

Running select * from sys.dm_clr_properties on my instance returns:

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

What do I need to change to get my project to publish successfully?

Full error message below.

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.
  • Are the 2012 and 2016 instances on the same physical server or different? If different, what is the highest patch-level of the .NET Framework on each server? It could be that this library went from pure MSIL to mixed-mode in a .NET Framework update and is no longer valid for use. Else you grabbed the DLL from the wrong place (i.e. don't get from GAC, get from original .NET Framework location in C:\Windows\... – Solomon Rutzky Jul 4 '17 at 15:57
  • 2012 is server-side and 2016 is on my laptop – mheptinstall Jul 4 '17 at 15:59
  • In SSDT the reference points to C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\System.DirectoryServices.dll if I try to create this assembly in my database in SSMS i get the same error – mheptinstall Jul 4 '17 at 16:01
  • Ok, so the 2012 is more likely to NOT be up to the latest .NET Framework update. But with the most recent comment here with the location, I can say that reference Assemblies don't work. Try using: C:\WINDOWS\Microsoft.NET\Framework64\v4.0.30319\System.Dir‌​ectoryServices.dll instead. – Solomon Rutzky Jul 4 '17 at 16:02
  • Same error regardless of which file I reference to in my SSDT project – mheptinstall Jul 4 '17 at 16:07
1

I am not sure if this behavior is intended by SSDT or a change from prior versions, but what is happening is that the "reference assembly" is being pulled in. Reference Assemblies aren't the actual Assembly and so they won't work to import into SQL Server. What is frustrating here is that even if you use "Browse" when adding the Reference and select C:\WINDOWS\Microsoft.NET\Framework64\v4.0.30319\System.DirectoryServices.dll and then select "true" for Model Aware under its properties, SSDT will still pull in the Reference Assembly (at 114 kb) instead of the DLL that you pointed to (which is 413 kb). More specifically, it will pull in the reference assembly from the folder that corresponds to the "Target Framework Version" set in project properties (e.g. 4.6.1 == C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.6.1 ). If it matters, I am using Visual Studio 2015 and SSDT for Management Studio 2017 (i.e. v14).

However, while not ideal, I was able to get this to work by copying the DLL to a non-system folder. I copied the full System.DirectoryServices.dll in C:\WINDOWS\Microsoft.NET\Framework64\v4.0.30319\ into my project folder, and then I added an "existing item" to point to the DLL (in my project folder), which has a "build action" of None, and then set the Reference properties to: Owner = dbo, Permission Set = UNSAFE, and Model Aware = True. After that it worked.

If this DLL had any dependencies then I imagine that I would need to copy them into my project as well. But I am not 100% sure about that part.

I guess we can add this experience to the list of reasons to not use unsupported .NET Framework libraries and/or SSDT ;-)

  • I get the same error when pointing to that file. SSDT does not allow you to create assembles that way in your project SQL70502: The assembly source is not valid. Only binary literals are allowed. – mheptinstall Jul 4 '17 at 16:10
  • Yes, I have changed the reference in my project. It was originally C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\System.Dir‌​ectoryServices.dll and I've tried both C:\WINDOWS\Microsoft.NET\Framework64\v4.0.30319\System.DirectoryServices.dll and C:\Windows\Microsoft.NET\assembly\GAC_MSIL\System.DirectoryServices\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.DirectoryServices.dll neither publish correctly from the project – mheptinstall Jul 4 '17 at 16:17
  • I've moved the discussion to chat... – mheptinstall Jul 4 '17 at 16:26
  • I have updated my answer with a way to get this to work. We can probably remove the comments here and on the question as they are in the chat room now. – Solomon Rutzky Jul 4 '17 at 19:20

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.