SQL Server Performance

sql 2005 cluster CheckServiceAlive:service is dead

Discussion in 'SQL Server 2005 Clustering' started by NewDBA, Jun 20, 2007.

  1. NewDBA Member

    Hi

    I have active/passive environment with sql 2005 sp1 on windows 2003 sp2
    I wanted to change account for sql services .
    I added account to SQL domain group, to local admin, Loged to virtual sql server, used SQL configuration manager changed startup account - as per Microsoft.
    Rebooted services in sql config. manager
    After change everything except Sql Service agent works fine ( starts and failover)
    My sql server agent will start on Node1 but will not failover to node 2 - will fail to start on node 2.

    If I change account back - everything works again.

    I actually tested this scenario: changed the account on full text, sql service agent,
    sql integration services to new one and left mssql service account to be old.
    This time everything worked fine. - including sql service agent.

    Errors in cluster log:
    [sqagtres] CheckServiceAlive : service is dead

    could someone help with this?
    thank you
  2. satya Moderator

    Do you have any antivirus software installed on this server?
    Also check for any Access Violation based errors from event viewer.

    http://support.microsoft.com/kb/290128

    KBAhttp://support.microsoft.com/kb/254321 refers
    quote:
    Security has changed for SQL Server 2005 failover clusters from security for SQL Server 2000 failover clusters. By default, SQL Server 2005 is more secure, and changes have been made to some of the basic requirements. SQL Server 2000 installations required the logon service accounts to be members of the local Administrators group. With SQL Server 2005, all service accounts that will administer SQL Server, SQL Server Agent, Full-Text Search, or SQL Server 2005 Analysis Services must be added to a domain group that has been added to the local Administrators group.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. NewDBA Member

    I do have virus scan but here is what it saing in application even log:


    SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role).


    I did added my new account to sysadmin - what have I missed?

    Maybe any other group that I should add it to ?
  4. NewDBA Member

    actuall when I check my sql server log this is the error I am getting :

    Message
    SQL Server blocked access to procedure 'dbo.sp_sqlagent_get_startup_info' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online.


    not sure if it is the cause or postmessage
  5. satya Moderator

    As you can see the solution is described on the error too [<img src='/community/emoticons/emotion-1.gif' alt=':)' />].<br /><pre id="code"><font face="courier" size="2" id="code">USE master;<br />GO<br />EXEC sp_configure 'show advanced option', '1';<br />RECONFIGURE;<br /><br />EXEC sp_configure;<br /></font id="code"></pre id="code"> <br /><br />The following enables the Agent XPs<br /><br /><pre id="code"><font face="courier" size="2" id="code">EXEC sp_configure 'Agent XPs', '1';</font id="code"></pre id="code"><br /><br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
  6. NewDBA Member

    Ok - I figured that out:

    simply created an other account : account # 3.
    and

    1. added it to domain group that originally was used as group to set up cluster
    2. added to local admin
    3. added to sql as user ; make account sysadmin
    4. went to local policy on compter and maked sure that account has the same priviledges as account #1
    5. Only after that I went on server ( virtual cluster server not virtual sql server) and
    changed the account using sql server configuration manager

    It;s working now.

    What I am trying to say is that sequence of those event seem to have crutial effect.

    My account #2 was not sysadmin at the time when I first changed it. And eventhoght i did added it to sysadmin role after - my sql agent was still failing with the same message
    I guess it was too late to do after the fact of sql account change.


    With account #3 I assigned all rights first and then changed the account

    At least that the only logical explanation that I can think of . It;s working on my end now and I hope somebody will avoid the mistake of sequence which I did.
  7. jsciii New Member

    quote:1. added it to domain group that originally was used as group to set up cluster
    2. added to local admin
    3. added to sql as user ; make account sysadmin

    Looking at step 2 and 3, if the domain group is in the SYSADMIN role and the account is in the domain group, is it necessary to add the account individually?

    Thanks
  8. satya Moderator

    It is better to add explicitly into local admins in this case.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  9. ACDC New Member

    Hi
    I had same problem, and reason was that Net Framework 2.0 didn't work in ore node. So, we decided to uninstall and to install it. After that, the problem was finished.
    Note: We discovered tha Net Fr.2.0 didn't run executing Caspol.exe command in Framework directory (C:WINDOWSMicrosoft.NETFrameworkv2.0.50727) in each node. If command requests errors, probably you should think about this.
    Best Regards

Share This Page