July 16, 2012 at 12:15 pm
Hi!
I did many searches but I am not able to get the exact answer for what I am looking for.
We have a SQL 2008 server.
To make a short story: I work in a college. Students are able to access Wifi using their student account. Every day, with the sql agent job, we run a stored procedure that compile data for the daily use for each student. Data are stored in a SQL table with the username, date, consommation (gb). At this time, if they use more than 20 GB, their internet account are closed until first of next month. We want automatize this task.
I though to create a new sql stored procedure that I could call just after the one that compile data is finished.
The way we stop user to access Internet is by an Active Directory group. If the user has the group Wifi, it is ok. If he doesn't have the Wifi group, the access to Internet will not work anymore.
My SQL Select command works fine (select the username where the consommation in the month exceed 20 GB). I know I have to add a Linked Server for Active Directory. After I would like to know of the username ISMEMBER() of the Wifi group. If yes, I would like to remove the group from the user account..?? Is it possible?? I don't think so but I would like for someone to confirm me..
The application I created is written in asp c#. I know how to do this (remove a group in one user account based on a SELECT query) in c# but I don't know how to call this programmation.. Could you help me please? English is not my first language. I hope I didn't make too much mistakes.. 🙂
Thanks
July 17, 2012 at 1:47 am
I dont think it is possible in SQL to update LDAP as I have only ever seen it being selected from.
One option that could potentially work, would be to build a powershell script which does the adding and removing from the WiFi group based on an array of usernames passed in from your stored procedure.
Store the users who have hit limit in a table, then powershell connects to SQL, gets the usernames from the table and removes them from the group, then at 1'st of month, get everyone from the table and add them back, truncate the table.
July 17, 2012 at 1:54 am
think it could be possible if you enable xp_cmdshell then use a dsmod command
what I would also suggest is that you add the users to an internet deny group rather than removing them
July 17, 2012 at 1:58 am
Can you force xp_cmdshell to open an elevated command window on the domain controller(s).
This is assuming that the OP is following best practise and has SQL installed on another server which is not the domain controller(s).
To use dsmod you need to have ADDS installed and run it from an elevated command prompt, hence why I thought that powershell might be the better option.
July 17, 2012 at 4:13 am
I'm fairly sure you can run the AD command line tools from any machine, workstation or server, thats in the domain not just a DC, you will of course need the correct level of privlages to make the required group membership change.
The only test i've done on this is to run dsmod /? from a workstation in 2003 and it doesn't throw an error
July 17, 2012 at 4:15 am
the technet link http://technet.microsoft.com/en-us/library/cc778414%28v=WS.10%29.aspx doesn't say if you need to run this from a DC either
July 17, 2012 at 4:17 am
One other thing having xp_cmdshell enabled is a risk so as part of your process you should enable and disable it as required
July 17, 2012 at 6:17 am
Thanks a lot. I will evaluate your recommandations.
You really help me!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply