May 27, 2008 at 8:02 am
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
May 27, 2008 at 8:32 am
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
May 28, 2008 at 2:03 am
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
May 28, 2008 at 7:25 am
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
May 29, 2008 at 6:37 am
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
May 29, 2008 at 11:05 am
No problem 🙂
"Got no time for the jibba jabba!"
-B.A. Baracus
June 11, 2008 at 9:04 am
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.
June 11, 2008 at 9:08 am
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
February 17, 2009 at 8:20 am
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.
March 10, 2009 at 5:20 pm
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.
March 11, 2009 at 2:56 am
As the_t has mentioned before:
It seems that your network account needs to be an administrator of the server which has the instance installed. If I run SSMS 2005 as my domain admin account this all works fine. however as my non admin account I cannot see the icons.
We have recently had to start using low/high privilege accounts due to some new legislation coming through for local government.
If you are having this issue and it suddenly happens I would start by asking if anyone has been changing any group policies/ user access rights to the servers. To get around it right click on the SSMS icon and use run as and use an account that has administrator rights to the server. If you are using Vista then download and use something called shell run as. Vista doesnt come with this feature as standard and microsofts solution is to use fast user switching and log on as another user.
May 26, 2009 at 6:09 pm
I'm trying to allow my SQL DBAs to restart SQL Server service remotely through SSMS without giving them local admin rights to the SQL Server. I was able to give them permissions to restart service through GPO. If I give them Remote Desktop permissions to the server (not local admin), they can connect through RDP and manage SQL Server service, (which to me means they have necessary rights), but they can not do it from SSMS. What am I missing? I tried assigning users Logon as service right with no effect. Any pointers would be greatly appreciated!
July 16, 2009 at 2:50 am
Hi there,
I am now having a similar issue with ssms 2008. Installed it yesterday and when it imported by server registrations, i get the server state on all but one. Its a 64-nit a/p that worked under ssms 2005.
Can you help?
Adam Zacks-------------------------------------------Be Nice, Or Leave
July 17, 2009 at 5:31 am
You need remote access to the target machine registry. Some sites lock this down, so you may need to talk to your Windows people to see what has been done.
Google can show you a lot of articles that explain moer about remote registry access.
Ultimately, if you have the correct rights there is no need for a DBA to have local Administrator access on any machine. The problem lies in finding what rights are needed...
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
July 17, 2009 at 5:41 am
Thanks Ed.
WIll speak to Winel team and let you guys know how it goes.
Adam Zacks-------------------------------------------Be Nice, Or Leave
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply