SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Unable to Start/Stop SQL Agent remotely through SSMS


Unable to Start/Stop SQL Agent remotely through SSMS

Author
Message
DavidT-603995
DavidT-603995
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 247
Hi Folks

I'm sure I have seen this issue before (in SQL 2000) but cannot for the life of me remember or find out what the specific issue is!

Using 2005 Management Studio, connected to remote servers, both SQL 2000 and SQL 2005, the SQL Agent does not show me it's status through the icon. Further more when I right click on it, both the start and stop options are disabled.
Also, the jobs underneath do not display their last run status (ie: if failed).

These are fine when I connect to the remote server and fire up SSMS from there, and when I look at the properties of my local SQL Server instance.

For the remote servers in question, both the SQL Server and SQL Server Agent services are run under the same domain user account.

No doubt it is a security issue somewhere, but please can anyone give me some pointers, (or better still, tell me exactly what I need to do to resolve this!!!)

Many Thanks
David
the__t
the__t
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2916 Visits: 419
If the account you are using in Object Explorer (this is your local Windows account) is a local administrator on the server in question (either explicitly, or through an group), the icon will behave as expected, and the options to stop/start/restart the agent will be accessible in the right-click menu.

"Got no time for the jibba jabba!"
-B.A. Baracus
DavidT-603995
DavidT-603995
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 247
Hi

Thanks for your response.
Perhaps you can help fill me in as to what I am missing!

The Server is logged on using an domain account, which is the administrator on the network.
The domain user is also a local administrator on the machine.

The SQL services are set up to run under the Local System account (in fact I have two instances on this machine, one is run under the local system (both SQL Server and SQL Server Agent) and the other is run under the aforementioned Domain account)

I am connecting to these server instances (through object explorer) from my local box using the sa account.
I cannot connect to the object explorer using the domain account as it is a windows login, and not my local windows login... I have always connected using sa in the past.

Have just moved up to SQL 2005 for this server, and did not have this problem with 2000 - has 2005 closed the door on something I am unaware of, or am I just missing something obvious.

?...Why can I not control the status of the SQL agent from my local machine...?

Thanks for any help on this

David
the__t
the__t
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2916 Visits: 419
What you see in Object Explorer, is actually a connection to the server using your local machine credentials, not any SA credentials. When you click on the server in registered servers (or click "Connect" from Object Explorer), you get a dialog box where you can enter SA credentials (or whatever) to log into the server, manage the databases, run queries, etc.

But the actual object explorer window doesn't care about those sa credentials, it is using your local machine credentials.

So, you (USER A) open up SSMS and hit "Connect" in Object Explorer and fill in information (sysadmin) to connect to SERVER A - if USER A is a local administrator on SERVER A, you will get the green arrow and be able to manage the Agent by right-clicking in Object Explorer. If USER A is not a local admin on SERVER A, you won't be able to. It makes no difference at all that you logged into SSMS (connected to SERVER A through SSMS) as sysadmin.

I hope that helps, it sounds confusing when I read it, but hopefully I made it clear enough.

"Got no time for the jibba jabba!"
-B.A. Baracus
DavidT-603995
DavidT-603995
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 247
Thank you very much for taking the time to reply.

Your response made perfect sense and provided me with the "missing link" of information I needed.

Having previously working where all our servers were "in-house", and where my account was a domain administrator, explains why I have not had this problem before.

Now where I work I am not an admin, thus the issue.

I have added myself as an administrator on one of our local boxes, and I can now see the agent status and stop start etc... - great.

Our other servers are remote and hosted by an external hosting company, thus I cannot add my self as an Admin User on them as they are not on our network. (Or if it can be done, I don't know how to!). Not to worry, at least I understand now!

(My previous comment about not having this problem here using SQL2000 tools was clearly incorrect).

Thank you for explaining this.

Many Thanks
David
the__t
the__t
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2916 Visits: 419
No problem Smile

"Got no time for the jibba jabba!"
-B.A. Baracus
MattFritz
MattFritz
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 17
Did you have to restart sql server for this to take effect? I'm having the same problems and added myself to the remote computer as an administrator to the machine and sql server, but I still can't get the agent start\stop options to appear on my local machine.
DavidT-603995
DavidT-603995
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 247
Hi

No, I did not need to stop start either the agent or the server services when I added myself as an admin on the local server in order to see the options.
I did not/have not set myself up as an admin on the remote server, and thus still cannot see the stop start commands, (but at least I know why).
David
Craig Pa
Craig Pa
Old Hand
Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)

Group: General Forum Members
Points: 320 Visits: 333
Hi,

Has anyone had this problem using a clustered instance? I seem to be ok will all of our other servers apart from this one. I have recently gone to vista enterprise. We are running sql2005 64bit standard on our cluster and I am unable to manage the agent or main mssql service. It not too much of a problem as I know this should be done throught the cluster administrator anyway but the fact the status icon does not show is quite frustrating as I can't take a quick glance to make sure the agent is up and running etc.
Joan OBryan
Joan OBryan
SSC-Addicted
SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)

Group: General Forum Members
Points: 454 Visits: 552
I am having this problem all of a sudden on both my 2000 and 2005 consoles. In Enterprise Manager (2000), I can't see the status of the SQL Server Agent, both the start and stop appear in the right-click menu, and no account is specified in the Service startup account in the Properties. I do see the status of the servers in EM. I'm using Windows authentication.

On 2005 SSMS, it's even worse, as no status appears on the icons for the servers or for the agent.

The only change I was aware of was patching my desktop with Windows updates, so I removed those, but the behavior in SQL Server Enterprise Manager has not changed. I didn't bother trying it on the machine with SSMS.

The one anomaly is that on my 2005 SSMS, I have one server that uses a domain account as the service account and I can communicate to that server with no problem. Green arrows appear on both the server and the agent icons. None of my other servers have service accounts configured with domain accounts (but they do use Windows accounts as opposed to the system account), but that has never caused a problem in the past.

I'm hoping someone has some insight into this sudden loss of functionality.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search