2

I wrote a SQL CLR function in C# that has some dependency (to npgsql.dll). When it didn't have any dependency in the past, I can add it as a new assembly in SQL Server without any problem - but now I can't do it.

I get this error:

Msg 10301, Level 16, State 1, Line 17
Assembly 'Database1' references assembly 'system.directoryservices, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2 (The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.

Edited: This problem is not just with 'system.directoryservices', so when i use another npgsql.dll i get following error instead:

Msg 10301, Level 16, State 1, Line 20 Assembly 'Database1' references assembly 'system.threading.tasks.extensions, version=4.1.0.0, culture=neutral, publickeytoken=cc7b13ffcd2ddd51.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.

and any try to add these library to GAC with Developer Command Prompt doesn't work.

Improve this question
3
  • Which part of which is NOT present ...... don't you understand?? This DLL you're referencing is not present in the SQL Server .NET environment - if you need it, you have to load it and add it to SQL Server before loading your own assembly. Commented Oct 11, 2017 at 14:34
  • 1
    Under normal circumstances, an assembly like System.DirectoryServices is loaded from the GAC. In SQL Server, those circumstances do not apply. It has its own logic for loading assemblies and ignores the GAC, except for a small list of whitelisted assemblies that are permitted -- of which System.DirectoryServices is not one. It (and its dependencies, in turn) must be added to SQL Server's assembly collection explicitly before it's usable. Apparently, earlier versions of the assemblies you already have simply didn't reference it. Commented Oct 11, 2017 at 14:41
  • Thanks for your help @Jeroen Mostert. I try to add 'System.DirectoryServices' in GAC, for do this i found this dll in .net path and use Developer Command Prompt. But again i have the same error. Is it because of my version? If yes how can i fix it? Commented Oct 12, 2017 at 5:44

1 Answer 1

Reset to default
1

This question has been asked and answered several times already on here. But, rather than just listing this as a duplicate, something needs to be said first:

Before importing the DirectoryServices DLL into SQL Server (an action that has some amount of risk), be sure that you actually can't go about that functionality in any other way. For example:

C# clr udf for Active Directory group membership

Otherwise, look here:

How to register System.DirectoryServices for use in SQL CLR User Functions?

The "fix" there is to enable TRUSTWORTHY, which isn't a great option by any stretch, but it is the common way to get unsupported .NET Framework libraries loaded. I am working on another means that is so far looking good and doesn't require TRUSTWORTHY, but I need to do a little more testing before I can recommend it. I will update this later with that info once I have time to fully test and document it.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.