Issues while installing or starting Applications Manager with Microsoft SQL Server backend database

Issues while installing or starting Applications Manager with Microsoft SQL Server backend database

Problem: Unable to install or start Applications Manager using Microsoft SQL Server database as backend.
Solution: Follow the below troubleshooting steps to resolve the issue:
1. On the Microsoft SQL server host, go to Start -> All Programs -> Microsoft SQL Server -> Configuration Tools -> SQL Server Configuration Manager -> SQL Server Services.  
2. Make sure that the below services are in 'Running' state:
     a. SQL Server
     b. MSSQL$<InstanceName> (Required when using a SQL server instance name)
     c. SQL Server Browser (Required when using a SQL server instance name)
3. In SQL Server Configuration Manager -> SQL Server Network configuration -> Protocols for MSSQLServer or SQLEXPRESS (or appropriate name of your SQL Server instance, make sure the following is enabled:
     a. Make sure that TCP/IP is enabled.
     b. Verify that the TCP/IP port of the corresponding IP Address (s) is enabled & active and it is correct.
4. Configure the firewall (if any) to allow network traffic that is related to SQL Server and to the SQL Server Browser service.
5. Open command prompt window and check if the appropriate port is listening in the Microsoft SQL server host using below command:

      Default port for SQL Server: 1433 (TCP)  
      Default port for SQL Server Browser: 1434 (UDP) (Required when using a SQL server instance name)
   netstat -an  | find "143"   
      TCP    0.0.0.0:1433           0.0.0.0:0              LISTENING  
      TCP    [::]:1433                    [::]:0                   LISTENING  
      UDP    0.0.0.0:1434              *:*            
      UDP    [::]:1434                     *:*    
Note: if you are not using default SQL server ports, then change the port number in the command as required instead of "1433".
6. Open command prompt in Applications Manager host and verify if you are able to connect to the SQL Server port using the following command:
telnet <SQL Server Hostname> 1433
7. In case you have entered an incorrect username/password/domain/instance name during installation, you can modify them using step 3 in the following link: https://apm.manageengine.com/migrate-mssql.html
8. You can choose default database collation as "SQL_Latin1_General_CP1_CI_AS" in the MS SQL Server. If you want to use a different collation, choose any case insensitive database collation.
9. Send the exact version of the MS SQL server and then open the command prompt window in Applications Manager host. Execute the MsSQLDebug.bat file available under <Applications Manager Home>\bin\troubleshooting\ directory and copy the result to appmanager-support@manageengine.com
10. If you are using Windows Authentication for connecting to MS SQL database, then you have to set the Authentication Mode of the SQL server from "Windows Authentication Mode (Windows Authentication)" to "Mixed Mode (Windows Authentication and SQL Server Authentication)".
11. If you are using NTLMv2 as security for the MS SQL database server, then open the database_params.conf file under <Applications Manager Home>\working\conf\MSSQL\ using any text editor and add the below parameter as well in the connection string line:
    ;useNTLMv2=true
    Example: 
    url jdbc:jtds:sqlserver://localhost:1433/AMDB;appName=ApplicationsManager;useNTLMv2=true AppModules TopoDB-MapDB-EventDB-AlertDB-PollDB-PolicyDB-USERSTORAGEDB-ApplnDB
12. Please check if you have administrator privileges in the server where you are installing Applications Manager. Also on the MS SQL Server, the database user should have administrator privileges.
13.  Please check if the MS SQL user account has 'db_owner' role and permission for CREATE DATABASE. Follow the steps given below to assign roles and permissions in SQL Server Management Studio:
  1. Connect to SQL Server Management Studio.
  2. In the Object Explorer window, go to Security -> Logins folder.
  3. Select the respective user.
  4. Right-click on it and choose Properties.
  5. Go to User Mappings and select the database.
  6. Under Database role membership, choose the role 'db_owner'.
  7. Go to Securables and choose 'CREATE ANY DATABASE' from the Permissions pane. Click Ok.
    Note: If you are using an existing DB, map it to the respective user.
    To map the user with the DB, follow the steps below:
         1. Connect to SQL Server Management Studio.
         2. In the Object Explorer window, go to Security -> Logins folder.
         3. Select the respective user.
         4. Right-click on it and choose Properties.
         5. Go to User Mappings and select the database.
         6. Enable the checkbox to map the user with the DB.


  8. Restart Applications Manager.
14. Please check if the Read-Only status is set to False for Applications Manager database. Follow the steps given below to check/modify the status:
  1. Open the file database_params.conf under<Applications Manager Home>/working/conf/ directory. Database name will present in the connection string as shown below:
  2.  jdbc:jtds:sqlserver://localhost:1433/<DATABASE_NAME>;appName=ApplicationsManager;useNTLMv2=true
             
    [OR]
    jdbc:sqlserver://localhost:1433;databaseName=<DATABASE_NAME>;applicationName=ApplicationsManager;useNTLMv2=true

  3. Connect to SQL Server Management Studio.
  4. In the Object Explorer window, go to Databases folder and open it.
  5. Right-click the database name found in database_params.conf file and select Properties. The database properties window will now be shown for that database.
  6. On the left side of the window, choose Options. Under other options, scroll down to State as shown in the image below
    Enabling (or) Disabling read only mode for a database
  7. Under State properties, Database Read-Only option will be present. Set the value to False and click Ok.
  8. Restart Applications Manager.
If the issue still exists, reach out to our support team at appmanager-support@manageengine.com along with the latest Support Information File (SIF) from Applications Manager with print all logs enabled for analysis.
Note: Make sure to take a backup of existing settings before any modification on the SQL Server.

                  New to ADSelfService Plus?

                    • Related Articles

                    • Installing Applications Manager in Microsoft Azure

                      Applications manager can be installed in Azure Virtual Machines in both Windows and Linux platforms.  Applications Manager comes with bundled PostgreSQL. Installing Applications Manager in VM for Windows Go to Virtual Machines and create a "Windows" ...
                    • Maintenance of Applications Manager backend database

                      Note: Please take complete database backup without fail prior to these steps. I. Applications Manager with PostgreSQL Database. Shutdown all Applications Manager related process as follows: For windows: Open services.msc --> stop ManageEngine ...
                    • Issues while installing or starting Applications Manager with PostgreSQL backend database

                      Problem 1 : When starting Applications Manger in Linux with PostgreSQL backend, the following error message is displayed : Check the permission of AppManager/working/pgsql/data/amdb directory. Permissions should be u=rwx (0700) or u=rwx,g=rx (0750). ...
                    • Issues while upgrading Applications Manager with PostgreSQL backend database

                      Problem 1: After installing Applications Manager with PostgreSQL as backend (For versions 14850 and above), the following error message is shown due to error the initPGSQLDB.sh file because of missing GLIBC_2.9 library file:  ./initdb: ...
                    • Installing Applications Manager in AWS

                      Applications Manager can be installed in EC2 instances in both Windows and Linux platforms.  Applications Manager comes with bundled PostgreSQL. Installing Applications Manager in EC2 for Windows Go to EC2 console and launch an "Windows" instance ...