This article only applies to Squared Up Version 2.0. If you're looking for help with Squared Up Version 3.0, please visit our new knowledge base

Symptoms

Sorry - an unexpected error occurred 

Something has gone wrong on the web server and all we can show is the following error message:


'The SELECT permission was denied on the object 'vRule', database 'OperationsManagerDW', schema 'dbo'.'


or

Running a query through the SQL plugin returns no results and you see a similar error in the rolling.log:

System.ArgumentException: SQL Error: The SELECT permission was denied on the object 'AlertView', database 'OperationsManager', schema 'dbo'. (229)


Cause

This error indicates that a SQL login exists, but that the login does not have the correct roles on the database.

  • If you are unable to view performance data and the error mentions OperationsManagerDW then it means that the correct login exists in SQL Server logins but that it does not have the 'OpsMgrReader' role on the data warehouse database.
  • If it is the SQL plugin you are having problems with it means that the login does not have the 'apm_datareader', 'db_datareader' roles on the database you are trying to query, for example the OperationsManager database.

Procedure

1. First identify the user account that needs to be edited. Squared Up uses the Application Pool Identity account to retrieve data, NOT the account with which you log on to Squared Up. By default the application pool identity is set to NetworkService, but you may have have changed this. To check the application pool identity see Checking and modifying the application pool identity and make a note of the application pool identity account for use in the next steps:

  • 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 Squared Up is installed on the same server as the data warehouse (or 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 Squared Up is NOT installed on the same server as the data warehouse (or database you are accessing) then add the relevant role for the Squared Up server's AD computer account (for example, mydomain\mywebserver$)


2. Open Microsoft SQL Server Management Studio and connect to the SQL server hosting the relevant database, for example the SCOM data warehouse. You will need to connect as a user who has the SQL sysadmin server role.

3. Browse to Security > Logins

4. Right click on the relevant login and select Properties.


5.  Click on User Mapping, and then click on the database you need access to, for example OperationsManagerDW.

  • To give Squared Up access to the data warehouse tick 'OperationsManagerDW'.
  • To give Squared Up access to another database tick the relevant database, for example 'OperationsManager'.



6. In the Database role membership section at the bottom of the page, tick the relevant role(s). (In Management Studio 2012 this can instead be found by clicking Membership on the left-hand side of the dialog box):

  • To give Squared Up access to the data warehouse to show Performance data tick the 'OpsMgrReader' role.
  • To give Squared Up access to another database, to allow a query to run in the SQL plugin, tick the 'apm_datareader', 'db_datareader' roles. 


7. Click OK


Test that you can now access the relevant data in Squared Up. 


For further information see:

Configure the data warehouse connection

Troubleshooting the Data Warehouse connection

How to configure a SQL Query Section using the Dashboard Designer

Configuring access to a database for use with the SQL plugin