MDB Database connection problem while SQL is up and running

Products

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

Issue/Introduction

 

During startup of the CA Service Desk service, even though SQL Server is known to be up and running, CA SDM cannot connect to the database server

Errors in STLDOG:

SDM-SERVER pdm_d_mgr               2440 SIGNIFICANT  ntservice.c            311 Command return non-zero exit code. Command: sql_check_db  -c. Exit Code: 99

CHECKDB.LOG shows the following error:

AHD50063:Unable to access pri table

Manually running the command "sql_check_db -c" from NX_ROOT\bin results in the same error as above.

Environment

CA Service Desk Manager 17.1 and higher

SQL Server DBMS

Cause

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

Additionally, the mdbadmin password may have been reset at the SQL Server level without SDM pdm_configure executed to update the change.

Resolution

If the determination is made that the mdbadmin credentials have been changed, run pdm_configure on each of the SDM Servers within the affected environment to update the mdbadmin password. 

Additionally, one may review the following to confirm the mdbadmin user configuration.

Access the SQL Server Management Studio, logging into the given database server as the 'sa' user or as a user with sa level privileges.

Note:  Use a separate login with sa level privileges over the DB Server.  Do not use the mdbadmin user for this purpose.

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

SDM does not have awareness of any changes that are made on the DB server and so must be updated via pdm_configure if the mdbadmin password is changed.

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