Solved

Can't start nor stop SQL Server Agent from EM on the client computer but started and stopped OK from the server

Posted on 2008-06-17
20
778 Views
Last Modified: 2013-12-04
I've recently moved the SQL Server 2000 to a new server.   SQL Server is unchanged but the OS is changed.  The OS version of the old box is Win 2000 and the new box is Win 2003.  I am wondering if this is the reason why I am no longer be able to see the status, start or stop SQL Server Agent from my computer.   I have no problem with that if I do it from the server either in Enterprise Manager or from the Services.  Another thing is broken after the move is that the store procedure that references the linked server returned the error.  
Error Diagnostic Information
ODBC Error Code = 42000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied on object 'xp_cmdshell', database 'master', owner 'dbo'.
SQL = "exec GetContentInfo @SiteID=379, @ContentID=7601"
Data Source = "COMMONSITEINFO"
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (25:3) to (25:48).
The error occurred while processing an element with a general identifier of (CFRETHROW), occupying document position (246:11) to (246:21).
Date/Time: 05/21/08 10:49:11
Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; InfoPath.1)
Remote Address: 129.4.54.6

This is the code of the store procedure:  
IF @Status = 1
 BEGIN
  --dbo.Content
  UPDATE SPDBDEV01LINKED.CommonSiteInfo.dbo.Content
  SET Hits = Hits+1
  WHERE ContentID = @ContentID AND SiteID = @SiteID
 END

If I remember correctly, these are the outline of what I dd to migrate the data after the new OS (Wind 2003( has been installed on the new server
1.  Install SQL Server on the new server
2.  Apply the patch and hot fix on the new server so that the versions are the same on both old and new servers.
3.  Shutdown SQL services on the both servers
4.  Copy all data files and log files
5.  Have the system admin to name the new server to be the same as the old server.
6.   Restart SQL Server services on the new server.
Everything is working fine except for the above two problems.   Could it be because the security is more tighten in Win 2003?   This is not the first time this SQL Server is moved.  The first time it was moved the OS version was unchanged and everything was working fine after the migration.  

I am stuck and this is the only place I think I can get help from.

Thanks.
mayxanh

0
Comment
Question by:mayxanh
  • 10
  • 7
20 Comments
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21811365
OK,

It is likely a couple of different things...

First the linked server - have you added a linked server login, and if so, has it got @useself = 'TRUE' which basically means your login/ sql server login needs to also exist on the new server - you will need to check credentials at a windows level as well as a sql server level ...

secondly the execute xp_cmdshell is also an advanced function, so likely has not been turned on as yet in the new environment...

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

that also needs the appropriate security to run, so the login id to the remote server needs to have appropriate permissions...
0
 

Author Comment

by:mayxanh
ID: 21818150
I've just found out that the linked server is not a problem.   Thanks anyway, Mark.

So now what is left is the first problem which is the one causing the access denied when starting or stopping SQL Server Agent from the client machine.  

Thanks,
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 21820535
You are using same user account to run SQL Server Agent and SQL Server service?
If not, check if the user is locked.
0
 

Author Comment

by:mayxanh
ID: 21823170
Yes, I am using the same user account for both.   The problem happened only after we migrated the Win 2000 server to Win 2003 server.  If I log the server via Smart Card, I would be able to start and stop SQL Server service without any problem.  
0
 

Author Comment

by:mayxanh
ID: 21900589
I am still hoping there is a solution for this.  

Anyway, I am new to this and do not know how the point system works.  Can anyone tell me?

Thanks.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21901143
Are you using Windows authentication ? and does that user have the appropriate priveleges ?

Might be best to set up a domain account, and then add that account as a sysadmin user in SQL server, then use that account to start the services.

Have a look at : http://support.microsoft.com/kb/283811
also: http://support.microsoft.com/kb/282254

also have a look at... http://msdn.microsoft.com/en-us/library/aa176610(SQL.80).aspx

I do recommend you set up a seperate (win domain) user, and add them to the sysadmin users (in sql) and use that user identity to start the service...
0
 

Author Comment

by:mayxanh
ID: 21901304
The services are already owned by a domain account.   The SQL Server agent status shows in the Enterprise Manager on the server.  I can start and stop the SQL Server agent from EM on the server.  All of these can't be done from the EM on my workstation.   I used to be able to do all of these until we moved the server to the new server with the new OS.  We upgraded the OS from Win 2000 and Win 2003.   I suspect that something on Win 2003 that stops the remote operation on the SQL Server agent.

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21901382
Well, there is a breakdown somewhere in that chain of security... It is a bit different you logging onto EM and starting / stopping the service, and as you say, it only went pear-shaped when you moved to a new operating system, so it is likely to do with the user - have a look at those links...
0
 

Author Comment

by:mayxanh
ID: 21901396
I have no idea how to go from here.   Do you have a specific suggestion?  Thanks.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 21904656
Well not sure myself,

You original error message didn't really indicate a problem with SQL Server agent as such,
You say you have checked all user security,
Don't know if you read the Microsoft Links,

Have you checked events and logs ? If it is trying to start as a service and fails, then there must be an error log... Need to see that.
0
 

Author Comment

by:mayxanh
ID: 21909030
Sorry for not not describing the error clearer.    The actual problem is that both the start and stop options for SQL Server Agent are listed instead of just listing either start or stop.  Also the status indicator is off.  This happens only on my computer but not on the server.   I log to EM under my NT account and I have a sys admin privilege.  This has been never changed.   The SQL Server  Agent service never failed.  It just that I was not allowed to start nor stop and could not see the status of the Agent.  
0
 

Author Comment

by:mayxanh
ID: 21956167
Any other suggestions, please?  Thanks.

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21956400
So, it is your local install causing problems on your machine - tell me, what do you see on the sql server icon in the task manager - then choose the drop down to select SQL Server Agent - do you have auto start checked ?
SQL-Sys-Tray-Icon.jpg
0
 

Author Comment

by:mayxanh
ID: 21956714
I am using the same Enterprise Manager on my local computer to monitor the other SQL Servers.  I have no problems with any of them.    I did not have any problem with this database server either before this server was replaced.   So I wouldn't say there is problems with my local install.   To answer your questions, SQL ServerAgent auto-start is checked.  Please note that SQL Server Agent is running fine.  I can start and stop SQL Server Agent from the Services Control Panel as well as from the EM that is on the server.   However, it throws me the error if I do it from the EM on my local machine.   This problem occurs only after the database server is replaced with the new machine and this new machine now has a new version of OS (Win 2003 instead of Win 2000 as before).  I expect to see the little arrow on the SQL Server Agent button to indicate its status.  In this case, there should be an green arrow because the Agent is actually running.   Also I expect to see only the Stop button is available for selection and the Start button is grayed out.   But instead both of the start and stop options are on and they both give the Access is Denied error if either one is selected.  I am attaching the



Error.doc
0
 

Author Comment

by:mayxanh
ID: 21956717
screen shot of the error.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21958124
Have you got the latest service pack 4 ? there are some new "features" and changes for SQL Agent... Have any firewalls (though would expect sql server to be a problem) been updated ? What about domain user controls - SQL server Agent is bound more by Domain users that SQL server (though, that domain user must also exists in sysadmin groups).

Have a read of and do a find for SQL Server Agent : http://download.microsoft.com/download/1/b/d/1bdf5b78-584e-4de0-b36f-c44e06b0d2a3/ReadmeSql2k32sp4.htm

also have a look at section 6 in : http://www.microsoft.com/technet/archive/security/chklist/sql2ksrg.mspx?mfr=true

also have a look at : http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c12ppcsq.mspx

I think you will find the problem somewhere in the security matrix for the domain user, the privileges, and that users access to the other sql servers...
0
 

Author Comment

by:mayxanh
ID: 21958389
I got it to work now after I added my NT account to be in the local admin group.    Accoring to my company policy, only the smart card account is allowed to be in the local admin group but not the regular NT account.  But for the sake of the test, I added it in.  Thanks Mark for giving me the idea.
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 21958909
That would do it...  The old admin group... Glad you found the "missing link" - I was running out of ideas...
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

SHARE your personal details only on a NEED to basis. Take CHARGE and SECURE your IDENTITY. How do I then PROTECT myself and stay in charge of my own Personal details (and) - MY own WAY...
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now