NAC fails at initialization of DB with error "Set a default schema *"

Products

• CA Release Automation - Release Operations Center (Nolio)

Issue/Introduction

We are setting up a test setup of Nolio in our test environment. We have followed Install for a distributed environment and setup a management server and the database setup has been completed via our DBA team. 

We are using MS SQL and integrated security and the account is part of an AD group which has been permissioned on our DB. The following configuration are made to manually connect to DB in Release Automation (RA) application.

distributed.properties

# Database props
data.management.database.host = DB_SERVER_NAME
data.management.database.port = DB_PORT
data.management.database.name = DB_NAME
data.management.database.user = 
data.management.database.pwd = 
data.management.database.create = true

database.properties

## MS SQL Server settings
hibernate.dialect = com.nolio.platform.server.dataservices.NolioMSSQLDialect
hibernate.dialect.class = com.nolio.platform.server.dataservices.NolioMSSQLDialect
driver.class.name = com.microsoft.sqlserver.jdbc.SQLServerDriver
## in case of non default port use the following: database.jdbc.url = jdbc:sqlserver://${data.management.database.host}:${data.management.database.port};databaseName=${data.management.database.name}
database.jdbc.url = jdbc:sqlserver://${data.management.database.host};databaseName=${data.management.database.name};integratedSecurity=true
database.username = ${data.management.database.user}
database.password = ${data.management.database.pwd}
encrypted.database.password = ${data.management.database.pwd}

We are receiving the following error in our nolio_dm_all.log and the NAC service on startup fails to initialize the Release Automation DB schema.

2022-05-09 11:41:12,100 [localhost-startStop-1] INFO  (com.nolio.platform.server.dataservices.services.upgrade.UpgradeServiceImpl:437) - Current value of hbm2dll.auto: 
2022-05-09 11:41:12,102 [localhost-startStop-1] INFO  (com.nolio.platform.server.dataservices.services.upgrade.UpgradeServiceImpl:243) - Calling Flyway Migrate...
2022-05-09 11:41:12,222 [localhost-startStop-1] ERROR (com.nolio.platform.server.dataservices.services.upgrade.UpgradeServiceImpl:268) - Error calling Flyway Migrate
org.flywaydb.core.api.FlywayException: Unable to determine schema for the metadata table. Set a default schema for the connection or specify one using the schemas property!

Could you advise what is causing the initialization of DB to fail.

Environment

Release : 6.x

Component : CA RELEASE AUTOMATION CORE

DB: MSSQL

Resolution

It looks like the default schema was not associated with the DB_USER and hence requested Database admin to set the same. The below query can be used to configure the same.

USE [DB_NAME]
GO
    ALTER USER [AD-DOMAIN\AD-USER] WITH DEFAULT_SCHEMA=[dbo]
GO