Installation issue with Clarity 13.2 w/ SQL Server 2012

[Anon Anon]

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!

[Luis Palacios]

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.

[Deactivated User]

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.


.

[Anon Anon]

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?

[Deactivated User]

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.

[Anon Anon]

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?

[Deactivated User]

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.

[Anon Anon]

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.

[Deactivated User]

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.

[Anon Anon]

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?

[Nika Hadzhikidi]

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

[Anon Anon]

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.

[Deactivated User]

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.

[Anon Anon]

Hi Nika,

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

Thanks.

[kumu]

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.

[Anon Anon]

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

[Anon Anon]

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