Importing and evaluating policies

Jun 15, 2012 at 10:10 PM
Edited Jun 15, 2012 at 10:25 PM

Hi All, 

I've just setup the EPM framework on a 2008 r2 CMS and i'm not fully understanding how to import policies and evaluate them.

I have registered a server in my CMS which i can connect to and query ok.  i have a policy on this server and can evaluate it locally but how do I evaluate it using the CMS?

Also, when i run the powershell script (after setting all the variables to be relevant to my environment) it doesn't populate any table.

Im really interested in this project and would appreciate any help in getting me up and running.

Cheers, 

Chris

Jun 15, 2012 at 10:35 PM

Never mind - I got it working.  Turns out letting windows name your machines (im playing on vms at the moment) is a bad idea and i got epically confused.  I put the right cms servername in the powershell script and created the mdw db on the cms instance and it works now. 

 

doh!

Coordinator
Jun 16, 2012 at 4:01 AM

Ha, glad you got it figured out! Yeah setting up EPMF definitely has its tricky parts. On my to-do list is putting together a series of videos stepping through the process. I just wrapped up writing on a few books so hopefully I'll be able to get this done in next couple of months.

Jun 16, 2012 at 4:07 PM

a video guide would be excellent :)

I've managed to get the whole thing set up including report server but my policies are failing with the following message - any ideas?

 

My environment:

1 windows 7 64bit machine hosting 2 VMs (windows server 2008 r2 enterprise with sql 2008 r2 enterprise).  This is a play machine.

 
Policy Target Server
 
SQLDB03
 
   
   
Microsoft.SqlServer.Management.Dmf.PolicyEvaluationException: Exception encountered while executing policy Recovery Model. ---> Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server . ---> System.Data.SqlClient.SqlException: Login failed for user NT AUTHORITY\ANONYMOUS LOGON.<?char 13?> at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)<?char 13?> at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)<?char 13?> at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)<?char 13?> at System.Data.SqlClient.SqlConnection.Open()<?char 13?> at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)<?char 13?> at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()<?char 13?> --- End of inner exception stack trace ---<?char 13?> at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()<?char 13?> at Microsoft.SqlServer.Management.Common.ConnectionManager.get_ServerVersion()<?char 13?> at Microsoft.SqlServer.Management.Common.ServerConnection.Microsoft.SqlServer.Management.Common.ISfcConnection.get_ServerVersion()<?char 13?> at Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection.get_ServerVersion()<?char 13?> at Microsoft.SqlServer.Management.Dmf.ObjectSet.<CalculateTargets>d__18.MoveNext()<?char 13?> at Microsoft.SqlServer.Management.Dmf.ObjectSet.CalculateTargets(IEnumerable objectSet, Condition condition, AdHocPolicyEvaluationMode evaluationMode, Object[]& conforming, TargetEvaluation[]& violating)<?char 13?> at Microsoft.SqlServer.Management.Dmf.ObjectSet.CalculateTargets(SqlStoreConnection targetConnection, Condition condition, AdHocPolicyEvaluationMode evaluationMode, String policyCategory, Object[]& conforming, TargetEvaluation[]& violating)<?char 13?> at Microsoft.SqlServer.Management.Dmf.Policy.EvaluatePolicyUsingConnections(AdHocPolicyEvaluationMode evaluationMode, SfcQueryExpression targetQueryExpression, Int64& historyId, ISfcConnection[] targetConnections)<?char 13?> --- End of inner exception stack trace ---
Jun 26, 2012 at 11:20 PM

Chris,

     Your error appears to be related to the PowerShell codes inability to connect to one of the servers registered in the CMS folder you are evaluating.  From the error message it appears you are trying to connect with NT AUTHORITY\ANONYMOUS LOGON.  This could be a result of the service account running the SQL Server Agent running under this account or you might be using a Proxy with this account.  It is important to remember that the PowerShell evaluation will run under the context of the SQL Server Agent even if you are manually running the job in Management Studio.  You can either change the SQL Agent Service account or assign a Proxy for PowerShell.

The best method to troubleshoot the login failed issue is to ensure the SQL Server Agent service account has rights to all the servers in the folder you are evaluating.  To verify all permissions are granted you can run the Management Studio under the context of the service account by holding Shift+Right click on SQL Server Management Studio and select "Run as different user".  Provide the credentials for the SQL Agent service account and verify connection by navigating to your Registered Servers -> Central Management Servers and attempting a multi-server query connecting to all the servers.  If you are able to connect to all servers in the list then the PowerShell code should not run into any further errors connecting.  If the multi-server query is still showing errors then run a simple query and the Messages tab will tell you which server connections are failing.