Is it possible to use T-SQL to create a user in Active Directory?

Is it possible to use T-SQL to create a user in Active Directory?

• Marco Otte-Witte

  Larry,

  A stored procedure is more along the lines of what I am looking for anyway. I have been going over some documentation regarding LDAP, ADSI, and System.DirectoryEntry information, but they all basically require that I write code using VB, C++, or C#.

  To further explain the purpose, My company is going to eventuallu migrate to an HRIS system that they want to basically control all levels of user creation in all the various systems that we use. That being said, the HRIS system uses a SQL db to store all of it's information. So once a user is created in the HRIS system we want it to create users in all of our other system that use SQL db's as well. THe problem that I am having is that they want this HRIS system to also create the users in AD & Exchange. Hence I have been looking for a method to write directly to AD through a SQL SP.

  I hope that all makes sense.

   

  ~JH

  
  

• bday55

  Mr. Pope,

  Thank you sir, that is exactly what I am looking for and through all of my searches on the internet I never cam accross this. So thank you again. I will work on making this into a SP that SQL can call upon. Also, thank you for the heads up about security, as I'm sure it come up...

  You have been a huge help.

  ~JH

  
  

• drinian

  One way is to create a .Net function/stored proc (CLR integration) and pass in the required parameters to create the new user in AD.
  
  This would require that the function be programmed in a .NET language (C#, VB etc) and you would need to figure out how to pass parameters to a .Net function.  I have yet to do this so I can't be much help.  I'm sure there are people in the other support groups that may have experience in this.
  
  Here is a link to a whole bunch of C# code that should be a good starting point for the .NET code.
  http://www.dotnettreats.com/tipstricks/adnet.aspx#s10
  
  The one area of concern is the security context under which the stored proc runs.  The user/account under which the .NET function/proc runs will need to be a member of the Administrators group on your domain.  Otherwise you won't be able to add an entry.  The trigger may run under the context of the Database Engine account or may inherit the context of the user that entered the record.  I'm not absolutely sure.
  
  Larry Pope
  
  

• prem_kumar_25

  While having the HRIS system manage this would be great, as of right now it does not have that capability. I figured that since it utilizes a SQL database I could create a trigger event to "trigger" a stored procedure to create the user in Active Directory. Mainly because I am having to do this same process to create users in other systems, i.e. the user created inHRIS is going to create user in our accounting software, time management (Payroll), benefits, CRM software, etc... But currently the only missing piece is create a user in AD.

  I am just not sure how the syntax should be in order to invoke a command in AD from SQL. While scripting is probably a viable (if not only) option, I am wanting to automate that process.

  Thank you for your help so far, I hope that you continue to have insight.

  ~JH

  
  

• Phillip Carruthers

  You could see if HRIS has any interfaces to AD, that way you wouldn't have to code anything.  If that is not the case, then you will have to code something or look to third party offerings. 
  
  If you don't need the user to be created as soon as the user is added into HRIS, then you could use SSIS to run your scripts to create the users that were recently added/deleted/disabled.
  
  The only question that really needs to be answered is group membership and whether or not that would be populated in HRIS and how they are then mapped to AD groups.
  
  Here is a link to various scripts that can be used to manage AD.
  http://www.microsoft.com/technet/scriptcenter/scripts/ad/default.mspx
  
  I'm not sure if this is applicable to HRIS or not, but have you looked at Microsoft Identity Integration Server
  
  Larry Pope
  
  
  
  

• Emilis

  John,
  
  Not directly.  T-SQL does not support the collections necessary to correctly populate Active Directory (i.e. Group Memberships, etc).  You could in theory write a CLR Stored Procedure that creates a new user in Active Directory and then call the stored proc from T-SQL.
  
  What is the purpose of populating AD from SQL   I might be able to give you some direction on other approaches.
  
  Larry Pope
  
  
• Is it possible to use T-SQL to create a user in Active Directory?