permissions about start and stop service

Question

Post reply

permissions about start and stop service

sqlfriend

SSC Guru

Points: 52794
More actions
June 6, 2012 at 10:13 am

#270260

We have a sql server job set up to stop and start a windows service which is a service of a software installed on the computer.
The step1 in the sql agent job is to use operating system (cmdexec) and use a command line like:
sc \\snocgdd1 stop "myservice_name"
Step 2 is to start the service.
The job is run under SQL service account.
When it runs, it gives error, Access denied. I guess this is because the sql service account is not a windows administrator, so it cannot restart the service.
But I know we should always give sql service account the minimum permission, so I really don't want to make it a windows admin.
Then what and how to grant the account the permission to start and stop service, or any other ideas?
Thanks

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

You must create a Proxy in order to do this.

The following article addresses your issue:

http://www.mssqltips.com/sqlservertip/1199/sql-server-agent-proxies/

I had a similar issue today where I had three external users that wanted permissions to create and Execute a Job that runs an SSIS Package.

Adding the User to the SQLAgentUserRole Role is not enough.

I followed the steps in the link below and revised the SQL Scripts to allow the users to be able to run the jobs.

http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/

HTH.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

sqlfriend SSC Guru Points: 52794 More actions · Answer 2

Thanks,

In my case, the proxy account credential needs to be windows system admin to start and stop service.

For example, I use my account (as a dba), I am a windows admin, as credentials.

Then create a proxy account to add SQL service account to be a principal.

But that still means the sql service account now will work like a windows admin because it is impersonate my user account in the credentials.

To let sql service account have windows admin rights, still goes back to my initial question, is it too much to give SQl service account windows admin rights?

Thanks

sqlfriend SSC Guru Points: 52794 More actions · Answer 3

or does it mean only when the job is running using this proxy account , sql service has windows admin permissions, other time it doesnot.

I forgot to say this job owner is the sql service account.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

sqlfriends (6/6/2012)
To let sql service account have windows admin rights, still goes back to my initial question, is it too much to give SQl service account windows admin rights?

If you referring to a Domain Windows Admin the answer is yes.

Using a Domain Admin does not fit the bill with respect to best practices.:-D

http://msdn.microsoft.com/en-us/library/ms144228.aspx

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

sqlfriend SSC Guru Points: 52794 More actions · Answer 5

Thanks,

So what is the better solution for my case described above?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

I have to ask, why are you using SQL Server to stop and start a windows service?

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

The following are the steps that you should follow.

You can do this in T-SQL or SSMS.

Add the SSQLServerAgent Account to the SQLAgentUserRole Role

Create a Credential

Create a Proxy

Associate Proxy with Subsystem

Grant Permissions to Use Proxy

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

Lynn Pettis (6/6/2012)
I have to ask, why are you using SQL Server to stop and start a windows service?

Why don't you use something like VBScript and add it to a scheduler to stop and strat the service?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

sqlfriend SSC Guru Points: 52794 More actions · Answer 9

These steps are initally setup by someone else.

For now I wouldn't want to change much about it. I know they can be scheduled to run as a windows scheduler job.

And there is also a step in between stop and start to run a sql statement.

If you think that is the way to go to use windows scheduler, I will change to do that.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

sqlfriends (6/6/2012)
These steps are initally setup by someone else.
For now I wouldn't want to change much about it. I know they can be scheduled to run as a windows scheduler job.
And there is also a step in between stop and start to run a sql statement.
If you think that is the way to go to use windows scheduler, I will change to do that.

Doesn't really answer the question, just creates more. What is being done between the STOP and the START? Why doe the service need to be stopped for this?

sqlfriend SSC Guru Points: 52794 More actions · Answer 11

The sql run in between is

using waitingfor delay 2 minutes.

I know this can be done through two windows scheduler jobs.

But it seems windows job cannot decide if the previous step is successful, then go to another one.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

Still haven't got a real answer. Why does SQL Server need to STOP and START the windows service? What is the purpose?

sqlfriend SSC Guru Points: 52794 More actions · Answer 13

Let me go and find out with the person who set it up initially

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 14

The reason for stopping & starting the service is proably classified.:-)

I have used the Start & StopService method of the Win32_Service Class to do this.

I would not use SQL Server.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/