Cannot insert the value NULL into column 'rowguid'
search cancel

Cannot insert the value NULL into column 'rowguid'

book

Article ID: 4399

calendar_today

Updated On:

Products

CA Single Sign On Secure Proxy Server (SiteMinder) CA Single Sign On SOA Security Manager (SiteMinder) CA Single Sign-On

Issue/Introduction

While in the process of upgrading CA Siteminder/CA Single Sign On, the upgrade of the XPS Data Dictionary in the policy store fails.

Error (example):

[SmObjProvider.cpp:187][ERROR][sm-Server-03090] Policy store failed operation 'Save' for object type 'PropertyCollection' . Unexpected error in database interface. Code is -1059 (DBMSG: <<<State = 23000 Internal Code = 515 - [DataDirect][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Cannot insert the value NULL into column 'rowguid', table 'SM_DB_New.dbo.smpropertycollection5'; column does not allow nulls. INSERT fails.>>>) 

[SmAdapterStore.cpp:257][CSmAdapterStore::FindPropertyCollection][ERROR][sm-xadobj-00110] Create failed. (metadata collection for AuthMethodGroup, Unknown Failure)

The Policy Store upgrade fails as a result.

Environment

Policy Server: AnyPolicy Server OS: AnyPolicy Store: MS SQL ServerPolicy Store OS: Windows Server

Cause

The 'rowguid' column is added to all of the tables in the Policy Store DB as a result of configuring then in a 'Merge' Replication Publication. Both 'Peer-to-Peer' Replication and 'Merge' replication support a Multi-Master Replication model. 'Merge' Replication is done on a schedule, while 'Peer-to-Peer' replication is a Multi-Master replication model which act more like 'Transactional' replication and is in real-time. 

 

Here is the summary when you configure Merge Replication in MSSQL on a Siteminder Policy Store: 

=================================================== 

All merge articles must contain a uniqueidentifier column with a unique index and the ROWGUIDCOL property. SQL Server adds a uniqueidentifier column to published tables that do not have one when the first snapshot is generated. 

Adding a new column will: 

» Cause INSERT statements without column lists to fail 

» Increase the size of the table 

» Increase the time required to generate the first snapshot 

 

SQL Server will add a uniqueidentifier column with a unique index and the ROWGUIDCOL property to each of the following tables. 

 

[dbo].[smactiveexpr_variable5] 

[dbo].[smactiveexpr5] 

[dbo].[smadmin4] 

[dbo].[smagent_agentgroup4] 

[dbo].[smagent5] 

[dbo].[smagentcommand4] 

[dbo].[smagentgroup4] 

[dbo].[smagentkey4] 

[dbo].[smagenttype4] 

[dbo].[smagenttypeactions4] 

[dbo].[smagenttypeattribute4] 

[dbo].[smagenttypeattrvalues4] 

[dbo].[smauthazmap4] 

[dbo].[smcertmap4] 

[dbo].[smdomain_admin4] 

[dbo].[smdomain_imsenv5] 

[dbo].[smdomain_userdirect4] 

[dbo].[smdomain5] 

[dbo].[smkeymanagement4] 

[dbo].[smodbcquery4] 

[dbo].[smpasswordpolicy5] 

[dbo].[smpolicy5] 

[dbo].[smpolicylink4] 

[dbo].[smproperty5] 

[dbo].[smpropertycollection5] 

[dbo].[smpropertysection5] 

[dbo].[smrealm5] 

[dbo].[smresponse_responseg4] 

[dbo].[smresponse4] 

[dbo].[smresponseattr5] 

[dbo].[smresponsegroup4] 

[dbo].[smrootconfig5] 

[dbo].[smrule_rulegroup4] 

[dbo].[smrule_variable5] 

[dbo].[smrulegroup4] 

[dbo].[smrules5] 

[dbo].[smscheme4] 

[dbo].[smselfreg4] 

[dbo].[smservercommand4] 

[dbo].[smsharedsecretpolicy6] 

[dbo].[smtaggedstring5] 

[dbo].[smtrustedhost5] 

[dbo].[smuserdirectory5] 

[dbo].[smuserpolicy4] 

[dbo].[smvariable_nestedvariable5] 

[dbo].[smvariable5] 

[dbo].[smvariabletype5] 

[dbo].[version] 

[dbo].[xpsKey] 

[dbo].[xpsObject] 

[dbo].[xpsParameterSetting] 

[dbo].[xpsProperty] 

=================================================== 

When the Publication is deleted, the 'rowguid' column is removed from the tables. 

When the 'rowguid' column is present and Merge Replication is enabled, then MS SQL isresponsible for updating the data in that column. As a result, running commands such as XPSImport and XPSDDInstall complete without any issues. CA Siteminder does not update these columns as this is schema added to our Policy Store by MSQL Merge Replication. MS SQL server is responsible for updating the data in the 'rowguid' column.  This occurs as a result of having an active Merge Replication Publication defined.  

Error:

[SmObjProvider.cpp:187][ERROR][sm-Server-03090] Policy store failed operation 'Save' for object type 'PropertyCollection' . Unexpected error in database interface. Code is -1059 (DBMSG: <<<State = 23000 Internal Code = 515 - [DataDirect][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Cannot insert the value NULL into column 'rowguid', table 'SM_DB_New.dbo.smpropertycollection5'; column does not allow nulls. INSERT fails.>>>) 

 

This error is occurring because a Merge Replication Publication was defined.  The 'rowguid' column was added to the tables in the Policy Store.  The Policy Store DB was manualy migrated to a new instance of MS SQL and mounted.  The 'rowguid' columns were migrated as part of the table schema, however there is no Merge Replication Publication defined on the new server.  As a result, SQL is not updating the 'rowguid' column during inserts.  The 'rowguid' columns is configured with 'Not Null' so the Null value in the insert is failing as a result of the Not Null constraint.  The update fails as a result.

 

 

 

Resolution

There are two possible solutions to this issue.

OPTION #1:  

Configure a Merge Replication Publication for the Policy Store DB on the new SQL instance.  The new Publication will begin updating the 'rowguid' column during updates.

 

OPTION #2:

Delete the 'rowguid' columns from the Policy Store DB.  

 

Example Script:

USE <PolicyStoreDB_Name> 

GO 

ALTER TABLE [dbo].[smactiveexpr_variable5] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smactiveexpr5] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smadmin4] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smagent_agentgroup4] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smagent5] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smagentcommand4] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smagentgroup4] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smagentkey4] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smagenttype4] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smagenttypeactions4] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smagenttypeattribute4] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smagenttypeattrvalues4] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smauthazmap4] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smcertmap4] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smdomain_admin4] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smdomain_imsenv5] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smdomain_userdirect4] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smdomain5] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smkeymanagement4] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smodbcquery4] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smpasswordpolicy5] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smpolicy5] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smpolicylink4] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smproperty5] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smpropertycollection5] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smpropertysection5] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smrealm5] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smresponse_responseg4] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smresponse4] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smresponseattr5] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smresponsegroup4] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smrootconfig5] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smrule_rulegroup4] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smrule_variable5] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smrulegroup4] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smrules5] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smscheme4] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smselfreg4] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smservercommand4] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smsharedsecretpolicy6] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smtaggedstring5] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smtrustedhost5] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smuserdirectory5] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smuserpolicy4] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smvariable_nestedvariable5] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smvariable5] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[smvariabletype5] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[version] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[xpsKey] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[xpsObject] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[xpsParameterSetting] DROP COLUMN rowguid ; 

GO 

ALTER TABLE [dbo].[xpsProperty] DROP COLUMN rowguid ; 

GO

 

Powered by Wolken Software