April 20, 2010 at 3:27 am
Hi all
I've searched quite a bit but can't find anything that can help me on this.
I need to stop and start a service on my Windows server sitting at my ISP, but doing a "net stop xxx" using xp_cmdshell gives me "Access is denied."
Is there a way that I can runt the "net stop xxx" bit as user administrator?
Maybe this is more a Windows question than MSSQL...
Many thanks,
Hein
April 20, 2010 at 9:14 am
I would create a CLR function to do this.
If you must use xp_cmdshell you can set up a proxy account. See BOL about configuring a cmdshell proxy.
The probability of survival is inversely proportional to the angle of arrival.
April 20, 2010 at 10:11 am
Thanks, I've tried the proxy account earlier, but it seems I need to restart the server for that to take effect. I could not access the server other than with T-SQL. I actually wanted to use xm_cmdshell to restart the VNC service so that I can regain access. I've had the ISP restart the server in the mean time.
I would still like to know if this is possible. I've seen the runas command but it prompts for a password. How does one feed the password in the buffer again? I tried
echo <password> | runas /user:Adminstrator notepad on my local computer but it immediately comes back with incorrect password.
I remembered since I have installed freeSSHd so I could have accessed the command shell on ssh!
Ah well, no urgent need for the moment. Thanks for your time.
April 20, 2010 at 11:14 am
The item that struck me was "at my ISP". What it sounds like is that SQL does not run as a priviliged account (in the administrators group) and therefore may be unable to do this directly. You might setup a proxy in SQL agent, which shouldn't require a SQL restart and probably not even an Agent restart.
Does the ISP allow you to start and stop services yourself?
CEWII
April 20, 2010 at 12:39 pm
I have a dedicated server at the ISP. The VNC I use to remote desktop to it stopped working. I just needed to restart the service.
My SQL Server Service is running under NT AUTHORITY\NetworkService.
I tried the Proxy route under the admin user, but to no avail.
April 20, 2010 at 1:26 pm
I did a quick search to see if NT AUTHORITY\NetworkService would normally have enough rights to stop/start a service, but I didn't see any answers or anything pointing me that way. Also, where are you setting your proxies up? In SSMS under SQL Server Agent under Proxies? If so that only affects SQL Agent jobs. The Server Proxy Account in server properties is typically used for xp_cmdshell users who are non-admin users. It almost always has less rights than the login SQL is running under.
I would setup a SQL Agent proxy and setup a SQL Agent job to use that proxy that executes your net stop/start command. It is unclear if this is what you tried before.
CEWII
April 21, 2010 at 2:00 am
Elliott W (4/20/2010)
I did a quick search to see if NT AUTHORITY\NetworkService would normally have enough rights to stop/start a service, but I didn't see any answers or anything pointing me that way. Also, where are you setting your proxies up? In SSMS under SQL Server Agent under Proxies? If so that only affects SQL Agent jobs. The Server Proxy Account in server properties is typically used for xp_cmdshell users who are non-admin users. It almost always has less rights than the login SQL is running under.I would setup a SQL Agent proxy and setup a SQL Agent job to use that proxy that executes your net stop/start command. It is unclear if this is what you tried before.
CEWII
I'm using the proxy settings as attached.
How would I call the SQL Agent Job?
Many thanks,
Hein
April 21, 2010 at 9:14 am
You are using the Server proxy. That (if I remember correctly) is only used by non-sysadmin users when they call xp_cmdshell. So even though you have it setup you probably aren't using it.
The server proxy is typically used to keep a normal user from getting a rights escalation.
I'm concerned that you don't know how to start a SQL Agent job. What version and edition of SQL are you using?
You can start it either by right-clicking on the job and clicking "start job at step" or using the sproc msdb.dbo.sp_start_job.
If you are using Express then this isn't going to be an option.
CEWII
April 21, 2010 at 9:21 am
Rats they dropped it then from MSDE to Express. Too bad.
Thanks for the info re. Proxy. So it has no effect on my xpcmd_shell right then.
April 21, 2010 at 9:31 am
no the way you are using it..
But you could create a SQL login that is NOT in the sysadmin role AND has been granted EXEC rights to xp_cmdshell. That might use that.
CEWII
April 21, 2010 at 9:37 am
Elliott W (4/21/2010)
no the way you are using it..
So I can still schedule stored procedures with SQL Express 2005 & 8? I can't find the option in Management Studio. I see the service, but it's not starting because aparently nothing is using it. Sorry I degress, but a pointer would be much appreciated.
But you could create a SQL login that is NOT in the sysadmin role AND has been granted EXEC rights to xp_cmdshell. That might use that.
CEWII
Will try that, thanks.
April 21, 2010 at 9:46 am
hein-1120388 (4/21/2010)
Elliott W (4/21/2010)
not the way you are using it..So I can still schedule stored procedures with SQL Express 2005 & 8? I can't find the option in Management Studio. I see the service, but it's not starting because aparently nothing is using it. Sorry I degress, but a pointer would be much appreciated.
That is a yes/no question. What I mean by that is that SQL Agent is NOT included and will NOT against express versions. However, with that said you CAN use the windows task scheduler. So it is sort of yes and sort of no.. But either way you don't do it in Management Studio..
When I recommended using SQL Agent I had made an assumption (apparently a bad one) that you were not using an express version. So that recommendation is withdrawn..
CEWII
April 21, 2010 at 9:58 am
Ok. I use SQL Server Standard om my web server but have SQL Express installed at clients. So your comment is still valid. I had some database management tasks that I normally ran on the desktops, but since SQL Express I had to do away with it. Windows task scheduler on XP can only do daily schedules.
April 21, 2010 at 10:04 am
hein-1120388 (4/21/2010)
Ok. I use SQL Server Standard om my web server but have SQL Express installed at clients. So your comment is still valid. I had some database management tasks that I normally ran on the desktops, but since SQL Express I had to do away with it. Windows task scheduler on XP can only do daily schedules.
I completely disagree that Task Scheduler can only do daily schedules. It can do daily, weekly, monthly, once, at system startup, at login, and when idle. I just looked at mine and I'm running XP..
CEWII
April 21, 2010 at 10:17 am
Elliott W (4/21/2010)
hein-1120388 (4/21/2010)
Ok. I use SQL Server Standard om my web server but have SQL Express installed at clients. So your comment is still valid. I had some database management tasks that I normally ran on the desktops, but since SQL Express I had to do away with it. Windows task scheduler on XP can only do daily schedules.I completely disagree that Task Scheduler can only do daily schedules. It can do daily, weekly, monthly, once, at system startup, at login, and when idle. I just looked at mine and I'm running XP..
CEWII
Sorry I meant as opposed to hourly.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply