jobs on cluster do not run in sqlserveragent account context

• hans@vesys.be

  SSC-Addicted

  Points: 462

  More actions

  November 30, 2005 at 3:54 am

  #110947

  Hi,

  according to BOL, jobs should run in the context of the SqlServerAgent account

  when run by a member of the sysadmin role. We found out that this is not

  true on our clusters : they run in the context of the Cluster service account.

  This is what happens :

  Cluster1 uses domain account DOM\Account1 for its cluster service. SQL Server

  and Agent both run under DOM\Account2. Both accounts are local Admins on

  both cluster nodes.

  Cluster1 is Windows2003 with SQL2K SP4. The Proxy account has not been set.

  A simple job with 1 step CmdExec step containing "echo %username%" returns

  DOM\Account1. It doesn't matter whether it's run as sa, or even DOM\Account2.

  Same thing happens when I run "xp_cmdshell %username%" in QA logged in as

  DOM\Account2.

  Cluster2 uses domain account DOM\Account3 for its cluster service. SQL Server

  and Agent both run under DOM\Account2. Both accounts are local Admins on

  both cluster nodes.

  Cluster2 is Windows2003 SP1 with SQL2K SP4. The Proxy account has not been

  set.

  A simple job with 1 step CmdExec step containing "echo %username%" returns

  DOM\Account3. It doesn't matter whether it's run as sa, or even DOM\Account2.

  Same thing happens when I run "xp_cmdshell %username%" in QA logged in as

  DOM\Account2.

  I'm probably overlooking the part in BOL where this is mentioned. Is this

  normal behaviour?

  Thanks

  Hans

• K. Brian Kelley

  SSC Guru

  Points: 114704

  More actions

  November 30, 2005 at 7:12 am

  #606717

  This is a known bug. Though the CmdExec job step is actually running with the account set for the service, it is being misreported. It's documented here:

  BUG: A SET USER Command Run by Using an Xp_cmdshell or a Cmdexec Job Step Returns Incorrect Value for the Service Log On Account in a SQL Server 2000 Cluster (821270)

  Now the article only mentions Windows 2000 clusters, but we see it on Windows Server 2003 clusters as well.

  K. Brian Kelley
  @kbriankelley

• hans@vesys.be

  SSC-Addicted

  Points: 462

  More actions

  November 30, 2005 at 7:22 am

  #606724

  Hi Brian,

  thanks for the information ! It looks like it is known since September 2003 already...

  Hans

• K. Brian Kelley

  SSC Guru

  Points: 114704

  More actions

  November 30, 2005 at 8:22 am

  #606758

  I guess it was. We discovered it only a few months ago ourselves. Even with the article, I had to do some testing to prove that what the article said was correct. Basically you can verify by creating a file share and giving access to the SQL Server Agent service account and not the cluster service account. When it succeeds in accessing the file, you know which account was used. Reverse the permissions and you'll get an access denied, this confirming the first result.

  K. Brian Kelley
  @kbriankelley