Symantec Identity Governance - Properties table missing in database, Invalid object name 'Properties'
search cancel

Symantec Identity Governance - Properties table missing in database, Invalid object name 'Properties'

book

Article ID: 250163

calendar_today

Updated On:

Products

CA Identity Suite CA Identity Governance

Issue/Introduction

Error can be observed when application is running on Wildfly:

14:16:59,131 WARN  [com.eurekify.security.saml.SAMLSecurityFilter] (default task-2) Exception occured while initializing the SAML Security Service. Please resolve the issue to enable SAML authentication.: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [ SELECT PropertyName, PropertyValue FROM Properties WHERE PropertyCategory = ?]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'Properties'.
 at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:233) [spring-jdbc-3.0.6.RELEASE.jar:3.0.6.RELEASE]
 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) [spring-jdbc-3.0.6.RELEASE.jar:3.0.6.RELEASE]

Why there is a reference to a non-existing table (Properties)?

Environment

Release : 14.4

Component : GovernanceMinder(Role & Compliance Manager)

Resolution

It seems table Properties was not created during previous upgrades.

Broadcom development team prepared a SQL script to apply to the Database to create the missing table.

Additional Information

Development has analyzed the issue and found that two tables are missed during the process of updating.

Please create those two tables with the below DB script:

 

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Properties]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    BEGIN
    CREATE TABLE [dbo].[Properties](
        [PropertyCategory] [nvarchar] (128) NOT NULL,
        [PropertyName] [nvarchar] (256) NOT NULL,
        [PropertyValue] [nvarchar] (2000),
     CONSTRAINT [PK_Properties] PRIMARY KEY CLUSTERED ([PropertyCategory] ASC,[PropertyName]  ASC))
    END;

     IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Certificates]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    BEGIN
    CREATE TABLE [dbo].[Certificates](
        [CertificateId] [bigint] IDENTITY(1,1) NOT NULL,
        [Name] [nvarchar] (256) NOT NULL,
        [Alias] [nvarchar] (256) NOT NULL UNIQUE,
        [PrivateKey] [varbinary](max),
        [X509Certificate] [varbinary](max) NOT NULL,
        [CertType] [nvarchar] (256) NOT NULL,
        [CreationDate] [datetime] NOT NULL,
     CONSTRAINT [PK_Certificates] PRIMARY KEY CLUSTERED ([CertificateId] ASC))
    END;