Database connection problem while SQL DBMS is up and running

Products

• CA Service Management - Service Desk Manager
• CA Service Desk Manager

Issue/Introduction

During startup of SDM Services, finding that while SQL Server is known to be up and running, the SDM cannot connect to the database server

stdlog will report:

11/14 22:24:20.09 SDM-SERVER pdm_d_mgr               2440 SIGNIFICANT  ntservice.c            311 Command return non-zero exit code. Command: sql_check_db  -c. Exit Code: 99

Also seeing in checkdb log and manual run of the "sql_check_db -c" command 

AHD50063:Unable to access pri table

Environment

Release : 17.1 and higher

Component :  CA Service Desk Manager

Cause

The mdbadmin user defined in SQL Server does not have its permissions set correctly.

Resolution

Access the SQL Server Management Studio, logging into the given database as the sa user or as a suitable DBA user.

Access the given SQL Server and under Security -> Logins, choose the mdbadmin user, right click and select "Properties"

Under the "General" section, the mdbadmin should have the default database set to mdb

Under User Mapping, select the mdb database, confirm that the user selected is "mdbadmin", that the Default Schema is "dbo" and that the Database role membership listing has the correct role memberships

The database role membership entries that should be checked ON are:

ams_group
db_ddladmin
db_owner
db_securityadmin
public
regadmin
service_desk_admin_group
service_desk_ro_group
workflow_admin_group

Additional Information

Older Service Desk implementations may be using the "ServiceDesk" user instead of the "mdbadmin" user.  Please check for the "ServiceDesk" user if the given user is applicable.

The SDM install's NX.env will list who the DB user and the MDB owner userid is defined as, with entries such as this:

@NX_DB_USERID=mdbadmin
@NX_MDB_OWNER=mdbadmin