Microsoft SQL Server Configuration for Applications Manager

If you choose to use Microsoft SQL as the backend database for Applications Manager, we recommend that you create a separate account for Applications Manager in your 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 Microsoft 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 2025/ 2022/ 2019/ 2017/ 2016 (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 Microsoft SQL server

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

1. To ensure proper communication between the Microsoft SQL database server and Applications Manager, create a new user 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.
   • For Linux installations, DB Name, Username and Password fields should not contain any special characters for Microsoft SQL backend database, except the following:
     • DB Name    - [ @ . , _ = ^ # - ]
     • User Name - [ @ . , _ = * ^ # - \ ]
     • Password    - [ @ . , _ = * ^ # - ]
   • Following are the supported characters for Windows installations:
     • 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.

   

Microsoft 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.

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.