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?

  • 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 '15 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 '15 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 '15 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 '15 at 18:59
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.

  • 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 '15 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 '15 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 '15 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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