Clarity

Expand all | Collapse all

Installation issue with Clarity 13.2 w/ SQL Server 2012

Jump to Best Answer
  • 1.  Installation issue with Clarity 13.2 w/ SQL Server 2012

    Posted 05-29-2013 11:44 AM
    Hi everyone,

    I'm performing a clean install of Clarity 13.2 on Win2008r2 with SQL Server 2012 sp1.

    I've been able to run the install.bat file, but now at CSA, I'm getting db connection issues and I'm unable to install the database.
    I'm getting the following errors as per the log file:
    SYS   2013-05-29 11:12:16,293 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) ==================================================================
    SYS   2013-05-29 11:12:16,293 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Tenants for this Application with instance id = nsa :
    SYS   2013-05-29 11:12:16,309 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) ==================================================================
    SYS   2013-05-29 11:12:16,309 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) ==================================================================
    SYS   2013-05-29 11:12:16,309 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) CLARITY
    
    SYS   2013-05-29 11:12:16,309 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Tenants: TenantInstance: id: clarity
    SYS   2013-05-29 11:12:16,309 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Tenants: TenantInstance: appId: app
    SYS   2013-05-29 11:12:16,309 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Tenants: TenantInstance: bgId: bg
    SYS   2013-05-29 11:12:16,309 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Tenants: TenantInstance: fileDirectory: tenantDirectory
    SYS   2013-05-29 11:12:16,309 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Tenants: TenantInstance: status: valid
    SYS   2013-05-29 11:12:16,309 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Properties/Database: id: clarity
    SYS   2013-05-29 11:12:16,309 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Properties/Database: serviceId: niku
    SYS   2013-05-29 11:12:16,309 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Properties/Database: driver: com.ca.clarity.jdbc.sqlserver.SQLServerDriver
    SYS   2013-05-29 11:12:16,309 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Properties/Database: url: jdbc:clarity:sqlserver://W2003CLARITY:1433;DatabaseName=niku;InsensitiveResultSetBufferSize=0;ProgramName=Clarity
    SYS   2013-05-29 11:12:16,309 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Properties/Database: username: testuser
    SYS   2013-05-29 11:12:16,309 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Properties/Database: schemaName: niku
    SYS   2013-05-29 11:12:16,309 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Properties/Database: serviceName: clarity
    SYS   2013-05-29 11:12:16,325 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Properties/Database: vendor: mssql
    SYS   2013-05-29 11:12:16,325 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Properties/Database: largeIndex: INDX_LARGE
    SYS   2013-05-29 11:12:16,325 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Properties/Database: smallIndex: INDX_SMALL
    SYS   2013-05-29 11:12:16,325 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Properties/Database: largeTables: USERS_LARGE
    SYS   2013-05-29 11:12:16,325 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Properties/Database: smallTables: USERS_SMALL
    SYS   2013-05-29 11:12:16,325 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) ==================================================================
    SYS   2013-05-29 11:12:16,325 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Active Tenants: clarity
    SYS   2013-05-29 11:12:16,325 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) ==================================================================
    WARN  2013-05-29 11:12:16,340 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Found invalid database connection for local tenant clarity
    WARN  2013-05-29 11:14:16,346 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Found invalid database connection for local tenant clarity
    WARN  2013-05-29 11:15:11,838 [http-bio-8090-exec-30] niku.nsa (clarity:sa:eyj32BvXL+MXVXfh1aXnP1RJJneDNvjMtjA/9ODMN2I0:nsa.properties.database.save) Unable to persist configuration properties to database. Likely cause is database properties are not yet set or misconfigured.
    Logs indicate there is a misconfigured entry in the Database Properties in the Admin section.
    I've added a login in SQL Server 2012 called "niku" along with a password, and I've also created a System DSN entry and am able to connect via the DSN.
    I've also imported the provided mssql_base.db from the install media and associated the role with my newly created SQL Server login.

    To my knowledge, I've checked & cross-checked the values and can't find out why it's not connecting or if I've missed out any steps.

    Appreciate any help - thanks!


  • 2.  RE: Installation issue with Clarity 13.2 w/ SQL Server 2012

    Posted 05-29-2013 12:07 PM
    Hello Amrahman,
    I've seen similar errors, but the message is generic as it doesn't explain why was it unable to connect. It may be the fact that 2012 is unsupported.
    When you set up the settings in nsa does the label says "available"?
    To troubleshoot, you can go into the db settings, set them up, click save and look in the log files for nsa to see the reason of the failure. It will give more details specific to db vendor.


  • 3.  RE: Installation issue with Clarity 13.2 w/ SQL Server 2012

    Posted 06-03-2013 05:48 AM
    Hi amrahman,

    why does the log say that the user you are using is "clarity:sa:....<errormessage>"?

    What kind of connection do you use within your ODBC-connection? I konw that there is no ODBC-connection necessary but you can try using it.

    If you connect to to MSSQL Management Console what user you are using?
    "niku" or "Windows Authentication"?
    Are you able to select the count of users within the Clarity-DB?
    Try to connect to Management Console using SQL-Server Authentication and use "niku" (or the designated user configured within the properties.xml) and select as follows:

    select count(*) from CMN_SEC_USERS


    Could you please post the outcome?

    Thanks in advance.

    Best regards,

    Florian


  • 4.  RE: Installation issue with Clarity 13.2 w/ SQL Server 2012

    Posted 05-29-2013 12:41 PM
      |   view attached
    The release notes say for v13.2 database

    Microsoft SQL Server 2008 Enterprise Edition: Any service pack 7c)
    Microsoft SQL Server 2008 R2 Enterprise Edition: Any service pack 7
    Microsoft SQL Server 2012 Enterprise Edition: Any service pack

    What I normally do is loging to SQL Server Management Studio
    If the login is OK then create and ODBC connection on the server to see that I can log in with TCP IP
    Then look at the database availability in CSA - as Luis says the database must be available prior to proceeding. If not I compare the settings to what I used in the two firs cases.

    Martti K.


    .


  • 5.  RE: Installation issue with Clarity 13.2 w/ SQL Server 2012

    Posted 06-11-2013 08:34 AM
    try to create a new user and change db settings in nsa. I recreated user account: niku1, change schema owner to niku1 and then changed settings in NSA to niku1 user. Then it looked as DB installed and worked then. Or have you found another solution on that issue?

    for me it worked.
    Lukas


  • 6.  RE: Installation issue with Clarity 13.2 w/ SQL Server 2012

    Posted 05-29-2013 03:00 PM
      |   view attached
    I do see the "Available" status in the Database properties.
    And I'm able to login successfully using the newly created user, as well as use the ODBC connection.

    Here's the screenshot of the configuration, attached.

    Any other suggestions?


  • 7.  RE: Installation issue with Clarity 13.2 w/ SQL Server 2012

    Posted 05-29-2013 03:26 PM
    That is good. The connections and CSA look OK. But

    Active Tenants: clarity
    Found invalid database connection for local tenant clarity
    Found invalid database connection for local tenant clarity
    niku.nsa (clarity:sa:eyj32BvXL+MXVXfh1aXnP1RJJneDNvjMtjA/9ODMN2I0:nsa.properties.database.save) Unable to persist configuration properties to database. Likely cause is database properties are not yet set or misconfigured

    don't look good.

    Did you restore the supplied db dump and create the niku used etc as given in the install guide?

    What does the health check say for the database?

    I did one install issuing admin db upgrade at command prompt when specifying an empty folder for Clarity install and the install script could not connect to the database.
    That command fixed it.

    See
    101081336

    Martti K.


  • 8.  RE: Installation issue with Clarity 13.2 w/ SQL Server 2012

    Posted 05-29-2013 04:19 PM
      |   view attached
    Thanks for the details. Yes, I've restored the database and created a user too, as per the guide.

    I've done a health check, it says database is valid (attached screenshot).

    However, the admin db upgrade command is failing, and here's the log.
    5/29/13 4:13 PM (admin) Executing upgrade process from upgrade directory.
    5/29/13 4:13 PM (admin) upgrade.phase = upgrade
    5/29/13 4:13 PM (admin) install.upgrade.dir = C:\clarity13_2/upgrade
    5/29/13 4:13 PM (admin) maintenance.release = false
    5/29/13 4:13 PM (admin) upgrade:phase = upgrade
    5/29/13 4:13 PM (admin) init_upgrade - HeapSize Max = 2048m, PermGenSize Max = 256m
    5/29/13 4:13 PM (admin) core-upgrade:skip.db.upgrade = false
    5/29/13 4:13 PM (admin) core-upgrade:install.upgrade.dir = C:\clarity13_2/upgrade
    5/29/13 4:13 PM (admin) Installing schema file: C:\clarity13_2/upgrade/infrastructure/schema/function/IS_CUSTOMER_DB.xml...
    May 29, 2013 4:13:42 PM Error executing command: db upgrade
    C:\clarity13_2\.setup\scripts\db.xml:1453: The following error occurred while executing this line:
    C:\clarity13_2\.setup\scripts\db.xml:1476: The following error occurred while executing this line:
    C:\clarity13_2\.setup\scripts\db.xml:1488: The following error occurred while executing this line:
    C:\clarity13_2\.setup\scripts\db.xml:1547: The following error occurred while executing this line:
    C:\clarity13_2\.setup\scripts\db.xml:2049: The following error occurred while executing this line:
    C:\clarity13_2\.setup\scripts\db.xml:774: java.sql.SQLSyntaxErrorException: [CA Clarity][SQLServer JDBC Driver][SQLServer]Cannot drop the function 'IS_CUSTOMER_DB', because it does not exist or you do not have permission.
    
    at org.apache.tools.ant.ProjectHelper.addLocationToBuildException(ProjectHelper.java:551)
    
    at org.apache.tools.ant.taskdefs.Ant.execute(Ant.java:444)
            ....
    I'm able to create/edit/delete tables from SQL Server's Maintenance Studio using the role I created.

    Any clues?


  • 9.  RE: Installation issue with Clarity 13.2 w/ SQL Server 2012

    Posted 05-29-2013 04:44 PM
    So you have done this

    CA Clarity PPM Database Options
    Important! Mixed collations is not supported. If you change the server-level default
    from SQL_Latin1_General_CP1_CI_AS to another collation, create a new SQL Server
    instance with the correct collation SQL_Latin1_General_CP1_CI_AS before creating the
    CA Clarity PPM database.
    The import procedure creates the database with the following options:
    Option Value / Choice
    ARITHABORT
    ANSI NULLS
    QUOTED IDENTIFIER
    ON
    To apply, execute:
    ALTER DATABASE <database>
    SET ARITHABORT ON
    ALTER DATABASE <database>
    SET ANSI_NULLS ON
    ALTER DATABASE <database>
    SET QUOTED_IDENTIFIER ON

    Option Value / Choice
    Compatibility level 90
    To apply, execute:
    EXEC SP_DBCMPTLEVEL <database>, 90
    Database user name niku
    The login name can be anything, but the
    databaseusername (schema) must be niku.
    Database user roles db_owner
    The database user must have the ability to alter the
    Clarity-schema and otherwise own the database.
    READ COMMITTED
    SNAPSHOT
    ON
    To apply, execute:
    ALTER DATABASE <database>
    SET READ_COMMITTED_SNAPSHOT ON
    VIEW_SERVER_STATE Granted to database user.
    To apply, execute:
    GRANT VIEW SERVER STATE to niku

    The according to the release notes the compatibility is set to 100 not 90


    [CA Clarity][SQLServer JDBC Driver][SQLServer]Cannot drop the function 'IS_CUSTOMER_DB', because it does not exist or you do not have permission.
    Suggests that the db connection is OK

    If you can access see if there is a function called IS_CUSTOMER_DB in the database.
    If there is then it is a rights issue. If not can't think a reson just now.

    Martti K.


  • 10.  RE: Installation issue with Clarity 13.2 w/ SQL Server 2012

    Posted 05-29-2013 05:02 PM
    Thanks for the details, once again.
    But no luck, still same issue.

    I've ensure all these steps have been applied with the correct values:
    [list]
    [*]ARITHABORT
    [*]ANI NULLS
    [*]QUOTED IDENTIFIER
    [*]Compatibilility Level = 90
    [*]READ COMMITED SNAPSHOT = ON
    [*]VIEW_SERVER_STATE
    [list]

    From SQL Server Maintenence Studio, I'm able to view the IS_CUSTOMER_DB function using the role I've created.

    Any other thoughts?
    Thanks again.


  • 11.  RE: Installation issue with Clarity 13.2 w/ SQL Server 2012

    Posted 05-29-2013 05:21 PM
    I take db connection details in CSA are for SQL authenthication and you are logged in to windows as admin and your regional settings are US English.

    The OS support is
    ■ Microsoft Windows
    Server 2008 (R2) SP1
    64-bit (Standard,
    Enterprise Edition)
    ■ Microsoft Windows
    Server 2012 64-bit
    (Standard)

    so your hostname W2003 sounds suspicious.
    My understanding is (from the What is new in v13.2 Brighttalk webinar or from a post by Josh) that v13.2 will not install on Windows 2003.

    Martti K.


  • 12.  RE: Installation issue with Clarity 13.2 w/ SQL Server 2012

    Posted 05-29-2013 05:58 PM
    Yes, the DB Authentication is set to SQL Authenication & Windows Login (mixed mode).

    My regional setting is US English on the OS.

    My current OS is Windows Server 2008 R2 x64, not Windows Server 2003. It's simply because the name of the machine was not updated.

    Any more suggestions?


  • 13.  RE: Installation issue with Clarity 13.2 w/ SQL Server 2012
    Best Answer

    Broadcom Employee
    Posted 06-11-2013 06:04 PM
    Hello amrahman,

    I had a similar error before and it was related to roles.

    Please could you make sure that the user niku does not have the server role = sysadmin.
    Sometimes the user niku gets created with this server role and this actually prevents him from using his default schema = niku. Please just make sure that the server role = sysadmin is unchecked for niku and retry.
    Hope this helps.

    Kind Regards
    Nika


  • 14.  RE: Installation issue with Clarity 13.2 w/ SQL Server 2012

    Posted 04-04-2014 03:18 PM

    Hello,

    I am also facing similar issue wih fresh intstallation of 13.3.0 usng oracle 11.2 database. Th NSA - Database tab shows db connection "Niku" is available however the install history and nsa logs shoowing connectio not available.

    logs message: config.TenantConfigurationManager(none:none:none:none)

    Please advise, what  check in case of Oracle database ? Thanks.



  • 15.  RE: Installation issue with Clarity 13.2 w/ SQL Server 2012

    Posted 04-03-2014 03:21 PM

    Hi Nika,

    What we should check in case of Oracle data base, giving same issue and How, steps ?

    Thanks.



  • 16.  RE: Installation issue with Clarity 13.2 w/ SQL Server 2012

    Posted 06-18-2013 09:27 AM
      |   view attached
    Hi everyone,

    I have been able to pin-point the issue. It was owing to incorrect assignment of server db roles.
    As Nika mentioned, the db user should not have the sysadmin role enabled.

    I've attached a screenshot of my current db user's server roles, and it seems to be work.

    Thank you to all for your replies for the help, and in particular to Nika for sharing her prior experience in fixing this issue.


  • 17.  RE: Installation issue with Clarity 13.2 w/ SQL Server 2012

    Posted 06-18-2013 10:09 AM
    Good to hear that the cause was found and it is working.

    Just wondering again....
    If the installation guide says

    Database user roles db_owner
    The database user must have the ability to alter the
    Clarity-schema and otherwise own the database.

    Why would you give all those other roels?

    Martti K.