# MSSQL Server Configuration for OpManager

If you choose to use MSSQL as the backend database for OpManager, we highly recommend creating a separate account for OpManager in your MSSQL database server to ensure 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.

For supported versions of MSSQL, refer [here](https://www.manageengine.com/network-monitoring/help/hardware-and-software-requirements.html).

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

- [MSSQL Configuration](https://www.manageengine.com/network-monitoring/help/mssql-configuration.html#mssql-configuration)
- [MSSQL Configuration - Roles to be selected](https://www.manageengine.com/network-monitoring/help/mssql-configuration.html#mssql-configuration-roles)
- [Steps to configure alternate roles to the db_owner role:](https://www.manageengine.com/network-monitoring/help/mssql-configuration.html#alt-roles-conf)

## MSSQL Configuration

Follow the below steps to configure MSSQL with OpManager

### 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

![MSSQL Server Configuration for OpManager: SQL management studio](https://www.manageengine.com/network-monitoring/help/images/installing-opmanager-enterprise-1.png)

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

![MSSQL Server Configuration for OpManager: SQL authentication type and credentials](https://www.manageengine.com/network-monitoring/help/images/installing-opmanager-enterprise-2.png)

![MSSQL Server Configuration for OpManager: Windows authentication type and credentials](https://www.manageengine.com/network-monitoring/help/images/MSSQL-server-config-01-new.png)

### Step 3:

Click on Server Role. Select Server Roles **"dbcreator", "public" and "sysadmin".**

![MSSQL Server Configuration for OpManager: Server role](https://www.manageengine.com/network-monitoring/help/images/installing-opmanager-enterprise-3.png)

### Step 4:

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

![MSSQL Server Configuration for OpManager: User mapping to map login](https://www.manageengine.com/network-monitoring/help/images/installing-opmanager-enterprise-4.png)

## 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 |
| OpManager DB - Installation and First Start | db_creator, Public | db_owner, Public |
| OpManager DB - Subsequent Starts | Public | db_owner, or other roles with the least privilege granted |

**Note:**

- The below steps are not applicable for installation and fresh start, as the db_owner role is mandatory for the same.
- System Performance analysis of MSSQL database [Support>SystemPerformance>MSSQL CPU Memory Utilization, MSSQL Query store, MSSQL database Info] **requires the sysadmin role**. If the sysadmin role is not mentioned, then System Performance analysis will not work.
- Contact [opmanager-support@manageengine.com](mailto:opmanager-support@manageengine.com) for any further queries.

## Steps to configure alternate roles 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 encounter the **"Cannot find the symmetric key"** error or the **"Cannot find the Certificate"** error, please contact [opmanager-support@manageengine.com](mailto:opmanager-support@manageengine.com).