The below post in of the new feature (Distributed views) in the DRS that was introduced in the ConfigMgr 2012 SP1 release. Distributed view in simple terms avoid the flow of the site data across the hierarchy as the views directly can fetch the data from the primary database. The views get created on the CAS site. I had an issue where the Distrbuted views were not getting created on the CAS site. Below is the description -
The Distributed Views do not get created in ConfigMgr 2012 SP1 because of the login issue. The Link is active as its catering only the Global data.
STATMSG: ID=7835 SEV=E LEV=M SOURCE="SMS Server" COMP="SMS_REPLICATION_CONFIGURATION_MONITOR" SYS=NDCSCCMCAS01.xxx.xxxx SITE=FMC PID=11016 TID=10600 GMTDATE=Tue Apr 30 14:12:35.637 2013 ISTR0="Keyboard_DATA" ISTR1="Microsoft.SqlServer.Management.Smo.FailedOperationException: Create failed for View 'dbo.Keyboard_DATA'. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> S" ISTR2="" ISTR3="" ISTR4="" ISTR5="" ISTR6="" ISTR7="" ISTR8="" ISTR9="" NUMATTRS=0 SMS_REPLICATION_CONFIGURATION_MONITOR 30/04/2013 8:12:35 AM 10600 (0x2968)
SqlException number:  SMS_REPLICATION_CONFIGURATION_MONITOR 30/04/2013 8:12:35 AM 10600 (0x2968)
Exception message: [Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.~~OLE DB provider "SQLNCLI11" for linked server "ndcsccmsql02.xxx.xxxx" returned message "Invalid connection string attribute".] SMS_REPLICATION_CONFIGURATION_MONITOR 30/04/2013 8:12:35 AM 10600 (0x2968)
Exception stack trace: [ at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)~~ at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)] SMS_REPLICATION_CONFIGURATION_MONITOR 30/04/2013 8:12:35 AM 10600 (0x2968)
ERROR: Failed to configure view Keyboard_DATA. Please turn on verbose logging for more information. SMS_REPLICATION_CONFIGURATION_MONITOR 30/04/2013 8:12:35 AM 10600 (0x2968)
WARNING: Failed to configure view Keyboard_DATA. SMS_REPLICATION_CONFIGURATION_MONITOR 30/04/2013 8:12:35 AM 10600 (0x2968)
ConfigMgr 2012 SP1 CU1
CAS - Site code: FMC Site server- NDCSCCMCAS01
this is where we are creating the distributed view
SQL / Provider : NDCSCCMSQL01
Primary- Site code: NSS : NDCSCCMNSS01
SQL/Provider : NDCSCCMSQL02
Testing from the Management studio:
-> logged onto SQL01, opened SSMS and tested connection to linked server - works - this is single hop
-> Logged onto SQL02, opened SSMS and tested connection to linked server - it fails with the same error Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
SPNs are registered correctly with FQDNs and port number but double hop delegation is false.
User Account Control (UAC): 4096 (0x1000)
Warning: Account must be trusted for delegation in order for Kerberos delegation to succeed.
We found NDCSCCMSQL01$ is missing 'trusted for delegation' property for double hop to work.
Once we enabled it. The ConfigMgr RCM was able to create the views and the site data went up to the CAS.
Hope it helps !!
Support Escalation Engineer | Microsoft System Center ConfigMgr
This posting is provided "AS IS" with no warranties and confers no rights