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 [::]: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
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.
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:
- Connect to SQL Server Management Studio.
- In the Object Explorer window, go to Security -> Logins folder.
- Select the respective user.
- Right-click on it and choose Properties.
- Go to User Mappings and select the database.
- Under Database role membership, choose the role 'db_owner'.
- 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.
- 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:
- Open the file database_params.conf under<Applications Manager Home>/working/conf/ directory. Database name will present in the connection string as shown below:
jdbc:jtds:sqlserver://localhost:1433/<DATABASE_NAME>;appName=ApplicationsManager;useNTLMv2=true
[OR]
jdbc:sqlserver://localhost:1433;databaseName=<DATABASE_NAME>;applicationName=ApplicationsManager;useNTLMv2=true
- Connect to SQL Server Management Studio.
- In the Object Explorer window, go to Databases folder and open it.
- 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.
- 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
- Under State properties, Database Read-Only option will be present. Set the value to False and click Ok.
- Restart Applications Manager.
Note: Make sure to take a backup of existing settings before any modification on the SQL Server.