Skip to main content

Manually creating the Data Warehouse permissions

Documentation Team
  • Updated

Manually creating the Data Warehouse permissions

Dashboard Server displays performance metrics as graphs, sparklines and bar charts. To show these performance metrics, Dashboard Server connects to the SCOM Data Warehouse using its IIS application pool identity, not the account with which you log on to Dashboard Server. By default, the application pool identity is set to NetworkService, however, this is configurable, see How to check and modify the application pool identity.

If graphs are not shown in Dashboard Server, then you should check the Data Warehouse connection as described in How to check and reconfigure the Data Warehouse connection

Auto-configure the Data Warehouse permissions

The setup wizard auto-configures the Data Warehouse connection for you. Firstly, it picks up the Data Warehouse location from SCOM, and secondly it gives the Dashboard Server application pool account the correct permissions by giving the account a SQL server login with the OpsMgrReader role for the Data Warehouse database.

We recommend you try the auto-configuration first as it is so quick and easy.

You can easily access the Data Warehouse stage of the setup wizard to auto-configure the Data Warehouse permissions by browsing to http://localhost/SquaredUpv5/setup?stage=datawarehouse or http://localhost/SquaredUpv5/setup?stage=datawarehouse as described in How to check and reconfigure the Data Warehouse connection - 2. Auto-configure the Data Warehouse permissions.

If you are not able to automatically configure the permissions then you can manually configure them as described:

How to configure the Data Warehouse permissions manually

  1. Open Microsoft SQL Server Management Studio and connect to the SQL server hosting the SCOM Data Warehouse. You will need to connect as a SCOM Admin user (Types of users in Dashboard Server) who has the SQL sysadmin server role (How to make a user a SQL sysadmin).

  2. Check the application pool identity and make a note of the application pool identity account.

    See How to check and modify the application pool identity.

  3. Identify the account you need to create by reviewing these options:

    A. If the application pool identity is running as a custom account (i.e. not NetworkService) then create a login with the same username as your application pool identity account.

    B. If the application pool is set to NetworkService and Dashboard Server is installed on the same server as the Data Warehouse then add the relevant role for the NT AUTHORITY\NETWORK SERVICE account.

    C. If the application pool is set to NetworkService and Dashboard Server is not installed on the same server as the Data Warehouse then add the relevant role for the server's active directory computer account. This will be the name of the server with a $ on the end, for example, domain\SquaredUpServer$

  4. In SQL Server Management Studio browse to Security > Logins
  5. Right-click and click New Login...

  6. In the text box asking for Login name enter the username you identified in step 3, i.e. either NETWORK SERVICE, or SquaredUpServer$, or your custom username.

    Do not use the Search option in SQL Server Management Studio: Usernames entered in this format (DOMAIN\machine$) will not succeed if you perform Check name. Manually type the name directly into the Name field, making sure to respect any capitalization in the domain name.

  7. Click OK.
  8. Right-click on the newly created login (Security > Logins) and click Properties.
  9. Click on User Mapping on the left-hand side under Select a page.
  10. Tick the Data Warehouse database under Users mapped to this login (for example, OperationsManagerDW).

  11. Tick OpsMgrReader under Database role membership at the bottom of the screen, as shown below:

  12. Click OK.

This has now configured your Data Warehouse permissions.

How to check the Data Warehouse permissions

  1. In SQL Server Management Studio browse to Databases > OperationsManagerDW > Security > Users

    Your SCOM Data Warehouse may have been given a name other than OperationsManagerDW.

  2. Identify the account used for your login (see step 3 above) and ensure that a corresponding user account has been created. The user account must be the same as the login account created. For instance, if your login account is created with learningzone\lz-squp$ then your user account must also be learningzone\lz-squp$.

    If a corresponding user account has not been created please repeat the steps 1-12 laid out in 'How to configure the Data Warehouse permissions manually'.

  3. Right-click on the account, for example, domain\SquaredUpServer$, and click Properties:

  4. In the Database role membership section, check that OpsMgrReader, apm_datareader and db_datareader are ticked.
  5. Click OK.

How to remove Data Warehouse Permissions

After uninstalling Dashboard Server uncheck the OpsMgrReader database role.

Alternatively, if the login is only used by Dashboard Server you can delete this login from the SQL server.

Please contact SquaredUp Support if you experience any problems and reply to the automatic response with the output of the Dashboard Server Diagnostics (see Collecting diagnostic information) and, if possible, a screenshot of the problem.