Sep 26 2019 03:18 AM
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
Oct 02 2019 11:51 AM
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
Oct 16 2019 02:30 AM
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?
Oct 23 2019 07:42 AM
Oct 23 2019 11:39 AM
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
Oct 31 2019 11:40 AM
@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.
Jun 10 2020 12:31 PM
@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.
Apr 09 2021 05:05 AM
@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