SysCtr 2012 SP1 – ACS upgrade error (#scom #sysctr)


SysCtr 2012 SP1 – ACS upgrade error

When upgrading your ACS infrastructure to SP1 you’re likely to hit the following bug (actually there are two bugs). If this is the case you either have the ACS collector crashing every night when a new partition is created or you don’t have the views for Windows 2012 claim authentication.

A quick background: SP1 should bring your ACS database schema version to version 8, you can check the schema version in table dtConfig with Id 2:

clip_image001

In all my ACS upgrades it remains to schema version 7, this means the sql script DbUpgV7toV8.sql it has not been applied during the update. If have 100% repro on this, so I’m expecting your DB schema will still be at level 7.

Upgrade to level 8 introduces, among other minor mods, the new tables for claim authentication:

  • dtClaimString
  • dtOldResourceAttribute
  • dtNewResourceAttribute
  • dtUserClaim
  • dtDeviceClaim

and the corresponding views (dv*) one for each partition present in the ACS database.

SP1 should as well upgrade some sql script, among them the DbCreatePartition.sql. The dbCreatePartition script creates a new set of tables and views for the new partition and updates the summary views where all the partitions are “unioned” together (i.e. [AdtServer].dv*).

The DBCreatePartion.sql sometimes doesn’t get updated, there’s no fixed rule here and I didn’t find an exact repro, I suspect this happens if the original sql has been modified, for example to include all the partitions (see http://support.microsoft.com/kb/2663919).

After the update completes we can find ourselves in two situations:

  1. the schema has not been updated and the dbCreatePartion has
  2. the schema and the dbCreatePartion has not been updated

In case 1. The ACS collector will stop when it creates a new partition, when it executes the dbCreatePartion and tries to create the summary view for claim authentication it ends in error since the claim authentication views for old partitions are missing.

You can find events similar to the following in the Operations Manager event log

Log Name:      Operations Manager

Source:        AdtServer

Date:          2/12/2013 02:01:31

Event ID:      4618

Task Category: None

Level:         Error

Keywords:      Classic

User:          N/A

Computer:     

Description:

Error occured on database connection:

Status: 0x0409C002

ODBC Error:       208

ODBC State:      42S02

Message:           [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name ‘dvUserClaims_81971a6f_d0dc_477c_9316_81def5fe3ac2’.

Database:           SqlWriter

Connection:      Maintenance

Statement

Log Name:      Operations Manager

Source:        AdtServer

Date:          2/12/2013 02:01:31

Event ID:      4680

Task Category: None

Level:         Error

Keywords:      Classic

User:          NETWORK SERVICE

Computer:     

Description:

The Operations Manager 2007 audit collector service encountered a fatal error while attempting to perform a database operation. The Operations Manager Audit Collector service is unable to continue operation and is stopping.

Please check the log for previous events describing the nature of the problem.

 

To restore the collector to operation, you must troubleshoot Microsoft SQL Server or the Microsoft SQL data access components to find the cause of the error and resolve it, then restart the collector service

To solve this situation you can just restart the collector and it will work ok until next partition creation. Once all the old partitions are purged, it depends on your retention policy, the collector will no longer stop. Obviously this is not an ideal situation to be in, my *totally unsupported*  workaround is to manually apply the DbUpgV7toV8.sql to your ACS database, this will fix the issue once for all. (as usual do it at your own risk, the supported way is to open a CSS call)

In case 2. You don’t have any visible issue, simply the ACS database doesn’t have the views for claim based authentication. If you’re in case 2. You probably didn’t notice anything. To have the claim based authentication views in place you should return to case 1., or:

          manually copy the correct DbCreatePartion.sql script

          apply DbUpgV7toV8.sql to your ACS database

Remember: unsupported, do it at your own risk.

The SQL scripts I’m mentioning can be found in the ACS\AMD64 folder in the SP1 ISO/DVD and once installed in c:\windows\system32\sceurity\adtserver much like the following screenshot

clip_image003

– Daniele

This posting is provided “AS IS” with no warranties, and confers no rights.

Advertisements
  1. #1 by Dmitry Spesiviy on May 16, 2013 - 12:51 pm

    I have faced with the same issue. Case 1
    Before finding this info – each time I just started ACS again.
    So in result I had new tables and views – and default DbUpgV7toV8.sql wasn’t work. I got alerts like “There is already an object named ‘dtClaimString_de5aa3ec_d1fa_46a8_8cf3_213be7f2c733′ in the database.”

    So I’ve modified DbUpgV7toV8.sql script.

    /******************************************************************************
    *
    * DbUpgV7toV8.sql
    *
    * Updates ACS DB schema from V7 to V8
    *
    ******************************************************************************/

    declare @iVersion int — current schema version

    set @iVersion = (select Value from dtConfig where Id = 2)

    if (@iVersion = 7)
    begin
    begin tran

    — update description for existing category
    if exists (select * from dtCategory where Id = 0)
    update dtCategory set Description = N’ACS’ where Id = 0
    else
    insert into dtCategory (Id, Description) values (0, N’ACS’)

    — insert new category entries
    if not exists (select * from dtCategory where Id = 101)
    insert into dtCategory (Id, Description) values ( 101, N’Event processing’)
    if not exists (select * from dtCategory where Id = 103)
    insert into dtCategory (Id, Description) values ( 103, N’Service shutdown’)
    if not exists (select * from dtCategory where Id = 0x3109)
    insert into dtCategory (Id, Description) values (0x3109, N’User / Device Claims’)
    if not exists (select * from dtCategory where Id = 0x320D)
    insert into dtCategory (Id, Description) values (0x320D, N’Central Access Policy Staging’)

    — create empty dtClaimString_!g! dtOldResourceAttribute_!g! dtNewResourceAttribute_!g! dtUserClaim_!g! dtDeviceClaim_!g! tables
    declare @vchStmt nvarchar(max)
    declare @vchPartitionId nchar(36)
    declare cPartition cursor for
    select PartitionId from dtPartition order by PartitionCloseTime desc

    open cPartition
    fetch next from cPartition into @vchPartitionId
    while @@fetch_status = 0
    begin
    set @vchStmt = N’create table dtClaimString_’ + @vchPartitionId + N’ ‘
    set @vchStmt = @vchStmt + N'(‘
    set @vchStmt = @vchStmt + N’Id int not null constraint pkClaimString_’ + @vchPartitionId + ‘ primary key identity (0,1),’
    set @vchStmt = @vchStmt + N’Hash binary(20) not null,’
    set @vchStmt = @vchStmt + N’strClaimId nvarchar(max) not null,’
    set @vchStmt = @vchStmt + N’strClaimDisplayName nvarchar(max) not null,’
    set @vchStmt = @vchStmt + N’strClaimValue nvarchar(max) not null,’
    set @vchStmt = @vchStmt + N’strClaimValueDisplayName nvarchar(max) not null’
    set @vchStmt = @vchStmt + N’)’

    IF OBJECT_ID(‘dtClaimString_’ + @vchPartitionId, ‘U’) IS NULL
    BEGIN
    exec (@vchStmt)
    END

    set @vchStmt = N’create table dtOldResourceAttribute_’ + @vchPartitionId + N’ ‘
    set @vchStmt = @vchStmt + N'(‘
    set @vchStmt = @vchStmt + N’EventId bigint not null,’
    set @vchStmt = @vchStmt + N’CreationTime datetime not null,’
    set @vchStmt = @vchStmt + N’ClaimId int not null constraint fkOldResourceAttributeClaimId_’ + @vchPartitionId + N’ references dtClaimString_’ + @vchPartitionId + N’ (Id),’
    set @vchStmt = @vchStmt + N’)’

    IF OBJECT_ID(‘dtOldResourceAttribute_’ + @vchPartitionId, ‘U’) IS NULL
    BEGIN
    exec (@vchStmt)
    END

    set @vchStmt = N’create table dtNewResourceAttribute_’ + @vchPartitionId + N’ ‘
    set @vchStmt = @vchStmt + N'(‘
    set @vchStmt = @vchStmt + N’EventId bigint not null,’
    set @vchStmt = @vchStmt + N’CreationTime datetime not null,’
    set @vchStmt = @vchStmt + N’ClaimId int not null constraint fkNewResourceAttributeClaimId_’ + @vchPartitionId + N’ references dtClaimString_’ + @vchPartitionId + N’ (Id),’
    set @vchStmt = @vchStmt + N’)’

    IF OBJECT_ID(‘dtNewResourceAttribute_’ + @vchPartitionId, ‘U’) IS NULL
    BEGIN
    exec (@vchStmt)
    END

    set @vchStmt = N’create table dtUserClaim_’ + @vchPartitionId + N’ ‘
    set @vchStmt = @vchStmt + N'(‘
    set @vchStmt = @vchStmt + N’EventId bigint not null,’
    set @vchStmt = @vchStmt + N’CreationTime datetime not null,’
    set @vchStmt = @vchStmt + N’ClaimId int not null constraint fkUserClaimClaimId_’ + @vchPartitionId + N’ references dtClaimString_’ + @vchPartitionId + N’ (Id),’
    set @vchStmt = @vchStmt + N’)’

    IF OBJECT_ID(‘dtUserClaim_’ + @vchPartitionId, ‘U’) IS NULL
    BEGIN
    exec (@vchStmt)
    END

    set @vchStmt = N’create table dtDeviceClaim_’ + @vchPartitionId + N’ ‘
    set @vchStmt = @vchStmt + N'(‘
    set @vchStmt = @vchStmt + N’EventId bigint not null,’
    set @vchStmt = @vchStmt + N’CreationTime datetime not null,’
    set @vchStmt = @vchStmt + N’ClaimId int not null constraint fkDeviceClaimClaimId_’ + @vchPartitionId + N’ references dtClaimString_’ + @vchPartitionId + N’ (Id),’
    set @vchStmt = @vchStmt + N’)’

    IF OBJECT_ID(‘dtDeviceClaim_’ + @vchPartitionId, ‘U’) IS NULL
    BEGIN
    exec (@vchStmt)
    END

    fetch next from cPartition into @vchPartitionId
    end

    close cPartition
    deallocate cPartition

    — create empty dvUserClaims_!g! dvDeviceClaims_!g! dvNewResourceAttribute_!g! dvOldResourceAttribute_!g! views
    declare cPartition cursor for
    select PartitionId from dtPartition order by PartitionCloseTime desc

    open cPartition
    fetch next from cPartition into @vchPartitionId
    while @@fetch_status = 0
    begin
    set @vchStmt = N’create view dvUserClaims_’ + @vchPartitionId + N’ ‘
    set @vchStmt = @vchStmt + N’as ‘
    set @vchStmt = @vchStmt + N’select ‘
    set @vchStmt = @vchStmt + N’ uc.EventId as EventId, ‘
    set @vchStmt = @vchStmt + N’ uc.CreationTime as CreationTime, ‘
    set @vchStmt = @vchStmt + N’ ucs.strClaimId as UserClaimId, ‘
    set @vchStmt = @vchStmt + N’ ucs.strClaimDisplayName as UserClaimDisplayName, ‘
    set @vchStmt = @vchStmt + N’ ucs.strClaimValue as UserClaimValue, ‘
    set @vchStmt = @vchStmt + N’ ucs.strClaimValueDisplayName as UserClaimValueDisplayName ‘
    set @vchStmt = @vchStmt + N’FROM ‘
    set @vchStmt = @vchStmt + N’ dtUserClaim_’ + @vchPartitionId + ‘ as uc ‘
    set @vchStmt = @vchStmt + N’ inner join dtClaimString_’ + @vchPartitionId + ‘ ucs on ucs.Id = uc.ClaimId ‘

    IF OBJECT_ID(‘dvUserClaims_’ + @vchPartitionId, ‘V’) IS NULL
    BEGIN
    exec (@vchStmt)
    END

    set @vchStmt = N’create view dvDeviceClaims_’ + @vchPartitionId + N’ ‘
    set @vchStmt = @vchStmt + N’as ‘
    set @vchStmt = @vchStmt + N’select ‘
    set @vchStmt = @vchStmt + N’ dc.EventId as EventId, ‘
    set @vchStmt = @vchStmt + N’ dc.CreationTime as CreationTime, ‘
    set @vchStmt = @vchStmt + N’ dcs.strClaimId as DeviceClaimId, ‘
    set @vchStmt = @vchStmt + N’ dcs.strClaimDisplayName as DeviceClaimDisplayName, ‘
    set @vchStmt = @vchStmt + N’ dcs.strClaimValue as DeviceClaimValue, ‘
    set @vchStmt = @vchStmt + N’ dcs.strClaimValueDisplayName as DeviceClaimValueDisplayName ‘
    set @vchStmt = @vchStmt + N’FROM ‘
    set @vchStmt = @vchStmt + N’ dtDeviceClaim_’ + @vchPartitionId + ‘ as dc ‘
    set @vchStmt = @vchStmt + N’ inner join dtClaimString_’ + @vchPartitionId + ‘ dcs on dcs.Id = dc.ClaimId ‘

    IF OBJECT_ID(‘dvDeviceClaims_’ + @vchPartitionId, ‘V’) IS NULL
    BEGIN
    exec (@vchStmt)
    END

    set @vchStmt = N’create view dvNewResourceAttributes_’ + @vchPartitionId + N’ ‘
    set @vchStmt = @vchStmt + N’as ‘
    set @vchStmt = @vchStmt + N’select ‘
    set @vchStmt = @vchStmt + N’ nra.EventId as EventId, ‘
    set @vchStmt = @vchStmt + N’ nra.CreationTime as CreationTime, ‘
    set @vchStmt = @vchStmt + N’ ncs.strClaimId as NewClaimId, ‘
    set @vchStmt = @vchStmt + N’ ncs.strClaimDisplayName as NewClaimDisplayName, ‘
    set @vchStmt = @vchStmt + N’ ncs.strClaimValue as NewClaimValue, ‘
    set @vchStmt = @vchStmt + N’ ncs.strClaimValueDisplayName as NewClaimValueDisplayName ‘
    set @vchStmt = @vchStmt + N’FROM ‘
    set @vchStmt = @vchStmt + N’ dtNewResourceAttribute_’ + @vchPartitionId + ‘ as nra ‘
    set @vchStmt = @vchStmt + N’ inner join dtClaimString_’ + @vchPartitionId + ‘ ncs on ncs.Id = nra.ClaimId ‘

    IF OBJECT_ID(‘dvNewResourceAttributes_’ + @vchPartitionId, ‘V’) IS NULL
    BEGIN
    exec (@vchStmt)
    END

    set @vchStmt = N’create view dvOldResourceAttributes_’ + @vchPartitionId + N’ ‘
    set @vchStmt = @vchStmt + N’as ‘
    set @vchStmt = @vchStmt + N’select ‘
    set @vchStmt = @vchStmt + N’ nra.EventId as EventId, ‘
    set @vchStmt = @vchStmt + N’ nra.CreationTime as CreationTime, ‘
    set @vchStmt = @vchStmt + N’ ncs.strClaimId as OldClaimId, ‘
    set @vchStmt = @vchStmt + N’ ncs.strClaimDisplayName as OldClaimDisplayName, ‘
    set @vchStmt = @vchStmt + N’ ncs.strClaimValue as OldClaimValue, ‘
    set @vchStmt = @vchStmt + N’ ncs.strClaimValueDisplayName as OldClaimValueDisplayName ‘
    set @vchStmt = @vchStmt + N’FROM ‘
    set @vchStmt = @vchStmt + N’ dtOldResourceAttribute_’ + @vchPartitionId + ‘ as nra ‘
    set @vchStmt = @vchStmt + N’ inner join dtClaimString_’ + @vchPartitionId + ‘ ncs on ncs.Id = nra.ClaimId ‘

    IF OBJECT_ID(‘dvOldResourceAttributes_’ + @vchPartitionId, ‘V’) IS NULL
    BEGIN
    exec (@vchStmt)
    END

    fetch next from cPartition into @vchPartitionId
    end

    close cPartition
    deallocate cPartition

    — mark all active partitions for closing
    if (select count(*) from dtPartition where Status = 0) > 0
    update dtPartition set Status = 1 where Status = 0

    — update schema version
    update dtConfig set Value = 8 where Id = 2
    commit tran
    end
    go

  2. #2 by RHC on April 4, 2013 - 10:08 am

    We have been fighting this problem for some time…. and this article solved our problem.. Thank you a lot guys!!!

    We had the problem where the collector stops each night… and the schema was not updated to version 8, and when we tried to run it manually it came with the: “There is already an object named ‘dtClaimString_[ID]′ in the database.”

    But then we just added this line: “IF not exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES (NOLOCK) WHERE TABLE_NAME = ‘dtClaimString_’ + @vchPartitionId + N’ ‘)”
    No it’s running the script, thank you a lot.

  3. #3 by Chris Burrett on February 21, 2013 - 4:52 pm

    Hi, thanks for the article!

    I got the error about tables and views already existing but this caused the script to stop and not fix the problem.
    I added “IF not exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ‘dtClaimString_’ + @vchPartitionId + N’ ‘)” For each section in the script, where dtClaimString is the beginning of the table name that already exists, after this it complained that views already exist, i just deleted these, on running the script again everything created fine and the schema version number updated.

    – Chris

  4. #4 by oportingale on February 19, 2013 - 12:17 pm

    Hi, thanks for the article. I tried running the script but got the following error
    “There is already an object named ‘dtClaimString_de5aa3ec_d1fa_46a8_8cf3_213be7f2c733’ in the database.”
    Did that happen to you? Thanks

    • #5 by Daniele Grandini on February 19, 2013 - 6:04 pm

      Hi this is expected if ACS has been able to run for more than one day, the newly created partitions already have the new tables and views, so when the script arrives to process the new partitions it stops in error since the tables are already there. You can safely assume everything is ok at this point. To be sure you can check if the AdtServer.dv* views have been created, if so you’re ok.
      If you want to be 100% sure you need to mod the sql script adding an If not exists (select …) statement before every exec statement.

      – Daniele

      • #6 by ADO on March 5, 2013 - 5:33 pm

        Thank you so much for you post – I was experiancing the exact same issue.

        What I wound up doing is deleting the 4 tables and views (dt??String.. and dv??String) associated with the upgrade script. Then ran a repair on the ACS install and it prompted for the connection string on the DB upgrade, chose trusted and made sure that the connection DB was set to my Auditing db (by default it chooses MASTER). That seemed to get things going in the right direction. I was running a trace on the AC db and verified that each of the script options ran.

        I believe the root culprit is probably choosing the correct db in the connection string in the db upgrade process – but I will bow to your expertise.

        thanks again!
        ADO

      • #7 by Daniele Grandini on March 6, 2013 - 8:28 am

        Hi ADO, interesting finding. I actually didn’t try to repair way, but it seems a good idea even if I fear it will break in error when at least one new partition has been created, unless you manually delete all the newly created views. Anyway I will give it a try at the next occasion and post the results here.

        Thanks for reading this blog and posting your findings.

        – Daniele

  1. NeWay Technologies – Weekly Newsletter #30 – February 14, 2013NeWay | NeWay
  2. System Center 2012 Service Pack 1 Upgrade – Operations manager « Quae Nocent Docent

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: