How to configure access to a database for use with the SQL tile

Overview

You will need to configure access if you wish to use the SQL tile to query a database.

For more information about the SQL tile configuration such as checking that the connection string is correct see How to use the SQL tile.

Configuring permissions in SQL Server Management Studio

If the SquaredUp server is domain joined, then the SquaredUp DS application pool account needs to be given the db_datareader role in SQL Server Management Studio for the database you wish to query (How to check and modify the application pool identity).

The SquaredUp DS application pool account needs to be given the db_datareader role on the database, as described below:

1. First identify the user account that needs to be given permission.

   SquaredUp DS uses the application pool identity account to retrieve data for the SQL tile, NOT the account with which you log on to SquaredUp. By default the application pool identity is set to NetworkService, but you may have changed this. For more information see How to check and modify the application pool identity

   You will need to RDP to the machine where SquaredUp DS is installed, so you will need to open port 3389 as described in the Microsoft article: Cannot connect remotely to a VM because RDP port is not enabled in NSG, and then log in using the credentials specified when creating the VM.

2. In IIS, click on Application Pools.
3. Right-click on the SquaredUp app pool and select Advanced Settings.

4. Under Process Model, you will see Identity.

   • If the application pool identity is running as a custom account then add the relevant roles for this custom account.
   • If the application pool is set to NETWORK SERVICE and SquaredUp DS is installed on the same server as the database you are accessing then add the relevant role for the NT AUTHORITY\NETWORK SERVICE account.
   • If the application pool is set to NETWORK SERVICE and SquaredUp DS is NOT installed on the same server as the database you are accessing then add the relevant role for the SquaredUp server's AD computer account (for example, domain\mywebserver$)
5. Open SQL Server Management Studio.
6. Expand Security and then Logins. Look for a login with the same name as the application pool identity, as noted above, if one doesn't exist you will need to create one.
7. Right-click on the relevant account (as described above) and select Properties.
8. Click on User Mapping.
9. Tick the database you wish to give access to (under Users mapped to this login).
10. In the bottom panel tick db_datareader.
11. Click OK.