ManageEngine has been recognized as a Customers' Choice in the 2023 Gartner Peer Insights™ Voice of the Customer for Application Performance Monitoring and Observability report. Learn more

✕

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 2022 / 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.

Note: The MSSQL backend has been tested for Applications Manager with cloud services such as AWS RDS, Azure SQL Server, Azure SQL Managed Instance, and Google Cloud Platform SQL.

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 accountusing 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 Typeand 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.
    • For Linux installations, DB Name, Username and Passwordfields should not contain any special characters for MS SQL backend database, except the following:
      • DB Name    - [ @ . , _ = ^ # - ]
      • User Name - [ @ . , _ = * ^ # - \ ]
      • Password    - [ @ . , _ = * ^ # - ]
    • Following are the supported characters for Windowsinstallations:
      • DB Name    - [ Alphanumeric characters and _ ]
      • User Name    - [ Only alphanumeric characters ]
      • Password    - [ All characters except '\' (Backslash) ]

  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

  • When creating a new database:
    • Ensure that the user account is part of the dbcreator server role.
  • In the case of utilizing an existing unused database:
    • It is sufficient for the user account to be assigned to the db_owner database role within the existing database.

Note: The user account used for installation must have access to the MASTER databases.

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.