December 19, 2005 at 3:02 pm
Hi, first time poster. My situation is as follows:
On our network where I work we've got an Active Directory group called noPolicy. If you are a part of this group, a popup window appears when you log on asking you to take a work policies quiz. Once you have successfully completed that quiz, you are supposed to be automatically removed from the noPolicy group so you don't get that popup window on logon anymore.
The process for removing users from this group goes like this:
A. From the quiz page (an ASP.NET page), when someone has successfully completed the quiz, xp_cmdshell is called with the argument of C:\noPolicy.vbs usersnamegoes here.
B. The noPolicy.vbs script takes the username passed to it and removes them from the noPolicy group in active directory.
This is all fairly straightforward, and it all worked just like it was supposed to until recently. Now, for some reason xp_cmdshell fails to remove a user from the noPolicy group when it is executed from the web box.
I have established that:
A. xp_cmdshell itself works fine. It returns results when I run other commands, such as a dir command (which returns the contents of the root directory).
B. The script for removing the user from Active Directory works fine. When I run it from the command-line after logging directly into the SQL box in which it is contained, it does exactly what it's supposed to.
So the disconnect is somehow occurring between xp_cmdshell and the VBscript. xp_cmdshell by itself works fine. The script by itself works fine. But try and use xp_cmdshell to run the script and for some reason it fails. This has to be permissions-related, but I'm not sure what could be stopping it. I do know that the user SQL is set up to run xp_cmdshell as is a domain admin user with all the authority it needs to make changes in Active Directory, and after all this was all working fine until just recently. I can't figure out what might've changed.
Well, if anyone is willing to read through all that and attempt a response, I will be very appreciative. This has been a real head-scratcher.
December 19, 2005 at 4:18 pm
Have you tried to redirect the command shell output to a file? This could give you some possible error messages to look at. If it is permissions related, you may have an error in the file.
For example: EXEC xp_cmdshell 'C:\noPolicy.vbs > c:\temp\output.txt'
December 19, 2005 at 4:27 pm
I just tried that, it doesn't seem to be putting anything in the file...unfortunately VBScript has very poor error handling. Anyways I'm fairly sure it has to be a permissions issue, since it seems very unlikely that anything else could be the culprit.
December 20, 2005 at 8:08 am
What type of login are you using to call xp_cmdshell in SQL Server? Is it a SQL Server login, or are you using a domain account? If this is permissions, there are a couple of things to look at. First, the account that you are using in SQL Server must have execute permissions on xp_cmdshell. Secondly, if it is accessing VB script in your file system, it must also have at least Read permissions to the directory where you are keeping the VB script. From your original problem description, it looks like your VB script is being stored on the root of your C:\ drive. I would recommend moving this to a folder. Depending on what account you are using, you may not have permissions to the root of C:\. Create a folder and move the VB script into that folder. Then check and adjust your security permissions on that folder according to the type of SQL Server authentication you are using.
December 20, 2005 at 8:22 am
1) If your user account which is running xp_cmdshell is a member of the sysadmin fixed server role, has the SQL Server service account changed recently?
2) If it isn't, is the SQL Agent proxy account still set?
3) In either case, can your directory administrators tell you if said Windows account still has permissions to make membership changes on the group in question?
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply