0

Running SQL Server 2008 (10.0.5520) - I have a trigger that is executed when a new record is added to the security table. I would like to extend this trigger, and create an entry in the AD system with a couple of the parameters from the insert.

Question is: can this be accomplished? If so, how?

It looks like a command line tool does exist:

Technet reference

To create a contact named Jeff Hay in a top-level OU named Service Dept in the fabrikam.com domain, at a command prompt, type the following command, and then press ENTER:

dsadd contact "cn=Jeff Hay,ou=service dept,dc=fabrikam,dc=com" 

Can this command line be executed from the trigger?

4
  • 3
    There's a lot of potential for security issues here and I would recommend AGAINST this sort of process. It means the SQL Service account has to have rights and you'll execute something like xp_cmdshell (which opens other holes). What's the problem you're trying to solve? Why do you want to create an AD account in this way?
    – Mike Fal
    Aug 3, 2015 at 16:49
  • 4
    Don't do this from within the trigger! The trigger should be fast and nimble - it should not do heavy lifting and time-consuming processing - and it should most definitely not be calling up an external resource that might take quite some time to respond! Instead - let your trigger make an entry into a "command" table, and have a separate process (T-SQL job or a little utility in any other language) read and interpret that command table and handle the actual creation of an AD user..
    – marc_s
    Aug 3, 2015 at 16:49
  • 1
    Service Pack 4 is available for SQL Server 2008. You should patch your SP3 instance in addition to the fine advice from marc_s and mike fal
    – billinkc
    Aug 3, 2015 at 18:44
  • All great points - thank you SP4 will not be added, as the software vendor does not support this program anymore. The triggers are pigs currently, I will try the xp_cmdshell first, if that causes problems, i will roll it out to a separate process.
    – pithhelmet
    Aug 3, 2015 at 18:59

1 Answer 1

2

What @Mike_Fal said. This is a huge gaping security hole. Not only does SQL need xp_cmdshell, but it will need Domain Admin rights. I also agree with the other sentiments around not using a trigger.

You can still tie it in with your existing process and make it database driven with the security table you are using. I'd probably write a separate, asynchronous process to read from the security table and add the AD users that way. This is a great job for PowerShell; connecting multiple systems and technologies.

3
  • Your recommendations are still based on the security hole(s) that you presented. I will still need AD rights - and that appears to be your largest concern. The trigger is already written, it is a pig, and the system is already perceived as very slow. so a few more cycles will not be noticed. The requirements have changed since the message was posted, so a different angle will need to be addressed. but thank you for the posts!
    – pithhelmet
    Aug 5, 2015 at 11:38
  • Yes, the process that ultimately adds the user to AD needs domain admin rights. Building it in a trigger means that the SQL Service account needs domain admin rights. Big difference.
    – cjsommer
    Aug 6, 2015 at 14:03
  • Regardless, it sounds like you are taking a different direction based on performance alone. Good luck with whatever solution you come up with!
    – cjsommer
    Aug 6, 2015 at 14:17

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.