2

I'm trying to add my first CLR routine to SQLServer 2008 (will upgrade to 2014 soon). The goal is to add a user object to Active Directory, passing in a dozen or so attribute values, to create the user based on data coming from an interface to an ERP (the interface works fine, the data is good). A colleague create a compact C# class that wraps the PrincipalContext, actually took the code from another working application and streamlined it a bit (see below).

We were able to turn on CLR, set the DB to TRUSTWORTHLY, and so on, then do the CREATE ASSEMBLY's for his code and the resources it needs, with UNSAFE option since the code is not part of Microsoft's subset of .NET classes vetted to run within SQLServer:

alter database Util SET TRUSTWORTHY ON

CREATE/ALTER ASSEMBLY SystemDSProtocols
FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.DirectoryServices.Protocols.dll'
WITH PERMISSION_SET = UNSAFE

CREATE/ALTER ASSEMBLY SystemDSAccountManagement
FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.DirectoryServices.AccountManagement.dll'
WITH PERMISSION_SET = UNSAFE

CREATE/ALTER ASSEMBLY XXXXXXXXX_CLR
FROM 'C:\XXXXXXX\XXXXX\XXXXXXXX.CLR\XXXXXXXX.CLR.dll'
WITH PERMISSION_SET = UNSAFE

(All the creates and subsequent ALTERs run fine, we ALTER the last one whenever we change the custom class library XXXXXXX_CLR which i've obscured here... the CLR stored procedure creates fine, and executes OK up until it tries to SAVE the user through the PrincipalContext object. Code and error message we are getting follow.

I've read 50 web pages looking for an answer to this error; we've temporarily made the user that SQLserver runs as a Domain Admin (dev system--terrible practice I'm sure), called it from a logged in SQL user with domain admin rights, given the domain service account that the code is able to pass in for explicit .NET AD connection domain admin, taken the user and password options off the call to PrincipalContext to see if SQLServer's user or the calling user's privs would control it, tried different combinations of the Options on the PrincipalContext constructor call, etc.

        // Create PrincipalContext
        PrincipalContext principalContext = null;
        try
        {
            // as given, used Simplebind only
            principalContext = new PrincipalContext(
                ContextType.Domain,    
                LdapName,              // Domain name  xxxx.yyyy
                StudentContainer,      // ou=aaaa,ou=bbbb,dc=xxxx,dc=yyyy
                ContextOptions.Negotiate
                //ContextOptions.SecureSocketLayer
                //ContextOptions.SimpleBind,
                //              | ContextOptions.Signing
                                | ContextOptions.Sealing,
                //              | ContextOptions.ServerBind
                ContextUser,
                ContextPassword
            );
        }
        catch (Exception ex)
        {
            throw new Exception("Failed to create PrincipalContext. Exception:", ex);
        }

        using (principalContext)
        {
            using (var p = new MaricopaUserPrincipal(principalContext))
            {
                p.SamAccountName = xxxxUserId;
                p.Name = xxxxUserName;

.... etc. for remaining attributes....we've done this using FIM and other C# middleware libary calls for years, ignore the details...we definitely have the correct AD attributes covered with the params we are passing in but I don't want to expose all that here.

So here's the error message that always appears (I know it is executing the C# CLR code OK as I had other attribute validation issues within the class library CLR to deal with, and it finally got to the point of making the call to PrincipalContext and fails there):

Msg 6522, Level 16, State 1, Procedure sp_XXXXXAddXXXXX_CLR, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_XXXXXAddXXXXX_CLR": 
System.Exception: Could not save user principal. Exception: ---> System.UnauthorizedAccessException: General access denied error
System.UnauthorizedAccessException: 
   at System.DirectoryServices.AccountManagement.ADStoreCtx.Insert(Principal p)
   at System.DirectoryServices.AccountManagement.Principal.Save()
   at XXXXXXX.CLR.xxxxxManager.CreateUser(String xxxxUserId, String xxxxUserName, ...other params follow)
System.Exception: at XXXXXXX.CLR.xxxxxManager.CreateUser(String xxxxUserId, String xxxxUserName, ...other params follow)

I read some stuff about Windows Identity "impersonation" but I can't understand why that is needed if I can't even get AD to run the PrincipalContext call when all the services involved are running as domain admin privs. I know I can't use that approach for real (security non-starter), but would like to know why it doesn't work and what alternative to try here so I can arrive at a solution that would fly for a production implementation. I did bounce SQLServer after putting the service account it is running under in the domain admins group, BTW.

Many thanks for your kind review of this long-winded problem.

  • That code does run if you add it to a simple console program? The callstack mentions a CreateUser method. I don't see that directly in the code snippet you have. Where is principal.Save called? – rene Oct 03 '15 at 19:21
  • It sounds like you covered the bases and then some. By default, the security context for SQLCLR is the Log On As account of the SQL Server NT service (each instance has its own service, so be sure you are setting the Login for the correct instance). If you aren't using Impersonation, then this is the only account to configure for security, and whatever login calls the stored proc is irrelevant. If you enable Impersonation, then the Windows Login (not SQL login) running the proc is the only security context you need to worry about. You might need to enable delegation for the account being used. – Solomon Rutzky Oct 03 '15 at 20:14
  • Also, have you checked the Windows Event Log? There is often additional info in there that could help. And is AD set up with a policy that disallows certain account-oriented actions from certain machines? or only allows from certain machines? A great test would be if you could create a console app of this code, put it on this SQL Server machine, and run it from xp_cmdshell (which is also running in the security context of the Log On As account of the SQL Server NT service). – Solomon Rutzky Oct 03 '15 at 20:31
  • rene, there's an invocation like "p.save" down below the code I included, after another dozen or so attributes are set. CreateUser starts dozens of lines above the part I showed where it goes to actually create the AD user object; I think it boils down to an access control/security problem. srutzky, thanks for the great suggestions, we'll run those down Monday morning. – Jason Pociask Oct 04 '15 at 07:25

0 Answers0