Database User Access Permissions
Create a new user with SQL server authentication and assign the following permissions for all the databases including the master database. 1. db_datareader. 2. Permission to alter trace, view server state, view database state and alter any event session.
Syntax:
use master;
grant alter trace to <new_user>;
grant view server state to <new_user>;
grant view database state to <new_user>;
grant alter any event session to <new_user>;
grant execute on xp_readerrorlog to <new_user>;Replace <new_user> with the username you have just created.
Example: If the new user name is appms. use that in place of <new_user>
use master;
grant alter trace to appms;
grant view server state to appms;
grant view database state to appms;
grant alter any event session to appms;
grant execute on xp_readerrorlog to appms;The agent should be installed on the same machine where SQL Server is running.
Enable TCP/IP in SQL Server Configuration Manager.
- Open SQL Server Configuration Manager.
- Expand SQL Server Network Configuration → Click Protocols for <your instance>.
- Ensure TCP/IP is enabled. If it's disabled, right-click and select Enable.
- After enabling TCP/IP, restart the SQL Server service.
After making all the changes connect that user in the SQL Server management studio and check.
[Screenshot 1 is not available on this page.]
Note: Encryption should be Optional.
Step 1: Install Applicare SingleAgent on the machine where the MS SQL database is running. Follow the instructions for installing Applicare SingleAgent
Step 2: After starting the agent, It will be automatically registered with the Applicare controller. Log in to the Applicare console and navigate to the Overview menu.
Step 3: Select the newly added agent and click the Edit button.
[Screenshot 2 is not available on this page.]
Step 4: Check the "Add Database Configuration" box and choose "SQL Server" from the Database dropdown menu. Select your SQL server version from the Version dropdown. If your specific version is not listed, select the most recent version.
Step 5: Enter the JDBC URL, username and Password then click the update button.
Testing the connection is optional. The 'Test connection' feature verifies the link between the controller machine to agent machine.
[Screenshot 3 is not available on this page.]
By default, it will capture the SQL statements executed across all databases.
To limit and capture SQL statements executed in specific databases, enter the database names in the "Custom Databases" text box.
[Screenshot 4 is not available on this page.]
Sample JDBC URL
Syntax: jdbc:sqlserver://[serverName]:[portNumber]\[instanceName];databaseName=[databaseName];
samples:
jdbc:sqlserver://168.18.25.84:1433;databaseName=fuzzy jdbc:sqlserver://localhost\SQLEXPRESS;databaseName=nopcom;
If you don't see the database metrics on the Applicare dashboard after completing the above steps then validate the trace configuration in sql server.
Refer here to view Other Database monitoring configuration.