MS SQL Server Configuration for Applications Manager

If you choose to use MS SQL as the backend database for Applications Manager, we recommend that you create a separate account for Applications Manager in your MS SQL 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. 

Note: For high availability/failover, we recommend to use MS SQL as database backend for Applications Manager.

Supported Versions

The supported Microsoft SQL database versions that you may use are: 64-bit version of Microsoft SQL Servers 2019/ 2017/ 2016/ 2014 (SQL Standard / Enterprise Edition). It is also recommended that the SQL server and Applications Manager are connected to the same LAN.

SQL Server Collation: Any case-insensitive collation. For Chinese and Japanese installations, use the collation settings Chinese_PRC_CI_AS and Japanese_CI_AS respectively.

Steps to configure MS SQL server

Follow the steps mentioned below to configure MS SQL server for Applications Manager:

  1. To ensure proper communication between the MS SQL database server and Applications Manager, create a new account using the below steps:
    • Open SQL Management Studio and login using your Server Account (sa)/ Windows credentials.
    • Right click on Logins.
    • Select New Login.

  2. Select the Authentication Type and proceed with Step 3.
    • For Windows authentication, select and login using your Windows login credentials.
    • For SQL Server authentication, specify the password.
    Note:
    • Access to the master database is required for the backend database.
    • DB Name, Username and Password fields should not contain any special characters for MS SQL backend database, except the following:
      • DB Name    - [ @ . , _ = ^ # - ]
      • User Name - [ @ . , _ = * ^ # - \ ]
      • Password    - [ @ . , _ = * ^ # - ]

  3. Click on Server Role. Select the server roles "dbcreator", "public" and "sysadmin".

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

    Note: If you are unable to provide the roles mentioned in Step 4, refer here.

MS SQL roles to be configured

The below table gives the details of the roles required for a user to be configured in Applications Manager for MS SQL database configuration:

DB Roles Server Roles UserMapping Roles
Master DB Public Public
Applications Manager DB - Installation and First Start db_creator, Public db_owner, Public
Applications Manager DB - Subsequent Starts Public db_owner, or other roles with the least privilege granted

Steps to configure alternate roles to the db_owner role

Note: The below steps are not applicable for installation and fresh start, as the db_owner role is mandatory for the same.

  1. Connect to SQL Server Management Studio.
  2. In the Object Explorer window, expand Security → Logins folder.
  3. Select the respective user. Right click on it and choose Properties.
  4. Go to User Mappings.
  5. Select the database mapped/to be mapped to Applications Manager.
  6. Under Database role membership, choose the below roles:
    db_datareader
    db_datawriter,
    db_ddladmin
    db_backupoperator
  7. Click Ok.
  8. Right click on database mapped/to be mapped to Applications Manager, select New Query.
  9. 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 Applications Manager. Example: GRANT CONTROL ON SYMMETRIC KEY::[##MS_DatabaseMasterKey##] TO [apmUser];

Note: If you encounter the "Cannot find the symmetric key" error or the "Cannot find the Certificate" error, please contact us at appmanager-support@manageengine.com email ID.