CMPivot from CAS - Issues

Copper Contributor

Has anyone tired CMPivot from CAS? with Double hop scenario, I have defined the constrained delegation. Any query is being passed from CAS to Primary and eventually clients, clients return the results and getting stored in primary DB, however the CMPivot window in CAS displays the following error "Unable to access data". Followed by the below error on SMSprovider.

 

Invalid object name 'dbo.cmpivotresult'. : vSMS_CMPivotTask

Could not use view or function 'vSMS_CMPivotTask' because of binding errors.

Could not use view or function 'vSMS_CMPivotTask' because of binding errors.

 

When i try to find the view on the CAS DB i could not find one, not sure why then this view is being referred. Eventually the CMPivot is not working from CAS.

Any help will be highly appreciated 

7 Replies

@Sudar123 

vSMS_CMPivotTask  should be there on CAS and primary site database. It has dependency on the view CMPivotResult.

The error you saw means on the CAS, it has not successfully configured the SQL object CMPivotResult, that usually means the kerberos delegation is not configured properly. Or SPN for the SQL service is not registered properly.

On CAS site server, can you check rcmctrl.log to see if there is any error configuring the SQL object CMPivotResult (On CAS, it should be converted to a view that does distributed query against the primary site database, if CAS site server has permission to make the configuration)

 

Thanks

Richard

hi @Richard_ConfigMgr 

i have the same issue.

 

inside the rcmctrl.log, i have this messages:

failed to create view CMPivotResult. SMS_REPLICATION_CONFIGURATION_MONITOR 16.10.2019 10:46:12 7732 (0x1E34)
Error: Failed to configure view CMPivotResult. Please turn on verbose logging for more information. SMS_REPLICATION_CONFIGURATION_MONITOR 16.10.2019 10:46:12 7732 (0x1E34)
STATMSG: ID=7839 SEV=I LEV=M SOURCE="SMS Server" COMP="SMS_REPLICATION_CONFIGURATION_MONITOR" SYS=CASSERVER.DOMAIN.NET SITE=CAS PID=2592 TID=7732 GMTDATE=Mi. Okt 16 08:46:12.670 2019 ISTR0="CMPivot CAS" ISTR1="" ISTR2="" ISTR3="" ISTR4="" ISTR5="" ISTR6="" ISTR7="" ISTR8="" ISTR9="" NUMATTRS=0 SMS_REPLICATION_CONFIGURATION_MONITOR 16.10.2019 10:46:12 7732 (0x1E34)
STATMSG: ID=7838 SEV=I LEV=M SOURCE="SMS Server" COMP="SMS_REPLICATION_CONFIGURATION_MONITOR" SYS=CASSERVER.DOMAIN.NET SITE=CAS PID=2592 TID=7732 GMTDATE=Mi. Okt 16 08:46:12.748 2019 ISTR0="CMPivot CAS" ISTR1="" ISTR2="" ISTR3="" ISTR4="" ISTR5="" ISTR6="" ISTR7="" ISTR8="" ISTR9="" NUMATTRS=0 SMS_REPLICATION_CONFIGURATION_MONITOR 16.10.2019 10:46:12 7732 (0x1E34)
Processing Replication success. SMS_REPLICATION_CONFIGURATION_MONITOR 16.10.2019 10:46:13 7732 (0x1E34)

if i configure Verbose logging for SMS_REPLICATION_CONFIGURATION_MONITOR, there wouldn't any other messages created for CMPivotResult

 

do you have any idea, what i can do?

I am still facing this issue, as you have mentioned i tried long back enabling verbose logging, nothing additional is getting logged :(

@Sudar123 

Please make sure the verbose logging is enabled, you will need to update <CMInstallDir>\bin\x64\smsexec.exe.config and change the value of switchValue to Verbose

  <system.diagnostics>
    <sources>
      <source name="SMS_REPLICATION_CONFIGURATION_MONITOR" switchValue="Verbose" >
...

and make sure to restart SMS_Executive service

 

And again, double check the SQL server configurations regarding to Kerberos.

You can check the SQL server log on the primary site SQL server to see if there is any Kerberos related error.

Besides the delegation configuration, make sure the SQL svc SPN get published also.

 

Thanks

@Josua_Burkard are you sure constrained delegation is working? And is the local group for ConfigMgr_DViewAccess populated? An ugly work-around could be to make your own linked server with a local SQL ID. I recall doing that long enough for CM to create the view.

@Richard_ConfigMgr Are you running SQL with a service account? if so try setting delegation on the account to itself. In my case it that did the trick, however not sure about supported configuration. 

@Sudar123 I had the same issue, i have documented all process in https://mn-techforum.com/2021/04/09/issues-cmpivot-from-cas/ please have a lookc