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.