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
783 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 49

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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
 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to selectively show certain fields based on user input using rules to gather relevant information and data from your forms. The rules feature provides you with an opportunity…

756 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