.net StoredProc

Hi,

I'm trying to use a LDAP request to simply fill a table. I want to use the database agent to schedule the insert every night. So I was trying to make a storedproc to do it, but I can't make a import of "System.DirectoryServices". Many references types look to be unavailable. How could I do it ? Some ideas ?
Regards
Jacques

Hi.

I find it easier to create a normal classlib and do the "create assembly", "create function", etc. myself. Your assembly must be marked EXTERNAL_ACCESS.

- Snippet
-- Delete all dependent objects from the assembly
DROP FUNCTION SomeXYZ

-- Drop and recreate the assembly
DROP ASSEMBLY MyImportClr
CREATE ASSEMBLY MyImportClr ...

-- Create the functions
CREATE PROC SomeXYZ EXTERNAL NAME ...

-- Test the new version of my stuff.

- EoSnippet

This is not to everyones taste, of course.
Hope this helps.

|||System.DirectoryServices is not in the SQL Server's supported list of framework libraries. The supported list of libraries have gone through heavy reliability testing to ensure that they are as reliable as other components of SQL Server.
If you want to use System.DirectoryServices you can register it as an unsafe user assembly in your database (as you would do for assemblies that you develop) and use it thereafter:
Create Assembly DirSvc from 'c:\Windows\Microsoft.NET\...\System.DirectoryServices.dll'
with permission_set=unsafe

however, you need to perform adequate reliability testing to make sure that the API's you are using from System.DirectoryServices (or any such non supported assembly) meet the reliability requirements of your application.

Thanks,
-Vineet.|||I'm experiencing a problem with this also. I've added the System.DirectoryServices assembly to SQL Server as UNSAFE (using a command like the one above), and then I've added my assembly (which uses System.DirectoryServices). Since my assembly is strong-named, I didn't use the UNSAFE permission set.

Everything imported fine, but when I try to run a function from my assembly, I get an error that says I don't have permission to access active directory:

System.Security.SecurityException: Request for the permission of type 'System.DirectoryServices.DirectoryServicesPermission, System.DirectoryServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' failed.

This assembly runs fine in the development environment, which raises the question - who is this assembly running as when I execute the function from SQL Server? Is it using my trusted login to access active directory, which should work, or is it using a different login, which may not have access to read from AD?

Any help available would be appriciated.|||It is using the account SQL Server runs under. In your code, before the call to AD, try and do an impersonation (look in BOL for SqlContext.WindowsIdentity) and see if that doesn't help.

Niels|||

It turns out that, since I had to import the System.DirectoryServices assembly as "UNSAFE", I couldn't access it from my other assembly, which was imported as "SAFE". In order to access the "UNSAFE" assembly, my assembly also needed to be marked as "UNSAFE".

Not the best solution, but since I'm only doing AD reads and not writes, I'm not worried about the security implications of marking my assembly as UNSAFE. I'm glad the problem is resolved, though I'd like to see System.DirectoryServices, along with other classes from the framework, trusted in future versions of SQL2005. Perhaps we'll get that with SP1.

|||

Thank,

I was able to include the system.directory assembly in the SQL sever, but I can fully work with it.

I use a "DirectoryEntry" in conjunction with a "DirectorySearcher" and when I try to link them, I received a error message. "Unknown mechanism of authentification". I has tried to impersonate and get the same error. The DirectoryEntry look to be initialize correctly, but when I set the Search Root, the DirectoryEntry seam to loose all it's references. Some Ideas ?

There the code :

Dim AdDirEntry As New DirectoryEntry()
Dim MySearcher As New System.DirectoryServices.DirectorySearcher()
Dim WIdentity As WindowsImpersonationContext

WIdentity = SqlContext.WindowsIdentity.Impersonate()

AdDirEntry.Path = LDAP://AdServer
AdDirEntry.Username = "AdUser"
AdDirEntry.Password = "AdPassword"

' Set the research criteria for Active Directory.
MySearcher.SearchRoot = AdDirEntry
MySearcher.PropertiesToLoad.Add("location")
MySearcher.PropertiesToLoad.Add("portName")
MySearcher.PropertiesToLoad.Add("drivername")
MySearcher.PropertiesToLoad.Add("description")
MySearcher.PropertiesToLoad.Add("printername")
MySearcher.PropertiesToLoad.Add("servername")

WIdentity.Undo()

Thank for your help

Jack