Evaluation Errors?

May 14, 2009 at 8:58 PM

Hi,

I have EPMF setup and it seems to be working pretty well, but I'm not seeing evaluation errors reported correctly.   I had a few SQL 2000 servers that I forgot to add the sqlaccount service account to and the powershell job wasn't able to connect to them.  They didn't show up in the evaluation results of the report but the evaluation errors link still lists "0". 

After looking a little closer at the powershell job, I see that all the results, (including connection failures), were put into the PolicyHistory table and the EvaluationErrorHistory table is empty.  Should these have gone into EvaluationErrorHistory?  Any suggestions on how to troubleshoot this?

Thanks

Charlie

 

 

Coordinator
May 18, 2009 at 2:12 AM

This is interesting.  Typically, all connection failures are put into EvaluationErrorHistory.  Is it possible that you were able to connect, but you were not able to execute any of the policies?  In other words, you had rights to connect, but did not have rights to see or do anything? 

I am updating the framework, and am working out some of the bugs with how errors are posted and evaluated.  I have not seen issues with connection failures writing to the PolicyHistory table, but I have found some other inconsistencies with how the errors are reported.  I hope to have a new version of the framework available in the next 2 weeks, and this should make error reporting much better. 

May 18, 2009 at 2:41 PM

The account was not able to connect at all.   Here's a sample of the failure XML that is going into the PolicyHistory table.  There's a message "Login failed for user 'DOMAIN\sqluser'" in the middle of the XML.   (note, I removed our exact server and account names)

 

 

<?xml version="1.0" encoding="utf-16"?>
<PolicyEvaluationResults
><model xmlns="http://schemas.serviceml.org/smlif/2007/02">
  <identity>
  <name>urn:uuid:96fe1236-abf6-4a57-b54d-e9baab394fd1</name>
  <baseURI>http://documentcollection/</baseURI>
  </identity>
  <definitions xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns="http://schemas.serviceml.org/smlif/2007/02">
  <document>
  <docinfo>
  <aliases>
  <alias>/system/schema/DMF</alias>
  </aliases>
  <sfc:version
  DomainVersion="3" />
  </docinfo>
  <data>
  <xs:schema
  targetNamespace="http://schemas.microsoft.com/sqlserver/DMF/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema"
  elementFormDefault="qualified">
  <xs:element
  name="EvaluationHistory">
  <xs:complexType>
  <xs:sequence>
  <xs:any
  namespace="http://schemas.microsoft.com/sqlserver/DMF/2007/08"
  processContents="skip"
  minOccurs="0"
  maxOccurs="unbounded" />
  </xs:sequence>
  </xs:complexType>
  </xs:element>
  <xs:element
  name="ConnectionEvaluationHistory">
  <xs:complexType>
  <xs:sequence>
  <xs:any
  namespace="http://schemas.microsoft.com/sqlserver/DMF/2007/08"
  processContents="skip"
  minOccurs="0"
  maxOccurs="unbounded" />
  </xs:sequence>
  </xs:complexType>
  </xs:element>
  </xs:schema>
  </data>
  </document>
  </definitions>
  <instances xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns="http://schemas.serviceml.org/smlif/2007/02">
  <document>
  <docinfo>
  <aliases>
  <alias>/PolicyStore/Policy/Backup within 24 hours/EvaluationHistory/1</alias>
  </aliases>
  <sfc:version
  DomainVersion="3" />
  </docinfo>
  <data>
  <DMF:EvaluationHistory xmlns:DMF="http://schemas.microsoft.com/sqlserver/DMF/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <DMF:Parent>
  <sfc:Reference
  sml:ref="true">
  <sml:Uri>/PolicyStore/Policy/Backup within 24 hours</sml:Uri>
  </sfc:Reference>
  </DMF:Parent>
  <DMF:ConnectionEvaluationHistories>
  <sfc:Collection>
  <sfc:Reference
  sml:ref="true">
  <sml:Uri>/PolicyStore/Policy/Backup within 24 hours/EvaluationHistory/1/ConnectionEvaluationHistory/1</sml:Uri>
  </sfc:Reference>
  </sfc:Collection>
  </DMF:ConnectionEvaluationHistories>
  <DMF:PolicyName
  type="string">Backup within 24 hours</DMF:PolicyName>
  <DMF:StartDate
  type="dateTime">2009-05-18T09:29:36.1186277-04:00</DMF:StartDate>
  <DMF:EndDate
  type="dateTime">2009-05-18T09:29:36.5386087-04:00</DMF:EndDate>
  <DMF:Exception
  type="string" />
  <DMF:ID
  type="long">1</DMF:ID>
  <DMF:Result
  type="boolean">false</DMF:Result>
  </DMF:EvaluationHistory>
  </data>
  </document>
  <document>
  <docinfo>
  <aliases>
  <alias>/PolicyStore/Policy/Backup within 24 hours/EvaluationHistory/1/ConnectionEvaluationHistory/1</alias>
  </aliases>
  <sfc:version
  DomainVersion="3" />
  </docinfo>
  <data>
  <DMF:ConnectionEvaluationHistory xmlns:DMF="http://schemas.microsoft.com/sqlserver/DMF/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <DMF:Parent>
  <sfc:Reference
  sml:ref="true">
  <sml:Uri>/PolicyStore/Policy/Backup within 24 hours/EvaluationHistory/1</sml:Uri>
  </sfc:Reference>
  </DMF:Parent>
  <DMF:ServerInstance
  type="string">SERVERNAME</DMF:ServerInstance>
  <DMF:Exception
  type="string">Microsoft.SqlServer.Management.Dmf.PolicyEvaluationException: Exception encountered while executing policy 'Backup within 24 hours'. ---&gt; Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server . ---&gt; System.Data.SqlClient.SqlException: Login failed for user 'DOMAIN\sqluser'.&lt;?char 13?&gt;
  at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)&lt;?char 13?&gt;
  at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)&lt;?char 13?&gt;
  at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)&lt;?char 13?&gt;
  at System.Data.SqlClient.SqlConnection.Open()&lt;?char 13?&gt;
  at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)&lt;?char 13?&gt;
  at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()&lt;?char 13?&gt;
  --- End of inner exception stack trace ---&lt;?char 13?&gt;
  at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()&lt;?char 13?&gt;
  at Microsoft.SqlServer.Management.Common.ConnectionManager.get_ServerVersion()&lt;?char 13?&gt;
  at Microsoft.SqlServer.Management.Smo.SqlServer.GetServerVersion(Object conn)&lt;?char 13?&gt;
  at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetServerVersion(Urn urn, Object ci)&lt;?char 13?&gt;
  at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetObjectInfo(Object ci, RequestObjectInfo req)&lt;?char 13?&gt;
  at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetObjectInfo(Object connectionInfo, RequestObjectInfo requestObjectInfo)&lt;?char 13?&gt;
  at Microsoft.SqlServer.Management.Dmf.ObjectSet.GetAdjustedFilter(TargetSet ts, Server server, PolicyCategory pc)&lt;?char 13?&gt;
  at Microsoft.SqlServer.Management.Dmf.ObjectSet.&lt;CalculateTargets&gt;d__18.MoveNext()&lt;?char 13?&gt;
  at Microsoft.SqlServer.Management.Dmf.ObjectSet.CalculateTargets(IEnumerable objectSet, Condition condition, AdHocPolicyEvaluationMode evaluationMode, Object[]&amp; conforming, TargetEvaluation[]&amp; violating)&lt;?char 13?&gt;
  at Microsoft.SqlServer.Management.Dmf.ObjectSet.CalculateTargets(SqlStoreConnection targetConnection, Condition condition, AdHocPolicyEvaluationMode evaluationMode, String policyCategory, Object[]&amp; conforming, TargetEvaluation[]&amp; violating)&lt;?char 13?&gt;
  at Microsoft.SqlServer.Management.Dmf.Policy.EvaluatePolicyUsingConnections(AdHocPolicyEvaluationMode evaluationMode, SfcQueryExpression targetQueryExpression, Int64&amp; historyId, ISfcConnection[] targetConnections)&lt;?char 13?&gt;
  --- End of inner exception stack trace ---</DMF:Exception>
  <DMF:ID
  type="long">1</DMF:ID>
  <DMF:Result
  type="boolean">false</DMF:Result>
  </DMF:ConnectionEvaluationHistory>
  </data>
  </document>
  </instances>
</model>
</PolicyEvaluationResults>

May 18, 2009 at 3:38 PM

PS - The same thing happens if I register a bogus server name.   In this case I registerd "asdfgsdf" as a server, (not a real server in our network).  The message below was part of the XML that was entered into PolicyHistory...

System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)<?

 

 

 

May 18, 2009 at 4:50 PM

Another thing I tried was putting in a bad value here...

$EvalMode = "Check"

When I did this, the PolicyErrorInsert code ran.

Server connection errors don't trigger this code though.....  Hmmm...

Coordinator
May 18, 2009 at 5:12 PM

The root issue is that the errors are written but not surfacing through the error views, correct?  I have the resolution of this issue and am testing the new version at this time.  The next version will correctly enumerate all errors.  In the meantime, are you able to query the PolicyHistory table for errors using the following:

 SELECT PolicyHistoryID
  , EvaluatedServer
  , EvaluationDateTime
  , EvaluatedPolicy
  , RIGHT(EvaluatedObject, CHARINDEX('\', REVERSE(EvaluatedObject)) - 1) 
  , ExceptionMessage
  , policy_id
  , CategoryName
  , MonthYear
  , PolicyResult
 FROM policy.v_PolicyHistory_LastEvaluation
 WHERE PolicyResult = 'ERROR'
 AND  EvaluationOrderDesc = 1

May 18, 2009 at 5:57 PM

That query doesn't return any results for me.

I'm not sure if this is a view problem or a powershell problem.  The issue I noticed is the results of connection failures are just being put into the policy.PolicyHistory table.  The table EvaluationErrorHistory is empty. When a connection failure happens, it doesn't look like this portion of the powershell script ever runs...

  trap [Exception]
  {
  $ExceptionText = $_.Exception.Message -replace "'", ""
  $ExceptionMessage = $_.Exception.GetType().FullName + ", " + $ExceptionText
  PolicyErrorInsert $HistoryServer $HistoryDatabase $ServerName $Policy.Name $ExceptionMessage;
  continue;  
  }

 

 

 

Aug 27, 2009 at 7:58 PM

tting a ton of evaluation errors.  Many more than I would expect.  One of the most prevalent is in regards to the Password Expiration and Password Policy.  This example is run against a SQL Server 9.0.3077.  It’s using an account with SYSADMIN and a local admin on the server.  The OS is WIN2K3 R2 64-bit.  Any known issues with these two.  The error is below and is the same on most.

 

Policy Name

 
   
   

SQL Server Password Policy

 
   
   

Policy Target Server

 

MyServer

 
   
   

Microsoft.SqlServer.Management.Dmf.PolicyEvaluationException: Exception encountered while executing policy SQL Server Password Policy. ---> Microsoft.SqlServer.Management.Dmf.NonRetrievablePropertyException: Property value PasswordPolicyEnforced is not available. ---> Microsoft.SqlServer.Management.Smo.PropertyCannotBeRetrievedException: Property PasswordPolicyEnforced is not available for Login [BUILTIN\Administrators]. This property may not exist for this object, or may not be retrievable due to insufficient access rights. <?char 13?> at Microsoft.SqlServer.Management.Smo.PropertyCollection.HandleNullValue(Int32 index)<?char 13?> at Microsoft.SqlServer.Management.Smo.PropertyCollection.GetValueWithNullReplacement(String propertyName, Boolean throwOnNullValue, Boolean useDefaultOnMissingValue)<?char 13?> at Microsoft.SqlServer.Management.Smo.Login.get_PasswordPolicyEnforced()<?char 13?> --- End of inner exception stack trace ---<?char 13?> at Microsoft.SqlServer.Management.Facets.FacetEvaluationContext.GetPropertyValue(String name)<?char 13?> at Microsoft.SqlServer.Management.Dmf.ExpressionNodeAttribute.DoEvaluate(FacetEvaluationContext context, Boolean checkSqlScriptAsProxy)<?char 13?> at Microsoft.SqlServer.Management.Dmf.ExpressionNodeOperator.DoEvaluate(FacetEvaluationContext context, Boolean checkSqlScriptAsProxy)<?char 13?> at Microsoft.SqlServer.Management.Dmf.Condition.Evaluate(Object target, AdHocPolicyEvaluationMode evaluationMode)<?char 13?> --- End of inner exception stack trace ---

 

Thanks,


Eddie H.

Coordinator
Aug 28, 2009 at 7:00 PM

Hello Eddie.  It looks like you need to update your policy to filter the target logins to SQL logins only.  Based on the error you supplied, the policy is failing to evaluate the BUILTIN/Administrator, and the PasswordPolicyEnforced setting is only available on SQL logins.  I hope this helps!