Scheduled job fails to run

Nov 21, 2011 at 9:35 PM
Edited Nov 21, 2011 at 9:36 PM

Hello everyone,

I have some policies created, I have the PowerShell script on the server, I've registered the central management server, created groups within the CMS, created a seperate database to hold the EPM data, run the Create_Objects.sql script and have successfully completed several manual runs and evaluated my policies against my various databases. 

My problem is that the Agent Job I created to automatically run EPM_EnterpriseEvaluation_3.0.0.ps1 fails.  The message in policy.EvaluationErrorHistory is:

ErrorHistoryID	EvaluatedServer	EvaluationDateTime		EvaluatedPolicy
13		VMDEV-APP2	2011-11-21 14:39:54.750	Database Auto Shrink
EvaluationResults
System.UnauthorizedAccessException, Access to the path D:\MSSQL10.CMS\EPM\Results\VMDEV-APP2_Database Auto Shrink.xml is denied.

Step 1 of my Agent job has the "Run as:" section set to "SQL Server Agent Service Account" which is the only option available.  I'm new to SQL Server and realize that the problem is not the script but the configuration of security.  Can someone tell me either 1) How to update the security of the SQL service account to allow it to run the job _OR_ 2) Explain how to update the security to allow other accounts to run the EPM Agent job?

Ken

Nov 21, 2011 at 10:32 PM

After doing some reading, I think there are two solutions to this:

1) My SQL database runs under the account svcsql_2008.  I can create a database login for this account and grant the appropriate permissions so it can run the agent job(s).  This may open up a security hole but seems like the best solution.  svcsql_2008 is not in the "Remote Desktop Users" group so there is no remote access granted to this account.  Right now, this account does not have the ability to connect to my SQL Server as it does not have a Login defined.

2) I can create a proxy account that references my personal account.  Since my personal account is in the Windows Administrator group, this violates the suggestion from Microsoft (in http://msdn.microsoft.com/en-us/library/ms190926.aspx) that says "Do not run the SQL Server Agent service under a Microsoft Windows account that is a member of the Windows Administrators group".

So it looks like I'm adding additional privileges to my service account.  Is this what other people are doing?

Ken

Coordinator
Nov 21, 2011 at 11:48 PM

Close. Your SQL Agent Service needs the rights on the target servers to evaluate the policies.  You can either set up a proxy account or run the SQL Agent service with a domain account that has rights on the target servers.  I would strongly recommend a proxy account set up with a domain account to execute the job.  I do not recommend running the SQL Agent service with such high priveleges to all of your servers. 

Nov 22, 2011 at 3:45 PM

My apologies but I don't understand your reply.  I'm new to SQL Server and am struggling with how security works and how it integrates with the OS.

Currently I have a domain account called "svcsql_2008" that is a member of the "Domain Users" group and nothing else.  "Domain Users" is defined as "All domain users" and my understanding is that it is the minimal requirement for all accounts on our network.  Currently, svcsql_2008 does not have the ability to run the powershell epm script as it does not have some needed privilege.  My plan was to figure out which specific privileges were missing and grant that access so the automated job will run.

Your reply recommends setting up a proxy account with a domain account.  Are you recommending a proxy to my existing svcsql_2008 account or do you think I need a new domain account?  Based on what I can see about my svcsql_2008 account, it appears to have very little privileges.  On my desktop, when I run Start > Admin Tools > Active Directory Users & Computers and search for svcsql_2008, I see the properties show this account is only a member of the Domain Users group.  On my SQL Server, when I run Start > Administrative Tools > Server Manager and navigate to Configuration > Local Users and Group, I do not see svcsql_2008 in any of the groups listed.  The only place I can find reference to svcsql_2008 is when I go to Start > Administrative Tools > Services and check the properties for the SQL services.  For all SQL-related services, like "SQL Server (CMS)" and 'SQL Server Agent (CMS)", the Log On tab of the properties shows "APACORP\svcsql_2008".  Other than being the Log On account for the service, svcsql_2008 does not appear to have any privileges at all so I'm confused by your comment "I do not recommend running the SQL Agent service with such high priveleges to all of your servers".  Now that you understand what privileges my domain account svcsql_2008 has, do you still recommend creating a proxy?  If so, do you recommend having the proxy access my svcsql_2008 account or a newly created domain account?  Finally, can you recommend and URLs that explain SQL Server security and all the local server groups?  I'm really struggling to understand how this all ties together.

Thanks for you patience.

Ken

Coordinator
Nov 26, 2011 at 1:14 AM

Hello Ken, 

You will need to set up an account that has rights to all of the SQL Server instances that will be evaluated.  The level of authority depends on the policies and what/how you are evaluating.  If you are evaluating objects that require higher priveleges, you may need to grant the account view server state (on all target servers) and view database state (on all databases).  If you are running the script with the option to fix, you will need priveleges to make changes - this may be as high as CONTROL SERVER/db_owner, but I am not in a position to tell you how to configure the security.

Once you have the account set up on all of the target servers, you will use this account as a proxy account for the SQL Server Agent job.  This is discussed in the EPM Framework documentation, and Allen White has a good blog talking about how to set this up (http://sqlblog.com/blogs/allen_white/archive/2008/05/06/use-a-sql-agent-proxy-for-special-tasks.aspx). 

Dec 2, 2011 at 8:00 PM
Edited Dec 2, 2011 at 8:36 PM

OK, I have created a new domain account "svcsql_2008EPM" and have added it as a login to my two development SQL servers.  When I look at the properties for these logins, I see Server Roles of  bulkadmin, dbcreator, diskadmin, ... etc.  but currently only "public" has a check beside it.  Under User Mapping, there are no check marks.  This is the same on both dev servers.  You mention granting "view server state":  Where do I find that?  Is that in SSMS or is that buried in the OS some place?

I also took my new svcsql_2008EPM account and added it as a login to my central management instance.  I then created a credential named "svcsql_2008EPM_credential" that references the account including its current password.  Next I created a "svcsql_2008EPM_proxy" proxy and granted it access to all subsystems except for replication which I am not using.  Finally, I have a Agent job with one step:

SL D:\MSSQL10.CMS\EPM
.\EPM_EnterpriseEvaluation_3.0.0.ps1 -ConfigurationGroup "Development_2008" -PolicyCategoryFilter "" -EvalMode "Check"

I have confirmed that when I open a PowerShell through SSMS, the above commands work.  However, when I try to run the job through the agent, it fails with the error shown below.  Can you provide any hints on what the source of my problem is?

Ken

 

Date        12/2/2011 1:33:30 PM
Log        Job History (Enterprise_Policy_Management_Master_Job)

Step ID        1
Server        VMDEV-APP2\CMS
Job Name        Enterprise_Policy_Management_Master_Job
Step Name        Run_EPM_PS_script_on_Dev_2008
Duration        00:00:03
Sql Severity        0
Sql Message ID        0
Operator Emailed       
Operator Net sent       
Operator Paged       
Retries Attempted        0

Message
Executed as user: APACORP\svcsql_2008EPM. 
The job script encountered the following errors.
These errors did not stop the script:
A job step received an error at line 25 in a PowerShell script.
The corresponding line is 'Remove-Item -Path $ResultDirDel'.
Correct the script and reschedule the job.
The error information returned by PowerShell is:
'Access to the path 'D:\MSSQL10.CMS\EPM\Results\VMDEV-APP2%5CDEV01_Data and Log File Location.xml' is denied. '
A job step received an error at line 25 in a PowerShell script.
The corresponding line is 'Remove-Item -Path $ResultDirDel'.
Correct the script and reschedule the job.
The error information returned by PowerShell is:
'Access to the path 'D:\MSSQL10.CMS\EPM\Results\VMDEV-APP2%5CDEV01_Database Auto Shrink.xml' is denied. '
A job step received an error at line 25 in a PowerShell script.
The corresponding line is 'Remove-Item -Path $ResultDirDel'.
Correct the script and reschedule the job.
The error information returned by PowerShell is:
'Access to the path 'D:\MSSQL10.CMS\EPM\Results\VMDEV-APP2%5CDEV01_Datafile_FreeSpace_Threshold_Policy.xml' is denied. '
A job step received an error at line 25 in a PowerShell script.
The corresponding line is 'Remove-Item -Path $ResultDirDel'.
Correct the script and reschedule the job.
The error information returned by PowerShell is:
'Access to the path 'D:\MSSQL10.CMS\EPM\Results\VMDEV-APP2%5CDEV01_Track database size policy.xml' is denied. '
A job step received an error at line 25 in a PowerShell script.
The corresponding line is 'Remove-Item -Path $ResultDirDel'.
Correct the script and reschedule the job.
The error information returned by PowerShell is:
'Access to the path 'D:\MSSQL10.CMS\EPM\Results\VMDEV-APP2_Data and Log File Location.xml' is denied. '
A job step received an error at line 25 in a PowerShell script.
The corresponding line is 'Remove-Item -Path $ResultDirDel'.
Correct the script and reschedule the job.
The error information returned by PowerShell is:
'Access to the path 'D:\MSSQL10.CMS\EPM\Results\VMDEV-APP2_Database Auto Shrink.xml' is denied. '
A job step received an error at line 25 in a PowerShell script.
The corresponding line is 'Remove-Item -Path $ResultDirDel'.
Correct the script and reschedule the job.
The error information returned by PowerShell is:
'Access to the path 'D:\MSSQL10.CMS\EPM\Results\VMDEV-APP2_Datafile_FreeSpace_Threshold_Policy.xml' is denied. '
A job step received an error at line 25 in a PowerShell script.
The corresponding line is 'Remove-Item -Path $ResultDirDel'.
Correct the script and reschedule the job.
The error information returned by PowerShell is:
'Access to the path 'D:\MSSQL10.CMS\EPM\Results\VMDEV-APP2_Track database size policy.xml' is denied. '
A job step received an error at line 73 in a PowerShell script.
The corresponding line is 'while ($dr.Read()) { '.
Correct the script and reschedule the job.
The error information returned by PowerShell is:
'Exception calling "Read" with "0" argument(s):
"The SELECT permission was denied on the object 'sysmanagement_shared_server_groups', database 'msdb', schema 'dbo'." '.
Process Exit Code -1. The step failed.
Dec 2, 2011 at 8:41 PM

Progress: Within Windows Explorer, I right-clicked on D:\MSSQL10.CMS and selected Properties > Security > Edit > Add > svcsql_2008EPM with the following permissions: Modify, Read & Execute, List, Read and Write.  This cleared the 'Access to Path' errors but the job still fails with the following error:

Executed as user: APACORP\svcsql_2008EPM. A job step received 
an error at line 73 in a PowerShell script. The corresponding line is 
'while ($dr.Read()) { '. Correct the script and reschedule the job. 
The error information returned by PowerShell is: 'Exception calling 
"Read" with "0" argument(s): "The SELECT permission was denied 
on the object 'sysmanagement_shared_server_groups', database 
'msdb', schema 'dbo'."  '.  Process Exit Code -1.  The step failed.

Dec 2, 2011 at 8:51 PM

Baby steps:  A quick search found that sysmanagement_shared_server_groups is a view within the msdb database so I edited the properties on svcsql_2008EPM and under "User Mapping", I put a check next to msdb and selected the role of db_datareader.

Now when I try to run the job through the agent, I get the following error:

Executed as user: APACORP\svcsql_2008EPM. The job script 
encountered the following errors. These errors did not stop the 
script:  A job step received an error at line 81 in a PowerShell script. 
The corresponding line is '            Invoke-PolicyEvaluation -Policy 
$Policy -TargetServerName $ServerName 
-AdHocPolicyEvaluationMode $EvalMode -OutputXML > $OutputFile;'. 
Correct the script and reschedule the job. The error information 
returned by PowerShell is: 'Property 'EngineEdition' does not exist.  
'  A job step received an error at line 81 in a PowerShell script. The 
corresponding line is '            Invoke-PolicyEvaluation -Policy 
$Policy -TargetServerName $ServerName 
-AdHocPolicyEvaluationMode $EvalMode -OutputXML > $OutputFile;'. 
Correct the script and reschedule the job. The error information 
returned by PowerShell is: 'Property 'EngineEdition' does not exist.  
'.  Process Exit Code 0.  The step succeeded.
This appears the be somehow related to security permissions for Invoke-PolicyEvaluation.  Hmm, back to searching . . .

Dec 2, 2011 at 10:23 PM

I added svcsql_2008EPM to the "Remote Desktop Users" group then signed on to my CMS server, ran SSMS and started a powershell.  I took my two custom created policies and saved them as xml files and tried to run them along with two Microsoft-delivered policies.  Imagine my surprise when my policies ran but the Microsoft ones errored out.  Here's the powershell commands that work:

Set-Location "D:\Program Files (x86)\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033"
Invoke-PolicyEvaluation -Policy 'Datafile_FreeSpace_Threshold_Policy.xml' -TargetServerName 'VMDEV-APP2' -AdHocPolicyEvaluationMode 'Check' -OutputXML
Invoke-PolicyEvaluation -Policy 'Track database size policy.xml' -TargetServerName 'VMDEV-APP2' -AdHocPolicyEvaluationMode 'Check' -OutputXML

Here are the ones that fail:

Invoke-PolicyEvaluation -Policy 'Data and Log File Location.xml' -TargetServerName 'VMDEV-APP2' -AdHocPolicyEvaluationMode 'Check' -OutputXML
Invoke-PolicyEvaluation -Policy 'Database Auto Shrink.xml' -TargetServerName 'VMDEV-APP2' -AdHocPolicyEvaluationMode 'Check' -OutputXML

and here's the error:

 

Invoke-PolicyEvaluation : Property 'EngineEdition' does not exist.
At line:1 char:24
+ Invoke-PolicyEvaluation <<<<  -Policy 'Database Auto Shrink.xml' -TargetServerName 'VMDEV-APP2' -AdHocPolicyEvaluationMode 'Check' -OutputXML
    + CategoryInfo          : NotSpecified: (:) [Invoke-PolicyEvaluation], MissingPropertyException
    + FullyQualifiedErrorId : PolicyEvaluationError,Microsoft.SqlServer.Management.PowerShell.InvokePolicyEvaluationCommand

Weird huh?  What permissions am I missing to allow my service account to run the Microsoft delivered policies?

 

Ken

Coordinator
Dec 3, 2011 at 1:24 AM

Ken, I have a feeling your PowerShell script might not be setup correctly. Have you correctly set the configurations?

Dec 5, 2011 at 4:17 PM

I followed all the steps in "EPM Configuration Documentation v3.docx" found on the download of http://epmframework.codeplex.com/.  As far as I know I have everything configured properly.

Ken

Dec 5, 2011 at 5:30 PM

In addition to CodePlex, I've posted this question of a few other forums and nobody has been able to help.  I've gone ahead and opened a case with Microsoft premier support to try and figure out why this doesn't work.  I'll update this discussion with any changes.

Coordinator
Dec 5, 2011 at 7:19 PM

You're getting an engine edition error. What condition filters do you have on that policy? Are those policies the ones that came from Microsoft? If so, I've had issues with them before where for some reason they don't play nice down level. Try this (worked for me): Create a new policy manually that does the exact same thing, in this case looking at the database facet and evaluating @Auto_Shrink = False. Have your script use that Policy in lieu of the Microsoft one and see if you still get error.

Dec 5, 2011 at 8:35 PM
Edited Dec 6, 2011 at 3:57 PM

Previously I was connecting using my personal administration account and was confused when receiving different errors.  To avoid confusion I've opened the properties of my  service account "svcsql_2008EPM", gone to User Mapping, highlighted the msdb database and put a checkmark on SQLAgentUserRole.  Now, I sign on to my CMS instance using my svcsql_2008EPM account, run SSMS and try to run the agent job.  Doing this gives me a slightly different error:

 

Date        12/5/2011 2:18:18 PM
Log        Job History (Enterprise_Policy_Management_Master_Job)

Step ID        1
Server        VMDEV-APP2\CMS
Job Name        Enterprise_Policy_Management_Master_Job
Step Name        Run_EPM_PS_script_on_Dev_2008
Duration        00:00:01
Sql Severity        0
Sql Message ID        0
Operator Emailed       
Operator Net sent       
Operator Paged       
Retries Attempted        0

Message
Unable to start execution of step 1 
(reason: JobOwner APACORP\svcsql_2008EPM doesn't have permissions to use proxy 3 for subsystem PowerShell). The step failed.

I've tried googling that error message but can't find any helpful information.  I've also tried creating a new condition as a copy of 'Auto Shrink Disabled' and updated the policy to use the new condition but it didn't make any difference.  Do you know if Microsoft has any step-by-step documentation on how to configure accounts to user EPM?

As for the conditions, I have 4 policies - two from MS and two custom babies.  The conditions vary depending on the nature of the policy with two (Data and Log File Location & Track database size) having "Every database", one (Database Auto Shrink) with "Online User Database" and one (Database FreeSpace Threshold Policy) having "Every File in Every FileGroup in User Databases".

Ken

Dec 5, 2011 at 11:02 PM

Microsoft got back to me with the suggestion to add svcsql_2008EPM to the sysadmin group.  That has resolved the issue of "doesn't have permissions to use proxy 3 for subsystem PowerShell" but I'm back to "Invoke-PolicyEvaluation... The error is: 'Property 'EngineEdition' does not exist.".  Just spent 1.5 hours on the phone with Premier support and they can't figure out what the problem is. 

Question:  Is it possible to run EPM will less than local administrator privilege?

Coordinator
Dec 6, 2011 at 1:55 AM

The engineedition error is the I got using the MS policies. Can you please export your policies and send them to me? I can take a look at them for you. jsegarra@pragmaticworks.com

Dec 6, 2011 at 3:56 PM

Solution: I added my service account (svcsql_2008EPM) to the sysadmin group on ALL my database instances and the job now runs.

Job:

Date        12/6/2011 9:28:18 AM
Log         Job History (Enterprise_Policy_Management_Master_Job)

Step ID        1
Server        VMDEV-APP2\CMS
Job Name        Enterprise_Policy_Management_Master_Job
Step Name        Run_EPM_PS_script_on_Dev_2008
Duration        00:00:11
Sql Severity        0
Sql Message ID      0
Operator Emailed       
Operator Net sent       
Operator Paged       
Retries Attempted   0

Message
Executed as user: APACORP\svcsql_2008EPM. The step did not generate any output.  Process Exit Code 0.  The step succeeded.

Results:

SELECT EvaluatedPolicy, EvaluatedServer, PolicyResult, COUNT(*)
FROM policy.v_PolicyHistory
group by EvaluatedPolicy, EvaluatedServer, PolicyResult

EvaluatedPolicy EvaluatedServer PolicyResult (No column name) Data and Log File Location VMDEV-APP2 FAIL 4 Data and Log File Location VMDEV-APP2 PASS 39 Data and Log File Location VMDEV-APP2\DEV01 FAIL 1 Data and Log File Location VMDEV-APP2\DEV01 PASS 1 Database Auto Shrink VMDEV-APP2 FAIL 1 Database Auto Shrink VMDEV-APP2 PASS 42 Database Auto Shrink VMDEV-APP2\DEV01 PASS 2 Datafile_FreeSpace_Threshold_Policy VMDEV-APP2 FAIL 22 Datafile_FreeSpace_Threshold_Policy VMDEV-APP2 PASS 30 Datafile_FreeSpace_Threshold_Policy VMDEV-APP2\DEV01 FAIL 1 Datafile_FreeSpace_Threshold_Policy VMDEV-APP2\DEV01 PASS 1 Track database size policy VMDEV-APP2 PASS 43 Track database size policy VMDEV-APP2\DEV01 PASS 2