MSSQL Server Configuration for OpManager

If you choose to use MSSQL as the backend database for OpManager, we recommend that you create a seperate account for OpManager in your MSSQL database server. This ensures proper functionality. However, if you wish to proceed with your existing server account credentials, you may skip this configuration procedure and proceed directly with the installation. 

Supported Versions: SQL 2017 | SQL 2016 | SQL 2014 | SQL 2012 | SQL 2008

Note: It is highly recommended that you use MSSQL database for production. This also provides failover/high availability. 

Step 1: To ensure proper communication between the MSSQL database server and OpManager, a new account has to be created with the below mentioned steps.

  • Open SQL Management Studio and login using your Server Account (sa)/ Windows credentials.
  • Right click on Logins
  • Select New Login

Step 2: Select Authentication type. For Windows authentication, select and login using your Windows login credentials. For SQL Server Authentication, enter the password. Then proceed with Step 3.

Step 3: Click on Server Role. Select Server Roles "dbcreator", "public" and "sysadmin". See below for more information.

Step 4: Click on User Mapping. Map this login to "master" with database role ownership as "db_owner" and "public". Click OK.

MSSQL Configuration - Roles to be selected

The below table gives the details of the roles required for a user to be configured in OpManager for MSSQL DB Configuration:

DB Roles Server Roles UserMapping Roles
Master DB Public Public
Application DB - First Start db_creator, Public db_owner, Public
Application DB - Subsequent Start Public db_owner, or other roles with the least privilege

Steps to provide the least privilege required to start the product with SQL Server, as an alternative to the db_owner role:

  • Connect to SQL Server Management Studio.
  • In the Object Explorer window, expand Security -> Logins folder.
  • Select the respective user. Right click on it and choose Properties.
  • Go to User Mappings.
  • Select the database mapped/to be mapped to OpManager.
  • Under Database role membership, choose the below roles:
    db_datareader
    db_datawriter,
    db_ddladmin
    db_backupoperator
  • Click OK.
  • Right click on database mapped/to be mapped to OpManager, select New Query.
  • Execute the below queries:
    GRANT CONTROL ON SYMMETRIC KEY::[##MS_DatabaseMasterKey##] TO [user];>
    GRANT CONTROL ON SYMMETRIC KEY::[MySymmetricKey] TO [user];
    GRANT CONTROL ON CERTIFICATE::[Certificate] TO [user];

*replace user with the username that is used in OpManager. Eg: GRANT CONTROL ON SYMMETRIC KEY::[##MS_DatabaseMasterKey##] TO [opmuser];

Note : If you find, "Cannot find the symmetric key" or "Cannot find the Certificate error", you need to decrypt certificate.name and symmetrickey.name from customer-config.xml using decryptor tool and then give decrypted name in respective query.